PART 02 — DMV（動的管理ビュー）による取得とセクション定義

-- VIEW SERVER STATE 権限を付与
GRANT VIEW SERVER STATE TO [monitoring_user];
-- データベースレベル DMV には VIEW DATABASE STATE も必要
GRANT VIEW DATABASE STATE TO [monitoring_user];

-- Top 10 待機イベント（AWR の Top 5 Timed Events 相当）
-- アイドル系待機を除外してボトルネック候補だけを抽出
SELECT TOP 10
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    wait_time_ms - signal_wait_time_ms  AS resource_wait_time_ms,
    signal_wait_time_ms,
    max_wait_time_ms,
    CAST(100.0 * wait_time_ms
         / SUM(wait_time_ms) OVER ()     AS DECIMAL(5,2)) AS pct_total
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN (
    -- アイドル系待機（調査対象外）
    'SLEEP_TASK','SLEEP_SYSTEMTASK','SLEEP_DBSTARTUP',
    'SLEEP_DBRECOVER','SLEEP_DBTASK','SLEEP_TEMPDBSTARTUP',
    'SERVER_IDLE_CHECK','DISPATCHER_QUEUE_SEMAPHORE',
    'BROKER_TO_FLUSH','BROKER_TASK_STOP','BROKER_EVENTHANDLER',
    'CHECKPOINT_QUEUE','DBMIRROR_EVENTS_QUEUE','SQLTRACE_BUFFER_FLUSH',
    'CLR_AUTO_EVENT','CLR_MANUAL_EVENT','DISPATCHER_QUEUE_SEMAPHORE',
    'FT_IFTS_SCHEDULER_IDLE_WAIT','XE_DISPATCHER_WAIT',
    'XE_TIMER_EVENT','WAIT_XTP_OFFLINE_CKPT_NEW_LOG',
    'HADR_WORK_QUEUE','HADR_FILESTREAM_IOMGR_IOCOMPLETION',
    'SP_SERVER_DIAGNOSTICS_SLEEP','SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
    'ONDEMAND_TASK_QUEUE','REQUEST_FOR_DEADLOCK_SEARCH',
    'RESOURCE_QUEUE','LAZYWRITER_SLEEP','LOGMGR_QUEUE',
    'DIRTY_PAGE_POLL','DBMIRROR_WORKER_QUEUE','DBMIRROR_EVENTS_QUEUE',
    'WAIT_XTP_ONLINE_CKPT_NEW_LOG','PREEMPTIVE_HADR_LEASE_MECHANISM'
)
ORDER BY wait_time_ms DESC;

-- Top SQL by 平均経過時間（AWR の SQL ordered by Elapsed Time 相当）
SELECT TOP 20
    qs.execution_count,
    qs.total_elapsed_time / 1000          AS total_elapsed_ms,
    qs.total_elapsed_time / qs.execution_count / 1000
                                           AS avg_elapsed_ms,
    qs.total_worker_time / 1000           AS total_cpu_ms,
    qs.total_worker_time / qs.execution_count / 1000
                                           AS avg_cpu_ms,
    qs.total_logical_reads,
    qs.total_logical_reads / qs.execution_count
                                           AS avg_logical_reads,
    qs.total_physical_reads,
    qs.creation_time                       AS plan_cached_at,
    SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
        ((CASE qs.statement_end_offset
            WHEN -1 THEN DATALENGTH(st.text)
            ELSE qs.statement_end_offset
          END - qs.statement_start_offset)/2) + 1
    )                                      AS sql_text,
    qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle)   st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_elapsed_time DESC;

-- ファイル別 I/O 統計（AWR の I/O Stat by Tablespace 相当）
SELECT
    DB_NAME(vfs.database_id)          AS database_name,
    mf.physical_name,
    mf.type_desc                       AS file_type,
    vfs.num_of_reads,
    vfs.num_of_bytes_read / 1048576.0 AS read_mb,
    vfs.io_stall_read_ms,
    CASE vfs.num_of_reads
        WHEN 0 THEN 0
        ELSE vfs.io_stall_read_ms * 1.0 / vfs.num_of_reads
    END                                AS avg_read_latency_ms,
    vfs.num_of_writes,
    vfs.num_of_bytes_written / 1048576.0 AS write_mb,
    vfs.io_stall_write_ms,
    CASE vfs.num_of_writes
        WHEN 0 THEN 0
        ELSE vfs.io_stall_write_ms * 1.0 / vfs.num_of_writes
    END                                AS avg_write_latency_ms,
    vfs.io_stall
