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

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

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

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

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

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

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

    f:id:koogucc11:20140323161442p:plain

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

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

    f:id:koogucc11:20140322101557p:plain

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

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

    ALTER DATABASE AdventureWorks2012 SET AUTO_UPDATE_STATISTICS_ASYNC ON

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

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

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

    ALTER DATABASE AdventureWorks2012 SET AUTO_CREATE_STATISTICS OFF
  • 統計情報を作成する。
     

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

    f:id:koogucc11:20140322102717p:plain

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

    f:id:koogucc11:20140322102735p:plain

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

     不足する統計情報がないように、出荷前の環境で必要な統計情報を抽出するといったフローを作成すべきかと思います。開発環境から出荷前の環境に移す統計情報をチェックするのに必要な下記の 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]')

    f:id:koogucc11:20140322113141p:plain

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

    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]')

    f:id:koogucc11:20140322150128p:plain

     ちょっと見やすくなりましたね。これらの情報を使用して、統計情報をチェックし、運用系などに適用していきます。統計情報を更新するには、下記のような 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

Microsoft SQL Server 2014 Query Tuning & Optimization