統計情報更新の動作を SQL Server Profiler を使って観察してみる

　統計情報の更新についての動作を SQL Server Profiler を使って観察してみましょう。今回は下記の条件下で観察します。

　データベースは、AdventureWorks、テーブルは Production.WorkOrder 統計は PK_WorkOrder_WorkOrderID を使用します。まず下記のクエリを SQL Server Management Studio で実行し、統計情報を更新しましょう。

USE AdventureWorks;  
GO  
UPDATE STATISTICS Production.WorkOrder PK_WorkOrder_WorkOrderID WITH FULLSCAN;  
GO

　統計情報更新を促すため、下記のクエリを SQL Server Management Studio で実行しましょう。(多めに更新してます。)

USE AdventureWorks
INSERT INTO
    Production.WorkOrder(ProductID, OrderQty, ScrappedQty, StartDate, EndDate, DueDate, ScrapReasonID, ModifiedDate)
    SELECT
        TOP 50000 ProductID,
        OrderQty,
        ScrappedQty,
        StartDate,
        EndDate,
        DueDate,
        ScrapReasonID,
        ModifiedDate
    FROM
        Production.WorkOrder

　SQL Server Profiler を起動して、トレースを取得します。

　下記のクエリを SQL Server Management Studio で実行します。

SELECT
    ProductID,
    OrderQty,
    ScrappedQty,
    StartDate,
    EndDate,
    DueDate,
    ScrapReasonID,
    ModifiedDate
FROM
    Production.WorkOrder
WHERE
    WorkOrderID BETWEEN 95000 AND 100000

　ここで SQL Server Profiler を参照してみましょう。何やら赤枠部分に出力されていますね。

SELECT
    StatMan([SC0])
FROM
    (
        SELECT
            TOP 100 PERCENT [WorkOrderID] AS [SC0]
        FROM
            [Production].[WorkOrder] WITH(READUNCOMMITTED)
        ORDER BY
            [SC0]
    ) AS _MS_UPDSTATS_TBL OPTION(MAXDOP 2)

　統計情報を生成するために実テーブルにアクセスしているクエリです。プログラムが発行するクエリの直前に発行されます。

　MAXDOP はサーバ設定値に従います。サーバの MAXDOP を 0 のままにしておくと、MAXDOP 16 でクエリが発行されます。(なぜ 16 なのかは不明です。ハードコーディングだったりww) しかし、これだとテーブルの件数が多いものは自動更新は使用できませんね。統計情報更新の考え方としては下記記事の内容で問題ないんですね。
ryuchan.hatenablog.com

　統計情報の更新はシステム規模に合わせてきちんと設計する必要がありますね。

　高すぎー。128,000 円！！

Lenovo 2in1 タブレット YOGA BOOK ZA150019JP /Windows 10/Office Mobile搭載/4GB/64GB/10.1インチ

統計情報を更新すると、『 クエリはリコンパイルされるよね普通 』と思いつつ、なんかうまくいってない感じがすごくするので、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

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

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%'

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

exec sp_recompile N'Sales.SalesOrderHeader'

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

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%'

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

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

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

※キャンプ道具♪これ、なんだ！？

KF047 レジャーハット　折りたたみ傘帽子 かぶる傘　両手が自由　釣りの際の日差しカット　屋外イベント　スポーツ　観戦キャンプ　屋外作業 つり用傘　すげ笠 釣り帽子 (シルバー)

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

SQL Server 2014 の新しい基数推定について自分で一から勉強するには少し大変なので、2014年4月に公開されたホワイトペーパー である 『 Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator 』 に頼りつつ勉強してみた

　SQL Server 2014 で基数推定が新しくなりました。2014 がリリースされてから、一か月ほど経ちますが、まったく勉強していませんでした。ゴールデンウイーク中に時間が取れたので、『 Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator 』 のホワイトペーパー ( Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator.docx ) を参考にしつつ勉強してみました。※このホワイトペーパーは、新しい基数推定についてかなり参考になります。興味のある方は、是非じっくり読んでみてください。

　今回は、偉大なこのホワイトペーパーの力をお借りして ( 手抜きともいいますwww)、色々実験してみます。サンプルに使用するのは、いつもの 『 AdventureWorks2012 』データベースです。ホワイトペーパーの P12 くらいからがわかりやすいですかね。下記のクエリを実行してみましょう。( 互換性レベルをSQL Server 2012 とした場合です。 )

USE [master];
GO

ALTER DATABASE [AdventureWorks2012] SET COMPATIBILITY_LEVEL = 110;
GO

USE [AdventureWorks2012];
GO

SET STATISTICS XML ON;

