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

公開 : 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 ステートメント (これ以降は "バッチ" と呼びます) が 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 は、多くの種類のバッチのクエリ プランをキャッシュできます。さまざまな種類のバッチを以下に示します。種類ごとに、プランの再利用に "必要な" 条件について説明します。これらの条件は "十分" ではない可能性があることに注意してください。読者には、本書の後半で全体像を示します。

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

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

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

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

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

複数レベルのキャッシュ

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

EXEC dbo.procA

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

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

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

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

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

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

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 列目には、キャッシュされたクエリ プランが存在しない場合の例について記述されています。各カテゴリ内では、クエリ プランと実行コンテキストの例が区別されています。テキスト部分では、構造体 (クエリ プランまたは実行コンテキスト) に何か起こるかを説明しています。つまり、構造体がキャッシュされるか、再利用されるか、使用されるかを示しています。

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

クエリ プランと実行コンテキストごとに、コストが格納されます。コストは、部分的に、プラン キャッシュにおけるプランまたはコンテキストの有効期間を制御します。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 コストの測定) + メモリコスト

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

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

ページのトップへ

本書後半のロードマップ

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

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

ページのトップへ

クエリ プランの再利用

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

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

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

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

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

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

ページのトップへ

再コンパイルの原因

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

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

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

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

オブジェクトのスキーマ

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

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

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 のクエリ プロセッサで統計 (ヒストグラム) を使用して監視されています。そのため、プランの最適性に関連する原因は、統計と密接な関係にあります。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

テーブルの変更カウンタ (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 の値の変更方法を定義しています。

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 の両方では、次の種類のバッチのコンパイルまたは再コンパイル中に、パラメータが探り出されます。

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

ページのトップへ

再コンパイルの特定

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

さらに、統計の更新に関連する再コンパイルを検出するために、"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

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

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

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

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

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

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

この場合、再コンパイルの原因となるステートメントが 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'

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

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

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

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

要するに、この例では、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

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

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 )

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

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

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

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

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