SQL Server 2005 のバッチのコンパイル、再コンパイル、およびプランのキャッシュに関する問題

  • [アーティクル]
このページはアーカイブです。記載されている内容は情報提供のみを目的としており、ページ内のリンクは有効でない可能性がありますが、これらの情報についてマイクロソフトはいかなる責任も負わないものとします。

Arun Marathe

公開 : 2004 年 7 月

概要 : 本書では、SQL Server 2005 でバッチをキャッシュおよび再利用する方法について説明し、キャッシュされたプランの再利用を最大限に活用するベスト プラクティスを提案します。また、バッチが再コンパイルされるシナリオについても説明し、不要な再コンパイルが取り除かれる、または取り除くためのベスト プラクティスを提供します。

トピック

本書の目標
再コンパイルの定義
SQL Server 2000 と SQL Server 2005 における再コンパイルの比較
プランのキャッシュ
本書後半のロードマップ
クエリ プランの再利用
再コンパイルの原因
コンパイル、再コンパイル、および同時実行
コンパイル、再コンパイル、およびパラメータ スニッフィング
再コンパイルの特定
ツールとコマンド
まとめ
付録 A: SQL Server 2005 でクエリが自動パラメータ化されない場合

本書の目標

本書には、複数の目標があります。本書では、SQL Server 2005 でバッチをキャッシュおよび再利用する方法について説明し、キャッシュされたプランを最大限に再利用することに関するベスト プラクティスを提案します。さらに、バッチが再コンパイルされるシナリオについても説明し、不要な再コンパイルが取り除かれる、または取り除くためのベスト プラクティスを提供します。本書では、SQL Server 2005 の "ステートメント レベルの再コンパイル" 機能について説明します。また、クエリのコンパイル、クエリの再コンパイル、プランのキャッシュ、およびプランの再利用のプロセスで、監視ツールとして役立つ多くのツールやユーティリティについても説明します。本書全体では、読者が理解しやすくなるように、SQL Server 2000 と SQL Server 2005 の動作を比較します。本書に記載されている内容は、SQL Server 2000 および SQL Server 2005 に当てはまります。SQL Server の 2 つのバージョン間の動作の違いは、明確に指摘されています。

本書は、以下の 3 つの立場の読者を対象としています。

  • ユーザー : SQL Server 用のアプリケーションを使用、管理、および開発する人々。本書には、SQL Server 2005 を使用したことのないユーザー、および SQL Server 2000 から移行しているユーザーにとって役立つ情報が含まれています。

  • 開発者 : 本書には、SQL Server 開発者にとって役立つ基礎的な情報が含まれています。

  • テスターとプログラム マネージャ : 本書は、"SQL Server 2005 のコンパイルおよび再コンパイル" 機能の機能仕様として役立ちます。

再コンパイルの定義

クエリ、バッチ、ストアド プロシージャ、トリガ、準備されたステートメント、または動的 SQL ステートメント (これ以降は "バッチ" と呼びます) が SQL Server で実行を開始する前に、バッチはプランにコンパイルされます。その後、プランは、その効果を上げるため、または結果を生成するために実行されます。

バッチには、1 つ以上の SELECT、INSERT、UPDATE、および DELETE ステートメントを含めることができます。さらに、T-SQL の "結合ステートメント" または SET、IF、WHILE、DECLARE などの制御構造でインタリーブされる可能性があるストアド プロシージャ コール、CREATE、DROP などの DDL ステートメント、および GRANT、DENY、REVOKE などの権限に関連するステートメントも含めることができます。バッチには、定義を含めたり、ユーザー定義型、関数、プロシージャ、集計などの CLR 構造の使用を含めることができます。

コンパイル済みのプランは、"プラン キャッシュ" と呼ばれる、SQL Server のメモリの一部に格納されます。プラン キャッシュでは、プランが再利用できるかどうかを判断します。バッチのプランが再利用されると、そのコンパイルにかかるコストが回避されます。SQL Server の資料では、本書で "プラン キャッシュ" と呼んでいるものを説明するのに、"プロシージャ キャッシュ" という用語が使用されていることに注意してください。"プラン キャッシュ" という呼び方はより厳密な表現です。これは、プラン キャッシュに格納されるクエリ プランは、ストアド プロシージャだけで構成されているわけではないためです。

SQL Server の専門用語では、紛らわしいことに、前の段落で説明したコンパイル プロセスが "再コンパイル" と呼ばれる場合がありますが、このプロセスは単なる "コンパイル" です。

再コンパイルの定義 : バッチが 1 つ以上のクエリ プランのコレクションにコンパイルされたと仮定します。SQL Server が個別のクエリ プランの実行を開始する前に、サーバーは、そのクエリ プランの有効性 (正確さ) および最適性をチェックします。いずれかのチェックに失敗すると、そのクエリ プランに対応するステートメント、またはバッチ全体が "再び" コンパイルされ、別のクエリ プランが生成される可能性があります。このようなコンパイルが "再コンパイル" と呼ばれます。

特に、バッチのクエリ プランはキャッシュしておく必要がないことに注意してください。実際には、ある種のバッチは決してキャッシュされず、再コンパイルの原因になることがあります。たとえば、8 KB を超えるリテラルを含むバッチを取得します。このバッチが一時テーブルを作成し、その後、そのテーブルに 20 行挿入するとします。7 番目の行を挿入すると、再コンパイルが発生しますが、大きなリテラルが原因で、バッチはキャッシュされません。

SQL Server における大多数の再コンパイルは、正当な理由で実行されます。再コンパイルには、ステートメントの正確さを裏付けるために必要なものもあれば、SQL Server データベース内のデータを変更する際に、場合によっては適切なクエリ実行プランを入手するために実行されるものもあります。ただし、再コンパイルはバッチの実行速度を大幅に低下させる場合があるため、その後、再コンパイルの回数を少なくすることが必要になります。

SQL Server 2000 と SQL Server 2005 における再コンパイルの比較

SQL Server 2000 では、バッチを再コンパイルすると、再コンパイルのきっかけとなったステートメントだけではなく、バッチ内の "すべて" のステートメントが再コンパイルされます。SQL Server 2005 では、バッチ全体ではなく、再コンパイルの原因であるステートメントのみをコンパイルすることによって、この動作が改善されています。この "ステートメント レベルの再コンパイル" 機能により、SQL Server 2005 の再コンパイル処理は、SQL Server 2000 と比較して向上しています。特に、SQL Server 2005 では、バッチの再コンパイル中に使用する CPU 時間およびメモリが削減され、コンパイル時に取得するロックが少なくなります。

ステートメント レベルの再コンパイルには、明らかなメリットが 1 つあります。つまり、長いストアド プロシージャを再コンパイルする場合に必要な負担をなくすためだけに、その長いストアド プロシージャを複数の短いストアド プロシージャに分割する必要がなくなります。

プランのキャッシュ

本書では、再コンパイルの問題に取り組む前に、クエリ プランのキャッシュに関連する重要なトピックについて多くのページを割いています。プランは、再利用できるようにキャッシュされます。クエリ プランがキャッシュされていないと、再利用できません。このようなプランが実行されるたびにコンパイルされると、パフォーマンスは低下します。まれに、キャッシュしないことが望ましい場合があるので、本書では、このような場合について後で説明します。

SQL Server は、多くの種類のバッチのクエリ プランをキャッシュできます。さまざまな種類のバッチを以下に示します。種類ごとに、プランの再利用に "必要な" 条件について説明します。これらの条件は "十分" ではない可能性があることに注意してください。読者には、本書の後半で全体像を示します。

  1. アドホック クエリ。アドホック クエリは、1 つの SELECT、INSERT、UPDATE、または DELETE ステートメントを含むバッチです。SQL Server では、2 つのアドホック クエリのテキストが完全に一致する必要があります。テキストの一致では、大文字と小文字、およびスペースの有無が区別されます。たとえば、次の 2 つのクエリは同じクエリ プランを共有しません (本書に記載されているすべての T-SQL コードは、SQL Server 2005 の AdventureWorks データベースを使用するものとします)。

    SELECT ProductID 
FROM Sales.SalesOrderDetail 
GROUP BY ProductID 
HAVING AVG(OrderQty) > 5 
ORDER BY ProductID 
SELECT ProductID 
  
FROM Sales.SalesOrderDetail 
GROUP BY ProductID 
HAVING AVG(OrderQty) > 5 
ORDER BY ProductId

  2. 自動パラメータ化クエリ。特定のクエリでは、SQL Server 2005 が、変数ごとに定数リテラル値を置き換え、クエリ プランをコンパイルします。後続のクエリで定数の値のみが異なる場合、このクエリは自動パラメータ化クエリに相当します。一般的に、SQL Server 2005 では、定数リテラルの特定の値に依存しないように、このようなクエリ プランを備えたクエリを自動パラメータ化します。

    付録 A には、SQL Server 2005 で自動パラメータ化されないステートメントの種類が記載されています。

    SQL Server 2005 の自動パラメータ化の例として、以下の 2 つのクエリでは、クエリ プランを再利用できます。

    SELECT ProductID, SalesOrderID, LineNumber 
 FROM Sales.SalesOrderDetail  
WHERE ProductID > 1000 
ORDER BY ProductID 
  
SELECT ProductID, SalesOrderID, LineNumber 
 FROM Sales.SalesOrderDetail  
WHERE ProductID > 2000 
ORDER BY ProductID

    上記のクエリを自動パラメータ化すると、次のような形式になります。

    SELECT ProductID, SalesOrderID, LineNumber 
 FROM Sales.SalesOrderDetail  
WHERE ProductID > @p1 
ORDER BY ProductID

    クエリに記述されている定数リテラルの値がクエリ プランに影響する可能性がある場合、クエリは自動パラメータ化されません。このようなクエリのクエリ プランはキャッシュされます。ただし、@p1 などのプレースホルダではなく、定数がプラグインされます。

    SQL Server の "プラン表示" 機能を使用して、クエリが自動パラメータ化されているかどうかを確認できます。たとえば、クエリを、"set showplan_xml on" モードで送信できます。結果として生じるプラン表示に @p1 や @p2 などのプレースホルダが含まれている場合、クエリは自動パラメータ化されています。それ以外の場合は、自動パラメータ化されていません。また、SQL Server の XML 形式のプラン表示には、コンパイル時 ('showplan_xml' モードと 'statistics xml' モード) および実行時 ('statistics xml' モードのみ) の両方のパラメータの値に関する情報も含まれています。

  3. sp_executesql プロシージャ。このプロシージャは、クエリ プランの再利用を促進するメソッドの 1 つです。sp_executesql を使用すると、ユーザーまたはアプリケーションはパラメータを明示的に識別します。以下に例を示します。

    EXEC sp_executesql N'SELECT p.ProductID, p.Name, p.ProductNumber  
FROM Production.Product p  
INNER JOIN Production.ProductDescription pd  
ON p.ProductID = pd.ProductDescriptionID  
WHERE p.ProductID = @a', N'@a int', 170 
  
