DMV Queries - Get Comprehensive Running Requests Summary

Applicability:

                 SQL Server 2000:        Not Supported
                 SQL Server 2005:        Tested
                 SQL Server 2008:        Tested
                 SQL Server 2008R2:    Tested
                 SQL Server 2012:        Not Tested        

Credits:

Author:    Jesse Roberge
Date:        16 Nov 2009

Description

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.

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

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




ċ
usp_ComprehensiveRunningRequests.sql
(5k)
Andy Hughes,
Aug 2, 2012, 7:14 AM
Comments