Credits:
Author: Jesse Roberge
Date: 16 Nov 2009
Description
It provides a comprehensive summary of information on the currently running queries.
Code
Stored Procedure:
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
--*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=
|
 Updating...
Andy Hughes, Aug 2, 2012, 7:14 AM
|