The Ultimate Running Requests Reporter
*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=
Util_RunningRequests
By 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
Usage:
EXECUTE Util.Util_RunningRequests
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.
*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING ON
GO
IF OBJECT_ID('Util.Util_RunningRequests', 'P') IS NOT NULL DROP PROCEDURE Util.Util_RunningRequests
GO
/**
*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=
Util_RunningRequests
By 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
Usage:
EXECUTE Util.Util_RunningRequests
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 Util.Util_RunningRequests 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 - Requests.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
--*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*= 
