One of your production queries out of nowhere started to run slow and users aren’t happy. Application takes forever to load its screen because of course, the query retrieving data from your database is not optimal (Yep, let’s give developers a pass here). A finance report was running OK up until yesterday but times out every time now. After all, the user cares how long it takes to render data on their screen and complete their query.
Just a few examples you run into in your daily database life. Optimizing SQL Server and tuning queries for better performance are not set-and-forget configurations but rather a challenge that keeps changing with new releases, ‘awesome’ business requests, and whatever the last guy did.
Tuning queries start with analyzing the execution plans and understanding various performance measures. Below are some of the tools I use to measure query performances.
SP_WHOISACTIVE
It will tell me exactly what is going on right now with my SQL Server. It will quickly help me identify why things are slow, what people are doing, and what exactly they are waiting on.
If you run SP_WHOISACTIVE without any parameters, it returns quickly with a lot of information. All the active queries, how long have they been running, actual SQL text, login information, the application, and performance measures like CPU cycles, and logical reads are also there.
However, I use two additional parameters to get more information. SP_WHOISACTIVE @get_plans = 1, @get_additional_info = 1
This will give you a query_plan (actual or estimated? Hint: You are tracking a live query that is running or about to run). Also, additional_info the column gives you helpful info.
Next up – Graphical Execution Plans
SQL Server query optimizer chooses the best execution plan based on the cost of each plan. SSMS can give you estimated as well as actual execution plans but my preference is to use the actual plan for analysis. This is the plan that is generated after the query completes its execution and will help you understand why some choices made by the optimizer might not have been the best. Read the plan from right to left, top to bottom, and understand why the thickness of an arrow changes. Leverage tooltips available for each icon and dive into run time statistics as the number of rows returned, estimated rows, execution of each operator and their cost, etc. Cardinality estimates are crucial for the query optimizer to make pick a better plan.
TIP: SentryOne’s free Plan Explorer helps with highlighting expensive operations and even allows you to sort by different measures that matter to you. While troubleshooting ad hoc queries, I enable SET STATISTICS IO and SET STATISTICS TIME. Former session options will display statistical information like logical and physical reads, scan counts, etc. The latter option will display the time in milliseconds required to complete a query.
The other built-in tool I go to is Extended Events (Nope, I am not using SQL Profile anymore, believe me). XE helps collect several different events from SQL Server and we can correlate those events to troubleshoot our issues at any given time. I finally stopped using Profiler and adopted XE because I started realizing we can capture more events and seem much more flexible than my one-time favorite Profiler. A few of my favorite events to capture are rpc_completed, sp_statement_completed and sql_statement_completed
TIP: Query cache hot or cold? Troubleshooting in different environments with the same code can be problematic if query cache is hot in Production but ‘ice’ cold in Development. To compare hot query cache, run the query twice and measure the performance of your second execution. For cold cache, run DROPCLEANBUFFERS (Be careful running this in production as it can kill your query performances)Temporary Stored Procedure
Due to reasons like – Oh, our change management process will not allow creating new database objects until approved or the DB developer does not have object CREATE permissions in the databases, or simply you want to test the stored procedure before actually creating/altering it, the temporary stored procedure is your friend.
I have not seen temporary stored procedures used very often but they come in handy during performance troubleshooting. Copy the guts out of the actual stored procedure having an issue and create a temporary stored procedure with the same definition. Use the temporary stored procedure to troubleshoot issues and once satisfied with updates, plan your deployment to the actual stored procedure.
- As the name suggests, they are temporary and are only available during a session but work like a normal stored procedure.
- To create a local temporary stored procedure, the name must start with (#), and for global use (##).
- Temporary stored procedures are created in TempDB and thus can be called from any database.
TIP: Troubleshooting a classic “Works on my machine’s management studio, not sure why application sucks”. Do not take the gut out of the stored procedure and run ad hoc. Use a temporary stored procedure instead. 