Pages

Friday, 21 September 2012

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.

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_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_addressWHERE os.status='VISIBLE ONLINE' GROUP BY os.parent_node_id ,task_address ) node ON node.task_address=er.task_addressLEFT 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 DESCGOThe 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