Siusic Dot Com

For many people tuning store procedure means looking at the query execution plan and finding the operation that cost the most. If there is operation like table scanning or index scanning, then they will think they found the performance problem.

For me I never use the query execution plan to determine where the performance bottle neck for a store procedure. If the store procedure has several thousand lines of codes calling several functions and using a bunch of tables, the query execution plan seldom let you identify the place where the performance problem locates. Query execution plan is very useful analyze tool for a simple query but in my experience is not effective in dealing with complex store procedures.

The strategy I use which I believe is the ultimate strategy of turning store procedure performance is very much like debugging a program. Finding a performance bottle neck is just like finding a bug. What I usually do is to first identify the performance target, determine how much time the store procedure needs to finishes its job. For example if the front-end developer complains the store procedure runs too slow and wants the data to be returned within two second window then you know you have to tune the store procedure so it finishes running and return data in less than two seconds.

With the target in mind I usually copy the query code within the store procedure and paste in into query analyzer, use profiler to catch the slowest store procedure calls and use the parameter values to sets up the parameters for the code you paste in query analyzer.

If the store procedure to be tuned is sp_foobar and its code is like:

create procedure sp_foobar @keyword varchar(20)
as
select * from table1 where keyword = @keyword
select * from table2 where keyword = @keyword
go

I will use profiler to find a sp_foobar call that is way out of the 2 second time frame. For example if I find this call exec sp_foobar 'car' takes 10 seconds to return then copy the store procedures code and sets up the parameters in query analyzer like this:

declare @keyword varchar(20)
set @keyword='car'
select * from table1 where keyword = @keyword
select * from table2 where keyword = @keyword

After that I will add some debugging code in there like:

create table #temp (step int, finishedtime)
declare @keyword varchar(20)
set @keyword=’car’
insert into #temp (step, finishedtime) values (1, getdate())
select * from table1 where keyword = @keyword
insert into #temp (step, finishedtime) values (2, getdate())
select * from table2 where keyword = @keyword
insert into #temp (step, finishedtime) values (3, getdate())
select b.step, datediff(second, a.finishedtime, b.finishedtime)
from #temp a join #temp b on a.step=b.step+1
order by b.step

You can see I put the insert statement into each identified break point. You can put in as much as you want. Now you can run the query. The last statement

select b.step, datediff(ms, a.finishedtime, b.finishedtime)
from #temp a join #temp b on a.step=b.step+1
order by b.step

is to display how much time each step spend. If it has obvious performance bottle neck you will see most of the steps finish in millisecond and a few of them take a lot longer. Focus on the piece of code that is the slowest and selectively place more insert statement and run the store proc again. Continue with this process eventually I will be able to narrowly down where the performance bottle neck is located. After that I will work on improving the code identified to be the slowest. It may need to build more indexes, more tables or use additional process to help speeding it up. It could be a very tedious process, speciously when there isn’t an obvious performance bottleneck. Because that means you have to work on optimizing more code and sometimes to completely rewrite of the store procedure.

Related Posts:

SQL Server 2005 Profiler Duration

How to Kill AutoShrink Process

Restore the latest database backup automatically and xp_dirtree

The Best Way to Grant Execution Right on xp_cmdshell and Cross Database Ownership Chaining

SQL Mail vs Database Mail