SQL Server 2005 で特定のデータベースメンテナンス操作または通常のトランザクション操作を実行した後、クエリのパフォーマンスが低下することがある

現象

Microsoft SQL Server 2005 では、特定のデータベースメンテナンス操作や通常のトランザクション操作を実行した後、クエリのパフォーマンスが低下することがあります。 たとえば、データベースのバックアップを復元すると、クエリのパフォーマンスが突然低下することがあります。SQL Server 2005 Service Pack 2 以降では、この問題が発生すると、SQL Server のエラーログに次のようなメッセージが表示されます。

ただし、DBCC FREE・キャッシュコマンドまたは DBCC FREESYSTEMCACHE コマンドを実行して、プロシージャキャッシュをフラッシュすることができます。 これらのコマンドのいずれかを実行して、プロシージャキャッシュがフラッシュされた場合、SQL Server エラーログに次のようなメッセージが表示されます。

そのため、SQL Server エラーログを調査して、この記事で説明されている問題によって問題が発生したかどうかを確認できます。注: この動作は、Microsoft SQL Server 2008 では発生しません。

原因

この問題は、特定のデータベースメンテナンス操作や通常のトランザクション操作で、プロシージャキャッシュ全体をクリアするために発生します。

状態

この動作は仕様です。

詳細情報

次のシナリオで特定のデータベースレベルの操作を実行すると、プロシージャキャッシュ全体が消去されます。

• データベースの [AUTO_CLOSE データベース] オプションが [オン] に設定されている。 ユーザー接続がデータベースを参照していない場合、またはデータベースを使用している場合、バックグラウンドタスクはデータベースを自動的に閉じてシャットダウンしようとします。

• 既定のオプションが設定されたデータベースに対して、複数のクエリを実行します。 次に、データベースが削除されます。

• ソースデータベースのデータベーススナップショットがドロップされます。注: データベーススナップショットは、Microsoft SQL Server 2005 Enterprise Edition でのみ利用できます。

• データベースの状態をオフラインまたはオンラインに変更します。

• データベースのトランザクションログが正常に再構築されました。

• データベースのバックアップを復元します。

• DBCC CHECKDB ステートメントを実行します。注: これは、SQL server 2005 SP2 より前のバージョンの SQL Server 2005 でのみ該当します。 SQL Server 2005 SP2 以降のバージョンをインストールした後は、DBCC CHECKDB ステートメントを実行しても、プロシージャキャッシュ全体がフラッシュされません。

• データベースをデタッチします。

• ALTER DATABASE ステートメントの実行時には、次のいずれかのオプションを指定します。

  • で

  • オンライン

  • ファイルグループの既定の変更

  • MODIFY_NAME

  • ファイルグループ READ_WRITE を変更する

  • コレート

  • ファイルグループ READ_ONLY を変更する

  • READ_ONLY

  • READ_WRITE

• 次のサーバーオプションのいずれかが再設定ステートメントによって変更された場合、プロシージャキャッシュ全体が消去されます。

  • クロスデータベース所有者チェーン

  • index create memory (KB)

  • リモートクエリのタイムアウト

  • ユーザーオプション

  • 最大テキスト repl size (B)

  • 並列処理のコストしきい値

  • 並列処理の最大次数

  • クエリあたりの最小メモリ (KB)

  • クエリ待機

  • 最小サーバーメモリ (MB)

  • 最大サーバーメモリ (MB)

  • クエリガバナーのコスト制限

  注: 実際の値が変更されない、または max server memory server オプションの新しい値が0に設定されている場合、プロシージャキャッシュは消去されません。

この問題が発生した場合は、パフォーマンスモニターを使用して SQL Server 2005 のパフォーマンスカウンターからデータを収集すると、次の値が変更されていることがわかります。

• パフォーマンスオブジェクト: プロセス カウンター: % Processor Time インスタンス: このカウンターの SQLSERVRTHE 値は、CPU アクティビティが増加したために増加します。 基本的に、この問題が発生した場合は、プロシージャキャッシュ全体が消去されます。 そのため、後続の要求では、キャッシュされる新しいプランを生成する必要があります。 この動作により CPU アクティビティが若干増加します。

• パフォーマンスオブジェクト: SQLServer: Plan Cache counter: Cache Object 計数 インスタンス: _Total Performance オブジェクト: SQLServer: Plan cache counter : cache Pages Instance: _Total これらのカウンターの値が突然減少します。 注: SQL Server 2005 の名前付きインスタンスの場合、パフォーマンスオブジェクトは "MSSQL $InstanceName:P lan キャッシュ" という名称になります。

• パフォーマンスオブジェクト: SQLServer: SQL Statistics Counter: sqlcompilations/秒このカウンターの値は、この問題の後で大きく増加します。 注: SQL Server 2005 の名前付きインスタンスの場合、パフォーマンスオブジェクトは "MSSQL $InstanceName: SQL 統計情報" という名前になります。

SP: CacheRemoveイベントを使って SQL プロファイラートレースをキャプチャした場合、この問題が発生すると、このイベントが次のTextData列の値と共に生成されていることがわかります。

"プロシージャキャッシュはすべてフラッシュされる"