Quick Tips on How to improve stored procedure performance in SQL Server

Today I got a issue on a very simple stored procedure. I created a stored procedure
to load data from a table.

the execution time of the stored procedure is about 2 minute and 50 second. there are only
5000 records being loaded from the database.

First step is to create indexes for the table to boost the data loading performance.

we can create the index from GUI in SSMS(Sql Server management Studio)



usually Cluster Index should be the columns which contains unique value in the table
such as the primary key. for my case it is the ID Column. you can set the sorting order in the windows as well. i set it to sort the ID column in descending order.






NonCluster Index will be the columns that I use for the Sorting in the stored procedure, and those columns likely contains duplicate values.




After I completed the Indexes creations for this specific table in the database, the stored procedure execution time improve to finish within a minutes. it is still unacceptable since there are only 5000 records return from the stored procedure execution.

Second Step is to optimize the store procedure

in my case reduce the number of columns in the sorting clause (order by columns)

when I check my stored procedure, I found that i sort the data with three columns,

I remove all columns that had been included in the Indexes that created in Step One.

after I compile and run the stored procedure, the execution time is less than 5 seconds to load 5000 records