-- TODO: scheduler stats
--------------------
-- 
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...'
SET LANGUAGE us_english
PRINT '-- Script Source --'
SELECT 'SQL 2008 Perf Stats Script' AS script_name, '$Revision: 12 $ ($Change: 3355 $)' AS revision
PRINT ''
PRINT '-- Script and Environment Details --'
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 Perf Stats Script'
PRINT 'Script File Name         $File: SQL_2005_Perf_Stats.sql $'
PRINT 'Revision                 $Revision: 12 $ ($Change: 3355 $)'
PRINT 'Last Modified            $Date: 2007/09/25 10:03:24 $'
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 ''%<SystemHealth>%'') 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 ''%<SystemHealth>%'') 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 ''%<SystemHealth>%'') 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
