読者です 読者をやめる 読者になる 読者になる

都内で働くSEの技術的なひとりごと

都内でサラリーマンやってます。マイクロソフト系(たまに、OSS系などマイクロソフト以外の技術も...)の技術的なことについて書いています。日々の仕事の中で、気になったことを技術要素関係なく気まぐれに選んでいるので記事内容は開発言語、インフラ等ばらばらです。なお、当ブログで発信、発言は私個人のものであり、所属する組織、企業、団体等とは何のかかわりもございません。ブログの内容もきちんと検証して使用してください。よろしくお願いします♪

統計情報を更新すると、『 クエリはリコンパイルされるよね普通 』と思いつつ、なんかうまくいってない感じがすごくするので、sp_recompile を実験してみる

 ちょっと色々あって、sp_recompile に頼ってみます。MSDN の一文が超気になる。『コンパイルされたストアドプロシージャ、トリガおよびユーザー定義関数は効率を失う可能性がある。』うーん。効率を失うとは....うーん。

The queries used by stored procedures, or triggers, and user-defined functions are optimized only when they are compiled. As indexes or other changes that affect statistics are made to the database, compiled stored procedures, triggers, and user-defined functions may lose efficiency. By recompiling stored procedures and triggers that act on a table, you can reoptimize the queries.

 通常、SQL が発行されるとプランがキャッシュされます。過去のブログでキャッシュプランの確認方法を書いていますので、確認してみてください。ryuchan.hatenablog.com

 実験するまでもないんですが、質問される可能性があるので実験しますー。まず、下記のような何の変哲もないクエリを実行してみます。

DECLARE @ParmDefinition nvarchar(500);
SET @ParmDefinition = NULL
EXECUTE sp_executesql N'SELECT oh.*,od.* FROM Sales.SalesOrderHeader oh INNER JOIN Sales.SalesOrderDetail od ON oh.SalesOrderID = od.SalesOrderID ORDER BY oh.ModifiedDate',@ParmDefinition

f:id:koogucc11:20150901231328p:plain

 キャッシュプランを確認します。該当のクエリのプランが作成されているのが確認できます。

SELECT [SQL文] = st.text, 
       [プランがコンパイルされた時間] = qs.creation_time
FROM sys.dm_exec_query_stats qs
    CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
    CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
WHERE st.text LIKE '%Sales.SalesOrderHeader%'

f:id:koogucc11:20150901231623p:plain

 下記のストアドを実行して、Sales.SalesOrderHeader に再コンパイルを設定します。

exec sp_recompile N'Sales.SalesOrderHeader'

f:id:koogucc11:20150901231831p:plain

 再度、下記のクエリを実行します。

DECLARE @ParmDefinition nvarchar(500);
SET @ParmDefinition = NULL
EXECUTE sp_executesql N'SELECT oh.*,od.* FROM Sales.SalesOrderHeader oh INNER JOIN Sales.SalesOrderDetail od ON oh.SalesOrderID = od.SalesOrderID ORDER BY oh.ModifiedDate',@ParmDefinition

 再度、キャッシュプランを確認します。該当のクエリのプランが再度作成されているのが確認できます。

SELECT [SQL文] = st.text, 
       [プランがコンパイルされた時間] = qs.creation_time
FROM sys.dm_exec_query_stats qs
    CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
    CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
WHERE st.text LIKE '%Sales.SalesOrderHeader%'

f:id:koogucc11:20150901232121p:plain

 下記のようになったときは、sp_recompile....

compiled stored procedures, triggers, and user-defined functions may lose efficiency.

 腑に落ちない記事でした。

※キャンプ道具♪これ、なんだ!?

※キャンプ場はここに行くのです。
イレブン オートキャンプ パーク