サブクエリ内で自動作成されるインデックス

Question

サブクエリ内でインデックスが自動作成されているのかどうかの質問になります。

数億件を格納するテーブルからデータを抽出しています。

簡略化して書きましたが、下記パターン①からパターン②のように、

クエリを完結に記述することと、処理時間を早くする為に、サブクエリをやめる改修を行いました。

すると、処理時間が増加してしまいました。

これは恐らく、パターン①ではサブクエリ内で自動でcol_04のインデックスが作成されていたのではないかと思っています。

サブクエリ内でインデックスが作成されていることを確認する方法はありますでしょうか？

また、その他の理由が原因で処理時間が増加してしまっているのでしょうか？

ご教授お願い致します。

--------------------------パターン①--------------------------------------

select
       *
into #test_table
from
(
select
        col_01
       ,col_02
       ,case when col_03 = 1 then 1
　       else 0
　       end as col_04
from
       table_01
) a
where
       col_04 = 1
---------------------------------------------------------------------------

--------------------------パターン②--------------------------------------
select
       *
into #test_table
from
       table_01
where
       col_03 = 1

---------------------------------------------------------------------------

Answer 1

インデックスが自動作成されることはないと思います…たぶん。

クエリが異なれば、当然使用するインデックスが異なったりロックすべき範囲が異なってきたりといろいろあります。先入観を持たずに実行プランを確認したり、SQL Server Profilerで実際の動作を確認することが解決への近道ではないでしょうか。

Answer 2

佐祐里 様
お返事ありがとうございます。
オプティマイザーが最適化する為に、インデックスをビューに内部的に自動作成するのかな、
と思ってましたが、やっぱりないんでしょうかね。。

実行プランは表面的にしか理解できないのですが、
「into #test_table」の記述を省いてselect文にすると、クエリコストはパターン①とパターン②で同一になり、
記述を省かずselect into にすると、「Table Insert」でパターン②の方がコスト高くなります。

profilerはどのように使えば動作確認できるのでしょうか？

Answer 3

このクエリの通りなら、SELECTする列の数が速度を落とす原因ではないかと思います。

• パタン２：　table_01の全列を選択（データ量＝全ての列のデータ×全行数）
• パタン１： サブクエリで明記した列のみ選択（データ量＝（col_01＋col_02＋col_04列のデータ）×全行数）

テンポラリテーブルへ出力しているデータ量がパタン１よりパタン２の方が多くなっているように見受けられます。

つまりパタン２でcol_01、col_02、col_03のみに限定してやれば、パタン１に近い速度まで戻ると思います。

その上で速度向上を望むなら、table_01にcol_03のみのインデックスを作るのが良いかと思います。

Answer 4

ArbaArteria 様
ご回答ありがとうございます。
すいません、私の説明が足りなかったのですが、
パターン①もパターン②もoutputは同一という前提になります。
また、別部署管理の為、元テーブルのtable_01にインデックス付与することはできないという前提になります。
よろしくお願い致します。

Answer 5

> サブクエリ内でインデックスが自動作成されているのかどうかの質問になります。
自動作成されません。

> これは恐らく、パターン①ではサブクエリ内で自動でcol_04のインデックスが作成されていたのではないかと思っています。
違います。

> また、その他の理由が原因で処理時間が増加してしまっているのでしょうか？
その通りです。

処理時間が増えている原因を追うには、現状ではアタリもついていない状況ですので、先ずは通常のパフォーマンスチューニングの手法から用いるのがよろしいかと思います。
主に以下 3 点を切り口に当たるのが良いかと思います。
1.実際の実行プラン(「推定実行プラン」ではなく)を読み解く
2.トレース で Read / Write / CPU の数値を確認する
3.sys.dm_os_wait_stats の変化を追う

1.については情報量が膨大で特定の見方だけで判断できるものではないので、手にあまるようであればスキルのある方に依頼するか有償サポートの利用などを検討されてください。
一応自力で頑張るのであれば、以下の辺りを理解されてから取り掛かることをオススメします。
http://blogs.msdn.com/b/jpsql/archive/2011/09/07/10207003.aspx
なお、テーブル定義、インデックス定義、TEXT 形式か XML 形式の実行プランの 3 点全てを(適宜マスクした状態で)ここに提示できるのであれば、ある程度は私ないし誰かが見てくれるかもしれません。

2.については以下の辺りを参考にすれば難しいことは無いかと思われます。
http://blogs.msdn.com/b/jpsql/archive/2011/01/26/sql.aspx
もしテスト環境で状況が再現しているのであれば、テスト環境で Profiler を使ったほうが手っ取り早いです。
Profiler はとりあえずデフォルト設定のままでも使えますので、起動して DB に接続し、「ファイル＞新規追加」を選択してそのまま「OK」し、トレースが開始されたら問題のクエリを実行し、トレースを停止するだけです。
TextData 列に各パターンのクエリが表示されている行について、Read / Write / CPU の値を確認し比べればボトルネックのアタリがつきます。

3.については、各パターンのクエリの実行前と実行後に sys.dm_os_wait_stats の値を取得し、各パターンごとにどの数値がどれだけ変化したかを見比べます。
その差異が大きい場所が原因ないし間接的な原因となっていますので、そこからアタリを付けることができます。

いずれにせよ、パフォーマンス問題の原因はケースバイケースなので、様々な情報を読み解きながら解析していくことになり、それなりの技術的なスキルが必要となります。
パフォーマンスチューニングや解析を仕事としている会社さんも多数ありますので、原因分析が必要なのであればそういったところにご相談する(お金を払う)こともご検討されたほうが良いかと思います。
私は現在はそういった仕事をしていないので可能な範囲で無償の場でお答えしていますが、スキルが要求される事柄を社外に依頼する場合は通常はお金がかかる話だと考えてください。
調査方法や着眼点の示唆を色々返信頂いているところ、原因分析や手順など仔細まで追加で求められているように見受けられましたので、蛇足ながら記しておきます。

---

MCITP（Database Developer/Database Administrator）

Answer 6

SELECT *

と

SELECT col_01, col_02, col_03

とで実行結果は同じでも、プランは異なる事もある事は意識した方が良いかもしれません。

※見当違いだったらスミマセン