Skip to content

Instantly share code, notes, and snippets.

/p_perfMon.sql

Created Oct 14, 2015
Embed
What would you like to do?
/* Create a dummy version of the procedure (so that we can use ALTER PROCEDURE below) */
IF object_id('dbo.p_perfMon') IS NULL
BEGIN
EXEC('CREATE PROCEDURE dbo.p_perfMon AS BEGIN RETURN END')
END
GO
/* GP 8/19/2014 Add a stored procedure to a look at some of the disk / CPU / memory internals provided by SQL Server
* Example usage:
* EXEC p_perfMon
* EXEC p_perfMon @deltaSeconds = 5
*/
ALTER PROCEDURE dbo.p_perfMon
@deltaSeconds INT = 1
AS
BEGIN
/******************************************************
* SANITIZE INPUTS
******************************************************/
IF (@deltaSeconds > 3559)
BEGIN
RAISERROR('@deltaSeconds must be between 1 and 3559, capping input value of %i at 3559', 0, 1, @deltaSeconds)
SET @deltaSeconds = 3559
END
ELSE IF (@deltaSeconds < 1)
BEGIN
RAISERROR('@deltaSeconds must be between 1 and 3559, capping input value of %i at 1', 0, 1, @deltaSeconds)
SET @deltaSeconds = 1
END
/******************************************************
* CPU UTILIZATION - REPORT (no deltas needed)
******************************************************/
;WITH runningTasks AS (
SELECT t.scheduler_id, t.session_id
FROM sys.dm_os_tasks t
JOIN sys.dm_exec_sessions es
ON es.session_id = t.session_id
AND es.is_user_process = 1
WHERE t.task_state = 'RUNNING'
AND t.session_id <> @@SPID
)
SELECT s.parent_node_id AS numa_node_id, COUNT(*) AS num_cpus, COALESCE(SUM(t.running_task_count), 0) AS running_task_count,
STUFF((
SELECT ', ' + CAST(x.num_tasks AS VARCHAR(MAX)) + 'x: ' + CAST(x.session_id AS VARCHAR(MAX))
FROM (
SELECT t.session_id, COUNT(*) AS num_tasks
FROM sys.dm_os_schedulers s2
JOIN runningTasks t
ON t.scheduler_id = s2.scheduler_id
WHERE s2.parent_node_id = s.parent_node_id
GROUP BY t.session_id
) x
ORDER BY x.num_tasks DESC, x.session_id ASC
FOR XML PATH('')
), 1, 2, '') AS running_sessions,
SUM(s.runnable_tasks_count) AS runnable_tasks_count, SUM(s.current_tasks_count) AS current_tasks_count,
SUM(s.pending_disk_io_count) AS pending_disk_io_count, MIN(os.cntr_value) AS page_life_expectancy, SUM(s.load_factor) AS load_factor
FROM sys.dm_os_schedulers s
LEFT OUTER JOIN (
SELECT scheduler_id, COUNT(*) AS running_task_count
FROM runningTasks
GROUP BY scheduler_id
) t
ON t.scheduler_id = s.scheduler_id
LEFT OUTER JOIN sys.dm_os_performance_counters os
ON os.object_name = 'SQLServer:Buffer Node'
AND os.counter_name = 'Page life expectancy'
AND TRY_CONVERT(INT, os.instance_name) = s.parent_node_id
WHERE s.scheduler_id < 1048576 /* "Those schedulers that have IDs greater than or equal to 1048576 are used internally by SQL Server" (http://msdn.microsoft.com/en-us/library/ms177526.aspx) */
GROUP BY s.parent_node_id
/******************************************************
* CREATE TEMP TABLES
******************************************************/
IF OBJECT_ID('tempdb..#io_file_stats1') IS NOT NULL
DROP TABLE #io_file_stats1
IF OBJECT_ID('tempdb..#io_file_stats2') IS NOT NULL
DROP TABLE #io_file_stats2
IF OBJECT_ID('tempdb..#os_perf_counters1') IS NOT NULL
DROP TABLE #os_perf_counters1
IF OBJECT_ID('tempdb..#os_perf_counters2') IS NOT NULL
DROP TABLE #os_perf_counters2
IF OBJECT_ID('tempdb..#wait_stats1') IS NOT NULL
DROP TABLE #wait_stats1
IF OBJECT_ID('tempdb..#wait_stats2') IS NOT NULL
DROP TABLE #wait_stats2
/******************************************************
* FILE STATS - BEFORE
******************************************************/
SELECT *
INTO #io_file_stats1
FROM sys.dm_io_virtual_file_stats(NULL, NULL)
/******************************************************
* WAIT STATS - BEFORE
******************************************************/
SELECT w.*, s.ms_ticks AS sample_ms
INTO #wait_stats1
FROM sys.dm_os_wait_stats w
CROSS JOIN sys.dm_os_sys_info s
/******************************************************
* PERFORMANCE COUNTERS - BEFORE
******************************************************/
SELECT *
INTO #os_perf_counters1
FROM sys.dm_os_performance_counters
WHERE object_name = 'SQLServer:Workload Group Stats'
AND instance_name = 'default' /* We currently only use the default resource governor work group */
AND counter_name IN ('CPU usage %', 'CPU usage % base', 'Max request memory grant (KB)', 'Active parallel threads')
UNION ALL
SELECT *
FROM sys.dm_os_performance_counters
WHERE object_name = 'SQLServer:Buffer Manager'
AND counter_name IN ('Buffer cache hit ratio', 'Buffer cache hit ratio base', 'Page life expectancy', 'Database pages', 'Page writes/sec', 'Page lookups/sec', 'Page reads/sec', 'Readahead pages/sec')
UNION ALL
SELECT *
FROM sys.dm_os_performance_counters
WHERE object_name = 'SQLServer:Memory Manager'
AND counter_name IN ('Database Cache Memory (KB)', 'Granted Workspace Memory (KB)', 'Memory Grants Outstanding', 'Memory Grants Pending')
/******************************************************
* DELAY
******************************************************/
DECLARE @waitString VARCHAR(MAX) = 'WAITFOR DELAY ''00:' + CAST(FLOOR(@deltaSeconds/60) AS VARCHAR) + ':' + CAST(@deltaSeconds%60 AS VARCHAR) + ''''
EXEC(@waitString)
/******************************************************
* FILE STATS - AFTER
******************************************************/
SELECT *
INTO #io_file_stats2
FROM sys.dm_io_virtual_file_stats(NULL, NULL)
/******************************************************
* WAIT STATS - AFTER
******************************************************/
SELECT w.*, s.ms_ticks AS sample_ms
INTO #wait_stats2
FROM sys.dm_os_wait_stats w
CROSS JOIN sys.dm_os_sys_info s
/******************************************************
* PERFORMANCE COUNTERS - AFTER
******************************************************/
SELECT *
INTO #os_perf_counters2
FROM sys.dm_os_performance_counters
WHERE object_name = 'SQLServer:Workload Group Stats'
AND instance_name = 'default' /* We currently only use the default resource governor work group */
AND counter_name IN ('CPU usage %', 'CPU usage % base', 'Active parallel threads')
UNION ALL
SELECT *
FROM sys.dm_os_performance_counters
WHERE object_name = 'SQLServer:Buffer Manager'
AND counter_name IN ('Buffer cache hit ratio', 'Buffer cache hit ratio base', 'Page life expectancy', 'Page writes/sec', 'Page lookups/sec', 'Page reads/sec', 'Readahead pages/sec')
UNION ALL
SELECT *
FROM sys.dm_os_performance_counters
WHERE object_name = 'SQLServer:Memory Manager'
AND counter_name IN ('Database Cache Memory (KB)', 'Granted Workspace Memory (KB)', 'Memory Grants Outstanding', 'Memory Grants Pending')
/******************************************************
* FILE STATS - COMPUTE DELTAS AND REPORT
******************************************************/
;WITH deltaCTE AS (
SELECT s2.database_id
, s2.file_id
, s2.sample_ms - s1.sample_ms AS sample_ms
, s2.num_of_reads - s1.num_of_reads AS num_of_reads
, s2.num_of_writes - s1.num_of_writes AS num_of_writes
, s2.num_of_bytes_read - s1.num_of_bytes_read AS num_of_bytes_read
, s2.num_of_bytes_written - s1.num_of_bytes_written AS num_of_bytes_written
, s2.io_stall_read_ms - s1.io_stall_read_ms AS io_stall_read_ms
, s2.io_stall_write_ms - s1.io_stall_write_ms AS io_stall_write_ms
FROM #io_file_stats2 s2
JOIN #io_file_stats1 s1
ON s1.database_id = s2.database_id
AND s1.file_id = s2.file_id
WHERE (s2.num_of_reads > s1.num_of_reads OR s2.num_of_writes > s1.num_of_writes)
)
SELECT DB_NAME(d.database_id) AS database_name
, f.type_desc
, LEFT(physical_name, 2) AS physical_drive
, COUNT(*) AS num_files_with_io
, AVG(d.sample_ms) AS sample_ms
, SUM(d.num_of_reads) AS num_of_reads
, SUM(d.num_of_writes) AS num_of_writes
, CONVERT(DECIMAL(9,2), SUM(d.num_of_bytes_read) / (1024.0 * 1024.0)) AS num_of_mb_read
, CONVERT(DECIMAL(9,2), SUM(d.num_of_bytes_written) / (1024.0 * 1024.0)) AS num_of_mb_written
, CONVERT(DECIMAL(9,2), SUM(d.num_of_bytes_read + d.num_of_bytes_written) / (1024.0 * 1024.0)) AS total_io_mb
, CONVERT(DECIMAL(9,2), SUM(d.io_stall_read_ms) / NULLIF(SUM(d.num_of_reads), 0)) AS avg_read_stall_ms
, CONVERT(DECIMAL(9,2), SUM(d.io_stall_write_ms) / NULLIF(SUM(d.num_of_writes), 0)) AS avg_write_stall_ms
FROM deltaCTE d
JOIN master.sys.master_files AS f
ON f.database_id = d.database_id
AND f.file_id = d.file_id
GROUP BY d.database_id, f.type_desc, LEFT(physical_name, 2)
ORDER BY total_io_mb DESC
/******************************************************
* WAIT STATS - COMPUTE DELTAS AND REPORT
* Adapted from http://www.sqlskills.com/blogs/paul/capturing-wait-statistics-period-time/
******************************************************/
;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],
[ts2].[sample_ms]
FROM #wait_stats2 AS [ts2]
LEFT OUTER JOIN #wait_stats1 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],
[ts2].[sample_ms] - ts1.[sample_ms] AS [sample_ms]
FROM #wait_stats2 AS [ts2]
LEFT OUTER JOIN #wait_stats1 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],
[sample_ms] / 1000.0 AS [SampleS],
[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]
)
SELECT [W1].[wait_type] AS [WaitType],
CAST ([W1].[SampleS] AS DECIMAL (16, 2)) AS [Sample_S],
CAST ([W1].[WaitS] AS DECIMAL (16, 2)) AS [Wait_S],
CAST([W1].[WaitS] / NULLIF([W1].[SampleS],0) AS DECIMAL (16, 3)) AS wait_s_per_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]
WHERE [W1].[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'TRACEWRITE', N'PREEMPTIVE_XE_DISPATCHER')
GROUP BY [W1].[RowNum], [W1].[wait_type], [W1].[SampleS], [W1].[WaitS],
[W1].[ResourceS], [W1].[SignalS], [W1].[WaitCount], [W1].[Percentage]
HAVING SUM ([W2].[Percentage]) - [W1].[Percentage] < 99.9 -- percentage threshold
ORDER BY Wait_S DESC;
/******************************************************
* PERFORMANCE COUNTERS - COMPUTE DELTAS AND REPORT
******************************************************/
;WITH deltaCTE AS (
SELECT p2.object_name
, CASE WHEN p2.counter_name LIKE '%(KB)%' THEN REPLACE(p2.counter_name, '(KB)', '(MB)') ELSE p2.counter_name END AS counter_name
, p2.cntr_type
, p2.cntr_value / CASE WHEN p2.counter_name LIKE '%(KB)%' THEN 1024 ELSE 1 END AS cntr_value
, p2.cntr_value - p1.cntr_value AS cntr_delta
, CONVERT(NUMERIC, 100.0 * p2.cntr_value / d2.cntr_value) AS cntr_percent
FROM #os_perf_counters2 p2
JOIN #os_perf_counters1 p1
ON p1.object_name = p2.object_name
AND p1.counter_name = p2.counter_name
LEFT OUTER JOIN #os_perf_counters2 d2
ON d2.object_name = p2.object_name
AND REPLACE(d2.counter_name, ' base', '') = p2.counter_name
AND d2.cntr_type = 1073939712
WHERE p2.cntr_type <> 1073939712
)
SELECT object_name, counter_name
, CASE WHEN cntr_type = 65792 THEN cntr_value
WHEN cntr_type = 272696576 THEN cntr_delta
WHEN cntr_type = 537003264 THEN cntr_percent
END AS counter_value
FROM deltaCTE
END
GO
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment