When I joined in a new project recently, I was given the task of tuning some pre-written stored procedures. Previously, I had experience in writing stored procedures, but not with tuning! After all, who cares about tuning?

This is almost like a completely new job to me. First, as we habitually do, I decided to start with Google. The search results gave a lot of information but my question was, from where I should start? The slow performance of stored procedures might have a lot of causes, like hardware, server memory, OS, network etc.

Again, I started to research. The first thing I decided to do was to prepare some proper documentation. It was simple, and only for my personal use, but it helped me get organized. I created the following table for this:

I was advised in my research to take readings from SQL Server Profiler. Hence, I opened Profiler for the first time in my life and was perplexed. There were lots of columns, some of which I could guess the meanings and others were totally Greek to me. Since time was short, again I started Googling.

From the ocean of information I read, I choose primarily four parameters to monitor. Those were :

CPU : The amount of CPU time in milliseconds.
READS : A scalar counter, which shows number of logical reads performed by the server.
WRITES : Number of physical disk writes performed by the server.
DURATION : Amount of time taken to execute the batch or SP.
But as I started to take the readings, again I was confused. All the columns gave different reading for each execution of the procedure. Which reading should I take?

Again started my research. I saw that logical READS was showing a constant reading after one or two executions, and CPU read was also almost constant. But DURATION, which I had given the most importance, varied almost every time. It was difficult to use DURATION as a deciding parameter in tuning.

I decided to take READS as the main parameter because it was actually the number of 8 KB pages SQL Server needed to read from the data cache. It almost gave an idea of how much work SQL Server needed to do. If we could reduce this number, the duration should also go down.

Armed with this, I started to begin my tuning. I opened the Management Studio on my own testing computer and opened the stored procedure that I needed to tune. Before running Profiler, I executed the two commands below.

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

These two commands cleared the machine cache and cleaned the buffers. This allowed me to get the Profiler readings from scratch. I executed the procedure with the EXEC command using parameters for the procedure I had collected from previous Profiler data.

Since Profiler was running, I got some readings and copied them to my small table. The procedure took more time because on the first execution the query optimizer had to create the execution plan. Later, after I had executed the procedure 5-6 times and taken the average, it showed a little variation between the readings because my procedure was not so complex. For a long and complex procedure it might show a lot of variations, so taking an average is a good idea.

We could take readings from SET STATISTICS IO ON and SET STATISTICS TIME ON in Management Studio as well, but here I continue my discussion on Profiler only. What personally I feel, it was better to execute the procedure directly through SSMS. We could do the same test from application also, but in that case, it would count the network delay time, web browser time etc.

Now I started to work on the procedure. There are a lot of performance tuning tips and books available on the Internet, hence I am not going to discuss in detail which changes I made (I can discuss the performance tips in some another article). After each change in the procedure, I took new readings from Profiler and copied it to my table along with a small note, explaining what changes I made. This let me keep track of each tuning and, if I needed to roll back for one intermediate change, I could easily do this. I was lucky for this procedure. With each change, I gained some performance benefits, so I didn't need to roll back any changes.

Specially I learned in tuning a few things:

• We should be careful about creating temporary tables, dynamic SQL and joining. Those things are the gold mine for the tuners as eliminating them can make your procedure much faster.
• Be very careful about the functionality of the procedure as your procedure should return the same results after tuning as before. It is not enough that SSMS is returning the "Command(s) completed successfully." message. We should check all the tables used in the procedures. Whether they have the same number of rows and whether the rows are contain the same data. These are the kinds of checks we should perform after tuning.
• We always want to see the execution plan for each DML statement. The execution plan is the road layout, based on which the optimizer will decide which path will take less time and resources.
• I learned to try and avoid table scans. If the query optimizer chooses a table scan, it will scan the whole table, which is far more time consuming than index scan. If possible, create indexes where table scan is used (though based on the size of the table, optimizer sometimes decide to go for a table scan though there are indexes present for the column, but in this case table should contains a small number of records).
• Always try to write all the SQL statements in ANSI format. Generally, ANSI syntax not reduces the logical Reads, but it is more helpful to understand.

As an conclusion, this is my advice to all of you. Do everything you want, there are no specific rules to tune SQL statements. The ultimate goal is to reduce time and system resources. It is better to do it in some logical way and document what impat your changes have. This allows you to explain it your senior or testing team what you have done and why you have done it.