Cost of finding the right cached execution plan for a query

Question

Cost of finding the right cached execution plan for a query

Yusuf Bhiwandiwala

Ten Centuries

Points: 1298
More actions
March 13, 2014 at 4:44 am

#283325

Hi people,
I have read here that:
SQL Server has an efficient algorithm to find any existing execution plans for any specific SQL statement. In most systems, the minimal resources that are used by this scan are less than the resources that are saved by being able to reuse existing plans instead of compiling every SQL statement.
Just out of curiosity, can this minimal cost and resources incurred by SQL Server in finding the right cached plan for a submitted batch/query be determined in terms of memory/CPU/time?
Best,
Yusuf

Viewing 6 posts - 1 through 5 (of 5 total)

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

free_mascot One Orange Chip Points: 27169 More actions · Answer 1

Not always but yes most of the time.

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

Yusuf Bhiwandiwala Ten Centuries Points: 1298 More actions · Answer 2

free_mascot (3/13/2014)
Not always but yes most of the time.

Hi, Is your comment on the quoted article text or are you saying we can determine the cost of matching plan with query most of the time? If latter, how please?

Grant Fritchey SSC Guru Points: 398872 More actions · Answer 3

The basic costs break down on the parsing of the query and/or getting the object id of the procedure and then retrieving those from cache by a search on the object id or the query hash values.

Measuring exactly what this costs as a part of the query execution is probably a little difficult. You can use extended events to observe events like plan_cache_attempt, plan_cache_hit and sp_cache_miss to see the management of the plan cache, but these events don't record execution time or CPU. You can also add in sp_cache_hit and sp_cache_insert and sp_cache_remove to further observe how the cache is behaving.

The best I can offer for seeing precisely what you're paying for in terms of cost (again, which is really, really small), is to look at the parse and compile time for your queries using STATISTICS TIME in SSMS. It's not completely accurate and it won't go below milliseconds in measuring, so you may see lots and lots of Zero values, but it'll give you an idea.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Yusuf Bhiwandiwala Ten Centuries Points: 1298 More actions · Answer 4

Grant Fritchey (3/13/2014)
The best I can offer for seeing precisely what you're paying for in terms of cost (again, which is really, really small), is to look at the parse and compile time for your queries using STATISTICS TIME in SSMS. It's not completely accurate and it won't go below milliseconds in measuring, so you may see lots and lots of Zero values, but it'll give you an idea.

Thanks Grant. Yes, the parse and compile time shown with STATISTICS TIME is always 0 except for the first compilation.

Best,

Yusuf

Grant Fritchey SSC Guru Points: 398872 More actions · Answer 5

Well, that's an indication anyway. Parse time includes time to check the cache, so you get some idea of how trivial that process can be. It won't always be though.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning