Performance Tuning - Slowness with a specific Stored Procedure or a Query - PART 2/2

Troubleshoot slowness with a specific Stored Procedure or a Query

1.        First, get more details like, how much time on an average this query was taking previously (baseline)?

2.        Check whether any changes to the stored procedure or query recently

3.        Find out how often this query does run

4.        Check whether the issue is reproduced on Test or Dev. severs

5.        Check if this query is being blocked by other sessions.

6.        Check whether any deadlocks are happened

7.        Check if this query is waiting some any resource using wait stats DMV’s.

8.        Check if statistics are up to date for the tables

9.        Check for any missing indexes/un-used indexes

10.     Check fragmentation of the objects in the stored procedure or the query.

11.     Collect execution plan of the Stored Procedure and the statements inside the SP or the query.

12.     Collect Read/Write and Time execution metrics of the query.

13.     Check for excess recompilations of the stored procedure.

14.     Last option is rewriting the query by following best practices.