--select * from sys.traces
-- Plan cache info
SELECT
[objtype],
COUNT_BIG(*) AS [Plans],
SUM(CAST([size_in_bytes] AS DECIMAL(18, 2))) / 1024 / 1024 AS [PlansMB],
SUM(CASE [usecounts]
WHEN 1
THEN 1
ELSE 0
END) AS [SingleUsePlans],
SUM(CASE [usecounts]
WHEN 1
THEN CAST([size_in_bytes] AS DECIMAL(18, 2))
ELSE 0
END) / 1024 / 1024 AS [SingleUsePlansMB]
FROM
[sys].[dm_exec_cached_plans]
GROUP BY
[objtype] WITH ROLLUP
ORDER BY
[Plans] DESC;
/*
-- single use plans cleanup
DECLARE @plan_handle VARBINARY(64);
DECLARE CUR CURSOR FAST_FORWARD
FOR SELECT
[plan_handle]
FROM
[sys].[dm_exec_cached_plans]
WHERE [usecounts] = 1;
OPEN CUR;
FETCH NEXT FROM CUR INTO @plan_handle;
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC FREEPROCCACHE(@plan_handle);
FETCH NEXT FROM CUR INTO @plan_handle;
END;
CLOSE CUR;
DEALLOCATE CUR;
*/
-- memory grant: watch for grant_time is null, waiting for memory
SELECT
*
FROM
[sys].[dm_exec_query_memory_grants] AS [g]
CROSS APPLY
[sys].[dm_exec_sql_text]([g].[sql_handle]) AS [qt]
CROSS APPLY
[sys].[dm_exec_query_plan]([g].[plan_handle]) AS [qp]
WHERE [g].[session_id] <> @@SPID
ORDER BY
[query_cost] DESC;
-- page life expectancy
WITH CTE_A
AS (SELECT
RIGHT('00'+CAST([memory_node_id] AS VARCHAR(3)), 3) AS [memory_node_id],
CAST((SUM([mc].[pages_kb]) / 1024.0) AS DECIMAL(15, 2)) AS [memory_usage_mb]
FROM
[sys].[dm_os_memory_clerks] AS [mc] WITH (NOLOCK)
WHERE [mc].[type] = 'MEMORYCLERK_SQLBUFFERPOOL'
GROUP BY
[memory_node_id]),
CTE_B
AS (SELECT
[object_name],
[instance_name],
[cntr_value] AS [page_life_expectancy]
FROM
[sys].[dm_os_performance_counters] WITH (NOLOCK)
WHERE [object_name] LIKE N'%Buffer Node%' -- Handles named instances
AND [counter_name] = N'Page life expectancy')
SELECT
[object_name],
[memory_usage_mb],
[page_life_expectancy],
CAST([memory_usage_mb] / 1024 / 4 * 300 AS BIGINT) AS [min_ple]
FROM
[CTE_A] AS [A]
JOIN
[CTE_B] AS [B] ON [A].[memory_node_id] = [B].[instance_name];
-- running cursors
SELECT
[qs].[plan_handle],
[s].[session_id],
[r].[cpu_time],
[r].[start_time],
[r].[status],
[r].[command],
[r].[blocking_session_id],
[r].[wait_time],
[r].[wait_resource],
[r].[last_wait_type],
[s].[program_name],
[s].[client_interface_name],
[s].[login_name],
[c].[client_net_address],
[qp].[query_plan],
[r].[plan_handle],
[r].[granted_query_memory] * 8 / 1024 AS [granted_memory_mb],
[r].[logical_reads],
[r].[logical_reads]*8/1024 AS [logical_reads_MB],
[r].[writes],
[r].[reads],
[r].[row_count],
SUBSTRING([qt].[text], [r].[statement_start_offset]/2, (CASE
WHEN [r].[statement_end_offset] = -1
THEN LEN(CONVERT(NVARCHAR(MAX), [qt].[text]))*2
ELSE [r].[statement_end_offset]
END-[r].[statement_start_offset])/2+2) AS [statement],
[r].[percent_complete],
SUBSTRING([cqt].[text], [cur].[statement_start_offset]/2, (CASE
WHEN [cur].[statement_end_offset] = -1
THEN LEN(CONVERT(NVARCHAR(MAX), [cqt].[text]))*2
ELSE [cur].[statement_end_offset]
END-[cur].[statement_start_offset])/2+2) AS [cursor_statement],
[cur].[cursor_id],
[cur].[plan_generation_num],
[cur].[dormant_duration],
CAST(r.context_info as varchar(max)) as [context_info]
FROM
[sys].[dm_exec_requests] [r]
JOIN
[sys].[dm_exec_sessions] [s] ON [s].[session_id] = [r].[session_id]
JOIN
[sys].[dm_exec_connections] [c] ON [s].[session_id] = [c].[session_id]
CROSS APPLY
[sys].[dm_exec_sql_text]([r].[sql_handle]) [qt]
CROSS APPLY
[sys].[dm_exec_cursors]([r].[session_id]) [cur]
CROSS APPLY
[sys].[dm_exec_sql_text]([cur].[sql_handle]) [cqt]
JOIN
[sys].[dm_exec_query_stats] [qs] ON [cur].[sql_handle] = [qs].[sql_handle]
AND [cur].[statement_start_offset] = [qs].[statement_start_offset]
AND [cur].[statement_end_offset] = [qs].[statement_end_offset]
CROSS APPLY
[sys].[dm_exec_query_plan]([qs].[plan_handle]) [qp]
WHERE [s].[session_id] <> @@SPID
AND [qt].[text] LIKE 'FETCH_API_CURSOR%'
ORDER BY
[s].[session_id] DESC,
[cur].[dormant_duration] DESC;
GO
-- running tasks
SELECT
CAST(r.context_info as varchar(max)) as [context_info],
[s].[session_id],
[r].[cpu_time],
[r].[start_time],
[r].[status],
[r].[command],
[r].[blocking_session_id],
[r].[wait_time],
[r].[wait_resource],
[r].[last_wait_type],
[s].[program_name],
[s].[client_interface_name],
[s].[login_name],
[c].[client_net_address],
[qp].[query_plan],
[r].[plan_handle],
[r].[granted_query_memory] * 8 / 1024 AS [granted_memory_mb],
[r].[logical_reads],
[r].[logical_reads]*8/1024 AS [logical_reads_MB],
[r].[writes],
[r].[reads],
[r].[row_count],
SUBSTRING([qt].[text], [r].[statement_start_offset]/2, (CASE
WHEN [r].[statement_end_offset] = -1
THEN LEN(CONVERT(NVARCHAR(MAX), [qt].[text]))*2
ELSE [r].[statement_end_offset]
END-[r].[statement_start_offset])/2+2) AS [statement],
[r].[percent_complete]
FROM
[sys].[dm_exec_requests] [r]
JOIN
[sys].[dm_exec_sessions] [s] ON [s].[session_id] = [r].[session_id]
JOIN
[sys].[dm_exec_connections] [c] ON [s].[session_id] = [c].[session_id]
CROSS APPLY
[sys].[dm_exec_sql_text]([r].[sql_handle]) [qt]
CROSS APPLY
[sys].[dm_exec_query_plan]([r].[plan_handle]) [qp]
WHERE [s].[session_id] <> @@SPID
ORDER BY
[r].[cpu_time] DESC;
GO
-- transaction info + log info
SELECT
[DTST].[session_id],
[DES].[login_name] AS [Login Name],
DB_NAME([DTDT].[database_id]) AS [Database],
[DTDT].[database_transaction_begin_time] AS [Begin Time],
DATEDIFF([ms], [DTDT].[database_transaction_begin_time], GETDATE()) AS [Duration ms],
CASE [DTAT].[transaction_type]
WHEN 1
THEN 'Read/write'
WHEN 2
THEN 'Read-only'
WHEN 3
THEN 'System'
WHEN 4
THEN 'Distributed'
END AS [Transaction Type],
CASE [DTAT].[transaction_state]
WHEN 0
THEN 'Not fully initialized'
WHEN 1
THEN 'Initialized, not started'
WHEN 2
THEN 'Active'
WHEN 3
THEN 'Ended'
WHEN 4
THEN 'Commit initiated'
WHEN 5
THEN 'Prepared, awaiting resolution'
WHEN 6
THEN 'Committed'
WHEN 7
THEN 'Rolling back'
WHEN 8
THEN 'Rolled back'
END AS [Transaction State],
[DTDT].[database_transaction_log_record_count] AS [Log Records],
[DTDT].[database_transaction_log_bytes_used] AS [Log Bytes Used],
[DTDT].[database_transaction_log_bytes_reserved] AS [Log Bytes RSVPd],
[DEST].[text] AS [Last Transaction Text],
[DEQP].[query_plan] AS [Last Query Plan]
FROM
[sys].[dm_tran_database_transactions] [DTDT]
INNER JOIN
[sys].[dm_tran_session_transactions] [DTST] ON [DTST].[transaction_id] = [DTDT].[transaction_id]
INNER JOIN
[sys].[dm_tran_active_transactions] [DTAT] ON [DTST].[transaction_id] = [DTAT].[transaction_id]
INNER JOIN
[sys].[dm_exec_sessions] [DES] ON [DES].[session_id] = [DTST].[session_id]
INNER JOIN
[sys].[dm_exec_connections] DEC ON DEC.[session_id] = [DTST].[session_id]
LEFT JOIN
[sys].[dm_exec_requests] [DER] ON [DER].[session_id] = [DTST].[session_id]
CROSS APPLY
[sys].[dm_exec_sql_text](DEC.[most_recent_sql_handle]) AS [DEST]
OUTER APPLY
[sys].[dm_exec_query_plan]([DER].[plan_handle]) AS [DEQP]
WHERE [des].[session_id] <> @@SPID
ORDER BY
[DTDT].[database_transaction_log_bytes_used] DESC;
-- ORDER BY [Duration ms] DESC;
-- tempdb usage
WITH task_space_usage
AS (
-- SUM alloc/delloc pages
SELECT
[session_id],
[request_id],
SUM([internal_objects_alloc_page_count]) AS [alloc_pages],
SUM([internal_objects_dealloc_page_count]) AS [dealloc_pages]
FROM
[sys].[dm_db_task_space_usage] WITH (NOLOCK)
WHERE [session_id] <> @@SPID
GROUP BY
[session_id],
[request_id])
SELECT
[TSU].[session_id],
[TSU].[alloc_pages] * 1.0 / 128 AS [internal object MB space],
[TSU].[dealloc_pages] * 1.0 / 128 AS [internal object dealloc MB space],
[EST].text,
-- Extract statement from sql text
ISNULL(NULLIF(SUBSTRING([EST].text, [ERQ].[statement_start_offset]/2,
CASE
WHEN [ERQ].[statement_end_offset] < [ERQ].[statement_start_offset]
THEN 0
ELSE([ERQ].[statement_end_offset]-[ERQ].[statement_start_offset])/2+2
END), ''), [EST].text) AS [statement text],
[EQP].[query_plan]
FROM
[task_space_usage] AS [TSU]
INNER JOIN
[sys].[dm_exec_requests] [ERQ] WITH (NOLOCK) ON [TSU].[session_id] = [ERQ].[session_id]
AND [TSU].[request_id] = [ERQ].[request_id]
OUTER APPLY
[sys].[dm_exec_sql_text]([ERQ].[sql_handle]) AS [EST]
OUTER APPLY
[sys].[dm_exec_query_plan]([ERQ].[plan_handle]) AS [EQP]
WHERE([EST].text IS NOT NULL
OR [EQP].[query_plan] IS NOT NULL)
AND ([TSU].[alloc_pages] > 0
OR [TSU].[dealloc_pages] > 0)
AND [ERQ].[session_id] <> @@SPID
ORDER BY
3 DESC,
5 DESC;
-- waiting tasks
SELECT
[wt].[session_id],
[wt].[exec_context_id],
[wt].[wait_duration_ms],
[wt].[wait_type],
[wt].[blocking_session_id],
[wt].[blocking_exec_context_id],
[wt].[resource_description],
[r].[command],
[r].[cpu_time],
[r].[total_elapsed_time],
[r].[reads],
[r].[writes],
[r].[logical_reads],
[r].[logical_reads]*8/1024 AS [logical_reads_MB],
[r].[row_count],
[r].[query_hash],
[s].[program_name],
[s].[client_interface_name],
[s].[login_name],
SUBSTRING([qt].[text], [r].[statement_start_offset]/2, (CASE
WHEN [r].[statement_end_offset] = -1
THEN LEN(CONVERT(NVARCHAR(MAX), [qt].[text]))*2
ELSE [r].[statement_end_offset]
END-[r].[statement_start_offset])/2+2) AS [statement],
[qp].[query_plan]
FROM
[sys].[dm_os_waiting_tasks] AS [wt]
LEFT OUTER JOIN
[sys].[dm_exec_requests] AS [r] ON [wt].[session_id] = [r].[session_id]
LEFT OUTER JOIN
[sys].[dm_exec_sessions] AS [s] ON [s].[session_id] = [r].[session_id]
OUTER APPLY
[sys].[dm_exec_sql_text]([r].[sql_handle]) AS [qt]
OUTER APPLY
[sys].[dm_exec_query_plan]([r].[plan_handle]) AS [qp]
WHERE [s].[is_user_process] = 1
AND [s].[session_id] <> @@SPID
ORDER BY
[s].[session_id];
WITH [Latches]
AS (SELECT [latch_class],
[wait_time_ms] / 1000.0 AS [WaitS],
[waiting_requests_count] AS [WaitCount],
100.0 * [wait_time_ms] / SUM([wait_time_ms]) OVER() AS [Percentage],
ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum]
FROM [sys].[dm_os_latch_stats]
WHERE [latch_class] NOT IN(N'BUFFER')
AND [wait_time_ms] > 0)
SELECT [W1].[latch_class] AS [LatchClass],
CAST([W1].[WaitS] AS decimal(14, 2)) AS [Wait_S],
[W1].[WaitCount] AS [WaitCount],
CAST([W1].[Percentage] AS decimal(14, 2)) AS [Percentage],
CAST(([W1].[WaitS] / [W1].[WaitCount]) AS decimal(14, 4)) AS [AvgWait_S]
FROM [Latches] AS [W1]
INNER JOIN [Latches] AS [W2] ON [W2].[RowNum] <= [W1].[RowNum]
WHERE [W1].[WaitCount] > 0
GROUP BY [W1].[RowNum],
[W1].[latch_class],
[W1].[WaitS],
[W1].[WaitCount],
[W1].[Percentage]
HAVING SUM([W2].[Percentage]) - [W1].[Percentage] < 95; -- percentage threshold
GO
WITH Tasks
AS (SELECT [session_id],
[wait_type],
[wait_duration_ms],
[blocking_session_id],
[resource_description],
[PageID] = CAST(RIGHT([resource_description], LEN([resource_description])-CHARINDEX(':', [resource_description], 3)) AS int)
FROM [sys].[dm_os_waiting_tasks]
WHERE [wait_type] LIKE 'PAGE%LATCH_%'
AND [resource_description] LIKE '2:%')
SELECT [session_id],
[wait_type],
[wait_duration_ms],
[blocking_session_id],
[resource_description],
[ResourceType] = CASE
WHEN [PageID] = 1
OR [PageID] % 8088 = 0
THEN 'Is PFS Page'
WHEN [PageID] = 2
OR [PageID] % 511232 = 0
THEN 'Is GAM Page'
WHEN [PageID] = 3
OR ([PageID] - 1) % 511232 = 0
THEN 'Is SGAM Page'
ELSE 'Is Not PFS, GAM, or SGAM page'
END
FROM [Tasks];
--select CAST(message_body as xml), queuing_order from msdb..DeadlockQueue order by queuing_order desc
WITH [Waits]
AS (SELECT [wait_type],
[wait_time_ms] / 1000.0 AS [WaitS],
([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS],
[signal_wait_time_ms] / 1000.0 AS [SignalS],
[waiting_tasks_count] AS [WaitCount],
100.0 * [wait_time_ms] / SUM([wait_time_ms]) OVER() AS [Percentage],
ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum]
FROM [sys].[dm_os_wait_stats]
WHERE [wait_type] NOT IN
(N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR', N'BROKER_TASK_STOP', N'BROKER_TO_FLUSH', N'BROKER_TRANSMITTER', N'CHECKPOINT_QUEUE', N'CHKPT', N'CLR_AUTO_EVENT', N'CLR_MANUAL_EVENT', N'CLR_SEMAPHORE',
-- Maybe uncomment these four if you have mirroring issues
N'DBMIRROR_DBM_EVENT', N'DBMIRROR_EVENTS_QUEUE', N'DBMIRROR_WORKER_QUEUE', N'DBMIRRORING_CMD', N'DIRTY_PAGE_POLL', N'DISPATCHER_QUEUE_SEMAPHORE', N'EXECSYNC', N'FSAGENT', N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX',
N'PREEMPTIVE_XE_DISPATCHER',
-- Maybe uncomment these six if you have AG issues
N'HADR_CLUSAPI_CALL', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION', N'HADR_LOGCAPTURE_WAIT', N'HADR_NOTIFICATION_DEQUEUE', N'HADR_TIMER_TASK', N'HADR_WORK_QUEUE', N'KSOURCE_WAKEUP', N'LAZYWRITER_SLEEP', N'LOGMGR_QUEUE', N'MEMORY_ALLOCATION_EXT', N'ONDEMAND_TASK_QUEUE', N'PREEMPTIVE_XE_GETTARGETSTATE', N'PWAIT_ALL_COMPONENTS_INITIALIZED', N'PWAIT_DIRECTLOGCONSUMER_GETNEXT', N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', N'QDS_ASYNC_QUEUE', N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP', N'QDS_SHUTDOWN_QUEUE', N'REDO_THREAD_PENDING_WORK', N'REQUEST_FOR_DEADLOCK_SEARCH', N'RESOURCE_QUEUE', N'SERVER_IDLE_CHECK', N'SLEEP_BPOOL_FLUSH', N'SLEEP_DBSTARTUP', N'SLEEP_DCOMSTARTUP', N'SLEEP_MASTERDBREADY', N'SLEEP_MASTERMDREADY', N'SLEEP_MASTERUPGRADED', N'SLEEP_MSDBSTARTUP', N'SLEEP_SYSTEMTASK', N'SLEEP_TASK', N'SLEEP_TEMPDBSTARTUP', N'SNI_HTTP_ACCEPT', N'SP_SERVER_DIAGNOSTICS_SLEEP', N'SQLTRACE_BUFFER_FLUSH', N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', N'SQLTRACE_WAIT_ENTRIES', N'WAIT_FOR_RESULTS', N'WAITFOR', N'WAITFOR_TASKSHUTDOWN', N'WAIT_XTP_RECOVERY', N'WAIT_XTP_HOST_WAIT', N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG', N'WAIT_XTP_CKPT_CLOSE', N'XE_DISPATCHER_JOIN', N'XE_DISPATCHER_WAIT', N'XE_TIMER_EVENT'
)
AND [waiting_tasks_count] > 0)
SELECT MAX([W1].[wait_type]) AS [WaitType],
CAST(MAX([W1].[WaitS]) AS decimal(16, 2)) AS [Wait_S],
CAST(MAX([W1].[ResourceS]) AS decimal(16, 2)) AS [Resource_S],
CAST(MAX([W1].[SignalS]) AS decimal(16, 2)) AS [Signal_S],
MAX([W1].[WaitCount]) AS [WaitCount],
CAST(MAX([W1].[Percentage]) AS decimal(5, 2)) AS [Percentage],
CAST((MAX([W1].[WaitS]) / MAX([W1].[WaitCount])) AS decimal(16, 4)) AS [AvgWait_S],
CAST((MAX([W1].[ResourceS]) / MAX([W1].[WaitCount])) AS decimal(16, 4)) AS [AvgRes_S],
CAST((MAX([W1].[SignalS]) / MAX([W1].[WaitCount])) AS decimal(16, 4)) AS [AvgSig_S],
CAST('https://www.sqlskills.com/help/waits/'+MAX([W1].[wait_type]) AS xml) AS [Help/Info URL]
FROM [Waits] AS [W1]
INNER JOIN [Waits] AS [W2] ON [W2].[RowNum] <= [W1].[RowNum]
GROUP BY [W1].[RowNum]
HAVING SUM([W2].[Percentage]) - MAX([W1].[Percentage]) < 95; -- percentage threshold
GO
-- Baseline
IF EXISTS
(
SELECT *
FROM [tempdb].[sys].[objects]
WHERE [name] = N'##TempSpinlockStats1'
)
DROP TABLE [##TempSpinlockStats1];
GO
SELECT *
INTO [##TempSpinlockStats1]
FROM [sys].[dm_os_spinlock_stats]
WHERE [collisions] > 0
ORDER BY [name];
GO
WAITFOR DELAY '00:00:01';
-- Capture updated stats
IF EXISTS
(
SELECT *
FROM [tempdb].[sys].[objects]
WHERE [name] = N'##TempSpinlockStats2'
)
DROP TABLE [##TempSpinlockStats2];
GO
SELECT *
INTO [##TempSpinlockStats2]
FROM [sys].[dm_os_spinlock_stats]
WHERE [collisions] > 0
ORDER BY [name];
GO
-- Diff them
SELECT '***' AS [New],
[ts2].[name] AS [Spinlock],
[ts2].[collisions] AS [DiffCollisions],
[ts2].[spins] AS [DiffSpins],
[ts2].[spins_per_collision] AS [SpinsPerCollision],
[ts2].[sleep_time] AS [DiffSleepTime],
[ts2].[backoffs] AS [DiffBackoffs]
FROM [##TempSpinlockStats2] AS [ts2]
LEFT OUTER JOIN [##TempSpinlockStats1] AS [ts1] ON [ts2].[name] = [ts1].[name]
WHERE [ts1].[name] IS NULL
UNION
SELECT '' AS [New],
[ts2].[name] AS [Spinlock],
[ts2].[collisions] - [ts1].[collisions] AS [DiffCollisions],
[ts2].[spins] - [ts1].[spins] AS [DiffSpins],
CASE([ts2].[spins] - [ts1].[spins])
WHEN 0
THEN 0
ELSE([ts2].[spins] - [ts1].[spins]) / ([ts2].[collisions] - [ts1].[collisions])
END AS [SpinsPerCollision],
[ts2].[sleep_time] - [ts1].[sleep_time] AS [DiffSleepTime],
[ts2].[backoffs] - [ts1].[backoffs] AS [DiffBackoffs]
FROM [##TempSpinlockStats2] AS [ts2]
LEFT OUTER JOIN [##TempSpinlockStats1] AS [ts1] ON [ts2].[name] = [ts1].[name]
WHERE [ts1].[name] IS NOT NULL
AND [ts2].[collisions] - [ts1].[collisions] > 0
ORDER BY [New] DESC,
[Spinlock] ASC;
GO
/*============================================================================
File: WaitStats2014.sql
Summary: 24-hour snapshot of wait stats
SQL Server Versions: 2005 onwards
------------------------------------------------------------------------------
Written by Paul S. Randal, SQLskills.com
(c) 2014, SQLskills.com. All rights reserved.
For more scripts and sample code, check out
http://www.SQLskills.com
You may alter this code for your own *non-commercial* purposes. You may
republish altered code as long as you include this copyright and give due
credit, but you must obtain prior permission before blogging this code.
THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF
ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED
TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
PARTICULAR PURPOSE.
============================================================================*/
IF EXISTS
(
SELECT *
FROM [tempdb].[sys].[objects]
WHERE [name] = N'##SQLskillsStats1'
)
DROP TABLE [##SQLskillsStats1];
IF EXISTS
(
SELECT *
FROM [tempdb].[sys].[objects]
WHERE [name] = N'##SQLskillsStats2'
)
DROP TABLE [##SQLskillsStats2];
GO
SELECT [wait_type],
[waiting_tasks_count],
[wait_time_ms],
[max_wait_time_ms],
[signal_wait_time_ms]
INTO [##SQLskillsStats1]
FROM [sys].[dm_os_wait_stats];
GO
WAITFOR DELAY '00:00:01';
GO
SELECT [wait_type],
[waiting_tasks_count],
[wait_time_ms],
[max_wait_time_ms],
[signal_wait_time_ms]
INTO [##SQLskillsStats2]
FROM [sys].[dm_os_wait_stats];
GO
WITH [DiffWaits]
AS (
SELECT
-- Waits that weren't in the first snapshot
[ts2].[wait_type],
[ts2].[wait_time_ms],
[ts2].[signal_wait_time_ms],
[ts2].[waiting_tasks_count]
FROM [##SQLskillsStats2] AS [ts2]
LEFT OUTER JOIN [##SQLskillsStats1] AS [ts1] ON [ts2].[wait_type] = [ts1].[wait_type]
WHERE [ts1].[wait_type] IS NULL
AND [ts2].[wait_time_ms] > 0
UNION
SELECT
-- Diff of waits in both snapshots
[ts2].[wait_type],
[ts2].[wait_time_ms] - [ts1].[wait_time_ms] AS [wait_time_ms],
[ts2].[signal_wait_time_ms] - [ts1].[signal_wait_time_ms] AS [signal_wait_time_ms],
[ts2].[waiting_tasks_count] - [ts1].[waiting_tasks_count] AS [waiting_tasks_count]
FROM [##SQLskillsStats2] AS [ts2]
LEFT OUTER JOIN [##SQLskillsStats1] AS [ts1] ON [ts2].[wait_type] = [ts1].[wait_type]
WHERE [ts1].[wait_type] IS NOT NULL
AND [ts2].[waiting_tasks_count] - [ts1].[waiting_tasks_count] > 0
AND [ts2].[wait_time_ms] - [ts1].[wait_time_ms] > 0),
[Waits]
AS (SELECT [wait_type],
[wait_time_ms] / 1000.0 AS [WaitS],
([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS],
[signal_wait_time_ms] / 1000.0 AS [SignalS],
[waiting_tasks_count] AS [WaitCount],
100.0 * [wait_time_ms] / SUM([wait_time_ms]) OVER() AS [Percentage],
ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum]
FROM [DiffWaits]
WHERE [wait_type] NOT IN(N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR', N'BROKER_TASK_STOP', N'BROKER_TO_FLUSH', N'BROKER_TRANSMITTER', N'CHECKPOINT_QUEUE', N'CHKPT', N'CLR_AUTO_EVENT', N'CLR_MANUAL_EVENT', N'CLR_SEMAPHORE', N'DBMIRROR_DBM_EVENT', N'DBMIRROR_EVENTS_QUEUE', N'DBMIRROR_WORKER_QUEUE', N'DBMIRRORING_CMD', N'DIRTY_PAGE_POLL', N'DISPATCHER_QUEUE_SEMAPHORE', N'EXECSYNC', N'FSAGENT', N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX', N'HADR_CLUSAPI_CALL', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION', N'HADR_LOGCAPTURE_WAIT', N'HADR_NOTIFICATION_DEQUEUE', N'HADR_TIMER_TASK', N'HADR_WORK_QUEUE', N'KSOURCE_WAKEUP', N'LAZYWRITER_SLEEP', N'LOGMGR_QUEUE', N'ONDEMAND_TASK_QUEUE', N'PWAIT_ALL_COMPONENTS_INITIALIZED', N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP', N'REQUEST_FOR_DEADLOCK_SEARCH', N'RESOURCE_QUEUE', N'SERVER_IDLE_CHECK', N'SLEEP_BPOOL_FLUSH', N'SLEEP_DBSTARTUP', N'SLEEP_DCOMSTARTUP', N'SLEEP_MASTERDBREADY', N'SLEEP_MASTERMDREADY', N'SLEEP_MASTERUPGRADED', N'SLEEP_MSDBSTARTUP', N'SLEEP_SYSTEMTASK', N'SLEEP_TASK', N'SLEEP_TEMPDBSTARTUP', N'SNI_HTTP_ACCEPT', N'SP_SERVER_DIAGNOSTICS_SLEEP', N'SQLTRACE_BUFFER_FLUSH', N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', N'SQLTRACE_WAIT_ENTRIES', N'WAIT_FOR_RESULTS', N'WAITFOR', N'WAITFOR_TASKSHUTDOWN', N'WAIT_XTP_HOST_WAIT', N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG', N'WAIT_XTP_CKPT_CLOSE', N'XE_DISPATCHER_JOIN', N'XE_DISPATCHER_WAIT', N'XE_TIMER_EVENT', N'QDS_SHUTDOWN_QUEUE'))
SELECT [W1].[wait_type] AS [WaitType],
CAST([W1].[WaitS] AS decimal(16, 2)) AS [Wait_S],
CAST([W1].[ResourceS] AS decimal(16, 2)) AS [Resource_S],
CAST([W1].[SignalS] AS decimal(16, 2)) AS [Signal_S],
[W1].[WaitCount] AS [WaitCount],
CAST([W1].[Percentage] AS decimal(5, 2)) AS [Percentage],
CAST(([W1].[WaitS] / [W1].[WaitCount]) AS decimal(16, 4)) AS [AvgWait_S],
CAST(([W1].[ResourceS] / [W1].[WaitCount]) AS decimal(16, 4)) AS [AvgRes_S],
CAST(([W1].[SignalS] / [W1].[WaitCount]) AS decimal(16, 4)) AS [AvgSig_S]
FROM [Waits] AS [W1]
INNER JOIN [Waits] AS [W2] ON [W2].[RowNum] <= [W1].[RowNum]
GROUP BY [W1].[RowNum],
[W1].[wait_type],
[W1].[WaitS],
[W1].[ResourceS],
[W1].[SignalS],
[W1].[WaitCount],
[W1].[Percentage]
HAVING SUM([W2].[Percentage]) - [W1].[Percentage] < 95 -- percentage threshold
ORDER BY [Percentage] DESC
GO
-- Cleanup
IF EXISTS
(
SELECT *
FROM [tempdb].[sys].[objects]
WHERE [name] = N'##SQLskillsStats1'
)
DROP TABLE [##SQLskillsStats1];
IF EXISTS
(
SELECT *
FROM [tempdb].[sys].[objects]
WHERE [name] = N'##SQLskillsStats2'
)
DROP TABLE [##SQLskillsStats2];
GO
--select * from sys.dm_os_latch_stats
-- latch stat
-- Baseline
IF EXISTS
(
SELECT *
FROM [tempdb].[sys].[objects]
WHERE [name] = N'##TempLatchStats1'
)
DROP TABLE [##TempLatchStats1];
GO
SELECT *
INTO [##TempLatchStats1]
FROM [sys].[dm_os_latch_stats]
WHERE [waiting_requests_count] > 0
ORDER BY [latch_class];
GO
WAITFOR DELAY '00:00:01';
-- Capture updated stats
IF EXISTS
(
SELECT *
FROM [tempdb].[sys].[objects]
WHERE [name] = N'##TempLatchStats2'
)
DROP TABLE [##TempLatchStats2];
GO
SELECT *
INTO [##TempLatchStats2]
FROM [sys].[dm_os_latch_stats]
WHERE [waiting_requests_count] > 0
ORDER BY [latch_class];
GO
GO
-- Diff them
SELECT '***' AS [New],
[ts2].[latch_class] AS [latch_class],
[ts2].[waiting_requests_count] AS [DiffWaitingRequestCount],
[ts2].[wait_time_ms] AS [DiffWaitTime]
FROM [##TempLatchStats2] AS [ts2]
LEFT OUTER JOIN [##TempLatchStats1] AS [ts1] ON [ts2].[latch_class] = [ts1].[latch_class]
WHERE [ts1].[latch_class] IS NULL
UNION
SELECT '' AS [New],
[ts2].[latch_class] AS [latch_class],
[ts2].[waiting_requests_count] - [ts1].[waiting_requests_count] AS [DiffWaitingRequestCount],
[ts2].[wait_time_ms] - [ts1].[wait_time_ms] AS [DiffWaitTime]
FROM [##TempLatchStats2] AS [ts2]
LEFT OUTER JOIN [##TempLatchStats1] AS [ts1] ON [ts2].[latch_class] = [ts1].[latch_class]
WHERE [ts1].[latch_class] IS NOT NULL
AND [ts2].[waiting_requests_count] - [ts1].[waiting_requests_count] > 0
ORDER BY [New] DESC,
[latch_class] ASC;
GO
SELECT *
FROM [sys].[dm_os_waiting_tasks]
WHERE [blocking_session_id] IS NOT NULL
AND [session_id] <> [blocking_session_id]
ORDER BY [session_id] DESC;
select SUM(wait_time_ms) as total_wait_ms, SUM(signal_wait_time_ms) as total_signal_wait_ms,
1.0*SUM(signal_wait_time_ms)/SUM(wait_time_ms)*100 as signal_wait_pct
from sys.dm_os_wait_stats
select scheduler_id, COUNT(w.worker_address) from sys.dm_os_schedulers s
join
sys.dm_os_workers w on s.scheduler_address = w.scheduler_address
WHERE
s.scheduler_id < 255
and
state = 'RUNNABLE'
group by
scheduler_id
SELECT CAST(100.0 * SUM(signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2)) AS [% Signal (CPU) Waits],
CAST(100.0 * SUM(wait_time_ms - signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2)) AS [% Resource Waits]
FROM sys.dm_os_wait_stats WITH (NOLOCK)
WHERE wait_type NOT IN (
N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR', N'BROKER_TASK_STOP',
N'BROKER_TO_FLUSH', N'BROKER_TRANSMITTER', N'CHECKPOINT_QUEUE',
N'CHKPT', N'CLR_AUTO_EVENT', N'CLR_MANUAL_EVENT', N'CLR_SEMAPHORE',
N'DBMIRROR_DBM_EVENT', N'DBMIRROR_EVENTS_QUEUE', N'DBMIRROR_WORKER_QUEUE',
N'DBMIRRORING_CMD', N'DIRTY_PAGE_POLL', N'DISPATCHER_QUEUE_SEMAPHORE',
N'EXECSYNC', N'FSAGENT', N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX',
N'HADR_CLUSAPI_CALL', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION', N'HADR_LOGCAPTURE_WAIT',
N'HADR_NOTIFICATION_DEQUEUE', N'HADR_TIMER_TASK', N'HADR_WORK_QUEUE',
N'KSOURCE_WAKEUP', N'LAZYWRITER_SLEEP', N'LOGMGR_QUEUE', N'ONDEMAND_TASK_QUEUE',
N'PWAIT_ALL_COMPONENTS_INITIALIZED', N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',
N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP', N'REQUEST_FOR_DEADLOCK_SEARCH',
N'RESOURCE_QUEUE', N'SERVER_IDLE_CHECK', N'SLEEP_BPOOL_FLUSH', N'SLEEP_DBSTARTUP',
N'SLEEP_DCOMSTARTUP', N'SLEEP_MASTERDBREADY', N'SLEEP_MASTERMDREADY',
N'SLEEP_MASTERUPGRADED', N'SLEEP_MSDBSTARTUP', N'SLEEP_SYSTEMTASK', N'SLEEP_TASK',
N'SLEEP_TEMPDBSTARTUP', N'SNI_HTTP_ACCEPT', N'SP_SERVER_DIAGNOSTICS_SLEEP',
N'SQLTRACE_BUFFER_FLUSH', N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', N'SQLTRACE_WAIT_ENTRIES',
N'WAIT_FOR_RESULTS', N'WAITFOR', N'WAITFOR_TASKSHUTDOWN', N'WAIT_XTP_HOST_WAIT',
N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG', N'WAIT_XTP_CKPT_CLOSE', N'XE_DISPATCHER_JOIN',
N'XE_DISPATCHER_WAIT', N'XE_TIMER_EVENT', N'QDS_SHUTDOWN_QUEUE') OPTION (RECOMPILE);