useful query for identifying performance problems within SQL Server


SELECT
@@SERVERNAME as ServerName,
a.session_id,
datediff(ss, a.Start_Time, getdate()) as seconds,
a.wait_type,
a.wait_time,
m.requested_memory_kb / 1024 as requestedMB,
a.granted_query_memory,
m.dop,
a.command,
d.Name as DBName,
a.blocking_session_id as blockedby,
LTRIM(b.text) as sproc,
substring(b.text, a.statement_start_offset / 2,
CASE WHEN
(a.statement_end_offset - a.statement_start_offset) / 2 > 0
THEN
(a.statement_end_offset - a.statement_start_offset) / 2
ELSE 1
END) as stmt,
a.last_wait_type,
a.wait_resource,
a.reads,
a.writes,
a.logical_reads,
a.cpu_time
FROM
sys.dm_exec_requests a with (NOLOCK)
OUTER APPLY sys.dm_exec_sql_text(a.sql_handle) b
LEFT JOIN
sys.dm_exec_query_memory_grants m (NOLOCK)
on m.session_id = a.session_id
and m.request_id = a.request_id
LEFT JOIN
sys.databases d
ON d.database_id = a.database_id
WHERE
a.session_id > 50
AND a.session_id < > @@spid
ORDER BY
datediff(ss, a.Start_Time, getdate()) DESC

Advertisements

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