Initial SQL Server performance issues (2/4): List the sessions waiting for resources

In the initial SQL Server performance issue (1/4), we learned how to quickly check the number of tasks that are running on the server instance and the number of IO waits. This is a lightweight script that does not put any pressure on the server, even if the server is under high load and can get the results normally.

The 2nd step in problem detection is to get a session that waits on any resource. The following script will help us get this information. This query requires a pre-established function that displays a specific job name if the session is started by SQL Server Agent.

1 /*****************************************************************************************2 Prerequisite FUNCTION3 ******************************************************************************************/4 Use MASTER5 GO6CREATE FUNCTION convertstringtobinary (@hexstring VARCHAR ( -)7) RETURNS BINARY ( the) as8 BEGIN9 TenRETURN (SELECT CAST ("'As XML). Value ('xs:hexbinary (substring (sql:variable ("@hexstring"), Sql:column ("T.pos" ))','varbinary (max)') OneFrom (SELECT case SUBSTRING (@hexstring,1,2) when'0x'Then3ELSE0END) As T (POS)) A END - /*************************************************************************************** - STEP 2:list The session which is currently waiting for resource the ****************************************************************************************/ - SELECT node.parent_node_id as node_id, - es. HOST_NAME, - es. Login_name, +Case if es.program_name like'%sqlagent-tsql jobstep%' Then -          ( +SELECT'SQL AGENT JOB:'+name from msdb. Sysjobs WHERE job_id= AMASTER. Dbo. Convertstringtobinary (LTRIM (RTRIM (SUBSTRING (Es.program_name,charindex ('(Job', Es.program_name,0)+4, *))))) at           ) - 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, in Wt.wait_type, - wt. Nothread, to Er.command, + Er.status, - Er.wait_resource, the Er.open_transaction_count, * Er.cpu_time, $ Er.total_elapsed_time as Elapsedtime_ms,Panax Notoginseng Er.percent_complete, - Er.reads, the Er.writes, + Er.logical_reads, A Wlgrp.name as Resoursepool, theSUBSTRING (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 theWait_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) WTWuyiINNER 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_id theINNER 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 WuSYS. 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 AboutOn node.task_address=er.task_address $ Cross APPLY SYS. Dm_exec_sql_text (Er.sql_handle) as Sqltxt -WHERE sql_handle is isn't NULL and Wt.wait_type not in ('WAITFOR','broker_receive_waitfor') -GO

View Code

each column of output results is described below:

• NODE_ID NUMA Node ID. Node mappings that can be queried by the dispatcher.
• HOST_NAME the name of the computer on which the connection is established.
• Login_name the session user name that is connected to the database server.
• Program name uses the name of the session's counterpart. You can set the program name in the connection string. If the session is part of a SQL Server Agent, the job name is displayed.
• The current database name of the DatabaseName session.
• session_id session ID.
• The session ID of the BLOCKING_SESSION_ID blocking statement.
• Wait_duration_ms wait time in milliseconds. This time does not include the signal wait time (signal wait times).
• Wait_type wait for the type name, for example: Sleep_task,cxpacket, and so on.
• Nothread the number of threads in the current session, if the current session is parallel execution (parallel execution).
• The command identifies commands of the current type, that is, T-SQL statements, such as Select,insert,update,delete.
• Status request state: Background,running,runnable,sleeping and Suspended.
• Wait_resource requests the currently waiting resource.
• Open_transaction_count the number of transactions opened by the current session.
• Cpu_time the CPU time, in milliseconds, that the request uses.
• Elapsedtime_ms the CPU time, in milliseconds, that has elapsed since the request arrived.
• PERCENT_COMPLETE specifies the progress of work completion for the operation, such as backup, restore, rollback, and so on.
• The reads request to perform the reading.
• Writes the write number of the request execution.
• Logical_reads the logical reading of the request execution.
• Resoursepool resource Management pool name.
• Individual Query runs a batch SQL statement in a session.
• Batch Query runs batches (stored procedures/series of statements) in a session.

After the above query executes multiple times, the output has a long Wait_duration_ms session, which is not blocked by other sessions and is always in the output. We have to look at the program name of this session, host name, login user name, and the corresponding execution statement, the specific operation caused by what. Based on this information, we can choose to terminate the session, and then analyze the specific execution statement. If the session is blocked, we need to use a different statement to find the blocked session.

The 3rd step is to list the sessions that are running on the server.

1 /***************************************************************************************2 STEP 3:list The session which is currently waiting/running3 ****************************************************************************************/4 SELECT node.parent_node_id as node_id,5 es. HOST_NAME,6 Es.login_name,7Case if es.program_name like'%sqlagent-tsql jobstep%' Then8(SELECT'SQL AGENT JOB:'+name from msdb. Sysjobs WHERE job_id=dbo. Convertstringtobinary (LTRIM (RTRIM (SUBSTRING (Es.program_name,charindex ('(Job', Es.program_name,0)+4, *)))))9 ) ELSE Es.program_name END as Program_name,Ten db_name (er.database_id) as DatabaseName, One er.session_id, A wt.blocking_session_id, - Wt.wait_duration_ms, - Wt.wait_type, the wt. Nothread, - Er.command, - Er.status, - Er.wait_resource, + Er.open_transaction_count, - Er.cpu_time, + Er.total_elapsed_time as Elapsedtime_ms, A Er.percent_complete, at 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 inEnd-er.statement_start_offset)/2) +1) as [individual Query], - Sqltxt. TEXT as [Batch Query] to  from +SYS. Dm_exec_requests er INNER JOIN SYS. Dm_exec_sessions es on es.session_id=er.session_id -INNER JOIN SYS. Dm_resource_governor_workload_groups Wlgrp on wlgrp.group_id=er.group_id the 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_addressPanax Notoginseng Left JOIN - (SELECT session_id, SUM (Wait_duration_ms) as theWait_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 AOn wt.session_id=er.session_id the Cross apply SYS. Dm_exec_sql_text (Er.sql_handle) as Sqltxt +WHERE sql_handle is not NULL and ISNULL (Wt.wait_type,"') not in -('WAITFOR','broker_receive_waitfor') $ ORDER by Er.total_elapsed_time DESC $  -GO

View Code

The output column here is exactly the same as the 2nd step, and I'll analyze total_elapsed_time for a longer session, consider terminating these sessions as appropriate, and parse the corresponding execution SQL statements. In most cases (the server is running consistently and suddenly stuck), using the steps above will solve the problem. In the next article we will look at the blocked session, as well as the session that opened the inactive transaction.

Initial SQL Server performance issues (2/4): List the sessions waiting for resources