SQL Server 2005 Tips and Tips

アドホックなクエリ (SQL) がたくさん実行されている環境では、SQL Server のパフォーマンスが大きく低下する可能性があります。アドホックなクエリは、実行プランの再利用効率が悪く、メモリ内のプロシージャ キャッシュ領域を無駄に消費することになるからです。また、実行プランを再利用できないと、実行のたびにコンパイルが発生する割合も高くなります。コンパイルは、CPU 負荷が高く、複雑なクエリになるほど、コンパイルにかかる時間が増えます。たとえば、テーブルの結合が多く、かつ WHERE 句で指定する検索条件が多い場合には、コンパイル時間に 2 秒〜 3 秒かかってしまうケースもあります。これでは、お客さんが「クエリ結果を 3 秒以内に取得したい」と要望する場合、コンパイルだけで時間がいっぱいになってしまいます。
したがって、アドホック クエリがたくさん実行される状況をできる限り回避することが、SQL Server のパフォーマンスを向上させるうえで非常に重要になります。その回避方法は、クエリをパラメータ化し、実行プランを再利用させることです。そこで今回は、このクエリをパラメータ化する方法をご紹介します。

トピック

	アドホック クエリとは
	プロシージャ キャッシュの中身を見る 〜 dm_exec_cached_plans 〜
	自動パラメータ化
	クエリのパラメータ化 〜 sp_executesql 〜

アドホック クエリとは

アドホック (Ad hoc) とは、「その場限りの」や「その場しのぎの」の意味で、アドホックなクエリは、まったく同じクエリのときのみ実行プランが再利用されるクエリのことをいいます。たとえば、以下のクエリは、アドホック クエリに該当します。

SELECT * FROM 社員 WHERE 姓 LIKE 'A%'

このクエリは、社員テーブルの「姓」列が “A” で始まる社員 (Aoki さんや Aiba さんなど) を検索しています。これを次のように “B” で始まる社員 (Baba さんや Bando さん) を検索するように変更します。

SELECT * FROM 社員 WHERE 姓 LIKE 'B%'

このクエリは、最初のクエリとほとんど同じ (A と B が違うだけ) ですが、最初のクエリで使用された実行プランは再利用せず、別個の実行プランが作成されます。
これらのクエリは、以下のように、まったく同じクエリのときだけしか実行プランが再利用されません。

SELECT * FROM 社員 WHERE 姓 LIKE 'B%'

このように、一字一句まったく同じであれば、B 用に作られた実行プランが再利用されます。では、これを確認してみましょう。確認には、プロシージャ キャッシュの中身を見るのが一番簡単です。

ページのトップへ

プロシージャ キャッシュの中身を見る 〜 dm_exec_cached_plans 〜

プロシージャ キャッシュの中身を参照するには、動的管理ビュー (DMV : Dynamic Management View) の dm_exec_cached_plans と、動的管理関数 (DMF : Dynamic Management Function) の dm_exec_sql_text を利用します。次のように実行すると、どの SQL に対応した実行プランなのかと、実行プランが再利用された回数を確認することができます。

SELECT objtype, text, usecounts, *    
 FROM sys.dm_exec_cached_plans    
         CROSS APPLY    
          sys.dm_exec_sql_text( plan_handle )

プロシージャ キャッシュの中身の参照
拡大図を見る

結果の各行は、実行プランに関する情報です。text 列が実行された SQL クエリ、usecounts 列が実行プランが再利用された回数です。”B” で始まる社員検索の実行プランは、usecounts 列が 2 であり、再利用されていることが確認できます。objtype 列は、実行プランのタイプで、Adhoc の場合は、まったく同じクエリの場合のみ再利用されます (アドホック クエリは、実行プランの objtype 列が Adhoc となるクエリのことを指します)。
このように、アドホック クエリがたくさん実行されていると、検索条件に指定した値ごとに異なる実行プランが格納されることになり、プロシージャ キャッシュを無駄に消費することになります。これでは、データ キャッシュなど、ほかのメモリ領域を圧迫することになり、SQL Server のパフォーマンスが大きく低下する原因になりかねません。
また、実行プランのコンパイルは、CPU 負荷が高く、複雑なクエリになるほど、コンパイル時間もかかります。したがって、複雑なクエリで、かつアドホックの場合には、コンパイルが多発するので、CPU ボトルネックになる可能性も高くなります。このような状況を回避する方法が、「クエリのパラメータ化」です。

ページのトップへ

自動パラメータ化

SQL Server には、単純なクエリであれば、自動的にクエリをパラメータ化する機能が備わっています。たとえば、以下のクエリを実行してみます。

SELECT * FROM 社員 WHERE 社員番号 = 1001

これにより、社員番号が 1001 番の社員を検索します。次に、社員番号が 9999 番の社員を検索してみます。

SELECT * FROM 社員 WHERE 社員番号 = 9999

さらに、社員番号が 7777 番の社員を検索してみます。

SELECT * FROM 社員 WHERE 社員番号 = 7777

この後、プロシージャ キャッシュの中身を参照すると、以下のようになります。

プロシージャ キャッシュの参照
拡大図を見る

objtype 列に「Prepared」と表示されるプランが存在し、usecounts 列 (再利用回数) が 3 回となっているのが確認できます。また、text 列は、以下のように表示されています。

(@1 smallint) SELECT * FROM [社員] WHERE [社員番号]=@1

