parse and compile time after plan cache is cleared

Question

Post reply

parse and compile time after plan cache is cleared

robh0502

SSC Enthusiast

Points: 199

November 17, 2023 at 1:17 am

#4329204

hi All,

Just trying to understand parse and compile time when statistics time is set to on. Specifically, parse and compile time shows 0 after clearing plan cache and rerunning the same query, but why? I've got the following script.

set statistics time on

create table testtab
(col1 int, col2 int)

--insert about 1M rows
insert into testtab values (1, 1)
go 100000

insert into testtab values (2, 2)
go 300000

insert into testtab values (3, 3)
go 400000

insert into testtab values (4, 4)
go 200000

--run a query
select col2 from testtab where col1 = 2 and 1 = (select 1)

--time stats shows as follows
SQL Server parse and compile time: 
   CPU time = 218 ms, elapsed time = 305 ms.

(300000 rows affected)

 SQL Server Execution Times:
   CPU time = 47 ms,  elapsed time = 1141 ms.

Completion time: 2023-11-16T18:08:18.9367540-07:00

--run the query again
select col2 from testtab where col1 = 2 and 1 = (select 1)

--time stats shows as follows
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 1 ms.

(300000 rows affected)

 SQL Server Execution Times:
   CPU time = 94 ms,  elapsed time = 1178 ms.

Completion time: 2023-11-16T18:13:25.5132443-07:00

--clear plan cache and rerun query
dbcc freeproccache

select col2 from testtab where col1 = 2 and 1 = (select 1)

--parse and compile time still show 0, but why?
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

(300000 rows affected)

 SQL Server Execution Times:
   CPU time = 31 ms,  elapsed time = 1141 ms.

Completion time: 2023-11-16T18:14:12.9819628-07:00

To reiterate, trying to understand why parse and compile time still shows 0 after clearing the plan cache.

Another question, does the same query get parsed each time it's executed?

Thanks!

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80325 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.