FAQS

This post is meant to give some information in question & answer pattern so that it'll be usefull for quick reference.I'l keep on adding stuff to these post when ever i found a deserving thing.

1)Tips to Optimize performance of stored procedure.
A developer has wrriten a stored procedure.Its working fine, in the sense its giving the expected output for set of given input.But you've got a feedback that the performance is not good & you take neccesary action to improove the performance.In such a scenario following the below things may lead to better performance.

a)Set 'SET NOCOUNT ON' before your stored procedure.This will stop sending number of rows affected as a result of this stored procedure.This can reduce network traffic.

b)calling stored procedures using thier fully qualified names may increase performance.Using fully qualifies names increases the chances reusing of stored procedure execution plans.

c)use RETURN statement whereever possible.This stops sqlserver building resultset.

e)dont use 'sp_' for stored procedures which are in databases other than in master database.If u hav a stored procedure with 'sp_' as a prefix , sql server will look in to the master database first & then looks for other database.

f)If the StoredProcedure is very large ,then consider to split it to many sub storedprocedure's .the reason is when any structural changes occured or bulk updates or inserts or deletes occured in table associated with stored procedure then stored procedure will recompile.If it is splitted in to many sub stored procedure's chances are more that few sub sp's will be recompiled.

g)Try to avoid using temporary tables in sp.using of temp tables will reduces the chances of reusing the execution plan of stored procedure.

h)try to avoid using DDL in stored procedure.using DDL in sp again redces the chance of reusing execution plan.

Checking for the above things helps in tracing where to refactor.