Get Comprehensive Running Requests Summary

DROP PROCEDURE dbo.usp_ComprehensiveRunningRequests

GO

/***=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=

Jesse Roberge - YeshuaAgapao@Yahoo.com

Reports all running requests along with the request's identifying information (SPID, login etc),

current resource consumption, query batch text, statement text, and XML query plan.

Can run from a central 'admin' database location.

Requires VIEW_SERVER_STATE permission to work. DB-owner does not have this permission.

Sysadmin does have this permission. VIEW_SERVER_STATE can be granted as a separate permission to some or all dbo users.

Update 2009-09-21:

Tweaked the pigging score - cpu*(reads+writes) is now cpu*(reads*10+writes*10+logical_reads)

Fixed EndPos for cases when it is -1

Added StatementTextLength

Update 2009-10-15:

Added DatabaseID, DatabaseName, BlockedBySessionID, BlockingRequestCount, WaitType, and PendingIOCount

Required Input Parameters

none

Optional Input Parameters

none

Copyright:

Licensed under the L-GPL - a weak copyleft license - you are permitted to use this as a component of a proprietary database and call this from proprietary software.

Copyleft lets you do anything you want except plagarize, conceal the source, or prohibit copying & re-distribution (NDAs etc) of this script/proc.

This program is free software: you can redistribute it and/or modify

it under the terms of the GNU Lesser General Public License as

published by the Free Software Foundation, either version 3 of the

License, or (at your option) any later version.

This program is distributed in the hope that it will be useful,

but WITHOUT ANY WARRANTY; without even the implied warranty of

MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the

GNU Lesser General Public License for more details.

see <http://www.fsf.org/licensing/licenses/lgpl.html> for the license text.

*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=

**/

CREATE PROCEDURE dbo.usp_ComprehensiveRunningRequests AS

--The Gritty Requests

SELECT

Sessions.session_id AS SessionID, Requests.request_id AS RequestID,

Requests.database_id AS DatabaseID, databases.name AS DatabaseName,

Sessions.login_name AS LoginName, Sessions.HOST_NAME AS HostName, Sessions.program_name AS ProgramName,

Sessions.client_interface_name AS ClientInterfaceName,

Requests.blocking_session_id AS BlockedBySessionID,

ISNULL(BlockRequests.BlockingRequestCount,0) AS BlockingRequestCount,

Requests.wait_type AS WaitType,

Requests.wait_time AS WaitTime, Requests.cpu_time AS CPUTime, Requests.total_elapsed_time AS ElapsedTime,

Requests.reads AS Reads, Requests.writes AS Writes, Requests.logical_reads AS LogicalReads,

dm_os_tasks.PendingIOCount, Requests.row_count AS [RowCount],

Requests.granted_query_memory*8 AS GrantedQueryMemoryKB,

CONVERT(BigInt, (Requests.cpu_time+1))*CONVERT(BigInt, (Requests.reads*10+Requests.writes*10+Requests.logical_reads+1)) AS Score,

Statements.TEXT AS BatchText,

LEN(Statements.TEXT) AS BatchTextLength,

Requests.statement_start_offset/2 AS StatementStartPos,

CASE

WHEN Requests.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX),Statements.TEXT))*2

ELSE Requests.statement_end_offset

END/2 AS StatementEndPos,

(CASE

WHEN Requests.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX),Statements.TEXT))*2

ELSE Requests.statement_end_offset

END - Requests.statement_start_offset)/2 AS StatementTextLength,

CASE

WHEN Requests.sql_handle IS NULL THEN ' '

ELSE

SUBSTRING(

Statements.TEXT,

(Requests.statement_start_offset+2)/2,

(CASE

WHEN Requests.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX),Statements.TEXT))*2

ELSE Requests.statement_end_offset

END - Requessts.statement_start_offset)/2

)

END AS StatementText,

QueryPlans.query_plan AS QueryPlan

FROM

sys.dm_exec_sessions AS Sessions

JOIN sys.dm_exec_requests AS Requests ON Sessions.session_id=Requests.session_id

LEFT OUTER JOIN sys.databases ON Requests.database_id=databases.database_id

LEFT OUTER JOIN (

SELECT blocking_session_id, COUNT(*) AS BlockingRequestCount FROM sys.dm_exec_requests GROUP BY blocking_session_id

) AS BlockRequests ON Requests.session_id=BlockRequests.blocking_session_id

LEFT OUTER JOIN (

SELECT request_id, session_id, SUM(pending_io_count) AS PendingIOCount

FROM sys.dm_os_tasks WITH (NOLOCK)

GROUP BY request_id, session_id

) AS dm_os_tasks ON

Requests.request_id=dm_os_tasks.request_id

AND Requests.session_id=dm_os_tasks.session_id

CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS Statements

CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS QueryPlans

ORDER BY score DESC

GO

--*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=