-- Legacy
SELECT	[AddressID],
	[AddressLine1],
	[AddressLine2]
FROM Person.[Address]
WHERE [StateProvinceID] = 9 AND
	[City] = 'Burbank';

-- New CE
SELECT	[AddressID],
	[AddressLine1],
	[AddressLine2]
FROM Person.[Address]
WHERE [StateProvinceID] = 9 AND
	[City] = 'Burbank'
OPTION (QUERYTRACEON 2312);
GO

SET STATISTICS XML OFF;

　実行プラン上だけでは、何の変化もありません。推定行数の差を見ることで違いが判断できます。新しい基数推定のほうが、実際の行数に近い値になっており、より正確に推定していることが判断できます。

※SQL Server 2012 以前

※SQL Server 2014

　ただし、SQL Server 2014 の基数推定が万能ではないので、現行システムで動作しているクエリはすべてテストする必要があります。下記のような感じですね。

	No change in performance	Improved Performance	Degraded Performance
No changes to estimates or the query execution plan	No action necessary	No action necessary	Not related to the new CE, but general performance tuning may be required
No changes to estimates but the query execution plan changed	No action necessary	No action necessary	Unlikely to be related to the new CE, but general performance tuning may be required
Changes to the estimates but not the query execution plan shape	No action necessary	No action necessary	Action related to the new CE may be necessary if the degradation exceeds workload performance service level agreements
Changes to the estimates and the query execution plan shape	No action necessary	No action necessary	Action related to the new CE may be necessary if the degradation exceeds workload performance service level agreements

　基数推定の何が SQL Server 2014 で変更されたのかは、P19 ページ以降に記載があります。

In this section, we discuss the primary changes made to the CE component for SQL Server 2014. The legacy CE’s default behavior has remained relatively static since SQL Server 7.0. The SQL Server 2014 changes address performance issues for key scenarios based on benchmark and customer workload testing. The new CE design also lays the groundwork for future improvements.  

While changes to the CE can improve overall workload performance, on average, over a range of applications, some queries may still regress. If this occurs, see the troubleshooting method section of this paper for potential solutions.

 　そうなんですね。Legacy な Cardinality Estimator は 7.0 から大幅に変化はないんですね。新しくなっても、まだまだ課題はあるようです。きっちり検証する必要がありますね。

　次に、SQL Server 2014 における行数の推定方法を見てみましょう。下記のクエリを実行してみます。

USE [AdventureWorks2012];
GO

SELECT	[AddressID],
	[AddressLine1],
	[AddressLine2]
FROM Person.[Address]
WHERE [StateProvinceID] = 9 AND
      [City] = N'Burbank' AND
      [PostalCode] = N'91502'
OPTION (QUERYTRACEON 9481); -- CardinalityEstimationModelVersion 70
GO

　実際の行数が 194 行、推定行数が 1 行となっています。かなりの差がありますね。

　この原因は、レガシーな基数推定にあります。レガシーな推定行数の公式は下記の通りです。Pnは密度を表し、P0は一番選択性の高いものになります。

推定行数 = 総行数 / ( 1 / ( P0 * P1 * P2 ... * Pn ) )

　それぞれの列密度は下記の通りです。密度はそれぞれの列統計を作成することで判断できます。

• StateProvinceID：0.009891
• City：0.009993
• PostalCode：0.232691

　上記の値から推定行数を計算すると、

19614 / ( 1 / ( 0.009891 * 0.009993 * 0.232691 ) ) = 0.451109368

となります。これにより、従来の SQL Server は選択性が非常に高くなってしまい、推定行数を 1 と判断してしまいます。これが、新しい基数推定になると、下記の通り結合選択性の高い算出方法に変更されます。P0 が選択性が一番高く、P1、P2、Pn段々と選択性が低くなっていきます。

推定行数 = 総行数 / ( 1 / ( P0 * P1^(1/2) * P2^(1/4) ) ... Pn^(1/2^n) ) )

　上記の公式に当てはめて計算を行うと、

19614 / ( 1 / ( 0.009891 * 0.009993 ^ ( 1/2 ) * 0.232691 ^ ( 1/4 ) ) ) = 13.46942986

 となります。実際に実行した結果は下図の通りです。

　そのほかに、Join、Distinct のアルゴリズムも変更されています。拡張イベントと連携した診断機能なども新しく追加されています。基数推定周りは色々とおもしろそうなので、今後も取り上げていきたいと思います。

※今回の記事では統計情報の見方・考え方はかなり省略した説明になっています。このあたりを理解するには、

Statistics - 都内で働くSEの技術的なひとりごと を参照するとより理解が深まります。( 深まるはずですww )

 ※統計まわりこれで勉強するとよいです。私も Kindle 版もってますー。

