/*****************************************************************************************
PREREQUISITE FUNCTION
******************************************************************************************/USE MASTERGO
CREATE FUNCTION ConvertStringToBinary ( @hexstring VARCHAR(100)) RETURNS BINARY(34) AS
BEGIN
RETURN(SELECT CAST('' AS XML).value('xs:hexBinary( substring(sql:variable("@hexstring"), sql:column("t.pos")) )', 'varbinary(max)')
FROM (SELECT CASE SUBSTRING(@hexstring, 1, 2) WHEN '0x' THEN 3 ELSE 0 END) AS t(pos)) END/***************************************************************************************
STEP 2: List the session which are currently waiting for resource
****************************************************************************************/SELECT node.parent_node_id AS Node_id,es.HOST_NAME,es.Login_name,CASE WHEN es.program_name LIKE '%SQLAgent - TSQL JobStep%' THEN
(
SELECT 'SQL AGENT JOB: '+name FROM msdb..sysjobs WHERE job_id=
MASTER.DBO.ConvertStringToBinary (LTRIM(RTRIM((SUBSTRING(es.program_name,CHARINDEX('(job',es.program_name,0)+4,35)))))
)
ELSE es.program_name END AS [Program Name] ,DB_NAME(er.database_id) AS DatabaseName,er.session_id, wt.blocking_session_id,wt.wait_duration_ms,wt.wait_type,wt.NoThread ,er.command,er.status,er.wait_resource,
er.open_transaction_count,
er.cpu_time,
er.total_elapsed_time AS ElapsedTime_ms,
er.percent_complete ,
er.reads,
er.writes,
er.logical_reads,wlgrp.name AS ResoursePool ,SUBSTRING (sqltxt.TEXT,(er.statement_start_offset/2) + 1,
((CASE WHEN er.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), sqltxt.TEXT)) * 2
ELSE er.statement_end_offset
END - er.statement_start_offset)/2) + 1) AS [Individual Query], sqltxt.TEXT AS [Batch Query] FROM (SELECT session_id, SUM(wait_duration_ms) AS wait_duration_ms,wait_type,blocking_session_id,COUNT(*) AS NoThread FROM SYS.DM_OS_WAITING_TASKS GROUP BY session_id, wait_type,blocking_session_id) wt INNER JOIN SYS.DM_EXEC_REQUESTS er ON wt.session_id=er.session_id INNER JOIN SYS.DM_EXEC_SESSIONS es ON es.session_id= er.session_idINNER JOIN SYS.DM_RESOURCE_GOVERNOR_WORKLOAD_GROUPS wlgrp ON wlgrp.group_id=er.group_id INNER JOIN (SELECT os.parent_node_id ,task_address FROM SYS.DM_OS_SCHEDULERS OS INNER JOIN SYS.DM_OS_WORKERS OSW ON OS.scheduler_address=OSW.scheduler_address
WHERE os.status='VISIBLE ONLINE' GROUP BY os.parent_node_id ,task_address ) node
ON node.task_address=er.task_address
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(er.sql_handle) AS sqltxt
WHERE sql_handle IS NOT NULL AND wt.wait_type NOT IN ('WAITFOR','BROKER_RECEIVE_WAITFOR')
GO
The Description of the columns in the result are given below.
| Column Name | Description |
| Node Id | NUMA node id . Can be mapped to the node id of the scheduler query. |
| Host_Name | Name of the computer from the connection is originated. |
| Login Name | Login used in the session to connect the database server |
| Program Name | Name of the program/application using this session. You can set the application name in the connection string. If this session is part of SQL server agent job, it will show the job name |
| Database Name | Current database of the session |
| Session Id | The session id |
| Blocking Session id | Session id blocking statement |
| wait_duration_ms | Total wait time for this wait type, in milliseconds. This time is inclusive of signal wait time |
| wait_type | Name of the wait type like SLEEP_TASK,CXPACKET etc |
| No of Thread | No of threads running on this session. If the session is in parallel execution |
| Command | Identifies the current type of command that is being processed like Select,insert,update,delete etc |
| Status | Status of the request. This can be of the following: Background,Running,Runnable,Sleeping and Suspended |
| Wait Resource | Resource for which the request is currently waiting |
| Open Transaction count | Number of transaction opened in this session |
| Cpu Time | CPU time in milliseconds that is used by the request. |
| Total Elapsed Time | Total time elapsed in milliseconds since the request arrived |
| Percent_Complete | Percent of work completed for certain operations like backup,restore rollback etc. |
| Reads | Number of reads performed by this request. |
| Writes | Number of writes performed by this request. |
| logical_reads | Number of logical reads performed by this request. |
| ResoursePool | Name of of Resource Governor Pool |
| Individual Query | current statement of the batch running on this session. |
| Batch Query | Current batch (procedure/set of sql statement) running on this session. |
/***************************************************************************************
STEP 3: List the session which are currently waiting/running
****************************************************************************************/SELECT node.parent_node_id AS Node_id,es.HOST_NAME,es.login_name,CASE WHEN es.program_name LIKE '%SQLAgent - TSQL JobStep%' THEN(SELECT 'SQL AGENT JOB: '+name FROM msdb..sysjobs WHERE job_id=ADMIN.DBO.ConvertStringToBinary (LTRIM(RTRIM((SUBSTRING(es.program_name,CHARINDEX('(job',es.program_name,0)+4,35)))))
)ELSE es.program_name END AS program_name ,DB_NAME(er.database_id) AS DatabaseName,er.session_id, wt.blocking_session_id,wt.wait_duration_ms,wt.wait_type,wt.NoThread ,er.command,er.status,er.wait_resource,er.open_transaction_count,er.cpu_time,er.total_elapsed_time AS ElapsedTime_ms,er.percent_complete ,er.reads,er.writes,er.logical_reads,wlgrp.name AS ResoursePool ,SUBSTRING (sqltxt.TEXT,(er.statement_start_offset/2) + 1, ((CASE WHEN er.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), sqltxt.TEXT)) * 2 ELSE er.statement_end_offset END - er.statement_start_offset)/2) + 1) AS [Individual Query],
sqltxt.TEXT AS [Batch Query]
FROM SYS.DM_EXEC_REQUESTS er INNER JOIN SYS.DM_EXEC_SESSIONS es ON es.session_id= er.session_idINNER JOIN SYS.DM_RESOURCE_GOVERNOR_WORKLOAD_GROUPS wlgrp ON wlgrp.group_id=er.group_id INNER JOIN (SELECT os.parent_node_id ,task_address FROM SYS.DM_OS_SCHEDULERS OS
INNER JOIN SYS.DM_OS_WORKERS OSW ON OS.scheduler_address=OSW.scheduler_address
WHERE os.status='VISIBLE ONLINE' GROUP BY os.parent_node_id ,task_address ) node ON node.task_address=er.task_address
LEFT JOIN (SELECT session_id, SUM(wait_duration_ms) AS wait_duration_ms,wait_type,blocking_session_id,COUNT(*) AS NoThread FROM SYS.DM_OS_WAITING_TASKS GROUP BY session_id, wait_type,blocking_session_id) wt ON wt.session_id=er.session_idCROSS apply SYS.DM_EXEC_SQL_TEXT(er.sql_handle) AS sqltxtWHERE sql_handle IS NOT NULL AND ISNULL(wt.wait_type ,'') NOT IN ('WAITFOR','BROKER_RECEIVE_WAITFOR')ORDER BY er.total_elapsed_time DESC
GO
The columns are same as we discussed in step 2 . I used to analyse the sessions with more total_elapsed_time and take appropriate actions like killing the session and look into the implementation. In most of the scenario (where server was running perfectly but all off sudden it become standstill) , I will be able fix the issue by following these steps. In the next part let us discuss about blocking session and session with open transaction which is not active.
If you liked this post, do like my page on FaceBook
