【SQL Server】Missing Indexの見方｜不足インデックス提案をそのまま作ってはいけない理由

SELECT TOP (30)
    qs.execution_count,
    qs.total_logical_reads,
    qs.total_worker_time,
    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 statement_text,
    qp.query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE CAST(qp.query_plan AS nvarchar(max)) LIKE '%<MissingIndexes>%'
ORDER BY qs.total_logical_reads DESC;

SELECT TOP (50)
    DB_NAME(mid.database_id) AS database_name,
    OBJECT_SCHEMA_NAME(mid.object_id, mid.database_id) AS schema_name,
    OBJECT_NAME(mid.object_id, mid.database_id) AS table_name,
    migs.user_seeks,
    migs.user_scans,
    migs.avg_total_user_cost,
    migs.avg_user_impact,
    migs.last_user_seek,
    migs.last_user_scan,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns,
    (migs.avg_total_user_cost * migs.avg_user_impact *
        (migs.user_seeks + migs.user_scans)) AS estimated_improvement
FROM sys.dm_db_missing_index_group_stats AS migs
JOIN sys.dm_db_missing_index_groups AS mig
    ON mig.index_group_handle = migs.group_handle
JOIN sys.dm_db_missing_index_details AS mid
    ON mid.index_handle = mig.index_handle
WHERE mid.database_id = DB_ID()
ORDER BY estimated_improvement DESC;

SELECT TOP (100)
    OBJECT_SCHEMA_NAME(mid.object_id, mid.database_id) AS schema_name,
    OBJECT_NAME(mid.object_id, mid.database_id) AS table_name,
    migs.user_seeks,
    migs.user_scans,
    migs.last_user_seek,
    migs.last_user_scan,
    migs.avg_total_user_cost,
    migs.avg_user_impact,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns,
    LEN(ISNULL(mid.included_columns, '')) AS included_columns_text_length,
    (migs.avg_total_user_cost * migs.avg_user_impact *
        (migs.user_seeks + migs.user_scans)) AS estimated_improvement,
    CASE
        WHEN (migs.user_seeks + migs.user_scans) < 10 THEN 'LOW_EXECUTIONS'
        WHEN migs.avg_user_impact < 20 THEN 'LOW_IMPACT'
        WHEN LEN(ISNULL(mid.included_columns, '')) > 1000 THEN 'TOO_MANY_INCLUDED_COLUMNS'
        ELSE 'REVIEW'
    END AS review_status
FROM sys.dm_db_missing_index_group_stats AS migs
JOIN sys.dm_db_missing_index_groups AS mig
    ON mig.index_group_handle = migs.group_handle
JOIN sys.dm_db_missing_index_details AS mid
    ON mid.index_handle = mig.index_handle
WHERE mid.database_id = DB_ID()
ORDER BY estimated_improvement DESC;

SELECT TOP (30)
    'CREATE INDEX IX_' +
        REPLACE(REPLACE(OBJECT_NAME(mid.object_id, mid.database_id), '[', ''), ']', '') +
        '_missing_' + CONVERT(varchar(20), mid.index_handle) +
        ' ON ' + mid.statement + ' (' +
        ISNULL(mid.equality_columns, '') +
        CASE
            WHEN mid.equality_columns IS NOT NULL
             AND mid.inequality_columns IS NOT NULL THEN ', '
            ELSE ''
        END +
        ISNULL(mid.inequality_columns, '') + ')' +
        ISNULL(' INCLUDE (' + mid.included_columns + ')', '') AS create_index_candidate,
    migs.user_seeks,
    migs.user_scans,
    migs.avg_total_user_cost,
    migs.avg_user_impact
FROM sys.dm_db_missing_index_group_stats AS migs
JOIN sys.dm_db_missing_index_groups AS mig
    ON mig.index_group_handle = migs.group_handle
JOIN sys.dm_db_missing_index_details AS mid
    ON mid.index_handle = mig.index_handle
WHERE mid.database_id = DB_ID()
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact *
         (migs.user_seeks + migs.user_scans) DESC;

SELECT
    i.name AS index_name,
    i.type_desc,
    i.is_unique,
    ic.key_ordinal,
    ic.is_included_column,
    c.name AS column_name
FROM sys.indexes AS i
JOIN sys.index_columns AS ic
    ON ic.object_id = i.object_id
   AND ic.index_id = i.index_id
JOIN sys.columns AS c
    ON c.object_id = ic.object_id
   AND c.column_id = ic.column_id
WHERE i.object_id = OBJECT_ID(N'dbo.Orders')
ORDER BY i.name, ic.key_ordinal, ic.index_column_id;

SELECT
    OBJECT_SCHEMA_NAME(i.object_id) AS schema_name,
    OBJECT_NAME(i.object_id) AS table_name,
    i.name AS index_name,
    us.user_seeks,
    us.user_scans,
    us.user_lookups,
    us.user_updates,
    us.last_user_seek,
    us.last_user_scan,
    us.last_user_update
FROM sys.indexes AS i
LEFT JOIN sys.dm_db_index_usage_stats AS us
    ON us.object_id = i.object_id
   AND us.index_id = i.index_id
   AND us.database_id = DB_ID()
WHERE i.object_id = OBJECT_ID(N'dbo.Orders')
  AND i.name IS NOT NULL
ORDER BY us.user_updates DESC, us.user_seeks DESC;

SELECT
    OBJECT_SCHEMA_NAME(i.object_id) AS schema_name,
    OBJECT_NAME(i.object_id) AS table_name,
    i.name AS index_name,
    SUM(ps.used_page_count) * 8.0 / 1024 AS used_mb,
    SUM(ps.row_count) AS row_count
FROM sys.indexes AS i
JOIN sys.dm_db_partition_stats AS ps
    ON ps.object_id = i.object_id
   AND ps.index_id = i.index_id
WHERE i.object_id = OBJECT_ID(N'dbo.Orders')
GROUP BY i.object_id, i.name
ORDER BY used_mb DESC;

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

-- 作成前後で同じ条件で比較する
SELECT
    OrderID,
    CustomerID,
    OrderDate,
    TotalAmount,
    Status
FROM dbo.Orders
WHERE CustomerID = 100
  AND OrderDate >= '2026-05-01'
  AND OrderDate <  '2026-06-01';

SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;

SELECT TOP (20)
    qsq.query_id,
    SUM(qrs.count_executions) AS executions,
    AVG(qrs.avg_logical_io_reads) AS avg_logical_reads,
    SUM(qrs.count_executions) * AVG(qrs.avg_logical_io_reads) AS estimated_total_reads,
    MAX(qsp.plan_id) AS sample_plan_id,
    MIN(qsrsi.start_time) AS first_seen,
    MAX(qsrsi.end_time) AS last_seen
FROM sys.query_store_query AS qsq
JOIN sys.query_store_plan AS qsp
    ON qsp.query_id = qsq.query_id
JOIN sys.query_store_runtime_stats AS qrs
    ON qrs.plan_id = qsp.plan_id
JOIN sys.query_store_runtime_stats_interval AS qsrsi
    ON qsrsi.runtime_stats_interval_id = qrs.runtime_stats_interval_id
WHERE qsp.query_plan LIKE N'%<MissingIndexes>%'
  AND qsrsi.start_time >= DATEADD(HOUR, -48, SYSDATETIME())
GROUP BY qsq.query_id
ORDER BY estimated_total_reads DESC;

SELECT sqlserver_start_time
FROM sys.dm_os_sys_info;