Maybe you’re new to SQL Server or maybe you’re just expanding into performance tuning a little more. Query tuning can be a very daunting task and sometimes it’s hard to decide where to look first. In this post I am going to show you a couple of my go-to methods for troubleshooting slow performing queries in SQL Server. This is not a “ZOMG my SQL Server is slow” list. This post is more geared to when I have been tasked to dig into a very specific query. This isn’t a be-all-end-all list, but its a good place to start and has served me well.
For the rest of this post I will be using the stored proc “spGetNames” as my problem query.
If you can run spGetNames safely against production, use SSMS and the “Include Actual Execution Plan” button. This will dump a graphical representation of the actual execution plan to SSMS and possibly help you determine where your query is running slow.
If you can’t run spGetNames safely in production, get it from the plan cache. Thank you to Jonathan Kehayias (B | T) over at SQLSkills for providing the basic query that I used. I just modified it to search for a specific query text. Jonathan’s original query can be found here. Below is my modified query. You just have to alter the WHERE clause to search for your query.
+EDIT: I’d like to thank Mike Fal (B | T) for helping point out that the plans that come from the plan cache are estimated plans. Most of the time they will be the same as the actual plan, but there times where that may not be true, but it’s a very important point to note.
SELECT
query_plan AS CompleteQueryPlan,
n.value('(@StatementText)[1]', 'VARCHAR(4000)') AS StatementText,
n.value('(@StatementOptmLevel)[1]', 'VARCHAR(25)') AS StatementOptimizationLevel,
n.value('(@StatementSubTreeCost)[1]', 'VARCHAR(128)') AS StatementSubTreeCost,
n.query('.') AS ParallelSubTreeXML,
ecp.usecounts,
ecp.size_in_bytes
FROM sys.dm_exec_cached_plans AS ecp
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS eqp
CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS qn(n)
WHERE n.value('(@StatementText)[1]', 'VARCHAR(4000)')
LIKE '%spGetNames%' -- Alter the search text to find your stored proc here
And here is the output. If you click on the Complete Query Plan XML field you will see the graphical query plan that came from the plan cache.
But that’s not the coolest part. Richie Rump (B | T) has built an online tool that will parse your statistics output and give you a friendlier view of the data. The URL for this awesome tool is StatisticsParser. All you have to do is to paste the statistics output from the SSMS message box and hit the parse button and whala…a nice display of the query statistics. This isn’t a great example because the query I used is pretty simple, but hopefully you get the idea.
Disclaimer: SQL Profiler/Trace and Extended Events can have a significant performance impact on your database server. I always refine my trace to be as narrow as possible using a DEV or TEST system before I even think about running it on a PROD server. ALWAYS!
Aside: I want to mention that this information is useful all the way up through SQL 2014. SQL 2016 is a game changer as far as performance tuning because of a new feature called Query Store. Here’s a snippet from MSDN.
The SQL Server Query Store feature provides DBAs with insight on query plan choice and performance. It simplifies performance troubleshooting by enabling you to quickly find performance differences caused by changes in query plans. The feature automatically captures a history of queries, plans, and runtime statistics, and retains these for your review. It separates data by time windows, allowing you to see database usage patterns and understand when query plan changes happened on the server.
I haven’t had the time to try it out yet, but I am dying to dig into it.