What’s slowing down production? It’s easy to find out in 60 seconds by running a simple plan cache query. See, in order to turn your beautifully written query into a set of results, SQL Server needs a plan – an execution plan. It caches these plans in memory along with statistics like the number of times a query has been run, how long it’s taking on average, and the execution plan itself. Whether you’re a junior DBA or a senior developer, you can use the three free tools covered in this video to find the culprit:
The links we discuss in the video are:
- ClearTrace – a free utility to slice and dice your Profiler trace files
- Plan cache check with sp_Blitz® – this replaces the plan cache query shown in the video. Use @CheckProcedureCache = 1 to achieve the same results.
- sp_WhoIsActive – replacement for sp_who that actually shows you the queries and execution plans
6 Comments. Leave new
Brent, could you share with us the link for the definition of the preemptive waits you mentioned during the q&a session? Thanks.
Travis – sure, it’s in the list of waits links here:
https://www.brentozar.com/sql/wait-stats/
For analyzing traces, you might want to check out Qure Analyzer. It’s a 100% free tool which does everything that was shown here and much more. It allows for multi dimensional, multi-level grouping and multi value filtering, kind of like a real ‘slice and dice’ BI. It provides both a normalized view stripped of parameter values but also allows you to drill down into those individual parameter values and the performance of each in case your statement behaves badly only with 1 specific set of parameter values…
It also provides a visual workload comparison feature so you can see exactly what has changed after your tuning effort. http://www.dbsophic.com/qure-analyzer.html
Check it out 🙂
Brent,
I dont find
http://www.brentozar.com/go/scripts.
Can you provide me link for the query to select TOP 20 ?
Virul – sure, check out sp_BlitzCache instead: https://www.brentozar.com/blitzcache/