完全独習 統計学入門

※これ、本当にほしい。

Microsoft SQL Server 2014 Query Tuning & Optimization

SQL Server の統計情報作成について書いてみた、ちょっとだけマジメにまとめてみた

　今まで統計情報の設定をまじめにしてなかったこともあり、今回は少しだけ、ちょっとだけ、ほんのちょっとだけ、マジメにまとめてみます。個人的な考えもあるので、ん？違うんじゃ？いけてないんじゃ？と思っても、気にしないで読んでください。( その場合は、コメントくださいね。 ) 統計情報の設定を考える上で、少しは役に立つと思います。

　設定方法は、大きく 2 パターンあると思っています。それでは、まず一つ目のパターンです。規模の小さなシステム向けです。( ここでのサンプルはいつもの AdventureWorks データベースを使用しており、SQL Server は 2014 CTP2 です。)

• 統計情報の自動作成は『 ON 』にする。 

  　統計情報を作成するにあたり、初めに決定すべきものは、統計情報を新規作成するかです。データベースのプロパティ or sys.databases テーブルを参照することで、初期値を確認することが可能です。 統計情報の自動作成プロパティは下図の赤枠部分です。( SQL Server のデフォルトは、『 ON 』なので、このケースの場合、設定の必要はありません。 )

  

  　sys.databases は、 Management Studio 上で下記のクエリを実行することで確認可能です。

  select * from sys.databases where name = 'Adventureworks2012'

  

• 統計情報の作成は、exec sp_createstats を実行する。
  　すべてのテーブルレイアウト、インデックスの設定が完了したら、exec sp_createstats を実行します。
• 統計情報の非同期更新を True にします。
  　非同期更新を True にすることで、更新　クエリの負荷を軽減することが可能です。
  

  クエリで変更するには、下記のクエリを Management Studio で実行します。

  ALTER DATABASE AdventureWorks2012 SET AUTO_UPDATE_STATISTICS_ASYNC ON

　二つ目のパターンは、規模の大きいシステム向けかなと思っています。

• 統計情報の自動作成は『 OFF 』にする。
  　統計情報の自動作成を False にします。大規模システムの運用環境では、統計情報の新規作成をすべきではないと考えます。( 開発環境は、新規作成ができるようにしておいたほうがよいかと思います。それぞれの環境により、使い分けが必要です。) 運用環境でインデックスを使用しない SQL などを間違って発行されたりすると、SQL Server が自動的に列統計などを作成し、統計情報更新時に無駄な I/O の発生原因となってしまいます。( _Waな列統計です。) 
  

  クエリで変更するには、下記のクエリを Management Studio で実行します。

  ALTER DATABASE AdventureWorks2012 SET AUTO_CREATE_STATISTICS OFF

• 統計情報を作成する。
  　

  　統計情報の新規作成を OFF にすると、新たなインデックス追加時などにも統計情報が作成されなくなってしまいます。それを検出するため、Missing Column Index イベントを仕掛けておきましょう。SQL Server Profiler であれば、下図の設定を行います。

  

  　拡張イベントであれば、下図のイベントを設定します。

  

  　上図のイベントを設定することで、足りない統計情報を探すことができます。( 運用中のシステムで発生してしまうと少し悲しいですが.... )

  　不足する統計情報がないように、出荷前の環境で必要な統計情報を抽出するといったフローを作成すべきかと思います。開発環境から出荷前の環境に移す統計情報をチェックするのに必要な下記の 3 つのテーブルがあります。

  • sys.stats
    データベース内のテーブル、インデックス、およびインデックス付きビューに対して存在する統計オブジェクトごとに 1 行のデータを格納します。
  • sys.stats_columns
    sys.stats 統計の一部である列ごとに 1 行のデータを保持します。
  • sys.columns
    ビューやテーブルなど、列を持つオブジェクトの列ごとに 1 行のデータを返します。  以下に、列を持つオブジェクトの種類の一覧を示します。

  　実際に中身を確認するため、以下のクエリを Management Studio で実行しましょう。（ここでは、AdventureWorks の Production.Product テーブルを使用しています。）

  select * from sys.stats where object_id = object_id('[Production].[Product]')
  select * from sys.stats_columns  where object_id = object_id('[Production].[Product]')
  select * from sys.columns  where object_id = object_id('[Production].[Product]')

  

  　上図ではいまいち関連・一覧性がないので、クエリを少し改変しましょう。テーブル名、統計名とその列名を列挙します。

  select syst.name as [テーブル名],
         syst.object_id as [テーブルオブジェクトID],
  	syss.name as [統計名],
  	syss.stats_id as [統計ID],
  	sysi.name as [インデックス名], 
  	sysi.index_id as [インデックスID], 
  	sysc.name as [カラム名],
  	sysc.column_id as [カラムID] from sys.stats_columns syssc 
  	inner join sys.stats syss 
  	on syssc.object_id = syss.object_id AND syssc.stats_id = syss.stats_id
  		inner join sys.columns sysc
  		ON syssc.object_id = sysc.object_id AND syssc.column_id = sysc.column_id
  			inner join sys.index_columns sysic
  			ON syssc.column_id = sysic.column_id AND syssc.object_id = sysic.object_id
  				inner join sys.indexes sysi 
  				ON sysic.object_id = sysi.object_id AND sysic.index_id = sysi.index_id
   					inner join sys.tables syst
  					ON syssc.object_id = syst.object_id
  						where syss.auto_created = 0  
  							AND syssc.object_id = object_id('[Production].[Product]')

  

  　ちょっと見やすくなりましたね。これらの情報を使用して、統計情報をチェックし、運用系などに適用していきます。統計情報を更新するには、下記のような DDL 文を使用します。この DDL 文を実行することで、上図の一行目の統計情報を作成することができます。この DDL 文は、PK_Product_ProductID に対する統計をサンプリング数全件で、処理終了後には、統計情報の更新をしないというモードで統計情報を作成します。

  CREATE STATISTICS PK_Product_ProductID ON Production.Product(ProductID) WITH FULLSCAN, NORECOMPUTE

