SE の雑記

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

SQL Server のクエリ実行時の簡易 / 強制パラメーター化について

leave a comment

SQL Server には単純なクエリが実行された際に、内部的に自動でパラメーター化して実行される [簡易パラメーター化] (SQL Server 2000 では自動パラメーター化と呼ばれていました)  という機能があります。
# 私はずっと自動パラメーター化と言っていたのですが、この投稿を書くために調べたら簡易パラメーター化と呼ぶということを知りました…。

[強制パラメーター化] は単純でないクエリに対しても自動でパラメーター化をして実行する機能になります。
# 強制パラメーター化を有効にしてもパラメーター化の条件に当てはまらないと通常のクエリとして実行されますが。

このパラメーター化クエリについて少しまとめてみたいと思います。
今回の投稿ではパラメーター化は、簡易パラメーター化で検証をしています。
# 強制パラメーター化でも基本的な動作は同じになるはずですので。

■パラメーター化クエリについて

まずは、パラメーター化クエリについて少しまとめてみたいと思います。

以下のような単純な SQL (アドホッククエリ) があります。

SELECT * FROM Table_1 WHERE Col1 = 10

これをパラメーター化クエリとして実行してみたいと思います。

SQL Server の T-SQL でパラメーター化クエリを実行するには、[sp_executesql] を使用します。

DECLARE @val int = 10
EXEC sp_executesql N’SELECT * FROM Table_1 WHERE Col1 = @param1′, N’@param1 int’, @param1 = @val

このような形式で実行することでパラメーター化クエリとして実行することが可能です。

それでは、10~15 までを条件としてクエリを実行してみます。

アドホッククエリで実行した場合は以下のようになります。
# 各 SQL は GO で区切り、一行で一つのバッチにしています。

SELECT * FROM Table_1 WHERE Col1 = 10
GO
SELECT * FROM Table_1 WHERE Col1 = 11
GO
SELECT * FROM Table_1 WHERE Col1 = 12
GO
SELECT * FROM Table_1 WHERE Col1 = 13
GO
SELECT * FROM Table_1 WHERE Col1 = 14
GO
SELECT * FROM Table_1 WHERE Col1 = 15
GO

この時にプロシージャキャッシュにキャッシュされるクエリを見てみます。

SELECT 
    decp.bucketid,
    decp.refcounts,
    decp.usecounts,
    decp.size_in_bytes,
    decp.cacheobjtype,
    decp.objtype,
    decp.memory_object_address,
    domo.type,
    text
FROM
    sys.dm_exec_cached_plans AS decp
    CROSS APPLY
        sys.dm_exec_sql_text(plan_handle)
    LEFT JOIN
        sys.dm_os_memory_objects AS domo
        ON
        decp.memory_object_address = domo.memory_object_address
WHERE
    cacheobjtype LIKE ‘Compiled Plan%’
GO

実行結果がこちらになります。
image

各 SQL が 1 行ずつキャッシュされています。

それではパラメーター化クエリで同様の結果を取得する SQL を実行してみます。

EXEC sp_executesql N’SELECT * FROM Table_1 WHERE Col1 = @param1′, N’@param1 int’, @param1 = 10
GO
EXEC sp_executesql N’SELECT * FROM Table_1 WHERE Col1 = @param1′, N’@param1 int’, @param1 = 11
GO
EXEC sp_executesql N’SELECT * FROM Table_1 WHERE Col1 = @param1′, N’@param1 int’, @param1 = 12
GO
EXEC sp_executesql N’SELECT * FROM Table_1 WHERE Col1 = @param1′, N’@param1 int’, @param1 = 13
GO
EXEC sp_executesql N’SELECT * FROM Table_1 WHERE Col1 = @param1′, N’@param1 int’, @param1 = 14
GO
EXEC sp_executesql N’SELECT * FROM Table_1 WHERE Col1 = @param1′, N’@param1 int’, @param1 = 15
GO

この時のプロシージャキャッシュの状態がこちらになります。
image

アドホッククエリとパラメータ化クエリではキャッシュされている SQL の行数に差があります。
アドホッククエリの場合は、一行一行が個別のキャッシュ (objtype = Adhoc) としてキャッシュがされています。
パラメーター化クエリの場合は、一行のキャッシュ (objtype = Prepared) としてキャッシュがされます。

同様の条件でパラメーター部だけが違う場合、パラメーター化クエリを使用したほうがキャッシュされるクエリの数が減りますので、効果的にプロシージャキャッシュの領域を使用することができます。

■簡易パラメーター化について


それでは、次に簡易パラメーター化についてみていきたいと思います。

SELECT * FROM Table_1 WHERE Col1 = 10

の SQL 実行時の実行プランを見てみます。
image

Clustered Index Seek(OBJECT:([TEST].[dbo].[Table_1].[PK_Table_1]), SEEK:([TEST].[dbo].[Table_1].[Col1]=CONVERT_IMPLICIT(int,[@1],0)) ORDERED FORWARD)

今回の実行しているクエリは簡単なクエリなので、[[Col1]=CONVERT_IMPLICIT(int,[@1],0)] というように自動的にパラメーター化されて実行がされています。

この動作が [簡易パラメーター化] になります。

先ほどの 10 ~ 15 の SQL のキャッシュの状態をもう一度確認してみます。
image

最後の行に [objtype = Prepared] のクエリがキャッシュされています。
これが簡易パラメーター化によって生成されたクエリになります。

簡易パラメータ化ですが、
image

というようには実行されません。

簡易パラメーター化する前のアドホッククエリもキャッシュされます。
image

実行プランに関してはパラメーター化されたものが使用されますので、各アドホッククエリ単位でコンパイルされるということはなく、最初の一回が実行された際にコンパイルされ、以降同等のクエリが実行される場合は、最初のコンパイルで生成されたプランが使用されます。
SQL Profiler で取得したトレースがこちらになります。
image

EventClass 13 : SQL BatchStarting / EventClass 168 : Showplan XML For Query Compile となります。
最初の SQL が実行されたタイミングでコンパイルイベントが発生していますが、以降のクエリではコンパイルイベントが発生していません。

簡易パラメーター化されたクエリがアドホッククエリで利用されていますので、最初にコンパイルされた実行プランを以降のクエリでも利用されています。
# 今回は簡易パラメーター化で書いていますが強制パラメーター化でも同様のキャッシュ状況となります。

参考になりますが、パラメーター化クエリで実行した場合のトレースがこちらになります。
image

コンパイルが発生しているタイミングは変わらないですね。

自動でパラメーター化されることで実行プランの再利用はされるようになりますが、アドホッククエリもキャッシュされますので自動化に頼りすぎずに、最初からパラメーター化クエリまたはストアドプロシージャで実行してプロシージャキャッシュにキャッシュされるクエリを抑えるのは重要になってくるかと。

x64 の SQL Server では、プロシージャキャッシュの上限が x86 より増えていますので、あまりにもアドホッククエリが多いとデータページのキャッシュを圧迫してしまいますしね。

Written by masayuki.ozawa

1月 23rd, 2011 at 8:02 pm

Posted in SQL Server

Tagged with

Leave a Reply

*