SQL Server/統計情報 の変更点

http://wiki.examind.net/index.php?SQL%20Server/%E7%B5%B1%E8%A8%88%E6%83%85%E5%A0%B1

[ トップ ]   [ 編集 | 差分 | バックアップ | 添付 | リロード ]   [ 新規 | 一覧 | 単語検索 | 最終更新 | ヘルプ | ログイン ]

• 追加された行はこの色です。
• 削除された行はこの色です。
• SQL Server/統計情報 へ行く。
• SQL Server/統計情報 の差分を削除

---

#author("2021-01-01T08:24:15+00:00","default:admin","admin")
#author("2021-01-01T08:34:36+00:00","default:admin","admin")
-[[統計:https://docs.microsoft.com/ja-jp/sql/relational-databases/statistics/statistics?view=sql-server-2017]]

-[[Does Updating Statistics Cause a Recompile if No Data Has Changed?:https://www.brentozar.com/archive/2015/01/updating-statistics-cause-recompile-no-data-changed/]]

-[[sys.dm_exec_query_stats (Transact-SQL):https://docs.microsoft.com/ja-jp/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-query-stats-transact-sql?view=sql-server-2017]]
-[[sp_autostats (TRANSACT-SQL):https://docs.microsoft.com/ja-jp/sql/relational-databases/system-stored-procedures/sp-autostats-transact-sql?view=sql-server-2017]]

-[[【統計情報】_WA_SYS_XXXについて:https://www.atmarkit.co.jp/bbs/phpBB/viewtopic.php?topic=20339&forum=26]]

-[[統計情報の自動更新に関する考察:https://blog.engineer-memo.com/2012/04/28/%E7%B5%B1%E8%A8%88%E6%83%85%E5%A0%B1%E3%81%AE%E8%87%AA%E5%8B%95%E6%9B%B4%E6%96%B0%E3%81%AB%E9%96%A2%E3%81%99%E3%82%8B%E8%80%83%E5%AF%9F/]]
-[[統計情報のサンプリングの実行プラン:https://blog.engineer-memo.com/2014/11/15/%E7%B5%B1%E8%A8%88%E6%83%85%E5%A0%B1%E3%81%AE%E3%82%B5%E3%83%B3%E3%83%97%E3%83%AA%E3%83%B3%E3%82%B0%E3%81%AE%E5%AE%9F%E8%A1%8C%E3%83%97%E3%83%A9%E3%83%B3/]]

-[[統計情報の自動更新・手動更新の使い分け:https://techinfoofmicrosofttech.osscons.jp/index.php?SQL%20Server%20%E3%81%AE%E3%82%AA%E3%83%97%E3%83%86%E3%82%A3%E3%83%9E%E3%82%A4%E3%82%B6#b4304d99]]

-[[SQL Server 統計情報の自動更新:http://tech.kou.asia/?p=1672]]
-[[SQL Server 2008(2012)の統計自動更新について：トレースフラグ2371:http://azwoo.hatenablog.com/entry/2013/02/14/125848]]

*統計情報がらみのトラブル [#y77773d1]
-[[トラブルシューティングから学ぶSQL Server統計情報の更新タイミング:https://techblog.zozo.com/entry/sqlserver-troubleshooting-statistics]]
--[[SQL Database で VIEW SERVER STATE が必要なログインを複数作成する:https://blog.engineer-memo.com/2017/04/19/sql-database-%E3%81%A7-view-server-state-%E3%81%8C%E5%BF%85%E8%A6%81%E3%81%AA%E3%83%AD%E3%82%B0%E3%82%A4%E3%83%B3%E3%82%92%E8%A4%87%E6%95%B0%E4%BD%9C%E6%88%90%E3%81%99%E3%82%8B/]]

-[[どうする？ SQL Server のクエリ パフォーマンスが低下した！:https://blogs.msdn.microsoft.com/jpsql/2013/09/03/sql-server-5/]]

-[[「あるタイミングから」処理が遅くなり、再起動しても再現されてしまう（パフォーマンストラブル）:https://www.atmarkit.co.jp/ait/articles/1706/12/news002.html]]

**統計情報を更新しても改善されない場合は、一度再起動してみる [#j2a38907]
-[[SQL Serverサービス再起動に伴う処理について:https://social.msdn.microsoft.com/Forums/sqlserver/ja-JP/14a49041-853f-4f3d-ab25-f52ca8396ccc/sql-server?forum=sqlserverja]]

-SQL Serverが狂った統計情報を使い続けている可能性あり
-SQL Serverを再起動することで、更新された統計情報が使用されるようになって、大体改善される

**再起動しなくても、キャッシュをクリアすれば良い [#kba8394b]
***[[DBCC FREEPROCCACHE (Transact-SQL):https://docs.microsoft.com/ja-jp/sql/t-sql/database-console-commands/dbcc-freeproccache-transact-sql?view=sql-server-ver15]] [#gdb5548b]

 DBCC FREEPROCCACHE

***[[DBCC FREESYSTEMCACHE (Transact-SQL):https://docs.microsoft.com/ja-jp/sql/t-sql/database-console-commands/dbcc-freesystemcache-transact-sql?view=sql-server-ver15]] [#w34bd65f]

-DBCC FREESYSTEMCACHE を実行すると、SQL Server のインスタンスのプラン キャッシュが消去される
-プラン キャッシュが消去されると、後続のすべての実行プランが再コンパイルされ、場合によっては、クエリ パフォーマンスが一時的に急激に低下する
-プラン キャッシュ内のキャッシュストアがクリアされるたびに、"SQL Server は、'DBCC FREEPROCCACHE' 操作または 'DBCC FREESYSTEMCACHE' 操作により、'%s' キャッシュストア (プラン キャッシュの一部) のキャッシュストア フラッシュを %d 個検出しました。" という情報メッセージが SQL Server エラー ログに記録される
-このメッセージは、5 分以内にキャッシュがフラッシュされる限り、5 分間隔でログに記録される

 DBCC FREESYSTEMCACHE('ALL')

*統計情報の設定 [#ndf0a833]
-[[SQL Server の統計情報作成について書いてみた、ちょっとだけマジメにまとめてみた:http://ryuchan.hatenablog.com/entry/2014/03/23/184242]]

**小規模向け [#t2c8062f]
-統計情報の自動更新　：True
-統計情報の非同期更新：True

**大規模向け [#hdd76fef]
-統計情報の自動更新　：False

-統計情報を手動で作成
-統計情報を手動で更新

*統計情報の自動更新 [#z5e39e94]
-[[統計の更新:https://docs.microsoft.com/ja-jp/sql/relational-databases/statistics/update-statistics?view=sql-server-2016]]

-[[SQL Server 統計情報の自動更新:http://tech.kou.asia/?p=1672]]
-[[SQL Server 2008(2012)の統計自動更新について：トレースフラグ2371:http://azwoo.hatenablog.com/entry/2013/02/14/125848]]

-[[統計情報のサンプリングについて:https://blog.engineer-memo.com/2012/10/21/%E7%B5%B1%E8%A8%88%E6%83%85%E5%A0%B1%E3%81%AE%E3%82%B5%E3%83%B3%E3%83%97%E3%83%AA%E3%83%B3%E3%82%B0%E3%81%AB%E3%81%A4%E3%81%84%E3%81%A6/]]

**自動更新の条件 [#t2247d57]
-テーブル行数が0から非0に推移した
-前回統計情報が更新された際のテーブル行数が500以下で、統計情報収集対象列が501回以上更新された場合
-前回統計情報が更新された際のテーブル行数が501以上で、統計情報収集対象列が500+(テーブル行数の20%)回以上変更された
-一時テーブルの場合は、上述の基準に加え、テーブル行数が6行を超えた

*統計情報の非同期更新 [#e546fc24]
-[[SQL Server 統計の非同期的自動更新:http://d.hatena.ne.jp/fyts/20081112/async]]

-非同期自動更新のメリット
--オンライン系のシステム (OLTP) と相性がいい
--統計を同期的に更新しないため、タイムアウトが発生する確率が減る
-非同期自動更新のデメリット
--データウェアハウスなど大規模データをバッチで回すような場合に性能が劣化する
--シングルユーザモードの利用が出来なくなる

*統計情報自動更新OFFでのシステム運用 [#s74c9a6d]
-統計情報の自動更新がONの状態で、オンライン中に統計情報の更新が発生すると、性能的に問題が出ることがある。
-しかし、統計情報の自動更新をOFFにした場合、結局、統計情報が実データと乖離した際に問題が発生する。
-従って、統計情報更新のOFF運用は以下のようになると考える。
--サーバ・メンテナンス時間帯に統計情報の更新をONにして統計情報を更新する。
--STATS_DATE関数で、統計の最終更新日を確認できる。
--サーバ・メンテナンス時間帯に問題（乖離）を発見して手動更新する（難しい）。

--[[参考：【SQL Server】統計情報のヒストグラムと実行プランの予測行数 - 小物SEのメモ帳:http://memorandom-nishi.hatenablog.jp/entry/2017/02/18/021834]]
--注意：OFFだと、新たなインデックス追加時などにも統計情報が作成されなくなる。
--Missing Column Index イベントをトレースして統計情報のないIndexが通知を受け取ることができる。

*統計情報に関するSQL [#nef7ad0f]
-[[STATS_DATE (Transact-SQL):https://docs.microsoft.com/ja-jp/sql/t-sql/functions/stats-date-transact-sql?view=sql-server-2017]]
-[[sys.dm_db_stats_properties (Transact-SQL):https://docs.microsoft.com/ja-jp/sql/relational-databases/system-dynamic-management-views/sys-dm-db-stats-properties-transact-sql?view=sql-server-2017]]

**統計情報の確認 [#x5e6797a]
 DBCC SHOW_STATISTICS([テーブル名],[統計情報名])

-このコマンドを実行すると、ヘッダー情報、密度情報、ヒストグラムの各項目が出力される
-このコマンドを実行すると以下が出力される
--ヘッダー情報
---統計情報名
---統計情報の最終更新日
---テーブルの行数
---統計情報作成時に使用されたサンプル行数
--密度情報
--ヒストグラム
---RANGE_HI_KEY ... ステップの上限キー値
---RANGE_ROWS ... ステップ範囲内の行数。RANGE_HI_KEYは含まない
---EQ_ROWS ... RANGE_HI_KEYと全く同じ値の行数
---DISTINCT_RANGE_ROWS ... ステップ範囲内の一意なキー値の数。RANGE_HI_KEYは含まない
---AVG_RANGE_ROWS ... ステップ範囲内の一意な値ごとの平均行数

**最終更新日時・更新回数の確認 [#g6e9c80f]

 SELECT
     OBJECT_NAME(dsp.object_id) 
     , dsp.object_id 
     , ss.name 
     , ss.auto_created
     , ss.user_created
     , ss.no_recompute
     , dsp.last_updated 
     , dsp.rows
     , dsp.rows_sampled 
     , dsp.steps 
     , dsp.unfiltered_rows 
     , dsp.modification_counter 
 FROM
     sys.stats AS ss 
     CROSS APPLY 
     sys.dm_db_stats_properties(ss.object_id, ss.stats_id) AS dsp 
 WHERE
     OBJECT_SCHEMA_NAME(dsp.object_id, DB_ID()) <> 'sys'
 ORDER BY
     dsp.last_updated desc

**統計情報更新 [#kfa63047]
 EXEC sp_updatestats;

       

Site admin: anonymous

PukiWiki 1.5.1 © 2001-2016 PukiWiki Development Team. Powered by PHP 5.6.99-hhvm. HTML convert time: 0.003 sec.