(DIR) Return Create A Forum - Home --------------------------------------------------------- ExcelSoft Database Professionals (HTM) 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 *****************************************************