FROM sys.dm_io_virtual_file_stats(NULL, NULL) vfs
JOIN sys.master_files mf
    ON vfs.database_id = mf.database_id
    AND vfs.file_id    = mf.file_id
ORDER BY vfs.io_stall DESC;

-- DB 別バッファプール使用量（AWR の SGA Buffer Cache 相当）
SELECT
    DB_NAME(database_id)               AS database_name,
    COUNT(*)        * 8 / 1024.0       AS buffer_pool_mb,
    SUM(CASE WHEN is_modified = 1 THEN 1 ELSE 0 END) * 8 / 1024.0
                                        AS dirty_pages_mb
FROM sys.dm_os_buffer_descriptors
WHERE database_id <> 32767   -- プリアロケートページを除外
GROUP BY database_id
ORDER BY buffer_pool_mb DESC;

-- メモリコンポーネント別使用量（AWR の Memory Statistics 相当）
SELECT
    type,
    name,
    SUM(pages_kb) / 1024.0 AS used_mb
FROM sys.dm_os_memory_clerks
GROUP BY type, name
ORDER BY used_mb DESC;

-- ブロッキングチェーンとブロックされているクエリ一覧
SELECT
    wt.session_id          AS blocked_session,
    wt.blocking_session_id AS blocking_session,
    wt.wait_type,
    wt.wait_duration_ms,
    wt.resource_description,
    SUBSTRING(st.text, (r.statement_start_offset/2)+1,
        ((CASE r.statement_end_offset
            WHEN -1 THEN DATALENGTH(st.text)
            ELSE r.statement_end_offset
          END - r.statement_start_offset)/2)+1
    )                      AS blocked_sql
FROM sys.dm_os_waiting_tasks wt
JOIN sys.dm_exec_requests   r
    ON wt.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) st
WHERE wt.blocking_session_id IS NOT NULL
ORDER BY wt.wait_duration_ms DESC;

-- Top 10 ラッチ待機（AWR の Latch Statistics 相当）
SELECT TOP 10
    latch_class,
    waiting_requests_count,
    wait_time_ms,
    max_wait_time_ms,
    CAST(100.0 * wait_time_ms
         / NULLIF(SUM(wait_time_ms) OVER (), 0) AS DECIMAL(5,2)) AS pct_total
FROM sys.dm_os_latch_stats
WHERE wait_time_ms > 0
ORDER BY wait_time_ms DESC;

-- ① スナップショット1（計測開始時）を一時テーブルに保存
SELECT wait_type, waiting_tasks_count, wait_time_ms, signal_wait_time_ms
INTO #wait_snap1
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN (
    'SLEEP_TASK','SLEEP_SYSTEMTASK','SERVER_IDLE_CHECK',
    'DISPATCHER_QUEUE_SEMAPHORE','LAZYWRITER_SLEEP','LOGMGR_QUEUE',
    'REQUEST_FOR_DEADLOCK_SEARCH','RESOURCE_QUEUE','XE_TIMER_EVENT',
    'XE_DISPATCHER_WAIT','WAIT_XTP_ONLINE_CKPT_NEW_LOG'
);

-- ② 計測期間待機（例: 30 分）
WAITFOR DELAY '00:30:00';

-- ③ スナップショット2（計測終了時）を取得して差分を計算
SELECT
    s2.wait_type,
    s2.waiting_tasks_count - s1.waiting_tasks_count AS tasks_delta,
    s2.wait_time_ms        - s1.wait_time_ms        AS wait_ms_delta,
    s2.signal_wait_time_ms - s1.signal_wait_time_ms AS signal_ms_delta,
    (s2.wait_time_ms - s1.wait_time_ms)
    - (s2.signal_wait_time_ms - s1.signal_wait_time_ms)  AS resource_ms_delta
FROM sys.dm_os_wait_stats s2
JOIN #wait_snap1 s1 ON s2.wait_type = s1.wait_type
WHERE (s2.wait_time_ms - s1.wait_time_ms) > 0
ORDER BY wait_ms_delta DESC;

DROP TABLE #wait_snap1;