When you send your beautifully hand-crafted organic T-SQL statement to SQL Server, the database engine takes a moment to appreciate the poetry of your work. Before it turns your request into results, it has to build an execution plan. SQL Server considers:
- Which tables it needs to join together
- What subqueries it needs to execute
- Whether it can reverse-engineer your intent to achieve the same results faster
- What indexes exist for the tables/views you’re trying to join
- If it can do partition elimination to make things go faster
- And much, much more
Much like you, SQL Server doesn’t like doing much work. SQL Server put a lot of work into building your execution plan (which may also be a work of art itself), and wants to avoid reinventing that wheel again, so it caches the execution plan in memory. If the same query comes in again, SQL Server can just check the cache, find your beautiful snowflake, and reuse the same plan.
This is the plan cache, and it stores more than just plans. We can get metrics about how many times the query was called and how much resources it used (min/max/avg/total).
It’s not perfect – there’s a lot of things that can cause the plan cache to flush completely or in part:
- Service restarts
- Database restores
- Statistics changing on an object
- Server comes under memory pressure
- People running DBCC FREEPROCCACHE
I still love the plan cache anyway. It’s not perfect (just like many of the things I love) but it’s a fast, easy-to-access way to discover some of the queries that have been using a lot of resources lately.
Making the Plan Cache Easier to Analyze
I’ve built up a set of queries to slice and dice my way through the plan cache, but I wanted to make it easier for people tackling their first performance tuning project.
At the PASS Summit this month, I unveiled the latest version of sp_Blitz®, which makes all this easier. I’ve added a few new parameters:
@CheckProcedureCache – if 1, we grab the top 20-50 resource-intensive plans from the cache and analyze them for common design issues. We’re looking for missing indexes, implicit conversions, user-defined functions, and more. This fast scan isn’t incredibly detailed – we’re just looking for queries that might surprise you and require some performance tuning.
@OutputProcedureCache – if 1, we output a second result set that includes the queries, plans, and metrics we analyzed. You can do your own analysis on these queries too looking for more problems.
@CheckProcedureCacheFilter – can be CPU, Reads, Duration, ExecCount, or null. If you specify one, we’ll focus the analysis on those types of resource-intensive queries (like the top 20 by CPU use.) If you don’t, we analyze the top 20 for all four (CPU, logical reads, total runtime, and execution count). Typically we find that it’s not 80 different queries – it’s usually 25-40 queries that dominate all of the metrics.
To learn more about how the plan cache works and how I analyze it, here’s a 30-minute video:
Or you can take a shortcut and just grab sp_Blitz® now. Enjoy!
22 Comments. Leave new
Good stuff, glad I finally got to see the blitz in action at PASS. The Plan Cache addition is awesome. Thanks for sharing this with the community.
Jason – yes, problems, twofold – one, you have to strip down the script to just be the CREATE. I believe you have to remove the GO at the very end, at which point SQLCMD or INVOKE-SQLCMD(2) can get the object on the server.
Getting the data back is proving a pain; using powershell I can easily add the records to a table, but I need to add a couple of fields (servername and time) and can’t figure out how. I may have to do hashtables, not sure yet. (will post here, hopefully, once I get it figured out).
Here’s the code I’m running thus far – it looks at my list of servers, grabs the data, then writes it into a table. Provided all the fields match, you’re golden. Adding the servername is where I’m stuck at for now.
. c:\sql_tools\invoke-sqlcmd2.ps1
. c:\sql_tools\write-datatable.ps1
invoke-sqlcmd2 -serverinstance “myrepositoryserver” -query “SELECT server FROM dba_rep.dbo.ServerList order by server” | foreach-object {invoke-sqlcmd2 -erroraction silentlycontinue -serverinstance $_.server -database “master” -query “IF OBJECT_ID(‘master.dbo.sp_Blitz’) IS NOT NULL DROP PROC dbo.sp_Blitz;”;$dt = invoke-sqlcmd2 -erroraction silentlycontinue -serverinstance $_.server -database “master” -inputfile c:\sql_tools\sp_blitz_install.sql }
Run sp_blitz:
. c:\sql_tools\invoke-sqlcmd2.ps1
. c:\sql_tools\write-datatable.ps1
invoke-sqlcmd2 -serverinstance “myrepositoryserver” -query “SELECT server FROM dba_rep.dbo.ServersToLookAt WHERE Connect = 1 order by server” | foreach-object { $dt = invoke-sqlcmd2 -serverinstance $_.server -database “master” -inputfile c:\sql_tools\sp_blitz_run.sql -As ‘Datatable’ ; write-datatable -serverinstance “myrepositoryserver” -DATABASE “dba_rep” -tablename “BlitzResults” -DATA $dt }
And, now that I think of it, I wonder if I could write a SP that takes $_.server as a parameter and does a simple copy into the BlitzResultsMain or whatever I’ll call it.
Anyone else had trouble running pushing it out to multiple servers? First time I’ve had this issue but haven’t had time to troubleshoot:
Msg 102, Level 15, State 1, Line 1736
Incorrect syntax near ‘GO’.
Hi, Jason. How are you pushing it out? Also, are all of the servers 2005 & higher with all databases in compat level 80 or newer?
Want to repeat, this was just the drop and create of the sproc.
The first way I tried was through CMS but I didn’t get as clean an error so I used the Multi Query in Idera SQL admin toolset and got back the error above. Took off the GO and it worked without issue (that I’m aware of).
Yes, all were 2008 R2 and 80 or better.
Ah, yep, if you want to execute multiple queries (drop & create) using those kinds of tools, you have to be aware of their limitations – typically only one batch per run.
Love the plan cache snooping additions. Thanks for sharing it, Brent.
I spend much of my day troubleshooting others SQL Server instances. And every time you share a new script, it often eliminates a script a lest concise script I currently use to find an issue, when it is not a completely new tool to add to the toolbox. Thanks for sharing your scripts.
Cary – cool, glad we can help!
Thank you for the great stuff Brent (as usual).
In the video you said SQL Server does a good job converting the output of sys.dm_exec_text_query_plan only (only) most of the times.
I watched a presentation by Bob Beauchemin where he said the function was written because xml in SQL Server can go only 64 levels deep, and in that case the textual version is the only way to view it.
Is this a case where casting to xml cannot be done? Are there other cases?
Nice Post. Having trouble getting sp_Blitz to skip a work database. @SkipChecksDatabase ???
Eric – that’s actually part of the other @SkipChecks parameters. If you specify a @SkipChecksDatabase, @SkipChecksSchema, and @SkipChecksTable, sp_Blitz® will look in that table for a list of things to skip. That table can include servers, databases, or check IDs. I’m working on an ebook that will explain how to use that in detail, but for now you’ll need to read the source code to understand how it works.
Hi Brent – I download the script for sp_Blitz from your website through below link :
https://www.brentozar.com/first-aid/downloads/end-user-license-agreement/downloads/
I created and executed the SP at my local machine but getting below error, however windows app. for the this SP works perfectly fine :
Msg 156, Level 15, State 1, Line 15
Incorrect syntax near the keyword ‘Clustered’.
Msg 156, Level 15, State 1, Line 15
Incorrect syntax near the keyword ‘Clustered’.
Msg 156, Level 15, State 1, Line 15
Incorrect syntax near the keyword ‘Clustered’.
Msg 156, Level 15, State 1, Line 15
Incorrect syntax near the keyword ‘Clustered’.
Msg 156, Level 15, State 1, Line 15
Incorrect syntax near the keyword ‘Clustered’.
Msg 213, Level 16, State 7, Line 1
Column name or number of supplied values does not match table definition.
I’m executing this SP through sample execution script provided by you :
EXEC [master].[dbo].[sp_Blitz]
@CheckUserDatabaseObjects = 1 ,
@CheckProcedureCache = 0 ,
@OutputType = ‘TABLE’ ,
@OutputProcedureCache = 0 ,
@CheckProcedureCacheFilter = NULL,
@CheckServerInfo = 1
Can you please help ?
Hmm, it’s working fine here – my guess is that you didn’t copy/paste the stored procedure exactly as is from our site.
I opened same link in IE and copied the script, problem got resolved. Previously I was using another web browser chrome which might have some problem. Thanks Brent.
hey Brent – I need to also capture database_id, blocking_session_id, wait_time, last_wait_type, and cpu_time in the below query. Can you please help out, SP_blitz don’t capture that info.
select top 10 qs.sql_handle, qs.statement_start_offset, qs.statement_end_offset
, qs.plan_handle
,execution_count
,st.text
,substring (st.text, (qs.statement_start_offset/2)+1,
((case qs.statement_end_offset
when – 1 then datalength(st.text)
else qs.statement_end_offset
end – qs.statement_start_offset)/2) + 1) as text_filters
, qp.query_plan
from sys.dm_exec_query_stats as qs
cross apply sys.dm_exec_sql_text (qs.sql_handle) as st
cross apply sys.dm_exec_text_query_plan (qs.plan_handle, qs.statement_start_offset,
qs.statement_end_offset) as qp
go
Hi Brent – I agree with Gaurav’s thought in above that some time we require to see wait type , cpu_time and other related information. I found one dmv name as sys.dm_exec_requests , which have all these fields but not getting expected result while trying to joining it.
Can you please share your expertise here ? Thanks a lot for wonderful sessions, keep rocking !
Dinesh & Guarav – I think those are interesting requests, but you can’t really join those together without doing real time sampling, and that’s not what we’re doing with sp_Blitz. If you need real time checking of what’s happening on your server, your best bet would be sp_AskBrent.
It’s partially helping Brent.
Great, glad I could partially help!
“When you send your beautifully hand-crafted organic T-SQL statement to SQL Server, the database engine takes a moment to appreciate the poetry of your work. Before it turns your request into results, it has to build an execution plan.”
This gave me a chuckle this morning. Great writing as usual.
Hahaha, thanks sir!