index-strategies

インデックス戦略

SQL Server のインデックス設計と最適化に関する包括的なガイドです。インデックスに関する推奨事項は、ワークロードと制約を考慮する必要があります。あるクエリに最適なインデックスが、書き込み、ストレージ、メンテナンス、パーティション切り替え、その他の重要なクエリに悪影響を及ぼすことがあります。

必須の事前確認事項

インデックスの DDL を推奨する前に、次の情報を収集するか、不明であることを明記してください。

• SQL Server のバージョン、エディション、互換性レベル、Azure SQL のサービスレベル。
• クエリテキスト、代表的なパラメーター、実際の実行プラン、行数、述語の選択性。
• 既存のクラスター化、非クラスター化、フィルター付き、列ストア、一意、無効、重複、重複部分のあるインデックス。
• テーブルの DDL、制約、データ型、計算列、圧縮、パーティション分割、統計情報。
• 書き込みワークロード：挿入／更新／削除の頻度、一括読み込み、メンテナンス時間、ストレージ予算。
• 変更の制約：新しいインデックスを追加できるか、大規模テーブルのインデックスを変更できるか、オンライン／再開可能なインデックス作成が許可されているか、パーティション切り替えが必要か、ステージングテーブルが許可されているか、書き込み負荷の増加を誰が承認するか。

../_shared/optimization-intake.md と ../_shared/assumption-tracker.md を使用してください。既存のインデックスとワークロードを確認せずに、不足インデックス DMV の出力を最終的な設計として提示しないでください。

クイックリファレンス

種類	最適な用途	注意事項
クラスター化	テーブルの主な並び順、範囲アクセス、幅が狭く安定したキー	変更にはコストがかかります。クラスター化キーは非クラスター化インデックスに含まれます。
非クラスター化	クエリ固有のシーク、結合、並べ替え	書き込みとストレージの負荷が増えます。
カバリング	キールックアップの繰り返しを回避	INCLUDE の肥大化は、キャッシュと書き込みに悪影響を及ぼすことがあります。
フィルター付き	安定した選択性の高いサブセット	クエリの述語がフィルター条件を満たす必要があります。パラメーターによって使用できない場合があります。
列ストア	分析、スキャン、集計、圧縮	小規模な更新や単一行のルックアップでは性能が低下することがあります。
一意	ビジネスルールの適用とオプティマイザーによる証明	実際のセマンティクスに適合する必要があります。

インデックス設計のワークフロー

1. クエリの形状から始める

クエリ内の列を役割ごとに分類します。

役割	インデックス設計への影響
等価述語	通常は先頭のキー列にします。選択性とワークロードでの再利用頻度を考慮して順序を決めます。
結合キー	シークキーや結合順序のサポートに役立ちます。
範囲述語	通常は等価キーの後に配置します。深くシーク可能な範囲条件は 1 つだけです。
ORDER BY ／ GROUP BY	並べ替えやストリーム集計を回避できるよう、キー順を検討します。
選択列	ルックアップのコストがストレージ／書き込みコストに見合う場合にのみ INCLUDE します。

例：

CREATE NONCLUSTERED INDEX IX_Orders_CustomerDate
ON dbo.Orders(CustomerID, OrderDate)
INCLUDE (Status, TotalAmount);

2. データ型と SARGability を検証する

述語が SARGable でない場合やデータ型が一致しない場合、インデックスの効果は十分に得られません。インデックスを追加する前に、パラメーター、一時テーブル、ソース列の型を確認してください。可能であれば、まず結合列／フィルター列の CONVERT_IMPLICIT を修正してください。

3. 既存のインデックスと比較する

新しいインデックスを追加する前に、次の点を確認してください。

• 既存のインデックスを安全に拡張できるか確認します。
• 重複する不足インデックスの要求を統合します。
• 先頭キーが同じ重複インデックスを特定します。
• INCLUDE 列を追加する前に、ルックアップ回数と選択列を評価します。
• フィルター付き、またはより幅の狭いインデックスで、書き込みコストを抑えながらホットパスを解決できるか検討します。

4. 変更の制約を考慮する

推奨事項を次のように分類します。

制約	より安全な対応
インデックスを追加できない	クエリの書き換え、統計情報、ヒント、一時ステージング、または Query Store ヒントを検討します。
大規模テーブルのインデックスを変更できない	幅の狭いフィルター付きインデックス、インデックス付きステージングテーブルを追加するか、テーブルにアクセスする前に対象行を減らします。
オンラインでのインデックス作成が許可されない	メンテナンス時間とブロッキングのリスクを計画します。サポートされている場合は、再開可能なインデックス作成を検討します。
パーティション切り替えが必要	アラインされたインデックスを優先し、明示的に許可されない限り、アラインされていないインデックスは避けます。
書き込み負荷が高い	キーの幅と INCLUDE リストを最小限にし、読み取り性能への効果を実証します。
ストレージに制約がある	重複を統合し、効果が不確かなカバリングインデックスは避けます。

クラスター化インデックスのガイドライン

理想的なクラスター化キーは、幅が狭く、一意で、変更されず、OLTP の挿入パターンでは通常、値が単調増加するものです。

CREATE CLUSTERED INDEX CIX_Orders ON dbo.Orders(OrderID);

意図したアクセスパターンと書き込みのトレードオフに合致する場合を除き、幅の広い複合クラスター化キーは避けてください。ランダムな GUID によるクラスター化はページ分割を引き起こすことがあります。適切な場合は、NEWSEQUENTIALID()、代理キー、またはフィルファクター戦略を検討してください。

カバリングインデックス

検索コストが大きい場合、カバリングインデックスを使用するとルックアップを回避できます。

