SE の雑記

WITH AggregatedCPU AS ( 
  SELECT 
    '0x'+ CONVERT(VARCHAR(16), CONVERT(VARBINARY(8), q.query_hash), 2) 
      AS query_hash, 
    ROUND( 
      SUM(count_executions * avg_cpu_time / 1000.0), 
      3) AS total_cpu_ms, 
    ROUND( 
      SUM(count_executions * avg_cpu_time / 1000.0)/ SUM(count_executions), 
      3) AS avg_cpu_ms, 
    ROUND(MAX(rs.max_cpu_time / 1000.00), 3) AS max_cpu_ms, 
    ROUND(MAX(max_logical_io_reads), 3) AS max_logical_reads, 
    COUNT(DISTINCT p.plan_id) AS number_of_distinct_plans, 
    COUNT(DISTINCT p.query_id) AS number_of_distinct_query_ids, 
    SUM( 
      CASE 
        WHEN rs.execution_type_desc = 'Aborted' THEN count_executions 
        ELSE 0 
      END) AS aborted_execution_count, 
    SUM( 
      CASE 
        WHEN rs.execution_type_desc = 'Regular' THEN count_executions 
        ELSE 0 
      END) AS regular_execution_count, 
    SUM( 
      CASE 
        WHEN rs.execution_type_desc = 'Exception' THEN count_executions 
        ELSE 0 
      END) AS exception_execution_count, 
    SUM(count_executions) AS total_executions 
  FROM sys.query_store_query_text AS qt 
  INNER JOIN sys.query_store_query AS q  
    ON qt.query_text_id = q.query_text_id 
  INNER JOIN sys.query_store_plan AS p 
    ON q.query_id = p.query_id 
  INNER JOIN sys.query_store_runtime_stats AS rs 
    ON rs.plan_id = p.plan_id 
  INNER JOIN sys.query_store_runtime_stats_interval AS rsi 
    ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id 
  WHERE rs.execution_type_desc IN ('Regular', 'Aborted', 'Exception') 
        AND rsi.start_time >= DATEADD(HOUR, -2, GETUTCDATE()) 
  GROUP BY query_hash 
), 
OrderedCPU AS ( 
  SELECT 
    *, 
    ROW_NUMBER() OVER (ORDER BY total_cpu_ms DESC, query_hash ASC) AS RN 
  FROM AggregatedCPU 
) 
SELECT * 
FROM OrderedCPU AS OD 
WHERE OD.RN <= 10 
ORDER BY total_cpu_ms DESC;


SELECT TOP 10 
  req.session_id, 
  req.status, 
  req.start_time, 
  ROUND(req.cpu_time, 3) AS 'cpu_time_ms', 
  '0x'+ CONVERT(VARCHAR(16), CONVERT(VARBINARY(8), req.query_hash), 2) AS query_hash, 
  req.logical_reads, 
  req.dop,s.login_name, 
  s.host_name, 
  s.program_name, 
  OBJECT_NAME(st.objectid, st.dbid) AS 'object_name', 
  REPLACE( 
    REPLACE( 
      SUBSTRING( 
        st.text, 
        (req.statement_start_offset / 2) + 1, 
        (CASE req.statement_end_offset 
           WHEN -1 THEN DATALENGTH(st.text) 
           ELSE req.statement_end_offset 
         END - req.statement_start_offset) / 2 + 1), 
      CHAR(10), ' '), 
    CHAR(13), ' ') AS statement_text 
FROM sys.dm_exec_requests AS req 
INNER JOIN sys.dm_exec_sessions AS s 
  ON req.session_id = s.session_id 
CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) AS st 
WHERE req.session_id <> @@SPID 
ORDER BY req.cpu_time DESC;