Script to get Long Running queries, current running queries in SQL Server


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

SELECT

DISTINCTTOP 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

APPLYsys.dm_exec_sql_text( s.sql_handle) t

ORDER

 

BY

 

s

.max_elapsed_time DESC

GO

 

—————————————————————–

— Identifying the current running queries :

SELECT

r.session_id, r.status, r.start_time, r.command, s.

text

FROM

 

 

sys.dm_exec_requests r

CROSS

APPLYsys.dm_exec_sql_text(r.sql_handle) s

WHERE

r.status=

‘running’

 

 

 

 

 

 

 

———————————————————————————

— the below query will return the top 10 I/O intensive queries, that are causing my tempdb to bloat like crazy.

 

SELECT

top 10(total_logical_reads/execution_count

),

(

 

 

total_logical_writes/execution_count

),

(

 

 

total_physical_reads/execution_count

),

 

Execution_count

,sql_handle, plan_handle

FROM

sys.dm_exec_query_stats

ORDER

BY (total_logical_reads + total_logical_writes)

Desc

 

 

 

 

 

————————————————————————————

/***SQL Server 2005 keeps alot of good information in the dynamic management views about this kind of thing. Below are the 2 main queries I use to find slow running application queries in our systems.

Queries taking longest elapsed time:***/

SELECT

TOP 100

qs

.total_elapsed_time / qs.execution_count / 1000000.0 AS average_seconds,

qs

.total_elapsed_time / 1000000.0 AS total_seconds,

qs

.execution_count,

SUBSTRING

(qt.text,qs.statement_start_offset/2,

(

CASEWHEN qs.statement_end_offset =1

THEN

LEN(CONVERT(NVARCHAR(MAX), qt.text))* 2

ELSE

qs.statement_end_offset END qs.statement_start_offset)/2)AS individual_query

,

 

o

.name ASobject_name,

DB_NAME

(qt.dbid)AS database_name

FROM

sys.dm_exec_query_stats qs

CROSS

APPLYsys.dm_exec_sql_text(qs.sql_handle)as qt

LEFT

OUTERJOINsys.objects o ON qt.objectid = o.

object_id

 

where

qt.dbid=DB_ID

()

 

ORDER

BY average_seconds DESC;

Advertisements

One response

  1. Good one

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: