LONG RUNNING QUERY REASONS

 LONG RUNNING QUERY REASONS

Well, if there is a "long running query" and I need to find the reason for "why" it is running so long, I proceed like this:

1. Run SQL Profiler; searching for queries which have a high number of "Reads". This mostly indicates "Index Scans" which could cause the long runtime. Check the "Execution PLan" of those queries to optimize e.g. Indexes etc.. According to this you should check the Statistics and Fragmentation degree of that table and fix it if necessary

2. Checking for Blocks. I proceed as described here: http://dynamicsuser.net/blogs/stryk/archive/2008/11/03/blocks-amp-deadlocks-in-nav-with-sql-server.aspx

3. Check "Wait Statistics" to find out if theres an I/O problem, e.g. a problem with network or disk-subsystem. Therefore I look into the "sys.dm_os_wait_stats" DMV 

Identifying longrunning query:

DBCC FREEPROCCACHE

Run following query to find longest running query using T-SQL.

SELECT DISTINCT TOP 10
t.TEXT QueryName,
s.execution_count AS ExecutionCount,
s.max_elapsed_time AS MaxElapsedTime,
ISNULL(s.total_elapsed_time / s.execution_count, 0) AS AvgElapsedTime,
s.creation_time AS LogCreatedOn,
ISNULL(s.execution_count / DATEDIFF(s, s.creation_time, GETDATE()), 0)AS FrequencyPerSec
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t
ORDER BY
s.max_elapsed_time DESC
GO