How to find slow performing SQL Server query

Approach to find slow performing SQL Server query

1. Check with Activity monitor in SSMS
2. sp_who2
3. For looking SPID, which is creating problem.

SELECT * 
FROM sys.dm_exec_sessions es
CROSS APPLY sys.dm_exec_input_buffer(es.session_id, NULL) ib
WHERE es.session_id > 50

4. With the help of below query to find out wait statistics, for slow running queries.

WITH Waits AS 
 ( 
 SELECT  
   wait_type,  
   wait_time_ms / 1000. AS wait_time_s, 
   100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct, 
   ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn 
 FROM sys.dm_os_wait_stats 
 WHERE wait_type  
   NOT IN 
     ('CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE', 
   'SLEEP_TASK', 'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH', 'WAITFOR', 
   'CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT') 
   ) -- filter out additional irrelevant waits 
    
SELECT W1.wait_type, 
 CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s, 
 CAST(W1.pct AS DECIMAL(12, 2)) AS pct, 
 CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct 
FROM Waits AS W1 
 INNER JOIN Waits AS W2 ON W2.rn <= W1.rn 
GROUP BY W1.rn,  
 W1.wait_type,  
 W1.wait_time_s,  
 W1.pct 
HAVING SUM(W2.pct) - W1.pct < 95; -- percentage threshold;

Make use of below Query to check the queries running multiple time and taking huge execution time.

SELECT TOP 10 
t.TEXT AS [QueryName],
s.execution_count AS [ExecutionCount],
s.max_elapsed_time AS [MaxElapsedTime],
ISNULL(s.total_elapsed_time / 1000 / NULLIF(s.execution_count,0),0) AS [AvgElapsedTime],
s.creation_time AS [LogCreatedOn],
ISNULL(s.execution_count / 1000 / NULLIF(DATEDIFF(s, s.creation_time, GETDATE()),0),0) AS [AvgExecutionCount],
query_plan
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_query_plan(s.plan_handle) u
CROSS APPLY sys.dm_exec_sql_text(s.plan_handle) t

ORDER BY MaxElapsedTime DESC



Drop All Auto Created Statistics

The way the statistics work is that when SQL Server runs any query and it needs the statistics for them, it will automatically create them if the required statistics do not exist.

When your database about to go live, do this practice to check all user created statistics by load testing on TEST/STAGE environment, So drop all the auto-created statistics, so when the system goes live, it can build from the scratch all the necessary statistics based on the query patterns.

SELECT DISTINCT 'DROP STATISTICS '
+ QUOTENAME(SCHEMA_NAME(ob.Schema_id)) + '.'
+ QUOTENAME(OBJECT_NAME(s.object_id)) + '.' +
QUOTENAME(s.name) DropStatisticsStatement
FROM sys.stats s
INNER JOIN sys.Objects ob ON ob.Object_id = s.object_id
WHERE SCHEMA_NAME(ob.Schema_id) <> 'sys'
AND Auto_Created = 1


The above script will generate the drop script which you can run for your server and drop all the auto created statistics. Remember, if you are not sure if this step will help you or not, you should reach out to any SQL Server Performance Tuning Expert before you execute the drop statistics query

You can download Database Performance Analyser from below link to find and tune heavy workload i.e. tables and queries 

https://bit.ly/dpa-blog-918

If you found the Slow running query, you can also go through for below checks.

• ·    Check for Execution Plan (Read execution plan right to left and top to bottom)
• ·    Check for Spooling
• Lazy Spooling - Duplicates of agreegation happens/ it's good in case of recursive CTE call
• Egar Spooling - is Good
• ·    Check for Hashmatch - Unsorted data (check for missing index)
• ·   Check for KeyLookup - Missing data (to remove keylookup use covering index with include keyword)
• ·    Check for Bad views - (Check for those joined tables, which are already including in the view and made extra join with that view)
• ·    Check for Multiple subqueries - (In any select statement in multiple subqueries used then use CROSS APPLY instead)
• Check for implicit conversion (remove or change the implicit conversion in where cause of data comparison)