Performance tunning of Stored Procedure : Tips to write Better Code

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.

Things to be remembered while writting a SQL code :

1 - Always use set nocount on.

2 - Avoid the use of Distinct keyword and use Group By.

3 - Avoide the use of Order By clause.

4 - Avoid the use of IN keyword use Exists or inner join.

5 - Avoid the use of NOT IN keyword use NOT Exists.

6 - Dont use the cursor use while loop with the help of identity column.

7 - Select only required field in query not use select *.

8 - In case of dynamic query always use sp_executesql instead of execute.

9 - Avoid use of temp table use table variable or CTE.

10 - Do not use the SP_ prefix with stored procedure name.

11 - Avoid any operation on the fields where possible. Some operations will prevent the use of index on this field even if it exist.
EX: where cast(dtfield as varchar(20)) = @DTval