How to tune a SQL script

[This was originally posted at http://timstall.dotnetdevelopersjournal.com/how_to_tune_a_sql_script.htm]

Performance is critical, and slow SQL procs are often a huge performance bottleneck. The problem is how to measure it. Microsoft provides SQL Profiler to help with that.

While I'm certainly no DBA, here's a basic tutorial on how to tune a SQL script (check here for more on SQL profiler).

1. Get a database with production-sized data.

Because performance can very exponentially (i.e. there may be twice as much data, but it goes twenty times slower), you absolutely need to test with production-sized data, else all your measurements could be off.

2. Be able to run the SQL script in an isolated, deterministic environment

We don't want to chase ghosts, so make sure you have a deterministic environment: (A) no one else is changing the script-under-test, (B) you can call the script with a single SQL command (like exec for a SP, or select for a function), (B) you can call the script repeatedly and get the same functional result every time. Once the script works, we can make it work fast.

3. Open up SQL Profiler for a tuning template.

SQL profiler lets you measure how fast each SQL command took. This is invaluable if you have a complicated script with many sub-commands. It's almost like stepping through the debugger where you can evaluate line-by-line.

1. Open up SQL Profiler (either from SQL Studio > Tools > SQL Server Profiler, or from the Start > Programs menu).
2. In SQL Profiler, go to File > New Trace, and connect as the SA user.
3. In the "Use the template", specify "Tuning"
4. 
5. Profiler starts recording every command being sent to the database server. To filter by the specific SPID that you're running your SP from, run the SP_WHO command in your SQL Studio window to get the SPID, and then in SQL profiler:
   1. Pause the SQL Profiler trace
   2. Goto File > Properties
   3. A new window opens up, go to the "Events Selection" tab
   4. Select SPID, and in the filter on the right enter the value into the "Equals" treeview option.
   5. 

 

4. Run your SQL statements and check the Profiler

Simply run your SQL statements in SQL studio, and check the results in SQL profiler.

The tuning template in profiler will record every command and sub-command being run. It will show the total duration (in milliseconds) for each line, and the full SQL text of what was run. This allows you to identify the bottlenecks, and tune those by changing the SQL code to something more optimal.

5. Compare the output to ensure same functionality

If you don't have an exhaustive suite of database tests, you can still help ensure that your proc is functionally equivalent by comparing the original SQL output (before tuning) to the new SQL output (after tuning). For example, you could save the output resultset as a file and then use a file-diff tool like Beyond Compare to ensure they're identical.

Summary

Again, books could be written on how to SQL tune. This is just a brief high-level tutorial to get you started.

 

---

Living in Chicago and interested in a great company? Check out the careers at Paylocity.