「社員番号 = 1001」や「社員番号 = 9999」と実行していた部分が、「社員番号 = @p1」とパラメータ化され、同じ実行プランが再利用されたことがわかります。このように SQL Server は、単純なクエリであれば、自動的にパラメータ化する機能が備わっています。この機能は「自動パラメータ化」または「簡易パラメータ化」と呼ばれています。
しかし、この機能は、あくまでも単純なクエリの場合にのみ有効であることに注意が必要です。たとえば、前述の LIKE 演算子を含むクエリや、IN 演算子を含むクエリ、テーブルの結合 (JOIN) を含むクエリだと、自動パラメータ化が行われません (アドホック クエリになります)。そのほかにも、以下のものを含むクエリは、自動パラメータ化が行われず、アドホック クエリになります。

このように、多くのクエリは、アドホック クエリとなるので注意が必要です。上記のほかにも、自動パラメータ化されないクエリがありますが、それらについては、以下のホワイト ペーパーに詳しく記載されているので参考にしてください。

SQL Server 2005 のバッチのコンパイル、再コンパイル、およびプランのキャッシュに関する問題
http://www.microsoft.com/japan/technet/prodtechnol/sql/2005/recomp.mspx

ページのトップへ

クエリのパラメータ化 〜 sp_executesql 〜

自動パラメータ化されないクエリでも、明示的にパラメータ化を行い、同じプランを再利用させることができます。明示的なパラメータ化を行うには、以下の 3 つの方法があります。

◎sp_executesql は、以下のように利用します。

sp_executesql N'SELECT * FROM 社員 WHERE 姓 LIKE @p1'   
  ,N'@p1 varchar(40)'   
  ,@p1 = 'A%'

これは、最初のクエリ (A で始まる社員の検索) をパラメータ化したものですが、「姓 LIKE @p1」のように “@p1” でパラメータ化し、このデータ型を第2引数で “varchar (40)” と定義し、第 3 引数で値 (A%) を代入しています。したがって、B で始まる社員を検索する場合は、以下のように第 3 引数を変えて実行します。

sp_executesql N'SELECT * FROM 社員 WHERE 姓 LIKE @p1'   
  ,N'@p1 varchar(40)'   
  ,@p1 = 'B%'

このように実行すると、プロシージャ キャッシュの中身は、以下のようになります。

プロシージャ キャッシュの中身の確認
拡大図を見る

objtype 列が「Prepared」で、usecounts 列 (再利用回数) が 2 回となり、クエリをパラメータ化できたことが確認できます。このようにパラメータ化を行うと、実行プランを再利用できるようになり、プロシージャ キャッシュ領域を無駄使いしないで済みます。また、余計なコンパイルも発生しないので、パフォーマンスが向上します。

◎ストアド プロシージャを利用する場合
ストアド プロシージャを利用してパラメータ化したい場合は、次のように入力パラメータを利用します。

CREATE PROCEDURE proc1  
 @p1 varchar(40)  
AS  
 SELECT * FROM 社員 WHERE 姓 LIKE @p1

◎ADO.NET の Parameter クラスを利用する場合
ADO.NET を利用してパラメータ化したい場合は、次のように SqlParameter クラスを利用します。

Imports System.Data.SqlClient  
Imports System.Data  
：
Using conn As New SqlConnection()
    conn.ConnectionString = "Data Source=salt;" _
                      & "Initial Catalog=Demo;" _
                      & "Integrated Security=SSPI;"
    Using cmd As New SqlCommand
        cmd.CommandType = CommandType.Text
        cmd.CommandText = "SELECT * FROM 社員 WHERE 姓 LIKE @p1"
        cmd.Connection = conn

        Dim p1 As SqlParameter = cmd.Parameters.Add("@p1", SqlDbType.VarChar, 40)
        p1.Value = "A%"
        Try
            conn.Open()
            Using reader As SqlDataReader = cmd.ExecuteReader()
                While reader.Read
                    Me.ListBox1.Items.Add(reader.GetValue(1).ToString())
                End While
            End Using
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            conn.Close()
        End Try
    End Using
End Using

このように SqlParameter クラスを利用すると、内部的には sp_executesql に変換されて実行されるようになります。これは、SQL Server Profiler ツールを使って、以下のように確認できます。

SQL Server Profiler で内部実行されたクエリを確認
拡大図を見る

◎結果セットが大きく異なる場合に注意
以上のように、クエリのパラメータ化は、プロシージャ キャッシュの節約と、コンパイル負荷の軽減により、パフォーマンスの向上に大きく貢献しますが、1 点だけ注意する必要があります。それは、パラメータに代入した値によって、結果セットの大きさが大きく異なる場合です。たとえば、最初のクエリの「姓 LIKE 'A%'」のように A で始まる社員は多いが、「姓 LIKE 'J%'」のように J で始まる社員 (Jojima さんなど) は少ないといったケースです。この場合は、前者はテーブル スキャンの方がパフォーマンスが良く、後者の場合は Index Seek の方がパフォーマンスが良いでしょう。しかし、「姓 LIKE @p1」のようにパラメータ化を行うと、どんな値が指定されても、同じ実行プランが再利用されてしまうのです (一番初めに代入された値のときの実行プランがその後のどんな値でも再利用されます)。
したがって、パラメータに代入する値によって結果セットが大きく異なる場合には、あえてパラメータ化をせずにアドホック クエリとして実行したり、アプリケーション内で特定の値のみをアドホックで実行するような工夫が必要です。また、ストアド プロシージャの場合は、WITH RECOMPILE を付けることで、実行のたびに再コンパイルするように作成することもできます。値によって結果セットの大きさが大きく異なる場合には、注意するようにしてください。

ページのトップへ

5 of 10