Author:    Jesse Roberge

Date:        16 Nov 2009

This code has been provided almost untouched (apart from the SP name) as it is paired with the comprehensive connection summary SP.

It provides a comprehensive summary of information on the currently running queries.

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 --*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=