背景

クエリチューニングのためにインデックスを新規作成することは定期的にあるかと思いますが、スペックおよびデータ量が同等のdev/stg環境が無い限り、ある程度は「リリースしてみないと効果が分からない」を受け入れざるを得ないと思います。
そんな時に、prd環境でインデックスチューニングの効果検証をリリース前に実施する方法をご紹介します。
なお、完璧な精度で事前検証できるわけではありません。

チューニング対象クエリ

以下のクエリが重く、table1のcol1にインデックスを作成すれば良いのでは、と考えているとします。

select *
from table1 a
join table2 b on a.pk = b.fk
where a.col1 = 1

検証方法

tempdbを使います。

インデックスを作成したいテーブル（table1）と同じ内容のテーブルをtempdbに作成します。

create table #table1 (pk bigint primary key colustered, col1 int)

insert into #table1
select pk, col1 from #table1

そして一時テーブルにインデックスを作成します。

create index ix_#table1 on #table1 (col1)

あとは元のクエリのテーブルを一時テーブルに置き換えてパフォーマンス検証します。

select *
from #table1 a
join table2 b on a.pk = b.fk
where a.col1 = 1

メリット

db_datareaderロールにしか所属していなくても（＝読み取りしかできなくても）、一時テーブルの作成と、一時テーブルへのインデックス作成は可能なので、prd環境でありながらユーザーDBのデータファイルに一切変化を加えることなくある程度確度の高い事前検証を実施できます。

注意事項

tempdbのサイズと、作ろうとしているテーブルのサイズを事前に把握したうえで、tempdbサイズに余裕があることを確認してから実施します。

以上、小技的なテクニックですがインデックスチューニングの効果検証を事前に実施する方法をご紹介しました。
チューニング対象クエリが複雑でインデックス作成による効果が予測し辛い場合に、特に効果を発揮すると思います。