DBCCコマンド月の12日目であることSteveStedman.com、今日の注目のDBCCコマンドはDBCC FREEPROCCACHEです。
説明:
DBCC FREEPROCCACHEは、解析されたすべてのクエリプランをメモリ不足に消去するために使用されます。 これは開発環境では一般的に使用されますが、本番環境では一般的ではありません。
パフォーマンスチューニングやクエリのパラメータ化など、開発環境での使用は一般的です。 DBCC FreeProcCacheを使用してプロシージャキャッシュをクリアし、データベースを使用しているプログラムまたはwebページを実行してから、プロシージャキャッシュに何が これは、パラメータ化する必要があるクエリを見つけるのに役立ちます。 使用する別の方法は、いくつかのプログラムによって実行されているクエリを見つけることです。 これを行うには、他の人が使用していないデータベースを操作し、dbcc FreeProcCacheでプロシージャキャッシュをクリアし、把握しようとしているプログラムを実行してから、キャッシュに何があるのかを確認することから始めます。これは開発環境やテスト環境で行うことができますが、本番環境で行うことはお勧めしません。
本番環境での使用はまれですが、これはSQL Serverが問題を抱えているときに試してみる一般的なことの1つです。 SQL Serverの応答が非常に遅く、原因を見つけることができなかった場合は、dbcc FreeProcCacheでプロシージャキャッシュを解放し、それが問題を解決するかどうかを確認
DBCC FreeProcCache構文:
dbcc freeproccache
例:
次の例は、Adventureworks2012データベースを使用した開発環境からのものです。
まず、Adventureworks2012に接続し、キャッシュに何があるかを確認します。
USE AdventureWorks2012;GOSELECT size_in_bytes, text FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st;
ここでは、キャッシュにたくさんあることがわかります。 次に、DBCC FreeProcCacheを使用してキャッシュをクリアし、キャッシュにあるものをもう一度見てみましょう。
DBCC FREEPROCCACHE;SELECT size_in_bytes, text FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st;
DBCC FreeProcCacheを実行すると、キャッシュに何も残っていないことがわかります。
次のクエリを実行するときは、キャッシュ内で既に解析されているクエリを使用するのではなく、再解析する必要があります。 これは、実行するために既に解析された計画があった場合よりも少し時間がかかります。 3つのクエリを実行してから、キャッシュを見てみましょう。
GOSELECT FirstName, MiddleName, LastName FROM person.Person p WHERE FirstName like 'John';GOSELECT FirstName, MiddleName, LastName FROM person.Person p WHERE FirstName like 'Mary';GOSELECT FirstName, MiddleName, LastName FROM person.Person p WHERE FirstName like 'Bill';
各クエリの間にGOステートメントがあることに注意してください。 これにより、SSMSは各クエリを個別のバッチとして実行するように指示します。 GO文がなければ、3つのクエリは単一のバッチとして解析されていました。
ここでは、3つのクエリの結果を確認します。 最初の2つは結果を返し、3番目の1つは結果セットに行がありませんでした。 ここで、キャッシュ
SELECT size_in_bytes, text FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st;
以下に、キャッシュ内の合計20個のアイテムを見てみましょう。 青で強調表示された一番上の項目は、キャッシュに何があったかを確認するために使用したクエリであり、赤で強調表示された2番目のブロックには上からの3つのクエリが含まれており、残りの3番目はSQLまたはその他のサポートクエリによって実行されているクエリです。 たとえば、13行目は、計画をチェックする上記のクエリから呼び出されるdm_exec_sql_textです。
私たちが書いたクエリだけにこれをフィルタリングしたい場合は、ここに示すようにWHERE text LIKE…句をクエリに追加することでそれを行うことがで
SELECT size_in_bytes, text FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS stWHERE text LIKE N'SELECT FirstName%';
ここでは、3つのクエリのみが表示され、これら3つのそれぞれがSQL Serverで約40Kのメモリを占有していることがわかります。 
では、なぜ同じSELECT文の3つのコピーがあるのですか、これは少し無駄に思えます。 実際には、詳細については、以前の投稿”How much Procedure Cache memory does one SQL Statement take up”を参照してください。 これを修正する方法があります。
特定のプランハンドルに対するDBCC FreeProcCacheの使用
すべてのプランハンドルではなく、単一のプランハンドルだけをクリアする場合は、@handleというオプショ
プランハンドルを取得するには、以前のクエリを変更してプランキャッシュに何が含まれているかを表示することから始めます。 独自のシステムでWHERE句を省略することもできますが、上から問題の3つのクエリだけを表示するためにここにあります。
SELECT size_in_bytes, text, plan_handle FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS stWHERE text LIKE N'SELECT FirstName%';
ここでは、plan_handleという名前の追加列を持つ、以前と同じ三つのクエリプランを参照してください。 単一のプランハンドルを解放するには、数値プランハンドルをコピーし、それをDBCC FreeProcCacheクエリに追加します。
DBCC FREEPROCCACHE(0x060007000100FF3310B8DA7D0600000001000000000000000000000000000000000000000000000000000000);SELECT size_in_bytes, text, plan_handle FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS stWHERE text LIKE N'SELECT FirstName%';
ここでは、プランキャッシュには2つの元のクエリしか表示されません。
プロシージャキャッシュの大きさはどれくらいですか？
次のクエリを実行して、プロシージャキャッシュのサイズを確認できます。私のテストサーバーで次の結果を生成する
SELECT count(*) AS NumPlans, objtype as Type,SUM(size_in_bytes)/(1024.0*1024.0) AS size_in_mbFROM sys.dm_exec_cached_plansGROUP BY objtype;
。
データベース正常性レポートとプランキャッシュ
ここに示すように、データベース正常性レポートアプリケーションを使用してプランキャッシュを表示
メモ:
詳細については、TSQL Wiki DBCC freeproccacheを参照してください。
DBCCコマンド月at SteveStedman.com ゼリーを食べるのと同じくらい楽しいです。
SteveとStedman Solutionsのチームは、すべてのSQL Serverのニーズのためにここにいます。
Contctあなたの自由な30分の相談のための私達今日。.
私たちは助ける準備ができています！