• 統計情報を更新する。
  SQL Agent 等を使い、下記の DDL 文 をジョブで実行するなど定期的な処理に加える必要があります。これにより、統計情報が最新に保たれるようになります。

  UPDATE STATISTICS Production.Product PK_Product_ProductID WITH FULLSCAN, NORECOMPUTE

  更新量にもよりますがトランザクション系データは、週に一回、更新頻度の低いマスタ系は、STATS_DATE 関数を使用して、統計情報の更新日をチェックし、古ければ更新するなどといった考慮も必要です。

  SELECT name AS stats_name, 
      STATS_DATE(object_id, stats_id) AS statistics_update_date
  FROM sys.stats 
  WHERE object_id = OBJECT_ID('Production.Product');

　ちょっとだけまじめにまとめてみました、書いてみました。サンプリング数とかも、適正値を見極めないといけません。( FULLSCAN だと I/O 負荷がすごいので。) このあたりも、今後、本気出して、頑張りますww

※こんな本の日本語版が出るといいのになぁ....

Microsoft SQL Server 2014 Query Tuning & Optimization

列統計ってそういうルールで作られるのねと思って書いてみた

　※ほとんど個人メモです。

　SQL Server の統計情報は初期設定で作成されるように設定されています。その場合、Where 句で指定されたものがインデックスでない場合は作成されるのは理解していたのですが、インデックス列であっても、インデックスの先頭列でない場合は作成されるんですね。

SQL Server の統計情報更新と MSSQLSERVER_833 について書いてみた

今日、関東地方は雪景色。

※DSC-QX10 で撮影しました。

大雪警報も発令されてますね。雪はまだまだやみそうにありません。お出かけの予定もすべてキャンセルになったので、ゆっくり記事を書こうかと思います。

 -----

　SQL Server で統計情報を更新するには、UPDATE STATISTICS 使用します。UPDATE STATISTICS table_name WITH FULLSCAN で実行すると、テーブルの統計情報更新が 1 トランザクションで処理されます。

　統計情報を更新に行われるデータの並び替え操作はワークスペース領域で収まらない場合、tempdb で処理されます。非常に容量が大きいテーブルで、統計情報が多く存在するテーブルをこのような更新方法を使用すると、tempdb の I/O が増加し、肥大化の原因になってしまいます。ある環境では、下記のようなログが出力され、tempdb が 50G まで拡張していました。E:\XXX\XXX\XXX.ndf の I/O に15秒以上 時間がかかっているという内容です。

2014-XX-XX XX:XX:XX.XX spid5s      SQL Server has encountered 7620 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [E:\XXX\XXX\XXX.ndf] in database [tempdb] (2).  The OS file handle is 0x000000000000075C.  The offset of the latest long I/O is: 0x000000990e0000

　SQL Server は、I/Oの要求が15 秒以上かかると、メッセージ 833 をイベント ログに記録し、SQL Server のエラーログに上記のようなメッセージを記録します。

　今回のような事象では、UPDATE STATISTICS table_name index_name or statistics_name と指定します。これにより、1トランザクションで処理されるデータ量を統計情報単位にまで削減でき、I/Oを低下させることが可能です。大事なことなので、覚えておきましょう。