SQL Server の統計情報作成について書いてみた、ちょっとだけマジメにまとめてみた - 都内で働くSEの技術的なひとりごと / Technical soliloquy of System Engineer working in Tokyo

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

　設定方法は、大きく 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');
```