【SQL Server】インデックスが使われない原因｜暗黙変換・関数・LIKE・複合インデックスの落とし穴

-- CustomerCode列がvarchar(20)なのに、nvarcharパラメータで比較している例
DECLARE @CustomerCode nvarchar(20) = N'C001';

SELECT OrderID, CustomerCode, OrderDate
FROM dbo.Orders
WHERE CustomerCode = @CustomerCode;

-- 列定義に合わせてパラメータ型をそろえる
DECLARE @CustomerCode varchar(20) = 'C001';

SELECT OrderID, CustomerCode, OrderDate
FROM dbo.Orders
WHERE CustomerCode = @CustomerCode;

SELECT TOP (50)
    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 '%CONVERT_IMPLICIT%'
ORDER BY qs.total_logical_reads DESC;

-- 悪い例: OrderDate列に関数をかけている
SELECT OrderID, OrderDate, CustomerID
FROM dbo.Orders
WHERE CONVERT(date, OrderDate) = '2026-05-01';

-- 改善例: 列側を加工せず、範囲条件にする
SELECT OrderID, OrderDate, CustomerID
FROM dbo.Orders
WHERE OrderDate >= '2026-05-01'
  AND OrderDate <  '2026-05-02';

-- 前方一致。Name列のインデックスを使いやすい
SELECT CustomerID, CustomerName
FROM dbo.Customers
WHERE CustomerName LIKE 'tanaka%';

-- 部分一致。通常のB-treeインデックスでは絞りにくい
SELECT CustomerID, CustomerName
FROM dbo.Customers
WHERE CustomerName LIKE '%tanaka%';

CREATE INDEX IX_Orders_CustomerID_OrderDate
ON dbo.Orders (CustomerID, OrderDate);

-- 使いやすい: 先頭列CustomerIDを条件に使っている
SELECT OrderID, CustomerID, OrderDate
FROM dbo.Orders
WHERE CustomerID = 100
  AND OrderDate >= '2026-05-01';

-- 使いにくい: 先頭列CustomerIDを使っていない
SELECT OrderID, CustomerID, OrderDate
FROM dbo.Orders
WHERE OrderDate >= '2026-05-01';

-- 未処理の注文だけを対象にするフィルターインデックス
CREATE INDEX IX_Orders_Unprocessed
ON dbo.Orders (OrderDate)
INCLUDE (CustomerID, TotalAmount)
WHERE ProcessedFlag = 0;

-- 使いやすい: フィルター条件と一致している
SELECT OrderID, CustomerID, OrderDate, TotalAmount
FROM dbo.Orders
WHERE ProcessedFlag = 0
  AND OrderDate >= '2026-05-01';

-- 使いにくい: フィルター条件がクエリから判断しにくい
DECLARE @ProcessedFlag bit = 0;

SELECT OrderID, CustomerID, OrderDate, TotalAmount
FROM dbo.Orders
WHERE ProcessedFlag = @ProcessedFlag
  AND OrderDate >= '2026-05-01';

-- 日付だけで検索するための計算列例
ALTER TABLE dbo.Orders
ADD OrderDateOnly AS CONVERT(date, OrderDate) PERSISTED;

CREATE INDEX IX_Orders_OrderDateOnly
ON dbo.Orders (OrderDateOnly);

SELECT OrderID, CustomerID, OrderDate
FROM dbo.Orders
WHERE OrderDateOnly = '2026-05-01';

-- 計算列インデックスやインデックス付きビューで重要なSETオプション
SET ANSI_NULLS ON;
SET ANSI_PADDING ON;
SET ANSI_WARNINGS ON;
SET ARITHABORT ON;
SET CONCAT_NULL_YIELDS_NULL ON;
SET QUOTED_IDENTIFIER ON;
SET NUMERIC_ROUNDABORT OFF;

-- OR条件で広く読まれることがある
SELECT OrderID, CustomerID, Status, OrderDate
FROM dbo.Orders
WHERE CustomerID = 100
   OR Status = 'Cancelled';

-- 条件ごとに分ける案。重複が出る条件なら除外条件やUNIONを検討する
SELECT OrderID, CustomerID, Status, OrderDate
FROM dbo.Orders
WHERE CustomerID = 100
UNION ALL
SELECT OrderID, CustomerID, Status, OrderDate
FROM dbo.Orders
WHERE Status = 'Cancelled'
  AND CustomerID <> 100;

CREATE INDEX IX_Orders_CustomerID
ON dbo.Orders (CustomerID);

-- CustomerIDではSeekできても、取得列が多いとLookupが発生することがある
SELECT
    OrderID,
    CustomerID,
    OrderDate,
    TotalAmount,
    ShippingAddress
FROM dbo.Orders
WHERE CustomerID = 100;

CREATE INDEX IX_Orders_CustomerID_Covering
ON dbo.Orders (CustomerID)
INCLUDE (OrderDate, TotalAmount, ShippingAddress);

SELECT
    s.name AS stats_name,
    sp.last_updated,
    sp.rows,
    sp.rows_sampled,
    sp.modification_counter
FROM sys.stats AS s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp
WHERE s.object_id = OBJECT_ID(N'dbo.Orders')
ORDER BY sp.modification_counter DESC;

SELECT
    OBJECT_SCHEMA_NAME(i.object_id) AS schema_name,
    OBJECT_NAME(i.object_id) AS table_name,
    i.name AS index_name,
    i.index_id,
    i.type_desc,
    us.user_seeks,
    us.user_scans,
    us.user_lookups,
    us.user_updates,
    us.last_user_seek,
    us.last_user_scan,
    us.last_user_lookup,
    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 OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1
ORDER BY us.user_seeks DESC, us.user_scans DESC;

SELECT TOP (30)
    DB_NAME(mid.database_id) AS database_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,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns
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;

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

-- 改善前後で同じ条件・同じパラメータで比較する
SELECT OrderID, CustomerID, OrderDate, TotalAmount
FROM dbo.Orders
WHERE CustomerID = 100
  AND OrderDate >= '2026-05-01'
  AND OrderDate <  '2026-06-01';

SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;