How to troubleshoot a slow running query in SQL Server

 “ I have a slow running query , what steps can I take to speed up the query and achieve an optimised execution plan?” .

Troubleshooting database server performance is an in-depth topic. A methodical  and repeatable approach solving the root cause is the perfect situation for any DBA.   There are different approaches to SQL Server Tuning. In reality , the root cause may not be a SQL query issue , it may be SAN  , SQL Server configuration issue or workload has suddenly increased.

But of course, you’re under pressure and the query is causing  delays for the end users.The steps below expand on earlier post :SQL Server Rapid Tuning, providing a very simple emergency approach.

The steps below are a straightforward approach to troubleshooting sub optimal SQL Execution Plans. Follow the steps and repeat as necessary.

Step 1)       Statistics – Is Auto Create and Update Statistics Enabled? If Auto Create statistics is disabled , this may indicate out of date statistics. If Disabled, than proceed to  Update Statistics – use sp_updatestats to update all statistics on the database. Run the query and check if any improvement.

Step 2)       If  Auto Create and Update Statistics is Enabled, Identify the longest running queriesor highest impact queries .( If the queries have high CPU usage go to step 6). Long duration and highest IO should be a priority.

Step 3)       Place every query in the SSMS and analyse the execution plan. First – check for tables or index scans.  If large table \ index scans are occurring – progress with Query Analysis.  The Query Analysis should ask questions such as : Are all JOINS valid ? Are the JOINS returning excessive data ? Search argument validity?   Functions in predicate? 

Step 4)       If no table or index scans exist and  the query is complex , for example a large transaction managing a booking process –  check for : excessive joins, temp tables, DDL changes,sub – queries , no set based approach to writing the queries. Review  the query ,  break it down into smaller parts or analyse the JOINS to invoke a new execution plan , ensuring a similar transaction integrity is retained

Step 5)       If the query is simple and no no index \table scans exists and executing in  SSMS responds with acceptable  performance - analyse the Application and how it processes the resultset.   Ask the right question a) are just relevant results returned?  Talk to the application developers.

Step  6)       If the query is no faster in SSMS , more complex query tuning is required,  research other methods or contact a performance tuning expert

Step 7)       If in Step  2 you identified queries  with high CPU usage , analyse in SSMS for Hash Joins, Sorts, Filters.  If any of these exists , progress with Query Analysis.

Step 8)       Repeat until the problem disappears.