DIR Return Create A Forum - Home
       ---------------------------------------------------------
       ExcelSoft Database Professionals
  HTML https://esdbp.createaforum.com
       ---------------------------------------------------------
       *****************************************************
   DIR Return to: Scripts
       *****************************************************
       #Post#: 113--------------------------------------------------
       Wait Script 
       By: srinivasma_exceldbp Date: December 10, 2014, 5:39 am
       ---------------------------------------------------------
       -- Different Wait related Scripts
       SELECT * FROM sys.dm_os_wait_stats
       --DBCC sqlperf('sys.dm_os_wait_stats',clear)
       --<<<<<<<<<<----------------------------------------------------
       ------------->>>>>>>>>>--
       --Keeping it simple: get a delta
       -- note: Deltas are a *very* powerful technique!
       
       SELECT wait_type , waiting_tasks_count
       , wait_time_ms , max_wait_time_ms
       , signal_wait_time_ms
       INTO #OriginalWaitStatsSnapshot
       FROM sys.dm_os_wait_stats;
       GO
       --wait for x amount of time
       WAITFOR DELAY '00:00:02';
       GO
       --collect again
       SELECT wait_type , waiting_tasks_count
       , wait_time_ms , max_wait_time_ms
       , signal_wait_time_ms
       INTO #LatestWaitStatsSnapshot
       FROM sys.dm_os_wait_stats;
       GO
       --compare the results
       SELECT l.wait_type , (l.wait_time_ms - o.wait_time_ms) as
       accum_wait_ms
       FROM #OriginalWaitStatsSnapshot as o
       INNER JOIN #LatestWaitStatsSnapshot as l
       ON o.wait_type = l.wait_type
       WHERE l.wait_time_ms > o.wait_time_ms
       ORDER BY l.wait_time_ms DESC;
       GO
       --<<<<<<<<<<----------------------------------------------------
       ------------->>>>>>>>>>--
       --sys.dm_os_waiting_tasks
       
       SELECT w.session_id
       , w.wait_duration_ms
       , w.wait_type
       , w.blocking_session_id
       , w.resource_description
       , s.program_name
       , t.text
       , t.dbid
       , s.cpu_time
       , s.memory_usage
       FROM sys.dm_os_waiting_tasks as w
       INNER JOIN sys.dm_exec_sessions as s
       ON w.session_id = s.session_id
       INNER JOIN sys.dm_exec_requests as r
       ON s.session_id = r.session_id
       OUTER APPLY sys.dm_exec_sql_text (r.sql_handle) as t
       WHERE s.is_user_process = 1;
       --<<<<<<<<<<----------------------------------------------------
       ------------->>>>>>>>>>--
       --Setting up Extended Event session
       
       
       CREATE EVENT SESSION session_waits ON SERVER
       ADD EVENT sqlos.wait_info
       (ACTION (sqlserver.sql_text , sqlserver.plan_handle ,
       sqlserver.tsqlstack)
       WHERE sqlserver.session_id = 57 AND duration > 0)
       ADD TARGET package0.asynchronous_file_target
       (SET FILENAME = N'C:\temp\wait_stats.xel' , metadatafile =
       N'C:\temp\wait_stats.xem');
       ALTER EVENT SESSION session_waits ON SERVER STATE = START;
       --<<<<<<<<<<----------------------------------------------------
       ------------->>>>>>>>>>--
       --Setting up Extended Event session
       
       --SELECT Name FROM HumanResources.Department
       -- e.g., spid 53
       --Query to capture event data
       SELECT wait_type
       , SUM(duration) as total_duration
       , SUM(signal_duration) as total_signal_duration
       FROM
       ( SELECT
       
       CONVERT(xml,event_data).value('(/event/data/text)[1]','nvarchar(
       50)')
       as wait_type
       ,
       CONVERT(xml,event_data).value('(/event/data/value)[3]','int') as
       duration
       ,
       CONVERT(xml,event_data).value('(/event/data/value)[6]','int') as
       signal_duration
       FROM sys.fn_xe_file_target_read_file
       (N'C:\temp\wait_stats*.xel' ,
       N'C:\temp\wait_stats*.xem' , NULL , NULL)) as xyz
       GROUP BY wait_type;
       --<<<<<<<<<<----------------------------------------------------
       ------------->>>>>>>>>>--
       --Interpreting Preemptive Wait Types
       SELECT wait_type , waiting_tasks_count
       FROM sys.dm_os_wait_stats
       WHERE wait_type = '%PREEMPTIVE%'
       ORDER BY waiting_tasks_count DESC
       
       
       *****************************************************