Event Session을 이용한 SQL OS Session Schedule check

Event Session을 이용한 SQL OS Session Schedule check

   

   

-       Version : SQL Server 2012

   

SQL Server 2012에서 이벤트를 사용하여 SQL Server의 SQL OS Schedule 상태에 대한 정보를 확인하여보자.

   

다음 스크립트를 사용하여 이벤트 정보를 확인한다. 이벤트는 QUERY_PROCESSING 정보이다.

SET NOCOUNT ON -- Fetch data for only SQL Server 2012 instances IF (SUBSTRING(CAST(SERVERPROPERTY ('ProductVersion') AS varchar(50)),1,CHARINDEX('.',CAST(SERVERPROPERTY ('ProductVersion') AS varchar(50)))-1) >= 11) BEGIN     -- Get UTC time difference for reporting event times local to server time     DECLARE @UTCDateDiff int = DATEDIFF(mi,GETUTCDATE(),GETDATE());          -- Store XML data retrieved in temp table      SELECT         TOP 1 CAST(xet.target_data AS XML) AS XMLDATA INTO #SystemHealthSessionData      FROM sys.dm_xe_session_targets xet         JOIN sys.dm_xe_sessions xe ON (xe.address = xet.event_session_address)      WHERE xe.name = 'system_health' AND xet.target_name = 'ring_buffer';       WITH CTE_HealthSession (EventXML) AS (         SELECT             C.query('.') as EventXML         FROM #SystemHealthSessionData a             CROSS APPLY a.XMLDATA.nodes('/RingBufferTarget/event') as T(C)      )         SELECT         DATEADD(mi,@UTCDateDiff,EventXML.value('(/event/@timestamp)[1]','datetime')) as [Event Time],         EventXML.value('(/event/data/value/queryProcessing/@maxWorkers)[1]','bigint') as [Max Workers],         EventXML.value('(/event/data/value/queryProcessing/@workersCreated)[1]','bigint') as [Workers Created],         EventXML.value('(/event/data/value/queryProcessing/@workersIdle)[1]','bigint') as [Idle Workers],         EventXML.value('(/event/data/value/queryProcessing/@pendingTasks)[1]','bigint') as [Pending Tasks],         EventXML.value('(/event/data/value/queryProcessing/@hasUnresolvableDeadlockOccurred)[1]','int') as [Unresolvable Deadlock],         EventXML.value('(/event/data/value/queryProcessing/@hasDeadlockedSchedulersOccurred)[1]','int') as [Deadlocked Schedulers]     FROM CTE_HealthSession     WHERE EventXML.value('(/event/@name)[1]', 'varchar(255)') = 'sp_server_diagnostics_component_result'         AND EventXML.value('(/event/data/text)[1]','varchar(255)') = 'QUERY_PROCESSING'     ORDER BY [Event Time];       DROP TABLE #SystemHealthSessionData   END

   

 

   

   

[참고자료]

http://troubleshootingsql.com/2013/07/16/powerview-and-system-health-sessionscheduler-health/

   

   

   

2013-09-04 / 강성욱 / http://sqlmvp.kr