CREATE NONCLUSTERED INDEX IX_Orders_CustomerID_Cover
ON dbo.Orders(CustomerID)
INCLUDE (OrderDate, TotalAmount, Status);

出力専用の列には INCLUDE 列を使用し、フィルターや並べ替えには使用しないでください。INCLUDE リストは最小限にしてください。幅の広い INCLUDE は、たまに発生するルックアップよりも不利になることがあります。

フィルター付きインデックス

フィルター付きインデックスは、安定したサブセットに効果的です。

CREATE NONCLUSTERED INDEX IX_Orders_Open_ByCustomer
ON dbo.Orders(CustomerID, OrderDate)
WHERE Status = 'Open';

要件：

• クエリの述語がフィルター条件を満たす必要があります。
• パラメーター化されたクエリで確実に一致させるには、再コンパイルまたはリテラルを指定した動的 SQL が必要になる場合があります。
• オプティマイザーにとって明確でない場合は、フィルター列を含める必要があります。
• パラメーター スニッフィングのリスクとプランキャッシュの動作を検証してください。

列ストアインデックス

分析スキャン、集計、圧縮には列ストアを使用してください。

CREATE CLUSTERED COLUMNSTORE INDEX CCI_FactSales
ON dbo.FactSales;

CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_Orders_Analysis
ON dbo.Orders(OrderDate, ProductID, Quantity, Amount)
WHERE Status = 'Completed';

ベストプラクティス：

1. 可能であれば、少なくとも 102,400 行のバッチをロードします。
2. セグメントの除外を改善するため、一般的な除外列に基づいてデータを並べ替えます。
3. REORGANIZE を使用して、デルタ行グループを圧縮し、行グループを統合します。
4. 純粋な列ストア設計では、頻繁に発生する単一行の更新を避けます。
5. 大規模なファクトテーブルでスライディングウィンドウが必要な場合は、管理しやすくするためにパーティション分割と組み合わせます。

パーティションの配置確認

パーティション分割されたテーブルでは、インデックス設計で除外とメンテナンスを考慮する必要があります。

確認事項：

• パーティション関数／スキーム、境界の種類、および RANGE LEFT と RANGE RIGHT のどちらか。
• 基本のパーティション分割列とクエリ述語の列。
• 重要な各非クラスター化インデックスが、パーティションに整合しているか、意図的に整合していないか。
• 必要に応じて、一意インデックスにパーティション分割列が含まれているか。
• 実行プランで実際にパーティションが除外されているか。
• 危険な述語：パーティション列に対する関数、型の不一致、関連する非パーティション日付列によるフィルター、すべてに一致する OR 述語、またはリモートソース。

互換性のある SARGable な形式でパーティション分割列を対象にしているか、信頼できる制約によって同等性が証明されていない限り、日付フィルターによってパーティションが除外されると断言しないでください。references/partition-alignment-analysis.md を参照してください。

メンテナンスと運用

断片化に関するルールはワークロードによって異なりますが、一般的な開始点は次のとおりです。

断片化	一般的な対応
5% 未満	対応不要です。
5～30%	ページ数とワークロードを考慮し、妥当であれば再構成します。
30% 超	メンテナンス時間、エディション、ブロッキングが許せば再構築します。

ALTER INDEX IX_Orders_CustomerID ON dbo.Orders REORGANIZE;

ALTER INDEX IX_Orders_CustomerID ON dbo.Orders
REBUILD WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 60);

自動作成統計またはサンプリングされた統計が不十分な場合は、意味のあるインデックス変更の後に統計を更新してください。

UPDATE STATISTICS dbo.Orders IX_Orders_CustomerID WITH FULLSCAN;

有用な診断情報

最後の再起動またはデータベースのアタッチ以降のインデックス使用状況：

SELECT
    OBJECT_SCHEMA_NAME(i.object_id) AS SchemaName,
    OBJECT_NAME(i.object_id) AS TableName,
    i.name AS IndexName,
    ius.user_seeks,
    ius.user_scans,
    ius.user_lookups,
    ius.user_updates
FROM sys.indexes AS i
LEFT JOIN sys.dm_db_index_usage_stats AS ius
  ON i.object_id = ius.object_id
 AND i.index_id = ius.index_id
 AND ius.database_id = DB_ID()
WHERE OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1
ORDER BY COALESCE(ius.user_seeks, 0) + COALESCE(ius.user_scans, 0) DESC;

不足しているインデックスの候補：

SELECT
    migs.avg_user_impact AS ImpactPercent,
    mid.statement AS TableName,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns
FROM sys.dm_db_missing_index_groups AS mig
JOIN sys.dm_db_missing_index_group_stats AS migs
  ON mig.index_group_handle = migs.group_handle
JOIN sys.dm_db_missing_index_details AS mid
  ON mig.index_handle = mid.index_handle
WHERE mid.database_id = DB_ID()
ORDER BY migs.avg_user_impact DESC;

推奨事項の形式

以下を提示します。

1. 根拠：プランの演算子、読み取り、ルックアップ、見積もり、不足インデックスのリクエスト、またはワークロード指標。
2. DDL：スキーマ修飾名と最小限のキー／INCLUDE 列を指定した、提案するインデックス。
3. 理由：アクセスパス、並べ替え、カバレッジ、または除外による利点。
4. コスト：ストレージ、書き込み、ブロッキング、メンテナンス、パーティションへの影響。
5. 検証：変更前後のプラン、論理読み取り、CPU、経過時間、書き込みへの影響。
6. ロールバックまたは代替案：特に巨大なテーブルの場合。

参考資料

• ../_shared/optimization-intake.md - 最適化前の情報収集。
• ../_shared/assumption-tracker.md - 仮定のステータス追跡。
• references/partition-alignment-analysis.md - パーティションスキームと除外の分析。