SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

SQL Server のクエリチューニングの参考情報

leave a comment

SQL Server のクエリチューニングをする際に参考になる情報のメモを。

SQL Server クエリのパフォーマンスを最適化する
クエリのチューニング
実行プランを読む – 基本編 (その 1)
実行プランを読む – 基本編 (その 2)
実行プランを読む – 基本編 (その 3)
どうする? SQL Server のクエリ パフォーマンスが低下した!

クエリチューニングの取り掛かりとしては SlideShare の以下の情報も参考になります。
SQL Server Query Tuning Tips – Get it Right the First Time
Microsoft SQL Server Query Tuning

クエリチューニングの考え方ですが、大きく分けると、

  • クエリ自体を改修することで実行効率を向上させる
  • クエリは改修せずに実行効率を向上させる

の 2 パターンがあるかと思います。

前者はクエリを書き換えることで実行効率を向上させるものになります。
データの取得方法自体を変更することで、実行効率を向上させるケースとなりますが、大幅な改修が入ることが多く、改修前と同等の実行結果を保証するのが難しいことが多々あるかと思います。
そのため、長期の改善としてクエリ改修が実施されるケースが多いかと。
# ストアドプロシージャ化もこちらに入るかと。

後者については、クエリは改修 (または小改修) せずに、クエリの実行効率を向上させるものになります。
こちらについてはインデックスチューニングが該当するかと。

インデックスチューニングとしては

  • Scan 操作を Seek 操作にするためのインデックス設定
  • Lookup を省略するためのインデックス設定
    • カバリングインデックス
    • 付加列インデックスを用いたカバリングインデックス
    • インデックス付きビュー

があるかと思います。
これらはインデックスを設定することで検索効率を上げるアプローチになり、基本的には既存のクエリの改修は行わずに実行プランを変更させます。
基本的にはインデックスを付与することで対応を行いますので、検索の効率と引き換えに、データ更新系のオーバーヘッドが高くなります。
また、インデックス付きビューについては、ビューのインデックスの解決 の以下の動作を使用した場合が、クエリの変更を伴わずに検索効率を向上させることになります。

インデックス付きビューは SQL Server のどのエディションでも作成できます。SQL Server Enterprise では、クエリ オプティマイザによる判断でインデックス付きビューが自動的に使用されます。その他のエディションでインデックス付きビューを使用するには、NOEXPAND テーブル ヒントを指定する必要があります。

この動作ですが、以下のようなインデックス付きビューを作成します。

CREATE VIEW IDX_VIEW
WITH SCHEMABINDING
AS
SELECT          
	C_CUSTKEY, N_NATIONKEY, O_ORDERKEY
FROM
	dbo.CUSTOMER 
	INNER JOIN dbo.ORDERS 
	ON CUSTOMER.C_CUSTKEY = ORDERS.O_CUSTKEY 
	INNER JOIN dbo.NATION 
	ON CUSTOMER.C_NATIONKEY = NATION.N_NATIONKEY
GO

CREATE UNIQUE CLUSTERED INDEX CIX_IDX_VIEW
ON IDX_VIEW (C_CUSTKEY,N_NATIONKEY,O_ORDERKEY)
GO

 

このようなインデックス付きビューを作成した場合、Enterprise Edition では、

SELECT    
	COUNT(*)
FROM
	dbo.CUSTOMER 
	INNER JOIN
	dbo.ORDERS 
	ON CUSTOMER.C_CUSTKEY = ORDERS.O_CUSTKEY 
	INNER JOIN
	dbo.NATION 
	ON CUSTOMER.C_NATIONKEY = NATION.N_NATIONKEY
GO

というような、インデックス付きビューを明示的に指定していないクエリを実行した場合に、インデックス付きビューを使用したほうがよいと判断されれば自動的にインデックス付きビューを使用してくれます。

この場合はクエリの改修は必要がなくなります。

クエリの小改修を伴うものとしては、

  • JOIN のヒント句
  • インデックスヒント
  • OPTION の指定 (MAXDOP や FORCESEEK)
  • RECOMPILE ヒントによる実行時引数に依存しないプランの作成
  • プランガイド

    # プランガイドは改修を伴わない方に入れてしまってもよいかもしれないですね。

があるかと。

これらは、クエリにオプションを設定する必要がありますので、クエリの小改修が必要となります。

あとはデータメンテナンスとして、

  • インデックスの再構成/再構築
  • 統計情報の最新化

があるかと。

インデックスの再構築時には 100% のデータサンプリングで統計情報が作成されますので、定期的に再構築を実施している場合には、統計情報の最新かも行われていることになります。

 

他にもいろいろとあるかと思いますが、クエリチューニングの取り掛かりとしてはこれらから始めてみるとよいかと。

Written by masayuki.ozawa

12月 10th, 2014 at 9:26 pm

Posted in SQL Server

Tagged with

Leave a Reply

*