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
You can’t perform that action at this time.