チューニング： インデックスに変更を加えずにルックアップ(キー参照)回数を減らす

クエリをチューニングする際、ボトルネックが大量のルックアップ（キー参照）にある場合、基本的なチューニングは「キー参照が発生しないようにカバリングインデックスを作成する」ことかと思いますが、カラム数が多いと容量の関係等でどうしてもインデックスをいじりたくないケースがあると思います。

ルックアップするときって、以下ABのいずれかのパターンかと思いますが、Bの場合はカバリングインデックスを作成しなくてもルックアップ回数を減らして高速化できることがあったので、その方法を紹介したいと思います。

A: 足りない述語を評価する

    SELECT
        Column1
    FROM
        SomeTable
    WHERE
        Column1 = 1
    AND Column2 = 2 -- ここを評価するためのルックアップをカットするためには、カバリングインデックスの作成が必要

B: SELECT句で指定された足りないカラムを取得する

    SELECT
        Column1
        ,Column2 -- ここを取得するためのルックアップは、場合によってはルックアップ回数を劇的に減らせる
    FROM
        SomeTable
    WHERE
        Column1 = 1

以降は、Bパターンのルックアップに限定して考えてます。

ルックアップの過剰発生を疑う

--サンプルテーブル
■ SomeTable1 (100万レコード)
　Column1が主キー
　Column1, Column2, Column3に複数列のインデックスが張ってある
　Column4-10はインデックスには含まれていないとする

■ SomeTable2 (100万レコード)
　Column1(SomeTable1との結合キー)
　ColumnA

    -- 問題のクエリ
    SELECT TOP (100) --上位100件取得
         Column1
        ,Column2
        ,Column3
        ,Column4
        ,Column5
        ,Column6
        ,Column7
        ,Column8
        ,Column9
        ,Column10
    FROM
         SomeTable1
    LEFT OUTER JOIN SomeTable2 ON SomeTable1.Column1 = SomeTable2.Column1
    WHERE
        SomeTable1.Column1 = 1
    AND SomeTable1.Column2 = 2
    AND SomeTable3.Column3 = 3
    ORDER BY
        SomeTable2.ColumnA

2テーブルをJOINし、WHERE句でレコードを絞り込み、ソートをかけて上位100件のレコードを取得するクエリです。

実行プラン

実行プランをみると、プランの前半でルックアップ（キー参照）でSELECT句で指定したカラムを取得し、それも含めて全件ソートしてます。
最終的にレコードは最大100件とれればよいため、ルックアップも最大100件で済むはずなのにもったいないなぁと。。

クエリを書き換えてルックアップを減らす

そこで、↓のようにクエリを書き換えます。

SELECT
     B.Column1
    ,B.Column2
    ,B.Column3
    ,B.Column4
    ,B.Column5
    ,B.Column6
    ,B.Column7
    ,B.Column8
    ,B.Column9
    ,B.Column10
FROM
(
    SELECT TOP (100) --上位100件取得
    -- 主キー(=ルックアップに必要な情報)と、ソートに必要な情報だけ取得
         Column1
        ,ColumnA
    FROM
         SomeTable1
    LEFT OUTER JOIN SomeTable2 ON SomeTable1.Column1 = SomeTable2.Column1
    WHERE
        SomeTable1.Column1 = 1
    AND SomeTable1.Column2 = 2
    AND SomeTable3.Column3 = 3
    ORDER BY
        SomeTable2.ColumnA
) AS A
JOIN SomeTable1 AS B ON A.Column1 = B.Column1
ORDER BY
    ColumnA

サブクエリの中ではレコードの絞込みに必要なカラムだけを指定し、ルックアップをカットしています。絞り込んだあとのレコードに対して、同じテーブルをわざとJOINさせ、擬似的にルックアップを必要最小限の回数で実現しているところがポイントです。
実行プランが以下のように変化しました。

↓SQLServedrProfilerでクエリ変更前後の計測結果（上：変更前　下：変更後）
このケースだとそれなりに高速化でき、負荷も減っています。

以上、わざとテーブルを追加でJOINさせることでルックアップ回数を減らし、クエリを高速化する方法でした。

このテクニックが有効なケースをまとめると
・キー参照が大量に発生していてボトルネックになってるが、容量等の問題でカバリングインデックスは作成したくない
・WHERE句の述語だけで絞り込んだ件数よりも、JOINして得られる最終的なレコード件数がかなり小さい

の2つを満たしているようなクエリであれば、試す価値があると思います！
※今回は分かりやすくするためにTOP(100)をつけて、最終的なレコード件数を無理やり100件に限定しています