SQL Server: Part 2 : Approaching Database Server Performance Issues

In the Part 1, we have seen how quickly we can check the runnable task and I/O pending task on an SQL server instance. This  is very light weight script and it will give the result even if the server is under pressure and will give an over all state of the server at that moment.

The next step (Step2)  in my way of diagnosing is to check the session that are waiting of any resources. Below script will help us. This query required a function as prerequisite,  which will help us to display the SQL server agent job name if the session started by SQL server agent.

/*****************************************************************************************
           PREREQUISITE FUNCTION
******************************************************************************************/
USE MASTER
GO 
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_id
INNER 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.

If there is a session with very long wait_duration_ms and  not blocked by any other session and  not going away from the list in the subsequent execution of the same query, I will look into the program name,host name,login name and the statement that is running which will give me an idea about the session.Based on all these information, I might decide to kill that session and look into the implementation of that SQL batch. If the session is blocked, I will look into the blocking session using a different script which I will share later.(Refer this post)

The next step (Step 3)  is to list all session which are currently running on the server. I use below query to do that.

/***************************************************************************************

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_id
INNER 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_id
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

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