EXEC sp_executesql N'SELECT p.ProductID, p.Name, p.ProductNumber 
FROM Production.Product p INNER JOIN Production.ProductDescription pd 
ON p.ProductID = pd.ProductDescriptionID  
WHERE p.ProductID = @a', N'@a int', 1201

    パラメータを順番に列挙することで、複数のパラメータを指定できます。実際のパラメータ値は、パラメータの定義に従います。プランを再利用できるかどうかは、クエリ テキスト (sp_executesql の後の最初の引数) の一致と、クエリ テキストに続くすべてのパラメータ (上記の例では N'@a int') に基づいて決定されます。パラメータ値 (170 および 1201) は、テキストが一致するかどうかの考慮対象にはなりません。そのため、上記の例では、2 つの sp_executesql ステートメントで、プランが再利用される可能性があります。

  4. 準備されたクエリ。このメソッドも、sp_executesql メソッドと同様、クエリ プランの再利用を促進します。バッチ テキストは、"準備" 時に 1 度送信されます。SQL Server 2005 は、実行時にバッチを呼び出すために使用できるハンドルを返すことでこれに応答します。実行時に、ハンドルとパラメータ値がサーバーに送信されます。ODBC および OLE DB は、SQLPrepare/SQLExecute および ICommandPrepare 経由でこの機能を公開します。たとえば、ODBC を使用するコードの一部は次のようになります。

    SQLPrepare(hstmt, "SELECT SalesOrderID, SUM(LineTotal) AS SubTotal 
FROM Sales.SalesOrderDetail sod 
WHERE SalesOrderID < ? 
GROUP BY SalesOrderID 
ORDER BY SalesOrderID", SQL_NTS)
SQLExecute(hstmt)

  5. ストアド プロシージャ (トリガを含む)。ストアド プロシージャは、プランの再利用を促進するようにデザインされています。プランの再利用は、ストアド プロシージャまたはトリガの名前に基づきます (ただし、トリガを直接呼び出すことはできません)。内部では、SQL Server がストアド プロシージャの名前を ID に変換し、その後、その ID の値に基づいてプランが再利用されます。トリガのプランのキャッシュおよび再コンパイルの動作は、ストアド プロシージャのとはわずかに異なります。この違いについては、本書の適切な場所で説明します。

    ストアド プロシージャを初めてコンパイルすると、実行時の呼び出しに付属するパラメータの値を使用して、そのストアド プロシージャ内のステートメントが最適化されます。このプロセスを "パラメータ スニッフィング" といいます。このようなパラメータ値が標準的な場合、そのストアド プロシージャの呼び出すと、多くの場合、効果的なクエリ プランの利点が得られます。本書では、ストアド プロシージャの特殊なパラメータ値を含むクエリ プランのキャッシュを防ぐ際に使用できる技法について後で説明します。

  6. バッチ。バッチ テキストが完全に一致する場合、バッチに対してクエリ プランを再利用できます。このテキストの一致では、大文字と小文字、およびスペースの有無が区別されます。

  7. EXEC (...) で実行するクエリ。SQL Server 2005 では、実行するために EXEC で送信された文字列をキャッシュできます。これを "動的 SQL" といいます。以下に例を示します。

    EXEC ( 'SELECT *' + ' FROM Production.Product pr  
INNER JOIN Production.ProductPhoto ph' + '  
ON pr.ProductID = ph.ProductPhotoID' +  
' WHERE pr.MakeFlag = ' + @mkflag )

    ステートメントの実行時に、上記の例にある @mkflag などの変数が実際の値に置き換えられた後の連結された文字列に基づいて、プランが再利用されます。

複数レベルのキャッシュ

複数の "レベル" で一致する場合、それぞれ独立してキャッシュが行われることを理解しておくことが重要になります。次に例を示します。(ストアド プロシージャでは "ない") バッチ 1 に (特に) 次のステートメントが含まれていると仮定します。

EXEC dbo.procA

バッチ 2 (これもストアド プロシージャではありません) は、バッチ 1 とテキストが一致していませんが、まったく同じストアド プロシージャを参照する "EXEC dbo.procA" を含んでいます。この場合、バッチ 1 とバッチ 2 のクエリ プランは一致しません。それにもかかわらず、他のバッチが現在のバッチの前に実行された場合、かつ procA のクエリ プランがプラン キャッシュにまだ存在している場合、"EXEC dbo.procA" が 2 つのバッチのいずれかで実行されるときは必ず、procA のクエリ プランが再利用される (およびこの資料で後から説明する、実行コンテキストが再利用される) 可能性があります。ただし、procA を実行するたびに、独自の実行コンテキストが取得されます。実行コンテキストは、(既存の実行コンテキストすべてが使用中の場合に) 新しく生成されるか、または (使用されていない実行コンテキストが使用可能な場合に) 再利用されます。EXEC を使用して動的 SQL が実行される場合、または自動パラメータ化されたステートメントがバッチ 1 およびバッチ 2 の内部で実行される場合でも、同じような再利用が行われる可能性があります。つまり、以下の 3 種類のバッチは、含まれているレベルのいずれかでキャッシュが一致したかどうかに関係なく、キャッシュが一致する独自の "レベル" で開始されます。

  • "EXEC dbo.stored_proc_name" などのストアド プロシージャの実行

  • "EXEC query_string" などの動的 SQL の実行

  • 自動パラメータ化クエリ

ストアド プロシージャは、上記のルールの "例外" です。たとえば、異なる 2 つのストアド プロシージャに "EXEC procA" というステートメントが含まれている場合、procA のクエリ プランおよび実行コンテキストは再利用されません。

クエリ プランと実行コンテキスト

キャッシュ可能なバッチは、実行するために SQL Server 2005 に送信されると、コンパイルされ、そのバッチの "クエリ プラン" はプラン キャッシュに格納されます。クエリ プランは、複数のユーザーが共有する、再入可能な読み取り専用の構造体です。プラン キャッシュには、常に、多くてもクエリ プランの 2 つのインスタンスしかありません。1 つはすべての直列実行用、1 つはすべての並列実行用です。並列実行のコピーは、並列処理の程度がすべて共通しています (厳密に言えば、同じセッション オプションが指定された異なる 2 つのセッションを使用して、同じユーザーが設定した同じ 2 つのクエリが、SQL Server 2005 に同時に到着すると、それらのクエリの実行中は 2 つのクエリ プランが存在することになります。ただし、実行終了時に、いずれかのクエリのみのプランがプラン キャッシュに残ります)。

クエリ プランから、"実行コンテキスト" が派生されます。実行コンテキストは、クエリ結果を生成するために "実行される" ものです。また、実行コンテキストもキャッシュおよび再利用されます。バッチを同時に実行する各ユーザーは、その実行に固有のデータ (パラメータ値など) を保持する実行コンテキストを所有します。実行コンテキストは再利用されますが、再入可能ではありません (つまり、実行コンテキストは単一スレッドになります)。つまり、ある時点では、実行コンテキストは、セッションで送信された 1 つのバッチだけを実行できます。さらに、その実行中、コンテキストはその他のセッションまたはユーザーに提供されません。

クエリ プランとそのプランから派生された実行コンテキストの関係を、次の図に示します。1 つのクエリ プランがあり、3 つの実行コンテキストがそのプランから派生されています。実行コンテキストには、パラメータ値およびユーザー固有の情報が含まれています。クエリ プランは、パラメータ値にもユーザー固有の情報にもとらわれません。

Recomp01.gif

1 つのクエリ プランと関連する複数の実行コンテキストは、プラン キャッシュで共存できます。ただし、(関連するクエリ プランを含まない) 実行コンテキストだけでは、プラン キャッシュに存在できません。クエリ プランがプラン キャッシュから削除されるたびに、関連するすべての実行コンテキストも一緒に削除されます。

プランを再利用できるかどうかをプラン キャッシュが検討する場合、実行コンテキストではなく、クエリ プランを比較します。再利用可能なクエリ プランが見つかると、使用可能な実行コンテキストが見つかる (実行コンテキストが再利用されます) か、または新しく生成されます。そのため、クエリ プランの再利用により、必ずしも実行コンテキストが再利用されるとは限りません。

バッチの実行が始まる前に、実行コンテキストが "一時的に" 派生され、スケルトン実行コンテキストが生成されます。実行が進むにつれて、必要な実行コンテキストが生成され、スケルトンに追加されます。つまり、2 つの実行コンテキストは、ユーザー固有の情報やクエリ パラメータがそのコンテキストから削除された後でも、同じになるとは限りません。同一のクエリ プランから派生された実行コンテキストの構造が互いに異なる可能性があるので、特定の実行に使用される実行コンテキストが、パフォーマンスに影響することがあります。このようなパフォーマンスの違いによる影響は、プラン キャッシュが "頻繁に再利用" されるようになり、安定状態に到達するとともに少なくなります。

例 :

バッチ B に "if" ステートメントが含まれていると仮定します。B が実行を開始すると、B の実行コンテキストが生成されます。この最初の実行中に、"if" の条件分岐で "true" を選択するとします。さらに、最初の実行中に、B が別の接続によって再び送信されたとします。その時点で存在する唯一の実行コンテキストが使用中だったため、2 つ目の実行コンテキストが生成され、2 番目の接続に提供されます。2 つ目の実行コンテキストが "if" の分岐条件で "false" を選択すると仮定します。両方の実行が完了した後、B は 3 番目の接続によって実行されます。B の 3 度目の実行で "true" を選択すると仮定すると、SQL Server がその接続に対して 2 番目の実行コンテキストではなく、B の最初の実行コンテキストを選択すると、実行は少し速く完了します。バッチ S の実行コンテキストは、S の呼び出し順が異なる場合でも、再利用できます。たとえば、ある呼び出し順は "stored proc 1 --> stored proc 2 --> S" になるのに対して、2 番目の呼び出し順は "stored proc 3 --> S" になる場合があります。S を最初に実行した場合の実行コンテキストは、S を 2 度目に実行する場合に再利用できます。

バッチの実行で重大度 11 以上のエラーが生成されると、実行コンテキストは破棄されます。バッチの実行で警告 (重大度 10 のエラー) が生成される場合、実行コンテキストは破棄されません。そのため、(プラン キャッシュが縮小する原因となる) メモリの使用制限がない場合でも、プラン キャッシュにキャッシュされた (特定のクエリ プランの) 実行コンテキストの数は増減します。

並列プランの実行コンテキストはキャッシュされません。SQL Server が並列クエリ プランをコンパイルするのに必要な条件は、プロセッサ アフィニティ マスクに対応するプロセッサ数と ("sp_configure" ストアド プロシージャを使用して設定できる) "max degree of parallelism" というサーバー側オプション値の最小値が 1 より大きいことです。並列クエリ プランがコンパイルされる場合でも、SQL Server の "クエリ実行" コンポーネントは、そのクエリ プランから直列実行コンテキストを生成することができます。並列プランから派生される実行コンテキストは、直列であっても並列であっても、キャッシュされません。ただし、並列クエリ プランはキャッシュされます。

クエリ プランのキャッシュとさまざまな SET オプション (プラン表示に関連するオプションとその他のオプション)

さまざまな SET オプションは、その大部分がプラン表示に関連していますが、クエリ プランおよび実行コンテキストのコンパイル、キャッシュ、および再利用に複雑に影響します。次の表では、この詳細をまとめています。

表は以下のように記述されています。バッチは、1 列目で指定した特定のモードで SQL Server に送信されます。キャッシュされたクエリ プランは、送信されたバッチのプラン キャッシュに存在するかどうかわかりません。2 列目と 3 列目には、キャッシュされたクエリ プランが存在する場合の例、4 列目と 5 列目には、キャッシュされたクエリ プランが存在しない場合の例について記述されています。各カテゴリ内では、クエリ プランと実行コンテキストの例が区別されています。テキスト部分では、構造体 (クエリ プランまたは実行コンテキスト) に何か起こるかを説明しています。つまり、構造体がキャッシュされるか、再利用されるか、使用されるかを示しています。

モード名

キャッシュされたクエリ プランが存在する場合

キャッシュされたクエリ プランが存在する場合

キャッシュされたクエリ プランが存在しない場合

キャッシュされたクエリ プランが存在しない場合

 

クエリ プラン

実行コンテキスト

クエリ プラン

実行コンテキスト

showplan_text, showplan_all, showplan_xml

再利用されます (コンパイルはされません)。

再利用されます。

キャッシュされます (コンパイルされます)。

1 つの実行コンテキストが生成されますが、使用およびキャッシュされません。

statistics profile, statistics xml, statistics io, statistics time

再利用されます (コンパイルはされません)。

再利用されません。新しい実行コンテキストが生成され、使用されますが、キャッシュされません。

キャッシュされます (コンパイルされます)。

1 つの実行コンテキストが生成され、使用されますが、キャッシュされません。

noexec

再利用されます (コンパイルはされません)。

再利用されます。

キャッシュされます (コンパイルされます)。

("noexec" モードのため) 実行コンテキストが生成されません。

parseonly (例、クエリ アナライザの [解析] や Management Studio の [parse] ボタンを押す場合)

適用なし

適用なし

適用なし

適用なし
クエリ プランと実行コンテキストに関連するコスト

クエリ プランと実行コンテキストごとに、コストが格納されます。コストは、部分的に、プラン キャッシュにおけるプランまたはコンテキストの有効期間を制御します。SQL Server 2000 および SQL Server 2005 では、異なる方法でコストが計算または処理されます。詳細を以下に示します。

SQL Server 2000 : クエリ プランの場合、コストは、クエリ オプティマイザがバッチを最適化する際にかかるサーバー リソース (CPU 時間と入出力) の目安です。アドホック クエリの場合、コストはかかりません。実行コンテキストの場合、コストは、サーバーが実行コンテキストを初期化して、個別のステートメントを実行可能な状態にする際にかかるサーバー リソース (CPU 時間と入出力) の目安です。実行コンテキストのコストには、バッチの実行中に発生するコスト (CPU と入出力) は含まれないことに注意してください。一般的に、実行コンテキストのコストは、クエリ プランのコストよりも低くなります。

SQL Server 2000 でバッチのクエリ プランにかかるコストは次のとおりです。コストに影響する要因は 4 つあります。4 つの要因とは、プランの生成にかかる CPU 時間 (cputime)、ディスクから読み取るページ数 (ioread)、ディスクに書き込むページ数 (iowrite)、およびバッチのクエリ プランが占有するメモリ ページ数 (pagecount) です。クエリ プランのコストは、次の式で表すことができます (f は数学関数を示します)。

Query plan cost c = f(cputime, ioread, iowrite) / pagecount

SQL Server 2000 でバッチの実行コンテキストにかかるコストは次のとおりです。上記の方程式で算出された個別のコスト c は、バッチ内のステートメントごとに計算され、累積されます。ただし、個別のコストは、現在のステートメントにかかる最初のコストであり、ステートメントのコンパイルまたは実行にかかるコストではないことに注意してください。

場合によっては、レイジー ライタ プロセスは、プラン キャッシュ全体を整理して、コストを削減します。コストは、4 つに分割され、必要に応じて切り捨てられます (たとえば、25 --> 6 --> 1 --> 0)。メモリの上限がある場合、コストのかからないクエリ プランおよび実行コンテキストは、プラン キャッシュから削除されます。クエリ プランまたは実行コンテキストが再利用されると、コストは、そのコンパイル (または実行コンテキストの生成) にかかるコストに戻されます。アドホック クエリのクエリ プランのコストは、常に 1 ずつ増加します。そのため、頻繁に実行されるバッチのクエリ プランは、あまり実行されないバッチのクエリ プランよりも長くプラン キャッシュに存在します。

SQL Server 2005 : アドホック クエリのコストはゼロです。それ以外の場合、クエリ プランのコストは、クエリ プランの作成に必要なリソースの目安です。具体的には、コストは最大値が 31 の "刻みの数" で計算され、3 つの要素で構成されます。

コスト = I/O コスト + コンテキスト スイッチ コスト (CPU コストの測定) + メモリコスト

コストの個々の要素は、次のように計算されます。

  • 2 つの入出力につきタイマ刻み 1 がかかります (最大値は 19)。

  • 2 つのコンテキスト スイッチにつきタイマ刻み 1 がかかります (最大値は 8)。

  • 16 個のメモリ ページ (128 KB) につきタイマ刻み 1 がかかります (最大値は 4)。

SQL Server 2005 では、プラン キャッシュがデータ キャッシュとは異なります。さらに、他の機能固有のキャッシュがあります。レイジー ライタ プロセスでは、SQL Server 2005 のコストは削減されません。その代わり、プラン キャッシュのサイズがバッファ プール サイズの 50% に到達するとすぐに、次のプラン キャッシュにアクセスするため、すべてのプランのタイマ刻みがそれぞれ 1 ずつ減少します。この数値の減少はプランを検索する目的でプラン キャッシュにアクセスするスレッドにかかっているので、ゆっくりと減少すると考えられることに注意してください。SQL Server 2005 内のすべてのキャッシュの合計サイズがバッファ プール サイズの 75% 以上になると、専用のリソース モニタ スレッドがアクティブになり、すべてのキャッシュ内のすべてのオブジェクトのタイマ刻みが減少します (そのため、このスレッドの動作は、SQL Server 2000 でのレイジー ライタ スレッドの動作に近くなります)。クエリ プランの再利用により、クエリ プランのコストが初期値にリセットされます。

本書後半のロードマップ

この時点で、読者に対し、SQL Server のバッチ実行の優れたパフォーマンスを得るために、次の 2 点を行う必要があることを明確する必要があります。

  • 可能な限り、クエリ プランを再利用する必要があります。これにより、クエリのコンパイルにかかる不要なコストが回避されます。また、プランの再利用により、プラン キャッシュの使用率が向上し、サーバーのパフォーマンスも向上します。

  • クエリの再コンパイル回数が増加する原因となる可能性のある実行を回避する必要があります。再コンパイルの回数を少なくすることにより、サーバー リソース (CPU およびメモリ) を節約し、バッチの実行回数を増加して、パフォーマンスを予測することができます。

次のセクションでは、クエリ プランの再利用について詳しく説明します。適切な場合は、プランの再利用を向上するベスト プラクティスを提供します。その後のセクションでは、再コンパイル回数が増加する原因となる一般的なシナリオについて説明し、それを回避する際のベスト プラクティスを提供します。

クエリ プランの再利用

プラン キャッシュには、クエリ プランと実行コンテキストが含まれます。クエリ プランは、概念的には、関連する実行コンテキストにリンクされています。バッチ S のクエリ プランの再利用は、S 自体 (たとえば、クエリ テキストやストアド プロシージャ名)、およびバッチ以外の複数の要因 (たとえば、S を生成したユーザー名、S を生成したアプリケーション、S に関連する接続の SET オプションなど) に依存しています。外部要因には、プランの再利用に影響するものがあります。これは、1 つの外部要因だけが異なる 2 つの同じクエリが、共通するプランを使用できなくなる場合です。また、外部要因には、プランの再利用に影響しないものもあります。

プランの再利用に影響する要因の大部分は、sys.syscacheobjects 仮想テーブルの列で公開されています。次の一覧では、"通常の使用状況" のシナリオにおける要因について説明します。場合によっては、エントリは、決してプランがキャッシュされない (そのため、再利用されない) 場合を単に指摘しています。

一般的に、クエリ プランをキャッシュする際に使用された接続のサーバー、データベース、および接続の設定が現在の接続の対応する設定と同じ場合、クエリ プランを再利用できます。第 2 に、バッチが参照するオブジェクトには、名前解決が必要ありません。たとえば、Sales.SalesOrderDetail には名前解決が必要ありませんが、一方で、SalesOrderDetail には必要になります。これは、複数のデータベースに SalesOrderDetail という名前のテーブルが存在する可能性があるためです。一般に、完全に修飾されたオブジェクト名により、プランを再利用できる機会が増加します。

プランの再利用に影響する要因

**クエリ プランがキャッシュされない場合は、再利用もできないことに注意してください。**そのため、ここではキャッシュされない場合を明確に指摘します。つまり、これには、再利用されないという意味も含まれています。

  1. ストアド プロシージャがデータベース D1 で実行される場合、そのクエリ プランは、別のデータベース D2 で同じストアド プロシージャを実行する際には再利用されません。この現象はストアド プロシージャのみに当てはまり、アドホック クエリ、準備されたクエリ、または動的 SQL には当てはまりません。

  2. トリガの実行の場合、"挿入" または "削除" されたテーブルの行数で測定されるような、その実行 (1 対 n) によって影響を受ける行数が、プラン キャッシュ ヒットを決定する際の特徴的な要因になります。この現象はトリガ固有なので、ストアド プロシージャには当てはまらないことに注意してください。

    SQL Server 2005 の INSTEAD OF トリガでは、"1-plan" は、0 行および 1 行に影響する実行で共有されます。それに対して、INSTEAD OF 以外のトリガ ("after") の場合、"1-plan" は、1 行に影響する実行のみで使用され、"n-plan" は、0 行および n 行 (n > 1) の両方に影響する実行で使用されます。

  3. 一括挿入ステートメントはキャッシュされませんが、一括挿入に関連するトリガはキャッシュされます。

  4. 8 KB を超える長さのリテラルが 1 つでも含まれているバッチはキャッシュされません。そのため、このようなバッチのクエリ プランは再利用できません (リテラルの長さは、定数の組み合わせが適用されてから測定されます)。

  5. (レプリケーション ユーザーに関連付けられている) "レプリケーション フラグ" が設定されているバッチは、そのフラグが設定されていないバッチと一致しません。

  6. SQL Server 2005 の共通言語ランタイム (CLR) から呼び出されるバッチは、CLR 以外から実行される同じバッチとは一致しません。ただし、CLR で実行される 2 つのバッチは、同じプランを再利用できます。同じ考え方が以下の項目に当てはまります。

    • CLR トリガおよび CLR 以外のトリガ

    • 通知クエリおよび非通知クエリ

  7. sp_resyncquery 経由で送信されるクエリのクエリ プランはキャッシュされません。そのため、(sp_resyncquery またはそれ以外を経由して) クエリが再送信されると、再コンパイルが必要になります。

  8. SQL Server 2005 では、T-SQL バッチの先頭でカーソルを定義できます。バッチが個別のステートメントとして送信されると、そのカーソルのプラン (の一部) を再利用しません。

  9. 以下の SET オプションは、プランの再利用に影響します。

    数値

    SET オプション名

    1

    ANSI_NULL_DFLT_OFF

    2

    ANSI_NULL_DFLT_ON

    3

    ANSI_NULLS

    4

    ANSI_PADDING

    5

    ANSI_WARNINGS

    6

    ARITHABORT

    7

    CONCAT_NULL_YIELDS_NULL

    8

    DATEFIRST

    9

    DATEFORMAT

    10

    FORCEPLAN

    11

    LANGUAGE

    12

    NO_BROWSETABLE

    13

    NUMERIC_ROUNDABORT

    14

    QUOTED_IDENTIFIER

    さらに、ANSI_DEFAULTS はプランの再利用に影響します。これは、ANSI_DEFAULTS を使用すると、ANSI_NULLS、ANSI_NULL_DFLT_ON、ANSI_PADDING、ANSI_WARNINGS、CURSOR_CLOSE_ON_COMMIT、IMPLICIT_TRANSACTIONS、IMPLICIT_TRANSACTIONS という、SET オプションをまとめて変更できるからです (オプションのいくつかはプランの再利用に影響します)。

    上記の SET オプションはプランの再利用に影響します。これは、SQL Server 2000 および SQL Server 2005 が "定数の組み合わせ" (一部の最適化を有効にするために、コンパイル時に定数式を評価すること) を実行していること、および SET オプションの設定がこのような式の結果に影響することが原因です。

    このような SET オプションの一部の設定 (たとえば、"langid" や "dateformat") は、sys.syscacheobjects 仮想テーブルの列で公開されてます。

    このような SET オプションの一部の値は、複数の方法を使用して変更できることに注意してください。

    • sp_configure ストアド プロシージャを使用する方法 (サーバー側の変更の場合)

    • sp_dboption ストアド プロシージャを使用する方法 (データベース側の変更の場合)

    • ALTER DATABASE ステートメントの SET 句を使用する方法

      SET オプションの値が矛盾する場合は、ユーザー レベルおよび接続レベルの SET オプションの値が、データベースおよびサーバー レベルの SET オプションの値よりも優先されます。さらに、データベース レベルの SET オプションが有効な場合、(異なる SET オプションの値が設定されている可能性のある) 複数のデータベースを参照するバッチでは、バッチが実行されているコンテキストを含むデータベースの SET オプションが、残りのデータベースの SET オプションよりも優先されます。

      ベスト プラクティス : SET オプションに関連する再コンパイルを回避するには、接続時に SET オプションを確立し、このオプションが接続中に変更されないようにします。

  10. 修飾されていないオブジェクト名を持つバッチは、クエリ プランを再利用しなくなります。たとえば、"SELECT * FROM MyTable" では、Alice がこのクエリを発行し、その名前の付いたテーブルを所有する場合、MyTable は Alice.MyTable に正しく解決できます。同様に、MyTable は Bob.MyTable に解決されることもあります。このような場合、SQL Server はクエリ プランを再利用しません。ただし、Alice が "SELECT * FROM dbo.MyTable" を発行する場合、オブジェクトは一意に識別されるため、曖昧さがなくなります。そして、クエリ プランを再利用できるようになります (sys.syscacheobjects の uid 列を参照してください。この列では、プランが生成された接続のユーザー ID が示されています。同じユーザー ID を持つクエリ プランのみが再利用の候補となります。uid が -2 の場合は、クエリが暗黙の名前解決に依存せず、さまざまなユーザー ID で共有できることを意味しています)。

  11. ストアド プロシージャが "CREATE PROCEDURE ... WITH RECOMPILE" オプションを使用して作成されると、そのクエリ プランは、このストアド プロシージャを実行してもキャッシュされません。プランを再利用する機会はありません。つまり、このプロシージャを実行するたびに、新たにコンパイルが行われます。

    ベスト プラクティス : "CREATE PROCEDURE ... WITH RECOMPILE" を使用して、多種多様なパラメータで呼び出され、呼び出し中に指定したパラメータ値に大きく依存する最も優れたクエリ プランのストアド プロシージャをマークできます。

  12. ストアド プロシージャ P を "EXEC ... WITH RECOMPILE" を使用して実行すると、P は新しくコンパイルされます。P のクエリ プランがプラン キャッシュに既に存在する場合でも、それ以外でこのプランを再利用できる場合でも、再利用されません。新しくコンパイルされた P のクエリ プランは、キャッシュされません。

    ベスト プラクティス : 特殊なパラメータ値を使用してストアド プロシージャを実行するときに、"EXEC ... WITH RECOMPILE" を使用すると、新しいクエリ プランが、一般的なパラメータ値を使用してコンパイルされた既存のキャッシュされたプランを置き換えないようにできます。

"EXEC ... WITH RECOMPILE" はユーザー定義関数とも一緒に使用できます。ただし、EXEC キーワードがある場合のみです。

  1. さまざまなパラメータ値を使用して実行される 1 つのクエリに対する複数のクエリ プランを回避するには、sp_executesql ストアド プロシージャを使用してクエリを実行します。この方法は、同じクエリ プランがすべてまたは大多数のパラメータ値に適切な場合に役立ちます。

  2. (セッション スコープおよびグローバルな) 一時ストアド プロシージャのクエリ プランはキャッシュされ、その結果、再利用できます。

  3. SQL Server 2005 では、(手動または自動で) 統計を作成または更新するクエリのプランはキャッシュされません。

再コンパイルの原因

SQL Server がバッチ B のステートメントを実行し始めた後、このステートメントの一部 (またはすべて) を再びコンパイルすると、バッチ B が再コンパイルされることを思い出してください。再コンパイルの原因は、大きく 2 つのカテゴリに分類できます。

  • 正確さに関連する原因。バッチを再コンパイルしないと、適切ではない結果または動作が生じる場合は、バッチを再コンパイルする必要があります。正確さに関連する原因は、さらに 2 つに分類されます。

    • オブジェクトのスキーマ。バッチ B が多くのオブジェクト (テーブル、ビュー、インデックス、統計、UDF など) を参照することがあります。そのため、B が最後にコンパイルされた後に、一部のオブジェクトのスキーマが変更された場合、ステートメントの正確さの理由から、B を再コンパイルする必要があります。

    • SET オプション。SET オプションの一部はクエリの結果に影響します。このようなプランの再利用に影響する SET オプションの設定がバッチ内部で変更されると、再コンパイルが行われます。

  • プランの最適性に関連する原因。B が参照するテーブル内のデータは、B が最後にコンパイルされた後、大幅に変更された可能性があります。このような場合、迅速になる可能性のあるクエリ実行プランを入手するために、B を再コンパイルすることがあります。

以下の 2 つのセクションでは、2 つのカテゴリについて詳しく説明します。

正確さに関連した、バッチの再コンパイルの原因

正確さに関連する再コンパイルの原因となる特定の操作を以下に列挙します。以下の操作はこのような再コンパイルを行う必要があるので、ユーザーは、以下の操作を行わないか、または SQL Server 運用ピーク時以外の時間に行うことを選択してください。

オブジェクトのスキーマ

  1. バッチで参照されるオブジェクトのいずれかに対してスキーマの変更が生じるたびに、バッチが再コンパイルされます。"スキーマの変更" は、次のように定義されています。

    • テーブルまたはビューに対して列を追加または削除します。

    • 制約、デフォルト、またはルールをテーブルに追加したり、テーブルから削除します。

    • テーブルまたはインデックス付きビューにインデックスを追加します。

    • テーブルまたはインデックス付きビューで定義されたインデックスを削除します (インデックスが問題のクエリ プランで使用されている場合のみ)。

    • (SQL Server 2000 の場合)。統計をテーブル上に手動で更新または削除する (作成はしません!) と、そのテーブルを使用するクエリ プランが再コンパイルされます。次に問題のクエリ プランが実行を開始すると、このような再コンパイルが行われます。

    • (SQL Server 2005 の場合)。テーブルで定義された統計を手動で作成または削除する (更新はしません!) と、(テーブル スキーマのバージョンが変更されたため) このテーブルを使用する任意のクエリ プランが再コンパイルされます。次に問題のクエリ プランが実行を開始すると、このような再コンパイルが行われます。

    • 自動作成または自動更新された統計は、スキーマの変更の原因にはなりませんが、SQL Server 2000 または SQL Server 2005 のいずれにおいても、正確さに関連する再コンパイルの原因になることに注意してください。SQL Server 2005 で手動で更新された統計にも同じことが当てはまります。ただし、「7.2 プランの最適性に関連した、バッチの再コンパイルの原因」で詳しく説明するように、この操作は、プランの最適性に関連する、(この統計を読み込むクエリ プランの) 再コンパイルの原因となります。

  2. ストアド プロシージャまたはトリガで sp_recompile を実行すると、次に実行するときに再コンパイルされる原因となります。sp_recompile がテーブルまたはビューで実行されると、そのテーブルまたはビューを参照するすべてのストアド プロシージャは、次に実行されるときに再コンパイルされます。sp_recompile は、問題のオブジェクトのディスク上のスキーマのバージョンを増加することによって、再コンパイルを行わせます。

  3. 以下の操作では、プラン キャッシュ全体をフラッシュします。その結果、その後最初に送信されるバッチが新たにコンパイルされます。

    • データベースのデタッチ

    • SQL Server 2000 へのデータベースのアップグレード (SQL Server 2000 の場合)

    • SQL Server 2005 へのデータベースのアップグレード (SQL Server 2005 サーバーの場合)

    • DBCC FREEPROCCACHE コマンド

    • RECONFIGURE コマンド

    • ALTER DATABASE ... MODIFY FILEGROUP コマンド

    • ALTER DATABASE ... COLLATE コマンドを使用した照合順序の変更

次の操作では、特定のデータベースを参照するプラン キャッシュのエントリをフラッシュし、その後、新たにコンパイルします。

  • DBCC FLUSHPROCINDB コマンド

  • ALTER DATABASE ... MODIFY NAME = コマンド

  • ALTER DATABASE ... SET ONLINE コマンド

  • ALTER DATABASE ... SET OFFLINE コマンド

  • ALTER DATABASE ... SET EMERGENCY コマンド

  • DROP DATABASE コマンド

  • データベースが自動的に終了する場合

  • CHECK OPTION を使用してビューが作成されると、ビューが作成されているデータベースのプラン キャッシュのエントリがフラッシュされます。

  • DBCC CHECKDB が実行されると、指定したデータベースのレプリカが作成されます。DBCC CHECKDB の実行の一環として、レプリカに対して一部のクエリが実行され、そのプランがキャッシュされます。DBCC CHECKDB の実行終了時に、レプリカが削除され、レプリカに設定されたクエリのクエリ プランも削除されます。

    "特定のデータベースを参照するプラン キャッシュのエントリ" という概念には、説明が必要です。データベース ID は、プラン キャッシュのキーの 1 つです。次のコマンド シーケンスを実行すると仮定します。

    use master 
go 
<-- b1 というデータベースを参照するクエリ Q --> 
go

    Q がプラン キャッシュにキャッシュされるとします。Q のプランに関連するデータベース ID は、"master" のデータベース ID になり、"db1" のデータベース ID にはなりません。

SQL Server 2005 のトランザクション レベルのスナップショット分離レベルが有効な場合は、従来どおり、再利用されます。スナップショット分離レベルのバッチ内のステートメントが、スナップショット分離モードを有効にしてから変更されたスキーマを含むオブジェクトを参照すると、そのステートメントのクエリ プランがキャッシュおよび再利用された場合に、ステートメント レベルの再コンパイルが行われます。新しくコンパイルされたクエリ プランはキャッシュされますが、(その分離レベルのセマンティクスのとおり) ステートメント自体は失敗します。クエリ プランがキャッシュされなかった場合、コンパイルが行われ、コンパイルされたクエリ プランがキャッシュされます。その後、ステートメント自体は失敗します。

SET オプション

「6. クエリ プランの再利用」で既に説明したとおり、バッチで実行を開始した後に、1 つ以上の SET オプションを変更すると、再コンパイルが行われます。該当する SET オプションは、ANSI_NULL_DFLT_OFF、ANSI_NULL_DFLT_ON、ANSI_NULLS、ANSI_PADDING、ANSI_WARNINGS、ARITHABORT、CONCAT_NULL_YIELDS_NULL、DATEFIRST、DATEFORMAT、FORCEPLAN、LANGUAGE、NO_BROWSETABLE、NUMERIC_ROUNDABORT、および QUOTED_IDENTIFIER です。

プランの最適性に関連した、バッチの再コンパイルの原因

SQL Server は、データベース内のデータが変更されるときに、最適なクエリ実行プランを生成するようにデザインされています。データの変更は、SQL Server のクエリ プロセッサで統計 (ヒストグラム) を使用して監視されています。そのため、プランの最適性に関連する原因は、統計と密接な関係にあります。

プランの最適性に関連する原因について詳しく説明する前に、プランの最適性に関連する再コンパイルが行われない条件を列挙してみます。

  • プランが "重要ではないプラン" の場合。クエリ オプティマイザで、クエリで参照されるテーブルおよびそのテーブルに存在するインデックスを前提として、プランが 1 つしか可能でないことが判断されると、重要ではないプランが生じます。明らかに、このような場合には、再コンパイルは役立ちません。当然ながら、重要ではないプランを生成したクエリが、常に、重要ではないプランを生成するとは限りません。たとえば、新しいインデックスが基になるテーブルで作成されると、複数のアクセス パスがクエリ オプティマイザで使用可能になります。このようなインデックスの追加は、「7.1. 正確さに関連した、バッチの再コンパイルの原因」で説明したように検出され、正確さに関連する再コンパイルは、重要ではないプランを重要なプランに置き換える可能性があります。

  • クエリに "KEEPFIXED PLAN" ヒントが含まれている場合、そのクエリのプランは、プランの最適性に関連する理由により、再コンパイルされません。

  • クエリ プランで参照されるすべてのテーブルが読み取り専用の場合、プランは再コンパイルされません。

  • この項目は SQL Server 2000 のみに当てはまります。クエリ プランが (再コンパイルではなく) コンパイルされており、コンパイルの一環として、クエリ プロセッサがテーブル T で統計 S を更新することを決定すると考えてみます。クエリ プロセッサは、T で特殊な "統計ロック" を取得しようとします。他のプロセスが T で一部の統計 (必ずしも S とは限りません!) を更新している場合、クエリ プロセッサは T で統計ロックを取得できません。このような場合、クエリ プロセッサは S を更新できません。さらに、問題のクエリ プランは、プランの最適性に関連する理由により、再び再コンパイルされることはありません。これは、クエリが "KEEPFIXED PLAN" ヒントを使用して実行されたかのように見えます。

  • この例は上記の例に似ています。ただし、この場合、クエリ プランはキャッシュされます。つまり、コンパイルに関する前の例とは対照的に、この例は再コンパイルに関連しています。このような再コンパイルの場合、クエリ プロセッサが T で "統計ロック" を取得しようとして失敗したと仮定します。この場合、クエリ プロセッサが統計 S の更新をスキップします。つまり、古い統計 S を使用します。その後、従来どおり、他の再コンパイルの手順または確認を続行します。したがって、クエリ実行プランの処理速度が低下する可能性を犠牲にしても、再コンパイルを回避します。

クエリのコンパイルの概要

次のフローチャートでは、SQL Server におけるバッチのコンパイルおよび再コンパイルについて簡単に説明しています。主な処理手順は次のとおりです (個々の手順については、後で詳しく説明します)。

  1. SQL Server でクエリのコンパイルを開始します (既に説明したとおり、バッチはコンパイルおよびキャッシュの単位ですが、バッチ内の個々のステートメントは順番にコンパイルされます)。

  2. 最適なクエリ プランの生成に役立つ可能性のある "注目すべき" すべての統計がディスクからメモリに読み込まれます。

  3. いずれかの統計の有効期限が切れると、これらの統計は 1 つずつ更新されます。クエリのコンパイルは、更新が完了するまで待機します。この手順に関する SQL Server 2000 と SQL Server 2005 の重要な違いとして、SQL Server 2005 では、オプションで統計を "非同期に" 更新できる点があります。つまり、クエリのコンパイルのスレッドは、スレッドを更新する統計ではブロックされません。コンパイルのスレッドは、状態の統計を継続します。

  4. クエリ プランが生成されます。クエリで参照されるすべてのテーブルの再コンパイルのしきい値は、クエリ プランと一緒に格納されます。

  5. この時点で、理論的には、クエリの実行が開始しています。ここで、クエリ プランは、正確さに関連する理由によりテストされます。この理由については、「7.1. 正確さに関連した、バッチの再コンパイルの原因」で説明しました。

  6. 正確さに関連する理由のいずれかにより、プランが適切ではない場合、再コンパイルが開始されます。理論的には、クエリの実行は開始されているため、ここで開始されたコンパイルは "再コンパイル" であることに注意してください。

  7. プランが "適切" な場合、さまざまな再コンパイルのしきい値は、テーブルの基数またはさまざまなテーブルの変更カウンタ (SQL Server 2000 では rowmodctr、SQL Server 2005 では colmodctr) のいずれかと比較されます。

  8. 手順 7 で行われた比較により、いずれかの統計の有効期限が切れていると見なされると、再コンパイルが行われます。

  9. 手順 7 のすべての比較が成功すると、実際のクエリの実行が開始します。

プランの最適性に関連する再コンパイルの全体像

SELECT、INSERT、UPDATE、および DETELE ステートメントはそれぞれ、1 つ以上のテーブルにアクセスします。テーブルのコンテンツは、INSERT、UPDATE、および DELETE などの操作により、変更されます。SQL Server のクエリ プロセッサは、場合によってはさまざまなクエリ プランを生成し、生成時に各プランを最適にすることによって、このような変更に対応するようにデザインされています。テーブルのコンテンツは、テーブルの基数を使用して直接的に監視したり、テーブル列で統計 (ヒストグラム) を使用して間接的に監視します。

各テーブルには、テーブルに関連付けられた "再コンパイルのしきい値" (RT) があります。RT は、テーブル内の行数の関数です。クエリのコンパイル中、クエリ プロセッサは、クエリで参照されるテーブル内で定義された 0 個以上の統計を読み込みます。このような統計は、"注目すべき統計" と呼びます。クエリで参照されるすべてのテーブルでは、コンパイルされたクエリ プランに以下のものが含まれます。

  • 再コンパイルのしきい値

  • クエリのコンパイル中に読み込まれたすべての統計の一覧。このような各統計では、テーブルの変更回数を数えるカウンタのスナップショット値が格納されます。このカウンタは、SQL Server 2000 では rowmodctr、SQL Server 2005 では colmodctr と呼ばれています。(保存されない計算列を除く) 各テーブル列に、個々の colmodctr が存在します。

クエリ プランを再コンパイルするかどうかを決めるために実行するしきい値超過テストは、次の式で定義されます。

| modctr(snapshot) – modctr(current) | >= RT

modctr(current) は変更カウンタの現在の値を示し、modctr(snapshot) はクエリ プランが最後に実行されたときの変更カウンタの値を示します。しきい値超過が注目すべき統計のいずれかで生じた場合、クエリ プランは再コンパイルされます。SQL Server 2000 では、クエリを含む "バッチ全体" が再コンパイルされますが、SQL Server 2005 では、問題のクエリのみが再コンパイルされます。

Recomp02.gif

テーブルまたはインデックス付きビュー T に統計がない場合、またはクエリのコンパイル中に T の既存の統計が "注目すべき" 統計と見なされない場合は、純粋に T の基数に基づいて、次のしきい値超過テストが実行されます。

| card(snapshot) – card(current) | >= RT

card(current) は現在の T の行数を示し、card(snapshot) はクエリ プランを最後に実行したときの行数を示します。

以下のセクションでは、"全体像" で紹介した重要な概念について説明します。

"注目すべき" 統計の概念

クエリ プラン P ごとに、オプティマイザは、P を生成するために読み込まれた統計の ID を格納します。"読み込まれた" セットには、以下の両方が含まれることに注意してください。

  • P で表示される操作の基数の推定として使用される統計

  • クエリの最適化中に検討されるが、P の利益になるように破棄されたクエリ プランで基数の推定として使用される統計

つまり、クエリ オプティマイザは、どういうわけか、読みまれたすべての統計を "注目すべき" 統計と見なします。

統計は手動でも自動でも作成または更新できることを思い出してください。また、以下のコマンドの実行により、統計の更新が行われます。

  • CREATE INDEX ... WITH DROP EXISTING

  • sp_createstats ストアド プロシージャ

  • sp_updatestats ストアド プロシージャ

  • DBCC DBREINDEX (DBCC INDEXDEFRAG ではありません!)

再コンパイルのしきい値 (RT)

テーブルの再コンパイルのしきい値により、テーブルを参照するクエリを再コンパイルする頻度が決まることがあります。RT は、テーブルの種類 (永続的なものか一時的なものか)、およびクエリ プランのコンパイル時のテーブル内の行数 (基数) によって異なります。バッチで参照されるすべてのテーブルの再コンパイルのしきい値は、そのバッチのクエリ プランと一緒に格納されます。

RT は次のように計算されます (n は、クエリ プランのコンパイル時のテーブルの基数を示します)。

  • パーマネント テーブルの場合

    • If n <= 500, RT = 500.

    • If n > 500, RT = 500 + 0.20 * n.

  • 一時テーブルの場合

    • If n < 6, RT = 6.

    • If 6 <= n <= 500, RT = 500.

    • If n > 500, RT = 500 + 0.20 * n.

  • テーブル変数の場合

    • RT は存在しません。そのため、テーブル変数の基数の変更が原因で再コンパイルが行われることはありません。

テーブルの変更カウンタ (rowmodctr および colmodctr) の場合

既に説明したとおり、RT は、テーブルで行われた変更回数に対して比較されます。テーブルで行われた変更回数は、rowmodctr (SQL Server 2000 の場合) および colmodctr (SQL Server 2005 の場合) というカウンタを使用して監視されます。これらのカウンタは両方ともトランザクション処理されません。たとえば、トランザクションが開始し、テーブルに 100 行挿入した後で、ロールバックされると、変更カウンタへの変更はロールバックされません。

Rowmodctr (SQL Server 2000)

各テーブルには、1 つの rowmodctr が関連付けられています。その値は、sysindexes システム テーブルから利用できます。rowmodctr のスナップショット値は、テーブルまたはインデックス付きビュー T の 1 つ以上の列で作成されたすべての統計に関連付けられています。手動であっても自動 (SQL Server の自動統計機能) であっても、この統計が更新されると、rowmodctr のスナップショット値も更新されます。

"しきい値超過" テストで説明した Rowmodctr(current) は、クエリのコンパイル中にテストが実行される場合に、(ヒープまたはクラスタ化インデックスの) sysindexes システム テーブルに保存される値です。

rowmodctr は、SQL Server 2005 サーバーで使用できますが、値は常に 0 になります。

補足になりますが、SQL Server 2000 では、rowmodctr が 0 の場合、再コンパイルの原因にはならないことがあります。

Colmodctr (SQL Server 2005)

rowmodctr とは異なり、colmodctr 値は "テーブル列ごと" に格納されます (ただし、保存されない計算列は除きます)。保存される計算列には、一般の列と同様に、colmodctrs が含まれています。colmodctr 値を使用して、テーブルへの変更をより細かく監視できます。ユーザーは Colmodctr 値を使用できません。つまり、この値は、クエリ プロセッサのみが使用できます。

テーブルまたはインデックス付きビュー T の 1 つ以上の列で (手動でも、または自動統計機能により自動でも) 統計が作成または更新されると、leftmost 列の colmodctr のスナップショット値は、統計 BLOB に格納されます。

"しきい値超過" テストで説明した Colmodctr(current) は、クエリのコンパイル中にテストが実行される場合に、SQL Server 2005 のメタデータに保存される値です。

rowmodctr の値と異なり、colmodctr の値は増加し続けます。そのため、colmodctr 値が 0 にリセットされることはありません。

保存されない計算列の Colmodctr 値は存在しません。これらの値は、計算に加える列から派生します。

rowmodctr と colmodctr を使用した、テーブルまたはインデックス付きビューの変更の監視

rowmodctr と colmodctr の値が再コンパイルの決定に使用されるため、これらの値は、テーブルの変更に応じて変更されます。次の説明では、テーブルについてのみに触れています。ただし、同じ説明がインデックス付きビューに当てはまります。テーブルは、INSERT、DELETE、UPDATE、一括挿入、およびテーブルの切り捨てのステートメントが原因で、変更されることがあります。次の表では、rowmodctr と colmodctr の値の変更方法を定義しています。

ステートメント

SQL Server 2000

SQL Server 2005

INSERT

rowmodctr += 1

すべて colmodctr += 1

DELETE

rowmodctr += 1

すべて colmodctr += 1

UPDATE

rowmodctr += 2 または 3。"2" になる根拠として、更新が削除 (1) と挿入 (1) を合わせた操作である点が挙げられます。

キー列以外の列を更新する場合は、更新されたすべての列に colmodctr += 1 を使用します。

キー列を更新する場合は、すべての列に colmodctr += 2 を使用します。

一括挿入

変更なし。

n 個の INSERT と同様。すべて colmodctr += n (n は一括挿入された行数)。

テーブルの切り捨て

変更なし。

n 個の DELETE と同様。すべて colmodctr += n (n はテーブルの基数)。
2 つの例外

以下の 2 つの例外では、プランの最適性に関連する再コンパイルが別の方法で処理されます。

例外 1: 空のテーブルまたはインデックス付きビューで作成された統計

SQL Server 2005 では、次のシナリオを SQL Server 2000 とは "異なる方法で" 処理します。ユーザーは、空のテーブル T を作成し、T の 1 つ以上の列で統計 S を作成します。T が空なので、統計 BLOB (ヒストグラム) は NULL ですが、統計が T に作成されました。クエリのコンパイル中に、S が "注目すべき" 統計であることがわかったと仮定します。再コンパイルのしきい値に関する "500 行" のルールにより、T に最低 500 行含まれた後のみ、SQL Server 2000 では、再コンパイルが行われます。そのため、ユーザーは、T に最低 500 行含まれるまで、最適ではないプランに苦しみます。

SQL Server 2005 では、この例外を検出し、別の方法で処理します。SQL Server 2005 では、このようなテーブルまたはインデックス付きビューの再コンパイルのしきい値は 1 です。つまり、T に 1 行でも挿入すると、再コンパイルが行われるようになります。このような再コンパイルが行われると、S は更新され、S のヒストグラムは NULL ではなくなります。ただし、この再コンパイルの後は、再コンパイルのしきい値の一般的なルール (500 + 0.20 * n) に従います。

SQL Server 2005 では、(1) T に統計がない場合、または (2) クエリのコンパイル中に "注目すべき" 統計と見なされる統計が T にない場合も、再コンパイルのしきい値は 1 です。

例外 2: トリガの再コンパイル

プランの最適性に関連する、再コンパイルのすべての原因は、トリガに当てはまります。さらに、プランの最適性に関連するトリガの再コンパイルも発生することがあります。これは、"挿入" または "削除" されたテーブル内の行数が、あるトリガを実行してから次のトリガを実行するまでに大幅に変更されるためです。

複数の行ではなく、1 つの行に影響するトリガは、互いに独立してキャッシュされることを思い出してください。"挿入" および "削除" されたテーブル内の行数は、トリガのクエリ プランと一緒に格納されます。これらの数値は、プランのキャッシュの原因となったトリガの実行の行数を表します。後に続くトリガを実行した結果、"挿入" または "削除" されたテーブルの行数が "大幅に異なる" と、トリガが再コンパイルされます (さらに、新しい行数を含む新たなクエリ プランがキャッシュされます)。

SQL Server 2005 では、"大幅に異なる" 行数が次の式で定義されます。

| log10(n) – log10(m) | > 1         if m > n 
| log10(n) – log10(m) | > 2.1      otherwise

この場合の n はキャッシュされたクエリ プランにある "挿入" または "削除" されたテーブル内の行数を示し、m は現在のトリガの実行に対応するテーブルの行数を示します。"挿入" されたテーブルと "削除" されたテーブルがどちらも存在する場合、上記のテストは、両方のテーブルに対して個別に実行されます。

計算の例として、行数を 10 から 100 に変更しても、再コンパイルは行われませんが、10 から 101 に変更すると、再コンパイルが行われます。

SQL Server 2000 では、"大幅に異なる" 行数が次の式で定義されます。

| log10(n+5) – log10(m+5) | >= 1

この場合の n および m は、既に説明したように定義されています。SQL Server 2000 では、この式に従って、"挿入" または "削除" されたテーブルのいずれかの基数を 5 から 95 に変更すると、再コンパイルが行われます。ただし、5 から 94 に変更しても、再コンパイルは行われません。

統計に関連する再コンパイルの特定

統計に関連する再コンパイルは、"Statistics changed" という文字列を含む、プロファイラ トレースの "EventSubClass" 列で特定できます (この列については、後で説明します)。

最後に

本書のトピックに直接関連しない問題として、同一の列セットの複数の統計が同じ順番になっている場合に、クエリ オプティマイザでは、クエリの最適化中に読み込む統計がどのように決まるかという点が挙げられます。答えは簡単ではありませんが、クエリ オプティマイザでは、古い統計より最新の統計を優先する、サンプルを使用して計算された統計より FULLSCAN オプションを使用して計算された統計を優先するなどのガイドラインを使用します。

プランの最適性に関連するコンパイル、再コンパイル、および統計の作成や更新の "因果" 関係に関して、混乱が生じる可能性があります。手動または自動で統計を作成または更新できることを思い出してください。コンパイルおよび再コンパイルのみが、統計の自動作成または更新の原因になります。一方、(手動または自動で) 統計が作成または更新されると、その統計が "注目すべき" 統計であることに気付くクエリ プランが再コンパイルされる可能性が高まります。

ベスト プラクティス

プランの最適性に関連するバッチの再コンパイルを少なくするためのベスト プラクティスを以下に示します。

ベスト プラクティス : テーブル変数の基数を変更しても、再コンパイルが行われないため、一時テーブルの代わりにテーブル変数を使用することを検討してください。ただし、クエリ オプティマイザがテーブル変数の基数を監視しなかったり、統計がテーブル変数で作成または管理されないため、最適ではないクエリ プランが生じる可能性があります。これが事実かどうかを調べて、適切に比較検討する必要があります。

ベスト プラクティス : KEEP PLAN クエリ ヒントは、一時テーブルの再コンパイルのしきい値を変更して、その値をパーマネント テーブルの再コンパイルのしきい値と同じにします。その結果、一時テーブルへの変更が多くの再コンパイルの原因となっている場合、このクエリ ヒントを使用できます。ヒントは、次の構文を使用して指定できます。

SELECT B.col4, sum(A.col1) 
FROM dbo.PermTable A INNER JOIN #TempTable B ON A.col1 = B.col2 
WHERE B.col3 < 100 
GROUP BY B.col4 
OPTION (KEEP PLAN)

ベスト プラクティス : プランの最適性に関連する (統計の更新に関連する) 原因による再コンパイルを完全に回避するには、次の構文を使用して、KEEPFIXED PLAN クエリ ヒントを指定できます。

SELECT c.TerritoryID, count(*) as Number, c.SalesPersonID 
FROM Sales.Store s INNER JOIN Sales.Customer c 
ON s.CustomerID = c.CustomerID 
WHERE s.Name LIKE '%Bike%' AND c.SalesPersonID > 285 
GROUP BY c.TerritoryID, c.SalesPersonID 
ORDER BY Number DESC 
OPTION (KEEPFIXED PLAN)

このオプションが有効な場合、正確さに関連する理由により、再コンパイルのみが行われる可能性があります。たとえば、ステートメントで参照されるテーブルのスキーマが変更されたり、テーブルに sp_recompile プロシージャでマークされたりします。

SQL Server 2005 では、以下のように、動作にわずかな変化があります。OPTION(KEEPFIXED PLAN) ヒントを含むクエリが初めてコンパイルされており、コンパイルが原因で統計の自動作成が行われると仮定します。SQL Server 2005 で特別な "統計ロック" を取得できる場合、再コンパイルが行われ、統計が自動的に作成されます。"統計ロック" を取得できない場合は、再コンパイルが行われず、クエリはその統計なしでコンパイルされます。SQL Server 2000 では、統計に関連する理由により、OPTION(KEEPFIXED PLAN) を含むクエリが再コンパイルされません。そのため、このシナリオでは、"統計ロック" の取得または統計の自動作成は試みられません。

ベスト プラクティス : インデックスの統計およびテーブルまたはインデックス付きビューで定義された統計の自動更新を無効にすると、これらのオブジェクトによって行われる、プランの最適性に関連する再コンパイルが停止します。ただし、この方法を使用して "自動統計" 機能を無効にすることは一般的に推奨されていないことに注意してください。クエリ オプティマイザがこのようなオブジェクトでのデータの変更に対して敏感ではなく、最適ではないクエリ プランが生じる可能性があります。この方法は、他のあらゆる手段を尽くした後、最後の手段としてのみ使用してください。

コンパイル、再コンパイル、および同時実行

SQL Server 2000 では、ストアド プロシージャ、トリガ、および動的 SQL のコンパイルおよび再コンパイルが順番に実行されます。たとえば、"EXEC dbo.SP1" を使用して、実行するためにストアド プロシージャが送信されるとします。SQL Server が SP1 をコンパイルしている間、同じストアド プロシージャを参照する別の要求 "EXEC dbo.SP1" が受け取るとします。2 番目の要求は、最初の要求が SP1 のコンパイルを完了するまで待機し、結果として生じるクエリ プランを再利用しようとします。SQL Server 2005 では、コンパイルは順番に実行されますが、再コンパイルは順番に実行されません。つまり、同じストアド プロシージャの再コンパイルが 2 つ同時に続行する可能性があります。最後に終了する再コンパイルの要求は、他の要求で生成されたクエリ プランを置き換えます。

コンパイル、再コンパイル、およびパラメータ スニッフィング

"パラメータ スニッフィング" とは、SQL Server の実行環境がコンパイルまたは再コンパイル中に現在のパラメータ値を "探り出し"、その値をクエリ オプティマイザに渡すプロセスのことです。その結果、この値を使用して、よリ処理速度の速いクエリ実行プランを生成できるようになる場合があります。"現在" という単語は、コンパイルまたは再コンパイルの原因となるステートメントの呼び出しに存在するパラメータ値のことです。SQL Server 2000 および SQL Server 2005 の両方では、次の種類のバッチのコンパイルまたは再コンパイル中に、パラメータが探り出されます。

  • ストアド プロシージャ

  • sp_executesql で送信されたクエリ

  • 準備されたクエリ

SQL Server 2005 では、OPTION(RECOMPILE) クエリ ヒントを使用して送信されたクエリの動作が拡張されます。このようなクエリ (SELECT、INSERT、UPDATE、または DELETE) では、パラメータ値、およびローカル変数の現在の値の両方が探り出されます。(パラメータおよびローカル変数の) 探り出された値は、OPTION(RECOMPILE) ヒントを含むステートメントの直前のバッチに存在する値です。特に、パラメータの場合、バッチの呼び出しに伴って生じる値は探り出されません。

再コンパイルの特定

SQL Server のプロファイラにより、再コンパイルの原因となるバッチを簡単に特定できます。新しいプロファイラ トレースを開始して、Stored Procedures イベント クラスの以下のイベントを選択します (生成されるデータの量を削減するには、その他のイベントの選択を解除することをお勧めします)。

  • SP:Starting

  • SP:StmtStarting

  • SP:Recompile

  • SP:Completed

さらに、統計の更新に関連する再コンパイルを検出するために、"Objects" クラスの "Auto Stats" イベントを選択できます。

ここでは、SQL Server 2005 Management Studio を起動して、次の T-SQL コードを実行します。

use AdventureWorks          -- SQL Server 2000 では "use pubs" と記述します。 
go 
drop procedure DemoProc1 
go 
create procedure DemoProc1 as 
create table #t1 (a int, b int) 
select * from #t1 
go 
exec DemoProc1 
go 
exec DemoProc1 
go

プロファイラ トレースを一時停止して、次のイベントのシーケンスを確認します。

EventClass

TextData

EventSubClass

SP:Starting

exec DemoProc1

 

SP:StmtStarting

-- DemoProc1 create table #t1 (a int, b int)

 

SP:StmtStarting

-- DemoProc1 select * from #t1

 

SP:Recompile

 

Deferred compile

SP:StmtStarting

-- DemoProc1 select * from #t1

 

SP:Completed

exec DemoProc1

 

SP:Starting

exec DemoProc1

 

SP:StmtStarting

-- DemoProc1 create table #t1 (a int, b int)

 

SP:StmtStarting

-- DemoProc1 select * from #t1

 

SP:Completed

exec DemoProc1

 

イベント シーケンスは、"select * from #t1" が再コンパイルの原因となるステートメントであったことを示しています。EventSubClass 列では、再コンパイルの原因を示しています。この場合、DemoProc1 がコンパイルされてから実行を開始すると、"create table" ステートメントはコンパイルされることがあります。後に続く "select" ステートメントはコンパイルされません。これは、このステートメントが、最初のコンパイル時に存在しなかった一時テーブル #t1 を参照したためです。したがって、DemoProc1 のコンパイル済みのプランは完了しませんでした。DemoProc1 が実行を開始した場合、#t1 が作成され、その後、"select" ステートメントがコンパイルされることがあります。DemoProc1 が既に実行しているので、再コンパイルの定義に従い、このコンパイルは再コンパイルと見なされます。この再コンパイルの原因は、正確には、"遅延したコンパイル" と見なされています。

興味深いことに、DemoProc1 を再度実行すると、クエリ プランは完成しません。再コンパイルにより、DemoProc1 の完成したクエリ プランがプラン キャッシュに挿入されます。そのため、2 回目の実行では、再コンパイルは行われません。

同じ現象が SQL Server 2000 で見られます。

また、以下のトレース イベントのセットを選択しても、再コンパイルの原因となるバッチを特定できます。

  • SP:Starting

  • SP:StmtCompleted

  • SP:Recompile

  • SP:Completed

この新しいトレース イベントのセットを選択した後に上記の例を実行すると、トレース出力は次のようになります。

EventClass

TextData

EventSubClass

SP:Starting

exec DemoProc1

 

SP:StmtCompleted

-- DemoProc1 create table #t1 (a int, b int)

 

SP:Recompile

 

Deferred compile

SP:StmtCompleted

-- DemoProc1 select * from #t1

 

SP:Completed

exec DemoProc1

 

SP:Starting

exec DemoProc1

 

SP:StmtCompleted

-- DemoProc1 create table #t1 (a int, b int)

 

SP:StmtCompleted

-- DemoProc1 select * from #t1

 

SP:Completed

exec DemoProc1

 

この場合、再コンパイルの原因となるステートメントが SP:Recompile イベントの "後" に出力されることに注意してください。この方法は、最初の方法よりも多少明確ではありません。そのため、今後は、最初のプロファイラ トレース イベントのセットをトレースすることにします。

SP:Recompile イベントに関して報告された、考えられるすべての再コンパイルの原因を表示するには、SQL Server 2005 で以下のクエリを発行します。

select v.subclass_name, v.subclass_value 
from sys.trace_events e inner join sys.trace_subclass_values v  
on e.trace_event_id = v.trace_event_id 
where e.name = 'SP:Recompile'

上記のクエリの出力結果は以下のとおりです (網掛けになっていない列のみが出力されます。網掛けになっている列には、詳細が追加されています)。

SubclassName

SubclassValue

再コンパイルの原因の詳細

Schema changed

1

スキーマ、バインド、または権限がコンパイルと実行間で変更されました。

Statistics changed

2

統計が変更されました。

Deferred compile

3

DNR (名前の遅延解決) による再コンパイル。チェックが実行時に遅延するため、コンパイル時にオブジェクトが見つかりません。

Set option change

4

Set オプションがバッチ内で変更されました。

Temp table changed

5

一時テーブルのスキーマ、バインディング、または権限が変更されました。

Remote rowset changed

6

リモート行セットのスキーマ、バインディング、または権限が変更されました。

Query notification environment changed

8

(SQL Server 2005 の新機能!)

Partition view changed

9

SQL Server 2005 では、一部のインデックス付きビュー内のクエリの WHERE 句に、データに依存することを示す述語が追加される場合があります。基になるデータが変更されると、このような暗黙の述語が無効になり、キャッシュされた関連するクエリ プランが再コンパイルを必要とします (SQL Server 2005 の新機能!)。

SQL Server 2000 では、EventSubClass 列に 1 から 6 までの整数値が含まれます。これらの値には、上記の表で説明したのと同じ意味が含まれています。最後の 2 つの項目は、SQL Server 2005 の新機能です。

このセクションにある 2 つの例では、SQL Server 2000 でのトレース出力は SQL Server 2005 でのトレース出力と同じです。ただし、SQL Server 2000 では、EventSubClass 列に "Deferred compile" という文字列ではなく、3 が含まれている点を除きます。SQL Server 2005 では、内部でステートメント レベルの再コンパイルが行われます。そのため、SQL Server 2005 では、"select * from #t1" のみが再コンパイルされるのに対して、SQL Server 2000 では、DemoProc1 全体が再コンパイルされます。

再コンパイルは、DDL と DML が混在することにより発生します。

データ定義言語 (DDL) ステートメントとデータ操作言語 (DML) ステートメントをバッチまたはストアド プロシージャ内に混在させることは、不要な再コンパイルの原因になる可能性があるため、お勧めしません。次の例では、ストアド プロシージャを使用して、このことを示しています (バッチの場合も同じ現象が発生しますが、SQL Server 2005 プロファイラでは、不要なトレース イベントが表示されないため、実際にその現象を確認することができません)。次のストアド プロシージャを作成します。

drop procedure MixDDLDML 
go 
create procedure MixDDLDML as 
create table tab1 (a int)            -- DDL 
select * from tab1                   -- DML 
create index nc_tab1idx1 on tab1(a)  -- DDL 
select * from tab1                   -- DML 
create table tab2 (a int)            -- DDL 
select * from tab2                   -- DML 
go 
exec MixDDLDML 
go

プロファイラのトレース出力では、次のイベント シーケンスを確認できます。

EventClass

TextData

EventSubClass

SP:Starting

exec MixDDLDML

 

SP:StmtStarting

-- MixDDLDML create table tab1 (a int)       --DDL

 

SP:StmtStarting

-- MixDDLDML select * from tab1   -- DML

 

SP:Recompile

 

Deferred compile

SP:StmtStarting

-- MixDDLDML select * from tab1   -- DML

 

SP:StmtStarting

-- MixDDLDML create index nc_tab1idx1 on tab1(a)    -- DDL

 

SP:StmtStarting

-- MixDDLDML select * from tab1   -- DML

 

SP:Recompile

 

Deferred compile

SP:StmtStarting

-- MixDDLDML select * from tab1   -- DML

 

SP:StmtStarting

-- MixDDLDML create table tab2 (a int)       --DDL

 

SP:StmtStarting

-- MixDDLDML select * from tab2   -- DML

 

SP:Recompile

 

Deferred compile

SP:StmtStarting

-- MixDDLDML select * from tab2   -- DML

 

SP:Completed

exec MixDDLDML

 

MixDDLDML のコンパイル方法を以下に示します。

  1. MixDDLDML を初めて (再コンパイルではなく) コンパイルしている間、そのスケルトン プランが生成されます。テーブルの tab1 および tab2 が存在しないため、3 つの "select" ステートメントのプランを作成できません。そのスケルトンには、2 つの "create table" ステートメントおよび 1 つの "create index" ステートメントのプランが含まれます。

  2. プロシージャが実行を開始すると、テーブルの tab1 が作成されます。最初の "select * from tab1" のプランがないので、ステートメント レベルの再コンパイルが行われます (SQL Server 2000 では、2 番目の "select * from tabl" のプランもこの再コンパイルで生成されます)。

  3. 2 番目の "select * from tab1" により、再コンパイルが行われます。これは、そのクエリのプランがまだ存在しないからです。SQL Server 2000 の場合は、この再コンパイルは行われますが、"tab1" で非クラスタ化インデックスが作成されることで "tab1" のスキーマが変更されたという別の理由によるものです。

  4. 次に、"tab2" が作成されます。"select * from tab2" により、再コンパイルが行われます。これは、そのクエリのプランが存在しないからです。

要するに、この例では、SQL Server 2000 および SQL Server 2005 のどちらにおいても、再コンパイルが 3 回行われます。ただし、SQL Server 2005 の再コンパイルは、SQL Server 2000 よりもコストがかかりません。これは、SQL Server 2005 の再コンパイルがストアド プロシージャ レベルではなく、ステートメント レベルで行われるためです。

ストアド プロシージャを次のように記述すると、興味深い現象が見られます。

create procedure DDLBeforeDML as 
create table tab1 (a int)            -- DDL 
create index nc_tab1idx1 on tab1(a)  -- DDL 
create table tab2 (a int)            -- DDL 
select * from tab1                   -- DML 
select * from tab1                   -- DML 
select * from tab2                   -- DML 
go 
exec DDLBeforeDML 
go

プロファイラのトレース出力では、次のイベント シーケンスを確認できます。

EventClass

TextData

EventSubClass

SP:Starting

exec DDLBeforeDML

 

SP:StmtStarting

-- DDLBeforeDML create table tab1 (a int)       -- DDL

 

SP:StmtStarting

-- DDLBeforeDML create index nc_tab1idx1 on tab1(a)    -- DDL

 

SP:StmtStarting

-- DDLBeforeDML create table tab2 (a int)       -- DDL

 

SP:StmtStarting

-- DDLBeforeDML select * from tab1   --DML

 

SP:Recompile

 

Deferred compile

SP:StmtStarting

-- DDLBeforeDML select * from tab1    --DML

 

SP:StmtStarting

-- DDLBeforeDML   select * from tab1    --DML

 

SP:Recompile

 

Deferred compile

SP:StmtStarting

-- DDLBeforeDML select * from tab1    --DML

 

SP:StmtStarting

-- DDLBeforeDML select * from tab2           -- DML

 

SP:Recompile

 

Deferred compile

SP:StmtStarting

-- DDLBeforeDML select * from tab2           -- DML

 

SP:Completed

exec DDLBeforeDML

 

SQL Server 2005 では、ステートメント レベルの再コンパイルにより、依然として再コンパイルが 3 回行われます。MixDDLDML ストアド プロシージャと比較すると、再コンパイルの回数は減少していません。同じ例を SQL Server 2000 で試した場合、再コンパイルの回数は 3 回から 1 回に減ります。SQL Server 2000 では、再コンパイルがストアド プロシージャ レベルで行われるため、3 つの "select" ステートメントを 1 回でコンパイルできます。つまり、SQL Server 2005 では、SQL Server 2000 と比較すると、再コンパイルにかかる作業は増加しませんが、再コンパイルの回数は増加しました。

次に、以下の T-SQL コードを考えてみます。

-- dbo.someTable を使用して、後で一時テーブルを設定
-- します。 
create table dbo.someTable (a int not null, b int not null) 
go 
declare @i int 
set @i = 1 
while (@i <= 2000) 
begin 
    insert into dbo.someTable values (@i, @i+5) 
    set @i = @i + 1 
end 
go 
  
-- これは最も関心のあるストアド プロシージャです。 
create procedure dbo.AlwaysRecompile 
as  
set nocount on 
  
-- 一時テーブルを作成します。
create table #temp1(c int not null, d int not null) 
  
select count(*) from #temp1 
  
-- この段階で、#temp1 に 2000 行を設定します。
insert into #temp1 
select * from dbo.someTable 
  
-- #temp1 でクラスタ化インデックスを作成します。
create clustered index cl_idx_temp1 on #temp1(c) 
  
select count(*) from #temp1 
go

SQL Server 2000 では、このストアド プロシージャを初めて実行するときに、最初の SP:Recompile イベントが最初の "select" ステートメントに対して生成されます。これは遅延したコンパイルであり、実際の再コンパイルではありません。2 番目の SP:Recompile イベントは、2 番目の "select" ステートメントに対するものです。SQL Server 2000 では、コンパイルがバッチ レベルで行われるため、最初の再コンパイルが行われたときに、2 番目の "select" も再コンパイルされました。その後、実行中に、#temp1 のスキーマは、新しく作成されたクラスタ化インデックスが原因で変更されました。したがって、2 番目の SP:Recompile の原因はスキーマの変更にあります。

行数の変更による再コンパイル

次のストアド プロシージャとその実行を考えてみます。

use AdventureWorks   -- SQL Server 2000 では "use pubs" と記述します。
go 
create procedure RowCountDemo 
as 
begin 
    create table #t1 (a int, b int) 
 
    declare @i int 
    set @i = 0    while (@i < 20) 
    begin 
       insert into #t1 values (@i, 2*@i - 50) 
 
       select a 
       from #t1  
       where a < 10 or ((b > 20 or a >=100) and (a < 10000)) 
       group by a 
  
       set @i = @i + 1 
    end 
end 
go 
exec RowCountDemo 
go

しきい値を計算する際に一時テーブルが空の場合、一時テーブルの再コンパイルのしきい値は 6 であることを思い出してください。RowCountDemo を実行する場合、#t1 にちょうど 6 行含まれると、"変更された統計" に関連する再コンパイルが見られます。"while" ループの上限を変更することにより、より多くの再コンパイルが見られます。

SET オプションの変更による再コンパイル

次のストアド プロシージャを考えてみます。

use AdventureWorks 
go 
create procedure SetOptionsDemo as 
begin 
    set ansi_nulls off 
    select p.Size, sum(p.ListPrice) 
    from Production.Product p  
         inner join Production.ProductCategory pc 
          on p.ProductSubcategoryID = pc.ProductCategoryID 
    where p.Color = 'Black' 
    group by p.Size 
end 
go 
exec SetOptionsDemo    -- 再コンパイルの原因となります。
go 
exec SetOptionsDemo    -- 再コンパイルの原因にはなりません。
go

SetOptionsDemo が実行されると、"ansi_nulls" が有効な "select" クエリがコンパイルされます。SetOptionsDemo が実行を開始すると、"set ansi_nulls off" により、その SET オプションの値が変更されます。その結果、コンパイルされたクエリ プランは "有効" ではなくなります。そのため、"ansi_nulls" が無効なクエリがコンパイルされます。2 番目の実行では、"ansi_nulls" が無効のキャッシュされたプランがコンパイルされるため、再コンパイルは行われません。

SQL Server 2000 より SQL Server 2005 で再コンパイルが多く行われる別の例

次のストアド プロシージャを考えてみます。

use AdventureWorks     -- SQL Server 2000 では "use pubs" と記述します。
go 
create procedure CreateThenReference as 
begin 
   -- 2 つの一時テーブルを作成します。
   create table #t1(a int, b int) 
   create table #t2(c int, d int) 
 
   -- これらのテーブルにいくつかのデータを設定します。
   insert into #t1 values (1, 1) 
   insert into #t1 values (2, 2) 
   insert into #t2 values (3, 2) 
   insert into #t2 values (4, 3) 
 
         -- これらのテーブルで 2 つのクエリを発行します。
   select x.a, x.b, sum(y.c) 
   from #t1 x inner join #t2 y on x.b = y.d 
   group by x.b, x.a 
   order by x.b 
  
   select *  
   from #t1 z cross join #t2 w 
   where w.c != 5 or w.c != 2 
end 
go 
exec CreateThenReference 
go

SQL Server 2005 では、CreateThenReference を初めて実行すると、ステートメント レベルの再コンパイルが 6 回行われます。そのうち、4 回は "insert" ステートメント、2 回は "select" ステートメントで行われます。ストアド プロシージャが実行を開始する場合、最初のクエリ プランには、"insert" または "select" のいずれのステートメントのプランも含まれません。これらのステートメントが参照するオブジェクト (つまり、一時テーブル #t1 および #t2) が存在しないからです。#t1 および #t2 が作成された後、"insert" および "select" のクエリ プランがコンパイルされます。このようなコンパイルは再コンパイルと見なされます。SQL Server 2000 では、ストアド プロシージャ全体が同時に再コンパイルされるため、(ストアド プロシージャ レベルの) 再コンパイルが 1 回しか行われません。これは、最初の "insert" が実行を開始する場合に行われます。その時点で、ストアド プロシージャ全体が再コンパイルされます。#t1 および #t2 が既に存在するため、後に続く "insert" および "select" がすべて 1 回でコンパイルされます。明らかに、SQL Server 2005 では、#t1 や #t2 などのオブジェクトを参照するステートメントの追加に関係なく、ステートメント レベルの再コンパイルの回数は増加する可能性があります。

ツールとコマンド

ここでは、再コンパイルに関連するシナリオを監視およびデバッグする際に存在するさまざまなツールとコマンドについて説明します。

Sys.syscacheobjects 仮想テーブル

この仮想テーブルは、概念上は master データベースにしか存在しませんが、任意のデータベースからクエリすることができます。この仮想テーブルの cacheobjtype 列は特に関心を引きます。cacheobjtype = "Compiled Plan" の場合、行はクエリ プランを参照します。cacheobjtype = "Executable Plan" の場合、行は実行コンテキストを参照します。既に説明したとおり、各実行コンテキストには、関連するクエリ プランが必要ですが、その逆はありません。興味深いもう 1 つの列に、objtype 列があります。この列は、キャッシュされるプランを持つオブジェクトの種類 (たとえば、"Adhoc"、"Prepared"、および "Proc") を示します。setopts 列は、プランをコンパイルしたときに設定されていた SET オプションを示すビットマップをエンコードします。場合によっては、(setopts 列のみが異なる) 同一のコンパイル済みのプランの複数のコピーが、プラン キャッシュにキャッシュされます。これは、さまざまな接続が SET オプションの異なる設定を使用しているという、あまり望ましくない状況を示します。usecounts 列は、オブジェクトがキャッシュされてから、キャッシュされたオブジェクトが再利用された回数を格納します。

この仮想テーブルの詳細については、BOL を参照してください。

DBCC FREEPROCCACHE

このコマンドは、キャッシュされたすべてのクエリ プランおよび実行コンテキストをプラン キャッシュから削除します。このコマンドは、実行中のアプリケーションのパフォーマンスに悪影響を与える可能性があるため、実稼動サーバーでは実行しないことをお勧めします。このコマンドは、再コンパイルの問題のトラブルシューティングを行う際、プラン キャッシュのコンテンツの制御に役立ちます。

DBCC FLUSHPROCINDB( db_id )

このコマンドは、キャッシュしたすべてのプランを特定のデータベースのプラン キャッシュから削除します。このコマンドは、実行中のアプリケーションのパフォーマンスに悪影響を与える可能性があるため、実稼動サーバーでは実行しないことをお勧めします。

プロファイラ トレース イベント

以下のプロファイラ トレース イベントは、プランのキャッシュ、コンパイル、および再コンパイルの動作を監視およびデバッグすることに関連があります。

  • 'Cursors: CursorRecompile' (SQL Server 2005 の新機能)。カーソルに関連するバッチが原因の再コンパイルを監視します。

  • 'Objects: Auto Stats'。SQL Server の "自動統計" 機能による統計の更新を監視します。

  • 'Performance: Show Plan All For Query Compile' (SQL Server 2005 の新機能)。バッチのコンパイルの監視に役立ちます。このイベントは、コンパイルと再コンパイルを区別しません。このイベントでは、("set showplan_all on" オプションを使用して作成されたプラン表示のデータに類似する) テキスト形式のプラン表示のデータが作成されます。

  • 'Performance: Show Plan XML For Query Compile' (SQL Server 2005 の新機能)。バッチのコンパイルの監視に役立ちます。このイベントは、コンパイルと再コンパイルを区別しません。このイベントでは、("set showplan_xml on" オプションを使用して作成されたプラン表示のデータに類似する) XML 形式のプラン表示のデータが作成されます。

  • 'Stored Procedures: SP: Recompile'。再コンパイルが行われるときに使用されます。たとえば、SP:CacheInsert、SP:StmtStarting、SP:CacheHit、SP:Starting など、"Stored Procedures" カテゴリの他のイベントも有効です。

パフォーマンス モニタ カウンタ

以下のパフォーマンス モニタ カウンタの値は、必要以上のコンパイルおよび再コンパイルにより発生する可能性があるパフォーマンスの問題をデバッグする際に関係します。

パフォーマンス オブジェクト

カウンタ

SQLServer: Buffer Manager

Buffer cache hit ratio, Lazy writes/sec, Procedure cache pages, Total pages

SQLServer: Cache Manager

Cache Hit Ratio, Cache Object Counts, Cache Pages, Cache Use Counts/sec

SQLServer: Memory Manager

SQL Cache Memory (KB)

SQLServer:SQL Statistics

Auto-Param Attmpts/sec, Batch Requests/sec, Failed Auto-Params/sec, Safe Auto-Params/sec, SQL Compilations/sec, SQL Re-Compilations/sec, Unsafe Auto-Params/sec

まとめ

SQL Server 2005 は、実行するために SQL Server 2005 に送信されたさまざまな種類のステートメントのクエリ プランをキャッシュします。クエリ プランのキャッシュにより、クエリ プランを再利用し、コンパイルに伴う負担を回避して、プラン キャッシュをうまく利用できます。コーディング方法によっては、クエリ プランのキャッシュや再利用を妨げることもあるため、このような方法を避ける必要があります。SQL Server では、クエリ プランが再利用できるかどうかが検出されます。特に、クエリ プランは、2 つの理由から再利用できなくなる可能性があります。その理由として、(a) クエリ プランに表示されるオブジェクトのスキーマがプランが無効になる程変更される、および (b) クエリ プランで参照するテーブル内のデータがプランが最適ではなくなる程変更されるという点が挙げられます。SQL Server では、クエリの実行時にこのような 2 種類の状況が検出されると、必要に応じて、バッチまたはその一部が再コンパイルされます。不適切な T-SQL のコーディングが行われると、再コンパイルの回数が増加し、SQL Server のパフォーマンスが低下する可能性があります。多くの場合、このような状況をデバッグして修正することができます。

付録 A: SQL Server 2005 でクエリが自動パラメータ化されない場合

自動パラメータ化とは、SQL Server が SQL ステートメントにあるリテラル定数を @p1 や @p2 などのパラメータに置き換えるプロセスです。SQL ステートメントのコンパイル済みのプランは、パラメータ化された形式でプラン キャッシュにキャッシュされます。これにより、後続のステートメントで、リテラル定数の値だけが異なる場合は、キャッシュされたプランを再利用できます。「4. プランのキャッシュ」で説明したとおり、クエリ プランの選択に影響しないパラメータ値の SQL ステートメントのみが自動パラメータ化されます。

SQL Server の LPE (Language Processing and Execution) コンポーネントは、SQL ステートメントを自動パラメータ化します。QP (クエリ プロセッサ) コンポーネントは、リテラル定数の値がクエリ プランの選択に影響しないことに気付くと、LPE による自動パラメータ化を "安全である" と定義し、自動パラメータ化が続行されます。それ以外の場合は、自動パラメータ化は "安全ではない" と定義され、中断されます。「11.5. パフォーマンス モニタ カウンタ」で説明した一部のパフォーマンス モニタ カウンタの値 ('SQLServer: SQL Statistics' カテゴリ) は、自動パラメータ化に関する統計情報を報告します。

以下に、SQL Server 2005 で自動パラメータ化されないステートメントの種類を記載します。

  • IN 句を含むクエリは自動パラメータ化されません。以下に例を示します。

  • WHERE ProductID IN (707, 799, 905)

  • BULK INSERT ステートメント。

  • 変数が含まれている SET 句を含む UPDATE ステートメント。以下に例を示します。

    UPDATE Sales.Customer 
SET CustomerType = N'S' 
WHERE CustomerType = @a

  • UNION を含む SELECT ステートメント。

  • INTO 句を含む SELECT ステートメント。

  • FOR BROWSE 句を含む SELECT または UPDATE ステートメント。

  • OPTION 句を使用して指定されたクエリ ヒントを含むステートメント。

  • DISTINCT を含む SELECT リストを持つ SELECT ステートメント。

  • TOP 句を含むステートメント。

  • WAITFOR ステートメント。

  • FROM 句を含む DELETE または UPDATE。

  • FROM 句に以下のいずれかが含まれている場合。

    • 複数のテーブル

    • TABLESAMPLE 句

    • テーブル値関数またはテーブル値変数

    • フルテキスト テーブル

    • OPENROWSET

    • XMLUNNEST

    • OPENXML

    • OPENQUERY

    • IROWSET

    • OPENDATASOURCE

    • テーブル ヒントまたはインデックス ヒント

  • SELECT クエリにサブクエリが含まれている場合。

  • SELECT ステートメントに GROUP BY、HAVING、または COMPUTE BY が含まれている場合。

  • WHERE 句内で OR で結合されている式。

  • 「expr <> non-null-constant」という形式の比較述語。

  • フルテキスト述語。

  • INSERT、UPDATE、または DELETE 内の対象となるテーブルがテーブル値関数の場合。

  • EXEC string で送信されたステートメント。

  • TF 447 で自動パラメータ化されるパラメータを使用せずに、sp_executesql、sp_prepare、および sp_prepexec で送信されたステートメント。

  • クエリ通知が要求される場合。

  • クエリに共通テーブル式の一覧が含まれる場合。

  • クエリに FOR UPDATE 句が含まれる場合。

  • UPDATE に ORDER BY 句が含まれる場合。

  • クエリに GROUPING 句が含まれる場合。

  • INSERT INTO T DEFAULT VALUES という形式の INSERT ステートメント。

  • INSERT ... EXEC ステートメント。

  • クエリに 2 つの定数の比較が含まれている場合。以下に例を示します。

    WHERE 20 > 5

  • 自動パラメータ化を行うことによって、1000 を超えるパラメータを作成できます。