Query Tuning Week: How to Start Troubleshooting a Slow Stored Procedure

When you need to find out why a stored procedure is running slow, here’s the information to start gathering:

Check to see if the plan is in the cache. Run sp_BlitzCache® and use several different @sort_order parameters – try cpu, reads, duration, executions. If you find it in your top 10 plans, you can view the execution plan (it’s in the far right columns of sp_BlitzCache®’s results), right-click on a select statement in the plan, and click Properties. You can see the optimized parameters in the details. Save this plan – it might be a good example of the query’s plan, or a bad one.

Collect a set of parameters that work. Sometimes the reporting end user will know what parameters to use to run the stored procedure, but that’s not always the case. You can also check the comments at the start of the stored proc – great developers include a sample set for calling or testing. Once you get past those easy methods, you may have to resort to tougher stuff:

  • If it runs frequently, run sp_BlitzFirst @ExpertMode = 1 while it’s running, and save the execution plan to a file. Look in the XML for its compiled parameter values.
  • Run a trace or use Extended Events to capture the start of execution. This can be pretty dicey and have a performance impact.
  • Worst case, you may have to reverse-engineer the code in order to find working params.

Find out if those parameters are fast, slow, or vary. Ideally, you want to collect a set of calling parameters that are slow, another set that are fast, and possibly a set that vary from time to time.

Find out if the stored proc does any writes. Look for inserts, updates, and deletes in the query. There’s nothing wrong with tuning those – you just have to do it in development, obviously. Not only that, but you also have to watch out for varying performance.

If it does writes, does it do the same amount of writes every time? For example, if it’s consistently updating the same rows every time it runs, it’ll probably take the same amount of time. However, if it’s adding more rows, or deleting rows, it may not – subsequent runs with the same parameters might not have work to do, or might have a lot of work to do. You may have to restore the database from backup each time you test it.

Does the stored proc run differently in development? Is it slow in production, and fast in development? Or is it consistently slow in both locations? If you measure the query with SET STATISTICS IO, TIME ON in both servers, does it produce the same number of reads and CPU time in both environments? If not, what are the differences between those environments? You may have to change the dev environment’s configuration in order to reproduce the issue, or you may have to resort to tuning in production.

Erik says: Relying on SQL to cache, and keep cached, relevant information about stored procedure calls can feel futile. I’d much rather be logging this stuff in a way that I have better control of. Whether it’s logged in the application, or the application logs to a table, it’s a much more reliable way to track stored procedure calls, parameters passed in, and how long they ran for. You can even track which users are running queries, and bring people running horrible ones in for training. This can take place a classroom or a dark alley.

Previous Post
[Video] Office Hours 2016/08/10 (With Transcriptions)
Next Post
How to Start Troubleshooting Parameter Sniffing Issues

7 Comments. Leave new

  • When I am performance tuning, I do these steps to compare before/after:
    1. Start trace filtered for particular SP name to capture commonly passed parameters. Let it run for a week (e.g. exec usp_getdata @user = ‘john’)
    2. Copy just parameters into a params.txt file (@user =’ john’ @user = ‘mike’)
    3. Create tuned version of SP (usp_getdata_new)
    4. Create batch file to run both SPs with all set of params captured above and redirect output of each to its own .txt file using sqlcmd e.g

    for /f “tokens=*” %%p in (params.txt) do (
    sqlcmd -S SQL03 -d CorpHQ -W -Q “set nocount on; exec usp_GetData %%p” -o output\old!x!.txt
    sqlcmd -S SQL03 -d CorpHQ -W -Q “set nocount on; exec usp_GetData_new %%p” -o output\new!x!.txt

    5. Start Perfmon CPU capture (%Processor Used)
    6. Start Trace filtered for both old & new SPs to capture duration
    7. Kick off batch script (step4)
    8. Wait until it cycles through old & new Sps for all params
    9. Run windiff (or any file comparison tool) on outputs to make sure data remains the same. When you peformance tune, you don’t want functionality to change.
    e.g. windiff output\old!x!.txt output\new!x!.txt
    10. Stop Perfmon & Trace. Compare results. Create pretty graphs for management. ??? . Profit

  • Is it a good practice to create indexes on temp tables, when troubleshooting?

  • I have a table with skewed statistics. The table’s application “auto-categorizes” transaction “categories”. The application also allows users to create their own “custom categories”. Thus there are hundreds of thousands to millions of “auto-categorized categories”. In skewed contrast, thus there are handfuls of “custom categories”. My favorite custom category is one named “crap” :). I have the luxury of working with the developers who allowed such crap into my database. So, I am asking them to put their custom crap in some other table, so that SQL Server can plan accordingly.

  • Hi Brent, how about looking into wait stats for the specific SP in question? Like, capturing wait info via extended events?


Leave a Reply

Your email address will not be published.

Fill out this field
Fill out this field
Please enter a valid email address.