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 DMVs – make sure to only do SELECT TOP 20
- sp_WhoIsActive – replacement for sp_who that actually shows you the queries and execution plans
Travis Gan June 20, 2012 | 1:34 pm
Brent, could you share with us the link for the definition of the preemptive waits you mentioned during the q&a session? Thanks.
Brent Ozar June 20, 2012 | 1:36 pm
Travis – sure, it’s in the list of waits links here:
http://www.brentozar.com/sql/wait-stats/
Ami Levin June 21, 2012 | 7:13 am
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
Pingback: Something for the Weekend - SQL Server Links 22/06/12