Description:
Example to show the realtime SQL Server 2008(R2) Performance Statistics,
Check the example below:
------------------------------------------------------------------------------------------------------
IF '%runmode%' != 'REALTIME' USE tempdb
GO
SET NOCOUNT ON
SET QUOTED_IDENTIFIER ON
GO
IF (CHARINDEX ('10.00', @@VERSION) = 0) BEGIN
PRINT ''
PRINT '**** NOTE ****'
PRINT '**** This script is for SQL Server 2008. Errors are expected when run on earlier versions.'
PRINT '**************'
PRINT ''
END
GO
PRINT 'Starting SQL Server 2008 Perf Stats Script...'
PRINT ''
SET LANGUAGE us_english
PRINT ''
PRINT '-- Script and Environment Details --'
PRINT ''
PRINT 'Name Value'
PRINT '------------------------ ---------------------------------------------------'
PRINT 'SQL Server Name ' + @@SERVERNAME
PRINT 'Machine Name ' + CONVERT (varchar, SERVERPROPERTY ('MachineName'))
PRINT 'SQL Version (SP) ' + CONVERT (varchar, SERVERPROPERTY ('ProductVersion')) + ' (' + CONVERT (varchar, SERVERPROPERTY ('ProductLevel')) + ')'
PRINT 'Edition ' + CONVERT (varchar, SERVERPROPERTY ('Edition'))
PRINT 'Script Name SQL 2008 Performance Stats'
PRINT 'Script File Name $File: SQL2008_PerfStats.sql'
PRINT 'Script Begin Time ' + CONVERT (varchar(30), GETDATE(), 126)
PRINT 'Current Database ' + DB_NAME()
PRINT ''
GO
IF OBJECT_ID ('sp_perf_stats10','P') IS NOT NULL
DROP PROCEDURE sp_perf_stats10
GO
CREATE PROCEDURE sp_perf_stats10 @appname sysname='PSSDIAG', @runtime datetime AS
SET NOCOUNT ON
DECLARE @msg varchar(100)
DECLARE @querystarttime datetime
DECLARE @queryduration int
DECLARE @qrydurationwarnthreshold int
DECLARE @servermajorversion int
DECLARE @cpu_time_start bigint, @elapsed_time_start bigint
DECLARE @sql nvarchar(max)
DECLARE @cte nvarchar(max)
DECLARE @rowcount bigint
SELECT @cpu_time_start = cpu_time, @elapsed_time_start = total_elapsed_time FROM sys.dm_exec_requests WHERE session_id = @@SPID
IF OBJECT_ID ('tempdb.dbo.#tmp_requests') IS NOT NULL DROP TABLE #tmp_requests
IF OBJECT_ID ('tempdb.dbo.#tmp_requests2') IS NOT NULL DROP TABLE #tmp_requests2
IF @runtime IS NULL
BEGIN
SET @runtime = GETDATE()
SET @msg = 'Start time: ' + CONVERT (varchar(30), @runtime, 126)
RAISERROR (@msg, 0, 1) WITH NOWAIT
END
SET @qrydurationwarnthreshold = 500
-- SERVERPROPERTY ('ProductVersion') returns e.g. "9.00.2198.00" --> 9
SET @servermajorversion = REPLACE (LEFT (CONVERT (varchar, SERVERPROPERTY ('ProductVersion')), 2), '.', '')
RAISERROR (@msg, 0, 1) WITH NOWAIT
SET @querystarttime = GETDATE()
SELECT
sess.session_id, req.request_id, tasks.exec_context_id AS ecid, tasks.task_address, req.blocking_session_id, LEFT (tasks.task_state, 15) AS task_state,
tasks.scheduler_id, LEFT (ISNULL (req.wait_type, ''), 50) AS wait_type, LEFT (ISNULL (req.wait_resource, ''), 40) AS wait_resource,
LEFT (req.last_wait_type, 50) AS last_wait_type,
/* sysprocesses is the only way to get open_tran count for sessions w/o an active request (SQLBUD #487091) */
CASE
WHEN req.open_transaction_count IS NOT NULL THEN req.open_transaction_count
ELSE (SELECT open_tran FROM master.dbo.sysprocesses sysproc WHERE sess.session_id = sysproc.spid)
END AS open_trans,
LEFT (CASE COALESCE(req.transaction_isolation_level, sess.transaction_isolation_level)
WHEN 0 THEN '0-Read Committed'
WHEN 1 THEN '1-Read Uncommitted (NOLOCK)'
WHEN 2 THEN '2-Read Committed'
WHEN 3 THEN '3-Repeatable Read'
WHEN 4 THEN '4-Serializable'
WHEN 5 THEN '5-Snapshot'
ELSE CONVERT (varchar(30), req.transaction_isolation_level) + '-UNKNOWN'
END, 30) AS transaction_isolation_level,
sess.is_user_process, req.cpu_time AS request_cpu_time,
/* CASE stmts necessary to workaround SQLBUD #438189 (fixed in SP2) */
CASE WHEN (@servermajorversion > 9) OR (@servermajorversion = 9 AND SERVERPROPERTY ('ProductLevel') >= 'SP2' COLLATE Latin1_General_BIN)
THEN req.logical_reads ELSE req.logical_reads - sess.logical_reads END AS request_logical_reads,
CASE WHEN (@servermajorversion > 9) OR (@servermajorversion = 9 AND SERVERPROPERTY ('ProductLevel') >= 'SP2' COLLATE Latin1_General_BIN)
THEN req.reads ELSE req.reads - sess.reads END AS request_reads,
CASE WHEN (@servermajorversion > 9) OR (@servermajorversion = 9 AND SERVERPROPERTY ('ProductLevel') >= 'SP2' COLLATE Latin1_General_BIN)
THEN req.writes ELSE req.writes - sess.writes END AS request_writes,
sess.memory_usage, sess.cpu_time AS session_cpu_time, sess.reads AS session_reads, sess.writes AS session_writes, sess.logical_reads AS session_logical_reads,
sess.total_scheduled_time, sess.total_elapsed_time, sess.last_request_start_time, sess.last_request_end_time, sess.row_count AS session_row_count,
sess.prev_error, req.open_resultset_count AS open_resultsets, req.total_elapsed_time AS request_total_elapsed_time,
CONVERT (decimal(5,2), req.percent_complete) AS percent_complete, req.estimated_completion_time AS est_completion_time, req.transaction_id,
req.start_time AS request_start_time, LEFT (req.status, 15) AS request_status, req.command, req.plan_handle, req.sql_handle, req.statement_start_offset,
req.statement_end_offset, req.database_id, req.[user_id], req.executing_managed_code, tasks.pending_io_count, sess.login_time,
LEFT (sess.[host_name], 20) AS [host_name], LEFT (ISNULL (sess.program_name, ''), 50) AS program_name, ISNULL (sess.host_process_id, 0) AS host_process_id,
ISNULL (sess.client_version, 0) AS client_version, LEFT (ISNULL (sess.client_interface_name, ''), 30) AS client_interface_name,
LEFT (ISNULL (sess.login_name, ''), 30) AS login_name, LEFT (ISNULL (sess.nt_domain, ''), 30) AS nt_domain, LEFT (ISNULL (sess.nt_user_name, ''), 20) AS nt_user_name,
ISNULL (conn.net_packet_size, 0) AS net_packet_size, LEFT (ISNULL (conn.client_net_address, ''), 20) AS client_net_address, conn.most_recent_sql_handle,
LEFT (sess.status, 15) AS session_status,
/* sys.dm_os_workers and sys.dm_os_threads removed due to perf impact, no predicate pushdown (SQLBU #488971) */
-- workers.is_preemptive,
-- workers.is_sick,
-- workers.exception_num AS last_worker_exception,
-- convert (varchar (20), master.dbo.fn_varbintohexstr (workers.exception_address)) AS last_exception_address
-- threads.os_thread_id
sess.group_id
INTO #tmp_requests
FROM sys.dm_exec_sessions sess
/* Join hints are required here to work around bad QO join order/type decisions (ultimately by-design, caused by the lack of accurate DMV card estimates) */
LEFT OUTER MERGE JOIN sys.dm_exec_requests req ON sess.session_id = req.session_id
LEFT OUTER MERGE JOIN sys.dm_os_tasks tasks ON tasks.session_id = sess.session_id AND tasks.request_id = req.request_id
/* The following two DMVs removed due to perf impact, no predicate pushdown (SQLBU #488971) */
-- LEFT OUTER MERGE JOIN sys.dm_os_workers workers ON tasks.worker_address = workers.worker_address
-- LEFT OUTER MERGE JOIN sys.dm_os_threads threads ON workers.thread_address = threads.thread_address
LEFT OUTER MERGE JOIN sys.dm_exec_connections conn on conn.session_id = sess.session_id
WHERE
/* Get execution state for all active queries... */
(req.session_id IS NOT NULL AND (sess.is_user_process = 1 OR req.status COLLATE Latin1_General_BIN NOT IN ('background', 'sleeping')))
/* ... and also any head blockers, even though they may not be running a query at the moment. */
OR (sess.session_id IN (SELECT DISTINCT blocking_session_id FROM sys.dm_exec_requests WHERE blocking_session_id != 0))
/* redundant due to the use of join hints, but added here to suppress warning message */
OPTION (FORCE ORDER)
SET @rowcount = @@ROWCOUNT
SET @queryduration = DATEDIFF (ms, @querystarttime, GETDATE())
IF @queryduration > @qrydurationwarnthreshold
PRINT 'DebugPrint: perfstats qry1 - ' + CONVERT (varchar, @queryduration) + 'ms, rowcount=' + CONVERT(varchar, @rowcount) + CHAR(13) + CHAR(10)
IF NOT EXISTS (SELECT * FROM #tmp_requests WHERE session_id <> @@SPID AND ISNULL (host_name, '') != @appname) BEGIN
PRINT 'No active queries'
END
ELSE BEGIN
-- There are active queries (other than this one).
-- This query could be collapsed into the query above. It is broken out here to avoid an excessively
-- large memory grant due to poor cardinality estimates (see previous bugs -- ultimate cause is the
-- lack of good stats for many DMVs).
SET @querystarttime = GETDATE()
SELECT
IDENTITY (int,1,1) AS tmprownum,
r.session_id, r.request_id, r.ecid, r.blocking_session_id, ISNULL (waits.blocking_exec_context_id, 0) AS blocking_ecid,
r.task_state, r.wait_type, ISNULL (waits.wait_duration_ms, 0) AS wait_duration_ms, r.wait_resource,
LEFT (ISNULL (waits.resource_description, ''), 140) AS resource_description, r.last_wait_type, r.open_trans,
r.transaction_isolation_level, r.is_user_process, r.request_cpu_time, r.request_logical_reads, r.request_reads,
r.request_writes, r.memory_usage, r.session_cpu_time, r.session_reads, r.session_writes, r.session_logical_reads,
r.total_scheduled_time, r.total_elapsed_time, r.last_request_start_time, r.last_request_end_time, r.session_row_count,
r.prev_error, r.open_resultsets, r.request_total_elapsed_time, r.percent_complete, r.est_completion_time,
-- r.tran_name, r.transaction_begin_time, r.tran_type, r.tran_state,
LEFT (COALESCE (reqtrans.name, sesstrans.name, ''), 24) AS tran_name,
COALESCE (reqtrans.transaction_begin_time, sesstrans.transaction_begin_time) AS transaction_begin_time,
LEFT (CASE COALESCE (reqtrans.transaction_type, sesstrans.transaction_type)
WHEN 1 THEN '1-Read/write'
WHEN 2 THEN '2-Read only'
WHEN 3 THEN '3-System'
WHEN 4 THEN '4-Distributed'
ELSE CONVERT (varchar(30), COALESCE (reqtrans.transaction_type, sesstrans.transaction_type)) + '-UNKNOWN'
END, 15) AS tran_type,
LEFT (CASE COALESCE (reqtrans.transaction_state, sesstrans.transaction_state)
WHEN 0 THEN '0-Initializing'
WHEN 1 THEN '1-Initialized'
WHEN 2 THEN '2-Active'
WHEN 3 THEN '3-Ended'
WHEN 4 THEN '4-Preparing'
WHEN 5 THEN '5-Prepared'
WHEN 6 THEN '6-Committed'
WHEN 7 THEN '7-Rolling back'
WHEN 8 THEN '8-Rolled back'
ELSE CONVERT (varchar(30), COALESCE (reqtrans.transaction_state, sesstrans.transaction_state)) + '-UNKNOWN'
END, 15) AS tran_state,
r.request_start_time, r.request_status, r.command, r.plan_handle, r.sql_handle, r.statement_start_offset,
r.statement_end_offset, r.database_id, r.[user_id], r.executing_managed_code, r.pending_io_count, r.login_time,
r.[host_name], r.program_name, r.host_process_id, r.client_version, r.client_interface_name, r.login_name, r.nt_domain,
r.nt_user_name, r.net_packet_size, r.client_net_address, r.most_recent_sql_handle, r.session_status, r.scheduler_id,
-- r.is_preemptive, r.is_sick, r.last_worker_exception, r.last_exception_address,
-- r.os_thread_id
r.group_id
INTO #tmp_requests2
FROM #tmp_requests r
/* Join hints are required here to work around bad QO join order/type decisions (ultimately by-design, caused by the lack of accurate DMV card estimates) */
/* Perf: no predicate pushdown on sys.dm_tran_active_transactions (SQLBU #489000) */
LEFT OUTER MERGE JOIN sys.dm_tran_active_transactions reqtrans ON r.transaction_id = reqtrans.transaction_id
/* No predicate pushdown on sys.dm_tran_session_transactions (SQLBU #489000) */
LEFT OUTER MERGE JOIN sys.dm_tran_session_transactions sessions_transactions on sessions_transactions.session_id = r.session_id
/* No predicate pushdown on sys.dm_tran_active_transactions (SQLBU #489000) */
LEFT OUTER MERGE JOIN sys.dm_tran_active_transactions sesstrans ON sesstrans.transaction_id = sessions_transactions.transaction_id
/* Suboptimal perf: see SQLBUD #449144. But we have to handle this in qry3 instead of here to avoid SQLBUD #489109. */
LEFT OUTER MERGE JOIN sys.dm_os_waiting_tasks waits ON waits.waiting_task_address = r.task_address
ORDER BY r.session_id, blocking_ecid
/* redundant due to the use of join hints, but added here to suppress warning message */
OPTION (FORCE ORDER)
SET @rowcount = @@ROWCOUNT
SET @queryduration = DATEDIFF (ms, @querystarttime, GETDATE())
IF @queryduration > @qrydurationwarnthreshold
PRINT 'DebugPrint: perfstats qry2 - ' + CONVERT (varchar, @queryduration) + 'ms, rowcount=' + CONVERT(varchar, @rowcount) + CHAR(13) + CHAR(10)
/* This index typically takes <10ms to create, and drops the head blocker summary query cost from ~250ms CPU down to ~20ms. */
CREATE NONCLUSTERED INDEX idx1 ON #tmp_requests2 (blocking_session_id, session_id, wait_type, wait_duration_ms)
RAISERROR ('-- requests --', 0, 1) WITH NOWAIT
/* Output Resultset #1: summary of all active requests (and head blockers) */
/* Dynamic (but explicitly parameterized) SQL used here to allow for (optional) direct-to-database data collection
** without unnecessary code duplication. */
SET @sql = '
SELECT TOP 10000 CONVERT (varchar(30), @runtime, 126) AS runtime,
session_id, request_id, ecid, blocking_session_id, blocking_ecid, task_state,
wait_type, wait_duration_ms, wait_resource, resource_description, last_wait_type,
open_trans, transaction_isolation_level, is_user_process,
request_cpu_time, request_logical_reads, request_reads, request_writes, memory_usage,
session_cpu_time, session_reads, session_writes, session_logical_reads, total_scheduled_time,
total_elapsed_time, CONVERT (varchar, last_request_start_time, 126) AS last_request_start_time,
CONVERT (varchar, last_request_end_time, 126) AS last_request_end_time, session_row_count,
prev_error, open_resultsets, request_total_elapsed_time, percent_complete,
est_completion_time, tran_name,
CONVERT (varchar, transaction_begin_time, 126) AS transaction_begin_time, tran_type,
tran_state, CONVERT (varchar, request_start_time, 126) AS request_start_time, request_status,
command, statement_start_offset, statement_end_offset, database_id, [user_id],
executing_managed_code, pending_io_count, CONVERT (varchar, login_time, 126) AS login_time,
[host_name], program_name, host_process_id, client_version, client_interface_name, login_name,
nt_domain, nt_user_name, net_packet_size, client_net_address, session_status,
scheduler_id,
-- is_preemptive, is_sick, last_worker_exception, last_exception_address
-- os_thread_id
group_id
FROM #tmp_requests2 r
WHERE ISNULL ([host_name], '''') != @appname AND r.session_id != @@SPID
/* One EC can have multiple waits in sys.dm_os_waiting_tasks (e.g. parent thread waiting on multiple children, for example
** for parallel create index; or mem grant waits for RES_SEM_FOR_QRY_COMPILE). This will result in the same EC being listed
** multiple times in the request table, which is counterintuitive for most people. Instead of showing all wait relationships,
** for each EC we will report the wait relationship that has the longest wait time. (If there are multiple relationships with
** the same wait time, blocker spid/ecid is used to choose one of them.) If it were not for SQLBUD #489109, we would do this
** exclusion in the previous query to avoid storing data that will ultimately be filtered out. */
AND NOT EXISTS
(SELECT * FROM #tmp_requests2 r2
WHERE r.session_id = r2.session_id AND r.request_id = r2.request_id AND r.ecid = r2.ecid AND r.wait_type = r2.wait_type
AND (r2.wait_duration_ms > r.wait_duration_ms OR (r2.wait_duration_ms = r.wait_duration_ms AND r2.tmprownum > r.tmprownum)))
'
IF '%runmode%' = 'REALTIME'
SET @sql = '
INSERT INTO tbl_REQUESTS (runtime, session_id, request_id, ecid, blocking_session_id, blocking_ecid,
task_state, wait_type, wait_duration_ms, wait_resource, resource_description, last_wait_type, open_trans,
transaction_isolation_level, is_user_process, request_cpu_time, request_logical_reads, request_reads, request_writes, memory_usage,
session_cpu_time, session_reads, session_writes, session_logical_reads, total_scheduled_time, total_elapsed_time, last_request_start_time,
last_request_end_time, session_row_count, prev_error, open_resultsets, request_total_elapsed_time, percent_complete, estimated_completion_time,
tran_name, transaction_begin_time, tran_type, tran_state, request_start_time, request_status, command, statement_start_offset,
statement_end_offset, database_id, [user_id], executing_managed_code, pending_io_count, login_time, [host_name], program_name, host_process_id,
client_version, client_interface_name, login_name, nt_domain, nt_user_name, net_packet_size, client_net_address, session_status,
most_recent_sql_handle, scheduler_id) ' + @sql
SET @querystarttime = GETDATE()
EXEC sp_executesql @sql, N'@runtime datetime, @appname sysname', @runtime = @runtime, @appname = @appname
SET @rowcount = @@ROWCOUNT
SET @queryduration = DATEDIFF (ms, @querystarttime, GETDATE())
RAISERROR ('', 0, 1) WITH NOWAIT
IF @queryduration > @qrydurationwarnthreshold
PRINT 'DebugPrint: perfstats qry3 - ' + CONVERT (varchar, @queryduration) + 'ms, rowcount=' + CONVERT(varchar, @rowcount) + CHAR(13) + CHAR(10)
/* Resultset #2: Head blocker summary */
/* Intra-query blocking relationships (parallel query waits) aren't "true" blocking problems that we should report on here. */
IF NOT EXISTS (SELECT * FROM #tmp_requests2 WHERE blocking_session_id != 0 AND wait_type NOT IN ('WAITFOR', 'EXCHANGE', 'CXPACKET') AND wait_duration_ms > 0)
BEGIN
PRINT ''
PRINT '-- No blocking detected --'
PRINT ''
END
ELSE BEGIN
PRINT ''
PRINT '-----------------------'
PRINT '-- BLOCKING DETECTED --'
PRINT ''
RAISERROR ('-- headblockersummary --', 0, 1) WITH NOWAIT;
/* We need stats like the number of spids blocked, max waittime, etc, for each head blocker. Use a recursive CTE to
** walk the blocking hierarchy. Again, explicitly parameterized dynamic SQL used to allow optional collection direct
** to a database. */
SET @cte = '
WITH BlockingHierarchy (head_blocker_session_id, session_id, blocking_session_id, wait_type, wait_duration_ms,
wait_resource, statement_start_offset, statement_end_offset, plan_handle, sql_handle, most_recent_sql_handle, [Level])
AS (
SELECT head.session_id AS head_blocker_session_id, head.session_id AS session_id, head.blocking_session_id,
head.wait_type, head.wait_duration_ms, head.wait_resource, head.statement_start_offset, head.statement_end_offset,
head.plan_handle, head.sql_handle, head.most_recent_sql_handle, 0 AS [Level]
FROM #tmp_requests2 head
WHERE (head.blocking_session_id IS NULL OR head.blocking_session_id = 0)
AND head.session_id IN (SELECT DISTINCT blocking_session_id FROM #tmp_requests2 WHERE blocking_session_id != 0)
UNION ALL
SELECT h.head_blocker_session_id, blocked.session_id, blocked.blocking_session_id, blocked.wait_type,
blocked.wait_duration_ms, blocked.wait_resource, h.statement_start_offset, h.statement_end_offset,
h.plan_handle, h.sql_handle, h.most_recent_sql_handle, [Level] + 1
FROM #tmp_requests2 blocked
INNER JOIN BlockingHierarchy AS h ON h.session_id = blocked.blocking_session_id
WHERE h.wait_type COLLATE Latin1_General_BIN NOT IN (''EXCHANGE'', ''CXPACKET'')
)'
SET @sql = '
SELECT CONVERT (varchar(30), @runtime, 126) AS runtime,
head_blocker_session_id, COUNT(*) AS blocked_task_count, SUM (ISNULL (wait_duration_ms, 0)) AS tot_wait_duration_ms,
LEFT (CASE
WHEN wait_type LIKE ''LCK%'' COLLATE Latin1_General_BIN AND wait_resource LIKE ''%\[COMPILE\]%'' ESCAPE ''\'' COLLATE Latin1_General_BIN
THEN ''COMPILE ('' + ISNULL (wait_resource, '''') + '')''
WHEN wait_type LIKE ''LCK%'' COLLATE Latin1_General_BIN THEN ''LOCK BLOCKING''
WHEN wait_type LIKE ''PAGELATCH%'' COLLATE Latin1_General_BIN THEN ''PAGELATCH_* WAITS''
WHEN wait_type LIKE ''PAGEIOLATCH%'' COLLATE Latin1_General_BIN THEN ''PAGEIOLATCH_* WAITS''
ELSE wait_type
END, 40) AS blocking_resource_wait_type, AVG (ISNULL (wait_duration_ms, 0)) AS avg_wait_duration_ms, MAX(wait_duration_ms) AS max_wait_duration_ms,
MAX ([Level]) AS max_blocking_chain_depth,
MAX (ISNULL (CONVERT (nvarchar(60), CASE
WHEN sql.objectid IS NULL THEN NULL
ELSE REPLACE (REPLACE (SUBSTRING (sql.[text], CHARINDEX (''CREATE '', CONVERT (nvarchar(512), SUBSTRING (sql.[text], 1, 1000)) COLLATE Latin1_General_BIN), 50) COLLATE Latin1_General_BIN, CHAR(10), '' ''), CHAR(13), '' '')
END), '''')) AS head_blocker_proc_name,
MAX (ISNULL (sql.objectid, 0)) AS head_blocker_proc_objid, MAX (ISNULL (CONVERT (nvarchar(1000), REPLACE (REPLACE (SUBSTRING (sql.[text], ISNULL (statement_start_offset, 0)/2 + 1,
CASE WHEN ISNULL (statement_end_offset, 8192) <= 0 THEN 8192
ELSE ISNULL (statement_end_offset, 8192)/2 - ISNULL (statement_start_offset, 0)/2 END + 1) COLLATE Latin1_General_BIN,
CHAR(13), '' ''), CHAR(10), '' '')), '''')) AS stmt_text,
CONVERT (varbinary (64), MAX (ISNULL (plan_handle, 0x))) AS head_blocker_plan_handle
FROM BlockingHierarchy
OUTER APPLY sys.dm_exec_sql_text (ISNULL (sql_handle, most_recent_sql_handle)) AS sql
WHERE blocking_session_id != 0 AND [Level] > 0
GROUP BY head_blocker_session_id,
LEFT (CASE
WHEN wait_type LIKE ''LCK%'' COLLATE Latin1_General_BIN AND wait_resource LIKE ''%\[COMPILE\]%'' ESCAPE ''\'' COLLATE Latin1_General_BIN
THEN ''COMPILE ('' + ISNULL (wait_resource, '''') + '')''
WHEN wait_type LIKE ''LCK%'' COLLATE Latin1_General_BIN THEN ''LOCK BLOCKING''
WHEN wait_type LIKE ''PAGELATCH%'' COLLATE Latin1_General_BIN THEN ''PAGELATCH_* WAITS''
WHEN wait_type LIKE ''PAGEIOLATCH%'' COLLATE Latin1_General_BIN THEN ''PAGEIOLATCH_* WAITS''
ELSE wait_type
END, 40)
ORDER BY SUM (wait_duration_ms) DESC'
IF '%runmode%' = 'REALTIME' SET @sql = @cte + '
INSERT INTO tbl_HEADBLOCKERSUMMARY (
runtime, head_blocker_session_id, blocked_task_count, tot_wait_duration_ms, blocking_resource_wait_type, avg_wait_duration_ms,
max_wait_duration_ms, max_blocking_chain_depth, head_blocker_proc_name, head_blocker_proc_objid, stmt_text, head_blocker_plan_handle) ' + @sql
ELSE
SET @sql = @cte + @sql
SET @querystarttime = GETDATE();
EXEC sp_executesql @sql, N'@runtime datetime', @runtime = @runtime
SET @rowcount = @@ROWCOUNT
SET @queryduration = DATEDIFF (ms, @querystarttime, GETDATE())
RAISERROR ('', 0, 1) WITH NOWAIT
IF @queryduration > @qrydurationwarnthreshold
PRINT 'DebugPrint: perfstats qry4 - ' + CONVERT (varchar, @queryduration) + 'ms, rowcount=' + CONVERT(varchar, @rowcount) + CHAR(13) + CHAR(10)
END
/* Resultset #3: inputbuffers and query stats for "expensive" queries, head blockers, and "first-tier" blocked spids */
PRINT ''
RAISERROR ('-- notableactivequeries --', 0, 1) WITH NOWAIT
SET @sql = '
SELECT DISTINCT TOP 500
CONVERT (varchar(30), @runtime, 126) AS runtime, r.session_id AS session_id, r.request_id AS request_id, stat.execution_count AS plan_total_exec_count,
stat.total_worker_time/1000 AS plan_total_cpu_ms, stat.total_elapsed_time/1000 AS plan_total_duration_ms, stat.total_physical_reads AS plan_total_physical_reads,
stat.total_logical_writes AS plan_total_logical_writes, stat.total_logical_reads AS plan_total_logical_reads,
LEFT (CASE
WHEN pa.value=32767 THEN ''ResourceDb''
ELSE ISNULL (DB_NAME (CONVERT (sysname, pa.value)), CONVERT (sysname, pa.value))
END, 40) AS dbname,
sql.objectid AS objectid,
CONVERT (nvarchar(60), CASE
WHEN sql.objectid IS NULL THEN NULL
ELSE REPLACE (REPLACE (SUBSTRING (sql.[text] COLLATE Latin1_General_BIN, CHARINDEX (''CREATE '', SUBSTRING (sql.[text] COLLATE Latin1_General_BIN, 1, 1000)), 50), CHAR(10), '' ''), CHAR(13), '' '')
END) AS procname,
CONVERT (nvarchar(300), REPLACE (REPLACE (CONVERT (nvarchar(300), SUBSTRING (sql.[text], ISNULL (r.statement_start_offset, 0)/2 + 1,
CASE WHEN ISNULL (r.statement_end_offset, 8192) <= 0 THEN 8192
ELSE ISNULL (r.statement_end_offset, 8192)/2 - ISNULL (r.statement_start_offset, 0)/2 END + 1)) COLLATE Latin1_General_BIN,
CHAR(13), '' ''), CHAR(10), '' '')) AS stmt_text,
CONVERT (varbinary (64), (r.plan_handle)) AS plan_handle,
group_id
FROM #tmp_requests2 r
LEFT OUTER JOIN sys.dm_exec_query_stats stat ON r.plan_handle = stat.plan_handle AND stat.statement_start_offset = r.statement_start_offset
OUTER APPLY sys.dm_exec_plan_attributes (r.plan_handle) pa
OUTER APPLY sys.dm_exec_sql_text (ISNULL (r.sql_handle, r.most_recent_sql_handle)) AS sql
WHERE (pa.attribute = ''dbid'' COLLATE Latin1_General_BIN OR pa.attribute IS NULL) AND ISNULL (host_name, '''') != @appname AND r.session_id != @@SPID
AND (
/* We do not want to pull inputbuffers for everyone. The conditions below determine which ones we will fetch. */
(r.session_id IN (SELECT blocking_session_id FROM #tmp_requests2 WHERE blocking_session_id != 0)) -- head blockers
OR (r.blocking_session_id IN (SELECT blocking_session_id FROM #tmp_requests2 WHERE blocking_session_id != 0)) -- "first-tier" blocked requests
OR (LTRIM (r.wait_type) <> '''' OR r.wait_duration_ms > 500) -- waiting for some resource
OR (r.open_trans > 5) -- possible orphaned transaction
OR (r.request_total_elapsed_time > 25000) -- long-running query
OR (r.request_logical_reads > 1000000 OR r.request_cpu_time > 3000) -- expensive (CPU) query
OR (r.request_reads + r.request_writes > 5000 OR r.pending_io_count > 400) -- expensive (I/O) query
OR (r.memory_usage > 25600) -- expensive (memory > 200MB) query
-- OR (r.is_sick > 0) -- spinloop
)
ORDER BY stat.total_worker_time/1000 DESC'
IF '%runmode%' = 'REALTIME'
SET @sql = 'INSERT INTO tbl_NOTABLEACTIVEQUERIES (runtime, session_id, request_id, plan_total_exec_count,
plan_total_cpu_ms, plan_total_duration_ms, plan_total_physical_reads, plan_total_logical_writes,
plan_total_logical_reads, dbname, objectid, procname, stmt_text, plan_handle)' + @sql
SET @querystarttime = GETDATE()
EXEC sp_executesql @sql, N'@runtime datetime, @appname sysname', @runtime = @runtime, @appname = @appname
SET @rowcount = @@ROWCOUNT
RAISERROR ('', 0, 1) WITH NOWAIT
SET @queryduration = DATEDIFF (ms, @querystarttime, GETDATE())
IF @rowcount >= 500 PRINT 'WARNING: notableactivequeries output artificially limited to 500 rows'
IF @queryduration > @qrydurationwarnthreshold
PRINT 'DebugPrint: perfstats qry5 - ' + CONVERT (varchar, @queryduration) + 'ms, rowcount=' + CONVERT(varchar, @rowcount) + CHAR(13) + CHAR(10)
IF '%runmode%' = 'REALTIME' BEGIN
-- In near-realtime/direct-to-database mode, we have to maintain tbl_BLOCKING_CHAINS on-the-fly
-- 1) Insert new blocking chains
INSERT INTO tbl_BLOCKING_CHAINS (first_rownum, last_rownum, num_snapshots, blocking_start, blocking_end, head_blocker_session_id,
blocking_wait_type, max_blocked_task_count, max_total_wait_duration_ms, avg_wait_duration_ms, max_wait_duration_ms,
max_blocking_chain_depth, head_blocker_session_id_orig)
SELECT rownum, NULL, 1, runtime, NULL,
CASE WHEN blocking_resource_wait_type LIKE 'COMPILE%' THEN 'COMPILE BLOCKING' ELSE head_blocker_session_id END AS head_blocker_session_id,
blocking_resource_wait_type, blocked_task_count, tot_wait_duration_ms, avg_wait_duration_ms, max_wait_duration_ms,
max_blocking_chain_depth, head_blocker_session_id
FROM tbl_HEADBLOCKERSUMMARY b1
WHERE b1.runtime = @runtime AND NOT EXISTS (
SELECT * FROM tbl_BLOCKING_CHAINS b2
WHERE b2.blocking_end IS NULL -- end-of-blocking has not been detected yet
AND b2.head_blocker_session_id = CASE WHEN blocking_resource_wait_type LIKE 'COMPILE%' THEN 'COMPILE BLOCKING' ELSE head_blocker_session_id END -- same head blocker
AND b2.blocking_wait_type = b1.blocking_resource_wait_type -- same type of blocking
)
PRINT 'Inserted ' + CONVERT (varchar, @@ROWCOUNT) + ' new blocking chains...'
-- 2) Update statistics for in-progress blocking incidents
UPDATE tbl_BLOCKING_CHAINS
SET last_rownum = b2.rownum, num_snapshots = b1.num_snapshots + 1,
max_blocked_task_count = CASE WHEN b1.max_blocked_task_count > b2.blocked_task_count THEN b1.max_blocked_task_count ELSE b2.blocked_task_count END,
max_total_wait_duration_ms = CASE WHEN b1.max_total_wait_duration_ms > b2.tot_wait_duration_ms THEN b1.max_total_wait_duration_ms ELSE b2.tot_wait_duration_ms END,
avg_wait_duration_ms = (b1.num_snapshots-1) * b1.avg_wait_duration_ms + b2.avg_wait_duration_ms / b1.num_snapshots,
max_wait_duration_ms = CASE WHEN b1.max_wait_duration_ms > b2.max_wait_duration_ms THEN b1.max_wait_duration_ms ELSE b2.max_wait_duration_ms END,
max_blocking_chain_depth = CASE WHEN b1.max_blocking_chain_depth > b2.max_blocking_chain_depth THEN b1.max_blocking_chain_depth ELSE b2.max_blocking_chain_depth END
FROM tbl_BLOCKING_CHAINS b1
INNER JOIN tbl_HEADBLOCKERSUMMARY b2 ON b1.blocking_end IS NULL -- end-of-blocking has not been detected yet
AND b2.head_blocker_session_id = b1.head_blocker_session_id -- same head blocker
AND b1.blocking_wait_type = b2.blocking_resource_wait_type -- same type of blocking
AND b2.runtime = @runtime
PRINT 'Updated ' + CONVERT (varchar, @@ROWCOUNT) + ' in-progress blocking chains...'
-- 3) "Close out" blocking chains that were just resolved
UPDATE tbl_BLOCKING_CHAINS
SET blocking_end = @runtime
FROM tbl_BLOCKING_CHAINS b1
WHERE blocking_end IS NULL AND NOT EXISTS (
SELECT * FROM tbl_HEADBLOCKERSUMMARY b2 WHERE b2.runtime = @runtime
AND b2.head_blocker_session_id = b1.head_blocker_session_id -- same head blocker
AND b1.blocking_wait_type = b2.blocking_resource_wait_type -- same type of blocking
)
PRINT + CONVERT (varchar, @@ROWCOUNT) + ' blocking chains have ended.'
END
RAISERROR ('', 0, 1) WITH NOWAIT
END
-- Raise a diagnostic message if we use much more CPU than normal (a typical execution uses <300ms)
DECLARE @cpu_time bigint, @elapsed_time bigint
SELECT @cpu_time = cpu_time - @cpu_time_start, @elapsed_time = total_elapsed_time - @elapsed_time_start FROM sys.dm_exec_requests WHERE session_id = @@SPID
IF (@elapsed_time > 2000 OR @cpu_time > 750)
PRINT 'DebugPrint: perfstats tot - ' + CONVERT (varchar, @elapsed_time) + 'ms elapsed, ' + CONVERT (varchar, @cpu_time) + 'ms cpu' + CHAR(13) + CHAR(10)
GO
IF OBJECT_ID ('sp_perf_stats_infrequent10','P') IS NOT NULL
DROP PROCEDURE sp_perf_stats_infrequent10
GO
CREATE PROCEDURE sp_perf_stats_infrequent10 @runtime datetime, @firstrun int = 0 AS
SET NOCOUNT ON
DECLARE @queryduration int
DECLARE @querystarttime datetime
DECLARE @qrydurationwarnthreshold int
DECLARE @cpu_time_start bigint, @elapsed_time_start bigint
DECLARE @servermajorversion int
DECLARE @msg varchar(100)
DECLARE @sql nvarchar(max)
IF @runtime IS NULL
BEGIN
SET @runtime = GETDATE()
SET @msg = 'Start time: ' + CONVERT (varchar(30), @runtime, 126)
RAISERROR (@msg, 0, 1) WITH NOWAIT
END
SET @qrydurationwarnthreshold = 750
SELECT @cpu_time_start = cpu_time, @elapsed_time_start = total_elapsed_time FROM sys.dm_exec_requests WHERE session_id = @@SPID
/* SERVERPROPERTY ('ProductVersion') returns e.g. "9.00.2198.00" --> 9 */
SET @servermajorversion = REPLACE (LEFT (CONVERT (varchar, SERVERPROPERTY ('ProductVersion')), 2), '.', '')
/* Resultset #1: Server global wait stats */
PRINT ''
RAISERROR ('-- dm_os_wait_stats --', 0, 1) WITH NOWAIT;
SET @sql = '
SELECT CONVERT (varchar(30), @runtime, 126) AS runtime, LEFT (wait_type, 45) AS wait_type, waiting_tasks_count, wait_time_ms, max_wait_time_ms, signal_wait_time_ms
FROM sys.dm_os_wait_stats
WHERE waiting_tasks_count > 0 OR wait_time_ms > 0 OR signal_wait_time_ms > 0
ORDER BY wait_time_ms DESC'
IF '%runmode%' = 'REALTIME' SET @sql = 'INSERT INTO tbl_OS_WAIT_STATS (runtime, wait_type,
waiting_tasks_count, wait_time_ms, max_wait_time_ms, signal_wait_time_ms) ' + @sql
SET @querystarttime = GETDATE()
EXEC sp_executesql @sql, N'@runtime datetime', @runtime = @runtime
SET @queryduration = DATEDIFF (ms, @querystarttime, GETDATE())
RAISERROR ('', 0, 1) WITH NOWAIT
IF @queryduration > @qrydurationwarnthreshold
PRINT 'DebugPrint: perfstats2 qry1 - ' + CONVERT (varchar, @queryduration) + 'ms' + CHAR(13) + CHAR(10)
/* Resultset #2: Spinlock stats. No DMV for this -- we will synthesize the [runtime] column during data load. */
PRINT ''
RAISERROR ('-- DBCC SQLPERF (SPINLOCKSTATS) --', 0, 1) WITH NOWAIT;
DBCC SQLPERF (SPINLOCKSTATS)
/* Resultset #2: dm_os_spinlock_stats */
PRINT ''
RAISERROR ('-- dm_os_spinlock_stats --', 0, 1) WITH NOWAIT;
SET @sql = ' SELECT CONVERT (varchar(30), @runtime, 126) AS runtime, * FROM sys.dm_os_spinlock_stats'
SET @querystarttime = GETDATE()
EXEC sp_executesql @sql, N'@runtime datetime', @runtime = @runtime
SET @queryduration = DATEDIFF (ms, @querystarttime, GETDATE())
RAISERROR ('', 0, 1) WITH NOWAIT
IF @queryduration > @qrydurationwarnthreshold
PRINT 'DebugPrint: perfstats2 qry1 - ' + CONVERT (varchar, @queryduration) + 'ms' + CHAR(13) + CHAR(10)
/* Resultset #3: basic perf-related SQL perfmon counters */
PRINT ''
RAISERROR ('-- sysperfinfo_raw (general perf subset) --', 0, 1) WITH NOWAIT;
/* Force binary collation to speed up string comparisons (query uses 10-20ms CPU w/binary collation, 200-300ms otherwise) */
SET @sql = '
SELECT
CONVERT (varchar(30), @runtime, 126) AS runtime,
SUBSTRING ([object_name], CHARINDEX ('':'', [object_name]) + 1, 30) AS [object_name],
LEFT (counter_name, 40) AS counter_name, LEFT (instance_name, 50) AS instance_name, cntr_value
FROM sys.dm_os_performance_counters
WHERE
([object_name] LIKE ''%:Memory Manager%'' COLLATE Latin1_General_BIN AND counter_name COLLATE Latin1_General_BIN IN (''Connection Memory (KB)'', ''Granted Workspace Memory (KB)'', ''Lock Memory (KB)'', ''Memory Grants Outstanding'', ''Memory Grants Pending'', ''Optimizer Memory (KB)'', ''SQL Cache Memory (KB)''))
OR ([object_name] LIKE ''%:Buffer Manager%'' COLLATE Latin1_General_BIN AND counter_name COLLATE Latin1_General_BIN IN (''Buffer cache hit ratio'', ''Buffer cache hit ratio base'', ''Page lookups/sec'', ''Page life expectancy'', ''Lazy writes/sec'', ''Page reads/sec'', ''Page writes/sec'', ''Checkpoint pages/sec'', ''Free pages'', ''Total pages'', ''Target pages'', ''Stolen pages''))
OR ([object_name] LIKE ''%:General Statistics%'' COLLATE Latin1_General_BIN AND counter_name COLLATE Latin1_General_BIN IN (''User Connections'', ''Transactions'', ''Processes blocked''))
OR ([object_name] LIKE ''%:Access Methods%'' COLLATE Latin1_General_BIN AND counter_name COLLATE Latin1_General_BIN IN (''Index Searches/sec'', ''Pages Allocated/sec'', ''Table Lock Escalations/sec''))
OR ([object_name] LIKE ''%:SQL Statistics%'' COLLATE Latin1_General_BIN AND counter_name COLLATE Latin1_General_BIN IN (''Batch Requests/sec'', ''Forced Parameterizations/sec'', ''SQL Compilations/sec'', ''SQL Re-Compilations/sec'', ''SQL Attention rate''))
OR ([object_name] LIKE ''%:Transactions%'' COLLATE Latin1_General_BIN AND counter_name COLLATE Latin1_General_BIN IN (''Transactions'', ''Snapshot Transactions'', ''Longest Transaction Running Time'', ''Free Space in tempdb (KB)'', ''Version Generation rate (KB/s)''))
OR ([object_name] LIKE ''%:CLR%'' COLLATE Latin1_General_BIN AND counter_name COLLATE Latin1_General_BIN IN (''CLR Execution''))
OR ([object_name] LIKE ''%:Wait Statistics%'' COLLATE Latin1_General_BIN AND instance_name COLLATE Latin1_General_BIN IN (''Waits in progress'', ''Average wait time (ms)''))
OR ([object_name] LIKE ''%:Exec Statistics%'' COLLATE Latin1_General_BIN AND instance_name COLLATE Latin1_General_BIN IN (''Average execution time (ms)'', ''Execs in progress'', ''Cumulative execution time (ms) per second''))
OR ([object_name] LIKE ''%:Plan Cache%'' COLLATE Latin1_General_BIN AND instance_name = ''_Total'' COLLATE Latin1_General_BIN AND counter_name COLLATE Latin1_General_BIN IN (''Cache Hit Ratio'', ''Cache Hit Ratio Base'', ''Cache Pages'', ''Cache Object Counts''))
OR ([object_name] LIKE ''%:Locks%'' COLLATE Latin1_General_BIN AND instance_name = ''_Total'' COLLATE Latin1_General_BIN AND counter_name COLLATE Latin1_General_BIN IN (''Lock Requests/sec'', ''Number of Deadlocks/sec'', ''Lock Timeouts (timeout > 0)/sec''))
OR ([object_name] LIKE ''%:Databases%'' COLLATE Latin1_General_BIN AND instance_name = ''_Total'' COLLATE Latin1_General_BIN AND counter_name COLLATE Latin1_General_BIN IN (''Data File(s) Size (KB)'', ''Log File(s) Size (KB)'', ''Log File(s) Used Size (KB)'', ''Active Transactions'', ''Transactions/sec'', ''Bulk Copy Throughput/sec'', ''Backup/Restore Throughput/sec'', ''DBCC Logical Scan Bytes/sec'', ''Log Flush Wait Time'', ''Log Growths'', ''Log Shrinks''))
OR ([object_name] LIKE ''%:Cursor Manager by Type%'' COLLATE Latin1_General_BIN AND instance_name = ''_Total'' COLLATE Latin1_General_BIN AND counter_name COLLATE Latin1_General_BIN IN (''Cached Cursor Counts'', ''Cursor Requests/sec'', ''Cursor memory usage''))
OR ([object_name] LIKE ''%:Catalog Metadata%'' COLLATE Latin1_General_BIN AND instance_name = ''_Total'' COLLATE Latin1_General_BIN AND counter_name COLLATE Latin1_General_BIN IN (''Cache Hit Ratio'', ''Cache Hit Ratio Base'', ''Cache Entries Count''))'
IF '%runmode%' = 'REALTIME' SET @sql = 'INSERT INTO tbl_SYSPERFINFO (runtime, object_name, counter_name, instance_name, cntr_value)' + @sql
SET @querystarttime = GETDATE()
EXEC sp_executesql @sql, N'@runtime datetime', @runtime = @runtime
SET @queryduration = DATEDIFF (ms, @querystarttime, GETDATE())
RAISERROR ('', 0, 1) WITH NOWAIT
IF @queryduration > @qrydurationwarnthreshold
PRINT 'DebugPrint: perfstats2 qry3 - ' + CONVERT (varchar, @queryduration) + 'ms' + CHAR(13) + CHAR(10)
/* Resultset #4: SQL processor utilization */
RAISERROR ('', 0, 1) WITH NOWAIT
RAISERROR ('-- Recent SQL Processor Utilization (Health Records) --', 0, 1) WITH NOWAIT;
SET @sql = ''
IF '%runmode%' = 'REALTIME'
SET @sql = @sql + '
INSERT INTO tbl_SQL_CPU_HEALTH (runtime, record_id, EventTime, system_idle_cpu, sql_cpu_utilization) '
/* Work around: SQLBUD #445875 - Scheduler is mispelled (Schedluer) in RING_BUFFER_SCHEDULER_MONITOR ring buffer XML output
** Unfortunately, xml.value only takes string literal arguments, so we have to duplicate the query text to query
** the two different paths. */
IF (@servermajorversion > 9)
BEGIN
/* SQL 2008 or later (no cpu_ticks_in_ms column, but timestamp is in ms) */
IF @firstrun=1 SET @sql = @sql + ' SELECT '
ELSE SET @sql = @sql + ' SELECT TOP 5'
SET @sql = @sql + '
CONVERT (varchar(30), @runtime, 126) AS runtime,
record.value(''(Record/@id)[1]'', ''int'') AS record_id,
CONVERT (varchar, DATEADD (ms, -1 * (inf.ms_ticks - [timestamp]), GETDATE()), 126) AS EventTime, [timestamp],
record.value(''(Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]'', ''int'') AS system_idle_cpu,
record.value(''(Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]'', ''int'') AS sql_cpu_utilization
FROM sys.dm_os_sys_info inf CROSS JOIN (
SELECT timestamp, CONVERT (xml, record) AS record
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = ''RING_BUFFER_SCHEDULER_MONITOR''
AND record LIKE ''%%'') AS t
ORDER BY record.value(''(Record/@id)[1]'', ''int'') DESC'
END
ELSE IF (@servermajorversion = 9 AND SERVERPROPERTY ('ProductLevel') >= 'SP2')
BEGIN
/* SQL 2005 SP2 or later (post-SQLBUD #445875 fix) */
/* Alternative to ms_ticks (but doesn't work on current Katmai builds) is cpu_ticks/cpu_ticks_in_ms. */
IF @firstrun=1 SET @sql = @sql + ' SELECT '
ELSE SET @sql = @sql + ' SELECT TOP 5'
SET @sql = @sql + '
CONVERT (varchar(30), @runtime, 126) AS runtime,
record.value(''(Record/@id)[1]'', ''int'') AS record_id,
CONVERT (varchar, DATEADD (ms, -1 * ((inf.cpu_ticks / inf.cpu_ticks_in_ms) - [timestamp]), GETDATE()), 126) AS EventTime, [timestamp],
record.value(''(Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]'', ''int'') AS system_idle_cpu,
record.value(''(Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]'', ''int'') AS sql_cpu_utilization
FROM sys.dm_os_sys_info inf CROSS JOIN (
SELECT timestamp, CONVERT (xml, record) AS record
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = ''RING_BUFFER_SCHEDULER_MONITOR''
AND record LIKE ''%%'') AS t
ORDER BY record.value(''(Record/@id)[1]'', ''int'') DESC'
END
ELSE
BEGIN
/* SQL 2005 RTM or SP1 (pre-SQLBUD #445875 fix) */
IF @firstrun=1 SET @sql = @sql + ' SELECT '
ELSE SET @sql = @sql + ' SELECT TOP 5'
SET @sql = @sql + '
CONVERT (varchar(30), @runtime, 126) AS runtime,
record.value(''(Record/@id)[1]'', ''int'') AS record_id,
CONVERT (varchar, DATEADD (ms, -1 * ((inf.cpu_ticks / inf.cpu_ticks_in_ms) - [timestamp]), GETDATE()), 126) AS EventTime, [timestamp],
record.value(''(Record/SchedluerMonitorEvent/SystemHealth/SystemIdle)[1]'', ''int'') AS system_idle_cpu,
record.value(''(Record/SchedluerMonitorEvent/SystemHealth/ProcessUtilization)[1]'', ''int'') AS sql_cpu_utilization
FROM sys.dm_os_sys_info inf CROSS JOIN (
SELECT timestamp, CONVERT (xml, record) AS record
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = ''RING_BUFFER_SCHEDULER_MONITOR''
AND record LIKE ''%%'') AS t
ORDER BY record.value(''(Record/@id)[1]'', ''int'') DESC'
END
SET @querystarttime = GETDATE()
EXEC sp_executesql @sql, N'@runtime datetime', @runtime = @runtime
SET @queryduration = DATEDIFF (ms, @querystarttime, GETDATE())
RAISERROR ('', 0, 1) WITH NOWAIT
IF @queryduration > @qrydurationwarnthreshold
PRINT 'DebugPrint: perfstats2 qry4 - ' + CONVERT (varchar, @queryduration) + 'ms' + CHAR(13) + CHAR(10)
/* Resultset #5: sys.dm_os_sys_info (used to determine the # of CPUs SQL is able to use at the moment) */
PRINT ''
RAISERROR ('-- sys.dm_os_sys_info --', 0, 1) WITH NOWAIT;
SELECT
CONVERT (varchar(30), @runtime, 126) AS runtime, sysinfo.*
FROM sys.dm_os_sys_info sysinfo
RAISERROR ('', 0, 1) WITH NOWAIT
/* Resultset #6: sys.dm_os_latch_stats */
PRINT ''
RAISERROR ('-- sys.dm_os_latch_stats --', 0, 1) WITH NOWAIT;
SELECT
CONVERT (varchar(30), @runtime, 126) AS runtime, latchstats.*
FROM sys.dm_os_latch_stats latchstats
WHERE latchstats.waiting_requests_count > 0 OR latchstats.wait_time_ms > 0 OR latchstats.max_wait_time_ms > 0
ORDER BY latchstats.wait_time_ms DESC
RAISERROR ('', 0, 1) WITH NOWAIT
/* Resultset #7: File Stats Full
** To conserve space, output full dbname and filenames on 1st execution only. */
PRINT ''
RAISERROR ('-- File Stats (full) --', 0, 1) WITH NOWAIT;
SET @sql = '
SELECT
CONVERT (varchar(30), @runtime, 126) AS runtime, '
IF @firstrun = 0
SET @sql = @sql + '
NULL AS [database], NULL AS [file], '
ELSE
SET @sql = @sql + '
d.name AS [database], f.physical_name AS [file], '
SET @sql = @sql + '
fs.DbId, fs.FileId,
fs.IoStallMS / (fs.NumberReads + fs.NumberWrites + 1) AS AvgIOTimeMS, fs.[TimeStamp], fs.NumberReads, fs.BytesRead,
fs.IoStallReadMS, fs.NumberWrites, fs.BytesWritten, fs.IoStallWriteMS, fs.IoStallMS, fs.BytesOnDisk,
f.type, LEFT (f.type_desc, 10) AS type_desc, f.data_space_id, f.state, LEFT (f.state_desc, 15) AS state_desc,
f.[size], f.max_size, f.growth, f.is_sparse, f.is_percent_growth
FROM ::fn_virtualfilestats (default, default) fs
INNER JOIN master.dbo.sysdatabases d ON d.dbid = fs.DbId
INNER JOIN sys.master_files f ON fs.DbId = f.database_id AND fs.FileId = f.[file_id]
ORDER BY AvgIOTimeMS DESC'
IF '%runmode%' = 'REALTIME'
SET @sql = 'INSERT INTO tbl_FILE_STATS ' + @sql
SET @querystarttime = GETDATE()
EXEC sp_executesql @sql, N'@runtime datetime, @firstrun int', @runtime = @runtime, @firstrun = @firstrun
SET @queryduration = DATEDIFF (ms, @querystarttime, GETDATE())
RAISERROR ('', 0, 1) WITH NOWAIT
IF @queryduration > @qrydurationwarnthreshold
PRINT 'DebugPrint: perfstats2 qry7 - ' + CONVERT (varchar, @queryduration) + 'ms' + CHAR(13) + CHAR(10)
/* Raise a diagnostic message if we use more CPU than normal (a typical execution uses <200ms) */
DECLARE @cpu_time bigint, @elapsed_time bigint
SELECT @cpu_time = cpu_time - @cpu_time_start, @elapsed_time = total_elapsed_time - @elapsed_time_start FROM sys.dm_exec_requests WHERE session_id = @@SPID
IF (@elapsed_time > 3000 OR @cpu_time > 1000) BEGIN
PRINT ''
PRINT 'DebugPrint: perfstats2 tot - ' + CONVERT (varchar, @elapsed_time) + 'ms elapsed, ' + CONVERT (varchar, @cpu_time) + 'ms cpu' + CHAR(13) + CHAR(10)
END
/* Resultset #8: dm_exec_query_resource_semaphores
** dm_exec_query_resource_semaphores */
PRINT ''
RAISERROR ('-- dm_exec_query_resource_semaphores --', 0, 1) WITH NOWAIT;
SET @sql = '
SELECT
CONVERT (varchar(30), @runtime, 126) AS runtime, * from sys.dm_exec_query_resource_semaphores'
EXEC sp_executesql @sql, N'@runtime datetime', @runtime = @runtime
/* Resultset #9: dm_exec_query_memory_grants
** dm_exec_query_memory_grants. */
PRINT ''
RAISERROR ('-- dm_exec_query_memory_grants --', 0, 1) WITH NOWAIT;
SET @sql = '
SELECT
CONVERT (varchar(30), @runtime, 126) AS runtime, * from sys.dm_exec_query_memory_grants'
EXEC sp_executesql @sql, N'@runtime datetime', @runtime = @runtime
/* Resultset #10: dm_os_memory_brokers
** dm_exec_query_memory_grants. */
PRINT ''
RAISERROR ('-- dm_os_memory_brokers --', 0, 1) WITH NOWAIT;
SET @sql = '
SELECT
CONVERT (varchar(30), @runtime, 126) AS runtime, * from sys.dm_os_memory_brokers'
EXEC sp_executesql @sql, N'@runtime datetime', @runtime = @runtime
GO
DECLARE @servermajorversion int
-- SERVERPROPERTY ('ProductVersion') returns e.g. "9.00.2198.00" --> 9
SET @servermajorversion = REPLACE (LEFT (CONVERT (varchar, SERVERPROPERTY ('ProductVersion')), 2), '.', '')
IF (@servermajorversion < 10)
PRINT 'This script only runs on SQL Server 2008 and later. Exiting.'
ELSE BEGIN
-- Main loop
DECLARE @i int
DECLARE @msg varchar(100)
DECLARE @runtime datetime
SET @i = 0
WHILE (1=1)
BEGIN
SET @runtime = GETDATE()
SET @msg = 'Start time: ' + CONVERT (varchar(30), @runtime, 126)
IF '%runmode%' = 'REALTIME'
INSERT INTO tbl_RUNTIMES (runtime, source_script) VALUES (@runtime, 'SQL 2008 Perf Stats Script')
PRINT ''
RAISERROR (@msg, 0, 1) WITH NOWAIT
-- Collect sp_perf_stats10 every 10 seconds
EXEC sp_perf_stats10 @appname = '%appname%', @runtime = @runtime
-- Collect sp_perf_stats_infrequent10 every minute
IF @i = 0
EXEC sp_perf_stats_infrequent10 @runtime = @runtime, @firstrun = 1
ELSE IF @i % 6 = 0
EXEC sp_perf_stats_infrequent10 @runtime = @runtime
WAITFOR DELAY '0:0:10'
SET @i = @i + 1
END
END
GO