Performance tunning of Stored Procedure

After follow these steps mentions in this article

http://manish-sqlserver.blogspot.com/2011/02/tips-write-better-sql-code.html


You can optimize your SP as:

O - First make sure that the statistics on all tables in your query plan are up to date if not then first update the statistics of all the tables(Using update statistics command) which are used in SP. And look at the query plan first. It will show you the optimal current execution plan from the query engine's point of view. Find the most expensive part of the plan and start optimizng from here.

O - If you see the table scan in that part that means the table used in that part have not only indexes but also it dont have clustered index, so first create the clustered index on that table and run again the SP and check the SP is working fine or not according to time.

O - If you see the clustered index scan in that part that means the table used in that part have the indexes but the indexes are not using by the execution plan to search the data. In that case you need to replace the indexes position that means indexes are not created on the right columns. So when you create the indexes on right column then the index seek is performed to search the data in table.

So finally you need to create the indexes in such a way that the query execution plan always perform the Index seek to search the data in table.