September 22, 2016 at 2:18 am
TL;DR: when does an execution plan get a)cached b) cleared from the cache?
As part of a continual improvement process I'm looking into the run times of various reporting stored procs with a view to establishing which of the frequently run procs take the longest. I've found various scripts to get execution times by looking at sys.dm_exec_procedure_stats. When I linked these scripts to the reporting procs though, I found some gaps. By this I mean that there was a proc associated with a report but my execution time query didn't return a proc name.
I then did a bit of searching and I found a post by some guy called Jeff Moden who pointed out that these scripts worked by querying the plan cache. Therefore if there was no plan in the cache, there would be nothing to return. This is where I did get stumped. There doesn't appear to be any pattern to what procs are returned by the query. One of the procs is with OPTION(RECOMPILE), that makes sense to me. If it's recompiled each time, there will be no point in caching a plan (I'll stand corrected on this, but it is a logical explanation to me) . Others are called from SSRS as a text dataset using 'EXEC Proc @parameter'. I can see why this wouldn't get cached. There are bits of ad hoc SQL to establish dates and users etc.; again I can see why these aren't cached. Others are less easy to explain. They appear to be pretty simple queries with a handful of joins and no variables. Other procs were run before them and they are returned by the execution time query so it doesn't look like they were 'aged out'.
What I know about the plan cache can be written on the back of a stamp so could somebody point me at a decent link to find out a bit more?
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
September 22, 2016 at 2:58 am
Search for 'plan eviction' - there's lots of good stuff out there, including TechNet articles
September 22, 2016 at 3:25 am
BWFC (9/22/2016)
TL;DR: when does an execution plan get a)cached
When it's run and there is no plan in cache (assuming no recompile hints or options)
b) cleared from the cache?
That's much harder.
Can be aged out due to memory pressure. Also there are things that can invalidate or remove cached plans. Kinda discussed at http://sqlinthewild.co.za/index.php/2011/08/16/compiles-and-recompiles/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 22, 2016 at 6:41 am
You may want to also look to sys.dm_exec_query_stats. The DMV you're referencing, sys.dm_exec_procedure_stats, only has statistics for stored procedures and parameterized queries. Any ad hoc or dynamic queries are in the other DMV. Before you get there, yes, there is cross-over. The query_stats has stats for all queries, in a procedure or not.
"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
September 22, 2016 at 8:02 am
Thanks for the responses folks.
It turns out that the cause was more simple than I feared. Allowing for aging out of anything older than a few days (The server has less memory than a drunken goldfish) and the one proc with OPTION(RECOMPILE), the reason I was not getting execution times returned was that the reporting server was pointing at the wrong data source. I was expecting to find it pointing at the live server but in many cases it was pointing at the failover. Obviously when I was checking execution times for procs on the live server they weren't there because they hadn't been run on that server. Now I've pointed them at the right place, I'm getting proc execution times that correspond with the report execution times.
I also spotted why the procs called from SSRS using EXEC proc @parameter didn't return anything. That was because I hadn't trimmed the parameters off the string I was comparing with the execution time name...Oops.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
September 22, 2016 at 10:02 am
Also, be aware that some plans are never cached at all, for example, a trivial plan to satisfy "select * from table_name".
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 22, 2016 at 11:04 am
ScottPletcher (9/22/2016)
Also, be aware that some plans are never cached at all, for example, a trivial plan to satisfy "select * from table_name".
Actually, trivial plans are stored in cache. You can test it with AdventureWorks. Run this query:
SELECT * FROM HumanResources.EmployeePayHistory;
Then run this query:
SELECT dest.text,deqp.query_plan
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) AS deqp
WHERE dest.text LIKE 'SELECT * FROM HumanResources.EmployeePayHistory;%';
That's a trivial plan and you can pull it right out of the cache. Trivial plans not being in cache is older behavior. I think it changed in 2005, but I could be wrong on the version, it might be 2008.
"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
September 22, 2016 at 12:51 pm
ScottPletcher (9/22/2016)
Also, be aware that some plans are never cached at all, for example, a trivial plan to satisfy "select * from table_name".
Trivial plans have been cached since SQL 2005. SQL 2000 and before didn't cache them
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply