DMV Queries - Get Comprehensive Connection 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).  I did start to reformat, but it wasn't making it much more readable and wouldn't have displayed well within this page format.

It has also been left in SP format rather than the TVFs that I have used for most other DMV code as it produces 3 results sets:

Comprehensive information about
  1. The single connection that is currently the most resource intensive (the 'piggiest')
  2. Summary by Login/Host Name
  3. Summary By Session (SPID)

Code

Stored Procedure:


DROP PROCEDURE dbo.usp_ComprehensiveConnectionSummary
GO

/**
*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=
Jesse Roberge - YeshuaAgapao@Yahoo.com

Reports summaries of connections, running requests, open transactions, open cursors, and blocking at 3 different levels of aggregation detail.
   Also gives the piggiest running request for each grouping along with its identifying information, query batch text, statement text, and XML query plan.
Most useful for finding SPIDs thare being hoggy right now - activity monitor gives session-scoped resource consumption, this aggregates active request scoped resource consumption.
Also useful for quickly finding blocking offenders and finding programs that are not closing connections, cursors or transactions.
Returns 3 result sets:
   Server-wide Total / Summary (No Group By)
   Connections and requests grouped by LoginName, HostName, Programname
   Connections and requests grouped by SessionID (can have more than 1 running request at a time if MARS is enabled)
Orders by ActiveReqCount DESC, OpenTranCount DESC, BlockingRequestCount DESC, BlockedReqCount DESC, ConnectionCount DESC, {group by column(s)}
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-04-28:
   Now also gives the piggiest running request for each grouping along with its
       identifying information, query batch text, statement text, and XML query plan.

Update 2009-09-21:
   Fixed piggiest running request - Forgot the DESC in the ORDER BY
   Tweaked the pigging score - cpu*(reads+writes) is now cpu*(reads*10+writes*10+logical_reads)
   Added StatementStartPos, StatementEndPos, and StatementTextLength

Update 2009-10-15:
   Added PendingIOCount, PiggiestRequestDatabseID, and PiggiestRequestDatabaseName

Required Input Parameters
   none

Optional Input Parameters
   none

Usage:
   EXECUTE Util.Util_ConnectionSummary

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_ComprehensiveConnectionSummary AS

--All connections
SELECT
  
ConnectionCount                     AS ConnectionCount,
  
OpenTranCount                       AS OpenTranCount,
  
OpenCursorCount                     AS OpenCursorCount,
  
ClosedCursorCount                   AS ClosedCursorCount,
  
BlockingRequestCount                AS BlockingRequestCount,
  
ActiveReqCount                      AS ActiveReqCount,
  
OpenResultSetCount                  AS OpenResultSetCount,
  
ActiveReqOpenTranCount              AS ActiveReqOpenTranCount,
  
BlockedReqCount                     AS BlockedReqCount,
  
WaitTime                            AS WaitTime,
  
CPUTime                             AS CPUTime,
  
ElapsedTime                         AS ElapsedTime,
  
Reads                               AS Reads,
  
Writes                              AS Writes,
  
LogicalReads                        AS LogicalReads,
  
PendingIOCount                      AS PendingIOCount,
  
[RowCount]                          AS [RowCount],
  
GrantedQueryMemoryKB                AS GrantedQueryMemoryKB,
  
PiggiestRequest.session_id          AS PiggiesstRequestSessionID,
  
PiggiestRequest.login_name          AS PiggiestRequestLoginName,
  
PiggiestRequest.HOST_NAME           AS PiggiestRequestHostName,
  
PiggiestRequest.program_name        AS PiggiestRequestProgramName,
  
PiggiestRequest.DatabaseID          AS PiggiestRequestDatabaseID,
  
PiggiestRequest.DatabaseName        AS PiggiestRequestDatabaseName,
  
PiggiestRequest.BatchText           AS PiggiestRequestBatchText,
  
PiggiestRequest.BatchTextLength     AS BatchTextLength,
  
PiggiestRequest.StatementStartPos   AS StatementStartPos,
  
PiggiestRequest.StatementEndPos     AS StatementEndPos,
  
PiggiestRequest.StatementTextLength AS StatementTextLength,
  
PiggiestRequest.StatementText       AS PiggiestRequestStatementText,
  
PiggiestRequest.QueryPlan           AS PiggiestRequestQueryPlanXML
FROM
  
(
      
SELECT
          
SUM(ConnectionCount)                                                            AS ConnectionCount,
          
SUM(CONVERT(BIGINT, ISNULL(dm_tran_session_transactions.TransactionCount,0)))    AS OpenTranCount,
          
SUM(CONVERT(BIGINT, ISNULL(dm_exec_cursors.OpenCursorCount,0)))                  AS OpenCursorCount,
          
SUM(CONVERT(BIGINT, ISNULL(dm_exec_cursors.ClosedCursorCount,0)))                AS ClosedCursorCount,
          
ISNULL(SUM(dm_exec_blockrequests.BlockingRequestCount),0)                       AS BlockingRequestCount,
          
SUM(dm_exec_requests.ActiveReqCount)                                            AS ActiveReqCount,
          
SUM(dm_exec_requests.open_resultset_count)                                      AS OpenResultSetCount,
          
SUM(dm_exec_requests.open_transaction_count)                                    AS ActiveReqOpenTranCount,
          
SUM(dm_exec_requests.BlockedReqCount)                                           AS BlockedReqCount,
          
SUM(dm_exec_requests.wait_time)                                                 AS WaitTime,
          
SUM(dm_exec_requests.cpu_time)                                                  AS CPUTime,
          
SUM(dm_exec_requests.total_elapsed_time)                                        AS ElapsedTime,
          
SUM(dm_exec_requests.reads)                                                     AS Reads,
          
SUM(dm_exec_requests.writes)                                                    AS Writes,
          
SUM(dm_exec_requests.logical_reads)                                             AS LogicalReads,
          
SUM(dm_exec_requests.PendingIOCount)                                            AS PendingIOCount,
          
SUM(dm_exec_requests.row_count)                                                 AS [RowCount],
          
SUM(dm_exec_requests.granted_query_memory)                                      AS GrantedQueryMemoryKB
      
FROM sys.dm_exec_sessions
      
LEFT OUTER JOIN
          
(
              
SELECT
                  
session_id,
                  
COUNT(*) AS ConnectionCount
              
FROM sys.dm_exec_connections
              
GROUP BY session_id
          
) AS dm_exec_connections
          
ON sys.dm_exec_sessions.session_id = dm_exec_connections.session_id
      
LEFT OUTER JOIN
          
(
              
SELECT
                  
session_id,
                  
COUNT(*) AS TransactionCount
              
FROM sys.dm_tran_session_transactions
              
GROUP BY session_id
          
) AS dm_tran_session_transactions
          
ON sys.dm_exec_sessions.session_id = dm_tran_session_transactions.session_id
      
LEFT OUTER JOIN
          
(
              
SELECT
                  
blocking_session_id,
                  
COUNT(*) AS BlockingRequestCount
              
FROM sys.dm_exec_requests
              
GROUP BY blocking_session_id
          
) AS dm_exec_blockrequests
          
ON sys.dm_exec_sessions.session_id = dm_exec_blockrequests.blocking_session_id
      
LEFT OUTER JOIN
          
(
              
SELECT
                  
session_id,
                  
SUM(CASE WHEN is_open=1 THEN 1 ELSE 0 END) AS OpenCursorCount,
                  
SUM(CASE WHEN is_open=0 THEN 1 ELSE 0 END) AS ClosedCursorCount
              
FROM sys.dm_exec_cursors (0)
              
GROUP BY session_id
          
) AS dm_exec_cursors
          
ON sys.dm_exec_sessions.session_id = dm_exec_cursors.session_id
      
LEFT OUTER JOIN
          
(
              
SELECT
                  
dm_exec_requests.session_id                                                     AS Session_ID,
                  
SUM(CONVERT(bigint, dm_exec_requests.open_transaction_count))                    AS open_transaction_count,
                  
SUM(CONVERT(bigint, dm_exec_requests.open_resultset_count))                      AS open_resultset_count,
                  
SUM(CASE WHEN dm_exec_requests.total_elapsed_time IS NULL THEN 0 ELSE 1 END) AS ActiveReqCount,
                  
SUM(CASE WHEN dm_exec_requests.blocking_session_id <> 0 THEN 1 ELSE 0 END)       AS BlockedReqCount,
                  
SUM(CONVERT(bigint, dm_exec_requests.wait_time))                             AS wait_time,
                  
SUM(CONVERT(bigint, dm_exec_requests.cpu_time))                                  AS cpu_time,
                  
SUM(CONVERT(bigint, dm_exec_requests.total_elapsed_time))                        AS total_elapsed_time,
                  
SUM(CONVERT(bigint, dm_exec_requests.reads))                                 AS Reads,
                  
SUM(CONVERT(bigint, dm_exec_requests.writes))                                    AS Writes,
                  
SUM(CONVERT(bigint, dm_exec_requests.logical_reads))                         AS logical_reads,
                  
SUM(CONVERT(bigint, dm_os_tasks.PendingIOCount))                             AS PendingIOCount,
                  
SUM(CONVERT(bigint, dm_exec_requests.row_count))                             AS row_count,
                  
SUM(CONVERT(bigint, dm_exec_requests.granted_query_memory*8))                    AS granted_query_memory
              
FROM sys.dm_exec_requests
              
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 dm_exec_requests.request_id = dm_os_tasks.request_id
                  
AND dm_exec_requests.session_id = dm_os_tasks.session_id
              
GROUP BY dm_exec_requests.session_id
          
) AS dm_exec_requests
          
ON sys.dm_exec_sessions.session_id = dm_exec_requests.session_id
      
WHERE sys.dm_exec_sessions.is_user_process = 1
  
) AS Sessions
  
LEFT OUTER JOIN
      
(
          
SELECT
              
Requests.login_name                     AS Login_Name,
              
Requests.HOST_NAME                      AS HOST_NAME,
              
Requests.program_name                   AS program_name,
              
Requests.session_id                     AS session_id,
              
Requests.database_id                    AS DatabaseID,
              
databases.name                          AS DatabaseName,
              
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
          
(
              
SELECT
                  
Sessions.login_name,
                  
Sessions.HOST_NAME,
                  
Sessions.program_name,
                  
Requests.session_id,
                  
Requests.database_id,
                  
CONVERT(BigInt, (Requests.cpu_time+1))*CONVERT(BigInt, (Requests.reads*10+Requests.writes*10+Requests.logical_reads+1)) AS score,
                  
Requests.sql_handle, Requests.plan_handle, Requests.statement_start_offset, Requests.statement_end_offset,
                  
ROW_NUMBER() OVER (
                      
ORDER BY CONVERT(BigInt, (Requests.cpu_time+1))*CONVERT(BigInt, (Requests.reads*10+Requests.writes*10+Requests.logical_reads+1)) DESC
                  
) AS RowNumber
              
FROM
                  
sys.dm_exec_sessions AS Sessions
                  
JOIN sys.dm_exec_requests AS Requests ON Sessions.session_id=Requests.session_id
          
) AS Requests
          
LEFT OUTER JOIN sys.databases ON requests.database_id=databases.database_id
          
OUTER APPLY sys.dm_exec_sql_text(sql_handle) AS Statements
          
OUTER APPLY sys.dm_exec_query_plan(plan_handle) AS QueryPlans
      
WHERE RowNumber=1
  
) AS PiggiestRequest ON 1=1

--*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=
--Connections by LoginName, Hostname, and ProgramName
SELECT
  
Sessions.login_name, Sessions.HOST_NAME, Sessions.program_name,
  
ConnectionCount, OpenTranCount, OpenCursorCount, ClosedCursorCount, BlockingRequestCount,
  
ActiveReqCount, OpenResultSetCount, ActiveReqOpenTranCount, BlockedReqCount,
  
WaitTime, CPUTime, ElapsedTime, Reads, Writes, LogicalReads, PendingIOCount, [RowCount], GrantedQueryMemoryKB,
  
PiggiestRequest.session_id AS PiggiestRequestSessionID,
  
PiggiestRequest.DatabaseID AS PiggiestRequestDatabaseID,
  
PiggiestRequest.DatabaseName AS PiggiestRequestDatabaseName,
  
PiggiestRequest.BatchText AS PiggiestRequestBatchText,
  
PiggiestRequest.BatchTextLength, PiggiestRequest.StatementStartPos,
  
PiggiestRequest.StatementEndPos, PiggiestRequest.StatementTextLength,
  
PiggiestRequest.StatementText AS PiggiestRequestStatementText,
  
PiggiestRequest.QueryPlan AS PiggiestRequestQueryPlanXML
FROM
  
(
      
SELECT
          
sys.dm_exec_sessions.login_name, sys.dm_exec_sessions.HOST_NAME, sys.dm_exec_sessions.program_name,
          
SUM(ConnectionCount) AS ConnectionCount,
          
SUM(CONVERT(bigint, ISNULL(dm_tran_session_transactions.TransactionCount,0))) AS OpenTranCount,
          
SUM(CONVERT(bigint, ISNULL(dm_exec_cursors.OpenCursorCount,0))) AS OpenCursorCount,
          
SUM(CONVERT(bigint, ISNULL(dm_exec_cursors.ClosedCursorCount,0))) AS ClosedCursorCount,
          
ISNULL(SUM(dm_exec_blockrequests.BlockingRequestCount),0) AS BlockingRequestCount,
          
SUM(dm_exec_requests.ActiveReqCount) AS ActiveReqCount,
          
SUM(dm_exec_requests.open_resultset_count) AS OpenResultSetCount,
          
SUM(dm_exec_requests.open_transaction_count) AS ActiveReqOpenTranCount,
          
SUM(dm_exec_requests.BlockedReqCount) AS BlockedReqCount,
          
SUM(dm_exec_requests.wait_time) AS WaitTime,
          
SUM(dm_exec_requests.cpu_time) AS CPUTime,
          
SUM(dm_exec_requests.total_elapsed_time) AS ElapsedTime,
          
SUM(dm_exec_requests.reads) AS Reads,
          
SUM(dm_exec_requests.writes) AS Writes,
          
SUM(dm_exec_requests.logical_reads) AS LogicalReads,
          
SUM(dm_exec_requests.PendingIOCount) AS PendingIOCount,
          
SUM(dm_exec_requests.row_count) AS [RowCount],
          
SUM(dm_exec_requests.granted_query_memory) AS GrantedQueryMemoryKB
      
FROM
          
sys.dm_exec_sessions
          
LEFT OUTER JOIN (
              
SELECT session_id, COUNT(*) AS ConnectionCount FROM sys.dm_exec_connections GROUP BY session_id
          
) AS dm_exec_connections ON sys.dm_exec_sessions.session_id=dm_exec_connections.session_id
          
LEFT OUTER JOIN (
              
SELECT session_id, COUNT(*) AS TransactionCount FROM sys.dm_tran_session_transactions GROUP BY session_id
          
) AS dm_tran_session_transactions ON sys.dm_exec_sessions.session_id=dm_tran_session_transactions.session_id
          
LEFT OUTER JOIN (
              
SELECT blocking_session_id, COUNT(*) AS BlockingRequestCount FROM sys.dm_exec_requests GROUP BY blocking_session_id
          
) AS dm_exec_blockrequests ON sys.dm_exec_sessions.session_id=dm_exec_blockrequests.blocking_session_id
          
LEFT OUTER JOIN (
              
SELECT session_id, SUM(CASE WHEN is_open=1 THEN 1 ELSE 0 END) AS OpenCursorCount, SUM(CASE WHEN is_open=0 THEN 1 ELSE 0 END) AS ClosedCursorCount
              
FROM sys.dm_exec_cursors (0)
              
GROUP BY session_id
          
) AS dm_exec_cursors ON sys.dm_exec_sessions.session_id=dm_exec_cursors.session_id
          
LEFT OUTER JOIN (
              
SELECT
                  
dm_exec_requests.session_id,
                  
SUM(CONVERT(bigint, dm_exec_requests.open_transaction_count)) AS open_transaction_count,
                  
SUM(CONVERT(bigint, dm_exec_requests.open_resultset_count)) AS open_resultset_count,
                  
SUM(CASE WHEN dm_exec_requests.total_elapsed_time IS NULL THEN 0 ELSE 1 END) AS ActiveReqCount,
                  
SUM(CASE WHEN dm_exec_requests.blocking_session_id <> 0 THEN 1 ELSE 0 END) AS BlockedReqCount,
                  
SUM(CONVERT(bigint, dm_exec_requests.wait_time)) AS wait_time,
                  
SUM(CONVERT(bigint, dm_exec_requests.cpu_time)) AS cpu_time,
                  
SUM(CONVERT(bigint, dm_exec_requests.total_elapsed_time)) AS total_elapsed_time,
                  
SUM(CONVERT(bigint, dm_exec_requests.reads))  AS Reads,
                  
SUM(CONVERT(bigint, dm_exec_requests.writes)) AS Writes,
                  
SUM(CONVERT(bigint, dm_exec_requests.logical_reads)) AS logical_reads,
                  
SUM(CONVERT(bigint, dm_os_tasks.PendingIOCount)) AS PendingIOCount,
                  
SUM(CONVERT(bigint, dm_exec_requests.row_count)) AS row_count,
                  
SUM(CONVERT(bigint, dm_exec_requests.granted_query_memory*8)) AS granted_query_memory
              
FROM
                  
sys.dm_exec_requests
                  
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
                      
dm_exec_requests.request_id=dm_os_tasks.request_id
                      
AND dm_exec_requests.session_id=dm_os_tasks.session_id
              
GROUP BY dm_exec_requests.session_id
          
) AS dm_exec_requests ON sys.dm_exec_sessions.session_id=dm_exec_requests.session_id
      
WHERE sys.dm_exec_sessions.is_user_process=1
      
GROUP BY sys.dm_exec_sessions.login_name, sys.dm_exec_sessions.HOST_NAME, sys.dm_exec_sessions.program_name
  
) AS Sessions
   LEFT OUTER JOIN (
      
SELECT
          
Requests.login_name, Requests.HOST_NAME, Requests.program_name, Requests.session_id,
          
Requests.database_id AS DatabaseID, databases.name AS DatabaseName,
          
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
          
(
              
SELECT
                  
Sessions.login_name, Sessions.HOST_NAME, Sessions.program_name, Requests.session_id,
                  
Requests.database_id,
                  
CONVERT(BigInt, (Requests.cpu_time+1))*CONVERT(BigInt, (Requests.reads*10+Requests.writes*10+Requests.logical_reads+1)) AS score,
                  
Requests.sql_handle, Requests.plan_handle, Requests.statement_start_offset, Requests.statement_end_offset,
                  
ROW_NUMBER() OVER (
                      
PARTITION BY Sessions.login_name, Sessions.HOST_NAME, Sessions.program_name
                      
ORDER BY CONVERT(BigInt, (Requests.cpu_time+1))*CONVERT(BigInt, (Requests.reads*10+Requests.writes*10+Requests.logical_reads+1)) DESC
                  
) AS RowNumber
              
FROM
                  
sys.dm_exec_sessions AS Sessions
                  
JOIN sys.dm_exec_requests AS Requests ON Sessions.session_id=Requests.session_id
          
) AS Requests
          
LEFT OUTER JOIN sys.databases ON requests.database_id=databases.database_id
          
OUTER APPLY sys.dm_exec_sql_text(sql_handle) AS Statements
          
OUTER APPLY sys.dm_exec_query_plan(plan_handle) AS QueryPlans
      
WHERE RowNumber=1
  
) AS PiggiestRequest ON
      
Sessions.login_name=PiggiestRequest.login_name
      
AND Sessions.HOST_NAME=PiggiestRequest.HOST_NAME
      
AND Sessions.program_name=PiggiestRequest.program_name
ORDER BY
  
Sessions.ActiveReqCount DESC, Sessions.OpenTranCount DESC,
  
Sessions.BlockingRequestCount DESC, Sessions.BlockedReqCount DESC, Sessions.ConnectionCount DESC,
  
Sessions.login_name, Sessions.HOST_NAME, Sessions.program_name

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

--Connections by session_id
SELECT
  
Sessions.session_id, Sessions.login_name, Sessions.HOST_NAME, Sessions.program_name,
  
Sessions.client_interface_name, Sessions.status,
  
ConnectionCount, OpenTranCount, OpenCursorCount, ClosedCursorCount, BlockingRequestCount,
  
ActiveReqCount, OpenResultSetCount, ActiveReqOpenTranCount, BlockedReqCount,
  
WaitTime, CPUTime, ElapsedTime, Reads, Writes, LogicalReads, PendingIOCount, [RowCount], GrantedQueryMemoryKB,
  
PiggiestRequest.DatabaseID AS PiggiestRequestDatabaseID,
  
PiggiestRequest.DatabaseName AS PiggiestRequestDatabaseName,
  
PiggiestRequest.BatchText AS PiggiestRequestBatchText,
  
PiggiestRequest.BatchTextLength, PiggiestRequest.StatementStartPos,
  
PiggiestRequest.StatementEndPos, PiggiestRequest.StatementTextLength,
  
PiggiestRequest.StatementText AS PiggiestRequestStatementText,
  
PiggiestRequest.QueryPlan AS PiggiestRequestQueryPlanXML
FROM
  
(
      
SELECT
          
sys.dm_exec_sessions.session_id,
          
MAX(sys.dm_exec_sessions.login_name) AS login_name, MAX(sys.dm_exec_sessions.HOST_NAME) AS HOST_NAME,
          
MAX(sys.dm_exec_sessions.program_name) AS program_name, MAX(sys.dm_exec_sessions.client_interface_name) AS client_interface_name,
          
MAX(sys.dm_exec_sessions.status) AS status,
          
SUM(ConnectionCount) AS ConnectionCount,
          
SUM(CONVERT(bigint, ISNULL(dm_tran_session_transactions.TransactionCount,0))) AS OpenTranCount,
          
SUM(CONVERT(bigint, ISNULL(dm_exec_cursors.OpenCursorCount,0))) AS OpenCursorCount,
          
SUM(CONVERT(bigint, ISNULL(dm_exec_cursors.ClosedCursorCount,0))) AS ClosedCursorCount,
          
ISNULL(SUM(dm_exec_blockrequests.BlockingRequestCount),0) AS BlockingRequestCount,
          
SUM(dm_exec_requests.ActiveReqCount) AS ActiveReqCount,
          
SUM(dm_exec_requests.open_resultset_count) AS OpenResultSetCount,
          
SUM(dm_exec_requests.open_transaction_count) AS ActiveReqOpenTranCount,
          
SUM(dm_exec_requests.BlockedReqCount) AS BlockedReqCount,
          
SUM(dm_exec_requests.wait_time) AS WaitTime,
          
SUM(dm_exec_requests.cpu_time) AS CPUTime,
          
SUM(dm_exec_requests.total_elapsed_time) AS ElapsedTime,
          
SUM(dm_exec_requests.reads) AS Reads,
          
SUM(dm_exec_requests.writes) AS Writes,
          
SUM(dm_exec_requests.logical_reads) AS LogicalReads,
          
SUM(dm_exec_requests.PendingIOCount) AS PendingIOCount,
          
SUM(dm_exec_requests.row_count) AS [RowCount],
          
SUM(dm_exec_requests.granted_query_memory) AS GrantedQueryMemoryKB
      
FROM
          
sys.dm_exec_sessions
          
LEFT OUTER JOIN (
              
SELECT session_id, COUNT(*) AS ConnectionCount FROM sys.dm_exec_connections GROUP BY session_id
          
) AS dm_exec_connections ON sys.dm_exec_sessions.session_id=dm_exec_connections.session_id
          
LEFT OUTER JOIN (
              
SELECT session_id, COUNT(*) AS TransactionCount FROM sys.dm_tran_session_transactions GROUP BY session_id
          
) AS dm_tran_session_transactions ON sys.dm_exec_sessions.session_id=dm_tran_session_transactions.session_id
          
LEFT OUTER JOIN (
              
SELECT blocking_session_id, COUNT(*) AS BlockingRequestCount FROM sys.dm_exec_requests GROUP BY blocking_session_id
          
) AS dm_exec_blockrequests ON sys.dm_exec_sessions.session_id=dm_exec_blockrequests.blocking_session_id
          
LEFT OUTER JOIN (
              
SELECT session_id, SUM(CASE WHEN is_open=1 THEN 1 ELSE 0 END) AS OpenCursorCount, SUM(CASE WHEN is_open=0 THEN 1 ELSE 0 END) AS ClosedCursorCount
              
FROM sys.dm_exec_cursors (0)
              
GROUP BY session_id
          
) AS dm_exec_cursors ON sys.dm_exec_sessions.session_id=dm_exec_cursors.session_id
          
LEFT OUTER JOIN (
              
SELECT
                  
dm_exec_requests.session_id,
                  
SUM(CONVERT(bigint, dm_exec_requests.open_transaction_count)) AS open_transaction_count,
                  
SUM(CONVERT(bigint, dm_exec_requests.open_resultset_count)) AS open_resultset_count,
                  
SUM(CASE WHEN dm_exec_requests.total_elapsed_time IS NULL THEN 0 ELSE 1 END) AS ActiveReqCount,
                  
SUM(CASE WHEN dm_exec_requests.blocking_session_id <> 0 THEN 1 ELSE 0 END) AS BlockedReqCount,
                  
SUM(CONVERT(bigint, dm_exec_requests.wait_time)) AS wait__time,
                  
SUM(CONVERT(bigint, dm_exec_requests.cpu_time)) AS cpu_time,
                  
SUM(CONVERT(bigint, dm_exec_requests.total_elapsed_time)) AS total_elapsed_time,
                  
SUM(CONVERT(bigint, dm_exec_requests.reads)) AS Reads,
                  
SUM(CONVERT(bigint, dm_exec_requests.writes)) AS Writes,
                  
SUM(CONVERT(bigint, dm_exec_requests.logical_reads)) AS logical_reads,
                  
SUM(CONVERT(bigint, dm_os_tasks.PendingIOCount)) AS PendingIOCount,
                  
SUM(CONVERT(bigint, dm_exec_requests.row_count)) AS row_count,
                  
SUM(CONVERT(bigint, dm_exec_requests.granted_query_memory*8)) AS granted_query_memory
              
FROM
                  
sys.dm_exec_requests
                  
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
                      
dm_exec_requests.request_id=dm_os_tasks.request_id
                      
AND dm_exec_requests.session_id=dm_os_tasks.session_id
              
GROUP BY dm_exec_requests.session_id
          
) AS dm_exec_requests ON sys.dm_exec_sessions.session_id=dm_exec_requests.session_id
      
WHERE sys.dm_exec_sessions.is_user_process=1
      
GROUP BY sys.dm_exec_sessions.session_id
  
) AS Sessions
  
LEFT OUTER JOIN (
      
SELECT
          
Requests.session_id,
          
Requests.database_id AS DatabaseID, databases.name AS DatabaseName,
          
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
          
(
              
SELECT
                  
Requests.session_id,
                  
Requests.database_id,
                  
CONVERT(BigInt, (Requests.cpu_time+1))*CONVERT(BigInt, (Requests.reads*10+Requests.writes*10+Requests.logical_reads+1)) AS score,
                  
Requests.sql_handle, Requests.plan_handle, Requests.statement_start_offset, Requests.statement_end_offset,
                  
ROW_NUMBER() OVER (
                      
PARTITION BY Requests.session_id
                      
ORDER BY CONVERT(BigInt, (Requests.cpu_time+1))*CONVERT(BigInt, (Requests.reads*10+Requests.writes*10+Requests.logical_reads+1)) DESC
                  
) AS RowNumber
              
FROM sys.dm_exec_requests AS Requests
          
) AS Requests
          
LEFT OUTER JOIN sys.databases ON requests.database_id=databases.database_id
          
OUTER APPLY sys.dm_exec_sql_text(sql_handle) AS Statements
          
OUTER APPLY sys.dm_exec_query_plan(plan_handle) AS QueryPlans
      
WHERE RowNumber=1
  
) AS PiggiestRequest ON Sessions.session_id=PiggiestRequest.session_id
ORDER BY
  
Sessions.ActiveReqCount DESC, Sessions.OpenTranCount DESC,
  
Sessions.BlockingRequestCount DESC, Sessions.BlockedReqCount DESC, Sessions.ConnectionCount DESC,
  
Sessions.login_name, Sessions.HOST_NAME, Sessions.program_name, Sessions.session_id

GO

ċ
usp_ComprehensiveConnectionSummary.sql
(26k)
Andy Hughes,
Aug 2, 2012, 7:06 AM
Comments