sqlserver 統計情報更新 ロック 7

SQL Serverのロックを理解するための3つのポイント . インデックス再構築時には統計情報の更新も行われるので、そのタイミングで統計情報の更新を行う必要はありません。, [Windows]Error code: 0xc000000eの直し方(Windows10), https://docs.microsoft.com/ja-jp/sql/relational-databases/system-stored-procedures/sp-updatestats-transact-sql?view=sql-server-2017, https://blogs.msdn.microsoft.com/jpsql/2013/09/03/sql-server-5/, [Visual Studio]Visual Studioサブスクリプションのライセンスは運用環境では利用できない, [SQLServer]Windowsのコンピュータ名変更をSQL Serverに反映する, [Office]Onenote for Windows10はローカルディスクにノートを保存できない, [SQLServer]SQL Serverでnot null 制約を付けたり外したりするには, [Windows]スリープ状態の解除元: タイマー - generic でスリープが解除される, [windows]iastordatasvcがCPUを食っていたらインテルのドライバを更新する, [Web]InternetExplorerで「現在のセキュリティ設定では、このファイルをダウンロードできません」が表示される. http://azwoo.hatenablog.com/entry/2013/02/14/125848, 商用環境では想定外の動きになるのを防止するため自動更新機能は利用せず自前で統計情報更新の処理を実装する事を検討。 以下を参考にSQLServerのチューニングを実施したが、その際の気づきを残しておく。 blogs.msdn.microsoft.com インデックス再構築によって更新される統計は、更新対象インデックスの統計であって、それ以外の統計 (更新対象ではないインデックスや列の統計) は更新されません。 イメージ図は以下の通り。, トランザクションを張っている期間は必要最小限に留め、可能な限り短くする。（コード量の観点からも、実行時間の観点からも） オプティマイザの種類 (CBO、RBO) - オラクル・Oracleをマスターするための基本と仕組み http://www.shift-the-oracle.com/inside/optimizer.html

https://docs.microsoft.com/en-us/archive/blogs/jpsql/on-12, https://fyts.hatenadiary.org/entry/20081112/async, 1 = AUTO_UPDATE_STATISTICS_ASYNC は ON です。, you can read useful information later efficiently. こちらは効果的ですが、毎回コンパイルの分だけ実行時間とCPU使用時間が増大してしまい、ユーザーおよびサーバーにとってマイナスの側面もあります。そのためできる限り使用は避けるべきと考えています。, その他の対策としてはOPTIMIZE FOR UNKNOWNというクエリヒントを使用する方法もあります。 →これが「ブロッキング」, 逆も同様。既にX Lockをかけていた場合は、S Lockはかけられない。S Lockをかけるためにクエリが待ち状態になる。, with(nolock)は、正確にはロックをかけないわけではなく、Sロックの代わりにSch-Sロック(スキーマ安定度ロック：Schema Stability Lock)という弱いロックをかけている。, ここはとても大事なところなので、Sch-SロックとSロックの図を並べて見比べてみる。, ポイント①：with(nolock)無しのSELECT文を長時間実行すると、取得したSロックによって更新処理(X Lock）をブロックしてしまう恐れがある ポイント：ロックの種類が異なるだけで、クエリを発行すると実は何らかのロックが必ず獲得されている。 ②プロセスBがテーブルBのKEYロックを取得 この挙動を全テーブルに対する統計情報の更新処理で実現するために、自分でクエリを作成しました。, 仮に統計情報をサンプリングレート100％でフルスキャンし、かつ定期的な更新で最新の状態に保ち続けたとしても、クエリが突然遅くなる可能性はまだあります。, ストアドプロシージャやパラメータ化クエリの場合、SQL Serverはコンパイル時に渡されたパラメータを考慮して、最適な実行プランを生成します（パラメータスニッフィングと呼ばれています）。 原因として、統計情報が古くなっていたことを疑い、該当クエリで使用しているテーブルの統計情報を更新してみました。, ※今回は統計情報の更新後にクエリがリコンパイルされることを期待し、期待通りリコンパイルされました。ただし、統計情報の更新＝必ずリコンパイル、というわけでもないようです。 本記事がトラブルシューティングの実例として参考になれば幸いです。, スタートトゥディテクノロジーズでは、一緒にサービスを作り上げてくれるエンジニアを大募集中しています。, vasilyjpさんは、はてなブログを使っています。あなたもはてなブログをはじめてみませんか？, Powered by Hatena Blog こんにちは。zozoバックエンド部の廣瀬です。 弊社のサービスではDBMSとしてMicrosoft社のSQL Serverを使用している箇所があります。 本記事では、過去に経験したSQL Server関連のトラブル及びその調査内容をご紹介し、最後にトラブルシューティングを通して策定した統計情報の更新に関する方針 … ・「ブロッキング」は、blockerのクエリが終了しない限りwaiterのクエリは無限に待たされる。一方で、「デッドロック」は、SQL Serverが数秒間隔で自動検出して自動解消してくれる。 文書番号：20529. 出典：https://docs.microsoft.com/ja-jp/previous-versions/sql/sql-server-2008-r2/ms175519%28v%3dsql.105%29, 「同一のロックリソースに対して、同時に旗を立てることができるかどうか」とイメージすると分かりやすい。, 互換性があるため、同時に2つ以上のS Lockをかけることができる 出典：https://docs.microsoft.com/ja-jp/sql/2014-toc/sql-server-transaction-locking-and-row-versioning-guide?view=sql-server-2014, SQL Serverのデータ構造と対応しており、階層構造になっている。 HOME >> Tips >> Microsoft SQL Server. ブログを報告する, ALTER INDEX において対象のインデックスを個別に指定せずALLとし再構築しても同様である。. | そのため、ボトルネックを改善するためのチューニング方法ではなく、なぜ突然CPU高負荷な実行プランが生成されてしまったかを考える必要があります。 ALTER DATABASE データベース名 SET AUTO_UPDATE_STATISTICS_ASYNC ON|OFF, http://azwoo.hatenablog.com/entry/2013/02/14/125848, https://blog.engineer-memo.com/2012/04/19/%E7%B5%B1%E8%A8%88%E6%83%85%E5%A0%B1%E3%81%AE%E6%9B%B4%E6%96%B0%E7%8A%B6%E6%B3%81%E3%81%AE%E7%A2%BA%E8%AA%8D/, http://wiki.examind.net/index.php?SQL%20Server/%E7%B5%B1%E8%A8%88%E6%83%85%E5%A0%B1. ポイント：最低限覚えておくと良いのは、「SELECTはS、INSERT/UPDATE/DELETEはX、with(nolock)つきのSELECTはSch-S」

Uロック / Xロック：クエリの開始からトランザクションのコミット又はロールバックが完了するまでロックを保持する。 インテントロックを用いると以下のように説明できる。

※実行にはVIEW SERVER STATE権限が必要です。, 実行結果の一部を抜粋します。クエリの内容は伏せさせていただきますが、同一のクエリが多数実行中で、かつ最長で20秒間も実行中の状態でした。, また、lastwaittypeカラムの多くがCPU高負荷の際に発生することが多いSOS_SCHEDULER_YIELDとなっており、突然のCPU使用率高騰との関連性が考えられます。, このクエリの平均のCPU使用時間を確認するため、さらに別のDMVを使ったクエリを実行します。, 平均のCPU使用時間が約5秒と非常に長いです。 そのためSSMS (SQL Server Management Studio)上で[実際の実行プランを含める]にチェックをつけた状態で該当クエリを実行しました。 公式ドキュメントだと文字だけの情報なので、図解することで分かりやすく理解してもらえるように説明してみました。 このクエリの1分当たりのクエリ実行回数を計算してみたところ、他のクエリと比べて実行頻度が高いようです。

https://docs.microsoft.com/en-us/archive/blogs/jpsql/on-12, デフォルトでは統計情報の自動更新はInsert/Update文の一部として実行されるが、"AUTO_UPDATE_STATISTICS_ASYNC"をONにする事により非同期に実行する事ができる。 そのため、原因を特定するための調査を実施しました。, 同一ホスト上で稼働している主要なプロセスはSQL Serverしか無かったため、SQL Server上でCPUリソースを多く消費するようなクエリが実行されていることを疑いました。, 現在実行中のクエリのステータスを確認するため、動的管理ビュー（Dynamic Management Viewの略。以下DMVと呼ぶ）を使用したクエリを実行します。 (background / running / runnable / sleeping / suspended), -- ,datediff(s, der.start_time, GETDATE()) / 60.0 as time_min, -- ,(select top (1) waitresource from master.dbo.sysprocesses where spid = der.session_id) as waitresource, -- ,(select top (1) lastwaittype from master.dbo.sysprocesses where spid = der.session_id) as lastwaittype, --JOIN sys.dm_exec_connections dec ON der.connection_id = dec.connection_id, --OUTER APPLY sys.dm_exec_query_plan(plan_handle) AS deqp, --AND dest.text like '%%' -- クエリの中身でlike検索したいときはここを編集, https://docs.microsoft.com/ja-jp/sql/2014-toc/sql-server-transaction-locking-and-row-versioning-guide?view=sql-server-2014, https://docs.microsoft.com/ja-jp/previous-versions/sql/sql-server-2008-r2/ms175519%28v%3dsql.105%29, https://docs.microsoft.com/ja-jp/previous-versions/sql/sql-server-2008-r2/ms186396(v=sql.105), https://raw.githubusercontent.com/MasayukiOzawa/SQLServer-Util/master/Lock/%E3%83%96%E3%83%AD%E3%83%83%E3%82%AD%E3%83%B3%E3%82%B0%E3%83%81%E3%82%A7%E3%83%BC%E3%83%B3%E3%81%AE%E5%8F%96%E5%BE%97.sql, you can read useful information later efficiently. 一方、テーブルにIXロックがあると、それだけでテーブルにXロックをかけられないことが分かるため、ロックの可否の判断効率が良い。, 先ほどの「上位の階層に互換性の無いロックがかかっていると、下位のリソースに対してロックをかけられない」というルールは、 弊社のサービスではDBMSとしてMicrosoft社のSQL Serverを使用している箇所があります。 出典：https://docs.microsoft.com/ja-jp/previous-versions/sql/sql-server-2008-r2/ms186396(v=sql.105), 上位の階層に互換性の無いロックがかかっていると、下位のリソースに対してロックをかけられない, レコードにXロックをかける場合、その上位階層であるPAGEとTABLEに対して、IXロック（インテントXロック）が自動でかけられる。 ただしサンプル数を指定する際、全テーブル一律でフルスキャンまたは一定のサンプリングレートしか指定できないようです。, update statisticsを実行する際、サンプリングレート未指定の場合はテーブルのレコード数によって動的にサンプリングレートが決定されます。

もしIXロックがなければ、例えば他のクエリがテーブル全体にXロックをかけたいときに、全ページと全レコードに互換性の無いロックがかかっていないか調べる必要がある。 https://docs.microsoft.com/en-us/archive/blogs/jpsql/on-12 | Help us understand the problem. ポイント：ブロッキングとデッドロックの違い クエリに登場するテーブルは開発時に使用した経験があり、各テーブルのレコード更新の性質についてたまたま熟知していました。, 対応策として実施した1日1回の定期的な統計情報の更新は、テーブルAのような性質を持ったテーブルには効果的でしたが、テーブルBに対しては効果が薄いようです。, よく「統計情報が古い」から実行プランがおかしくなったという表現を使いますが、これは決して統計情報の最終更新日から時間が経過している、という意味では無いと思い知らされました。 自動統計の増分オプションの既定の設定を示します。 0 = 自動作成の統計は非増分です。 1 = 可能な場合は、自動作成の統計情報は増分されます。 適用対象: SQL Server 2014 (12.x) 以降。 is_auto_update_stats_on: bit: 1 = AUTO_UPDATE_STATISTICS は ON です。 … ・「ブロッキング」は、SQL Serverの介入が無いためKILLしない限りblockerもwaiterも最終的には実行完了する。一方で、「デッドロック」は、クエリ実行中であっても片方のプロセスがSQL Serverによって強制終了される。, Microsoft MVPの小澤さんのgithubで公開されているクエリが素晴らしく便利。（そのまま実行してOK） 統計情報を更新してクエリのパフォーマンスを改善する . https://fyts.hatenadiary.org/entry/20081112/async, ALTER DATABASE データベース名 SET AUTO_CREATE_STATISTICS ON|OFF それを回避するためにトレースフラグ2371をオンにすることで動的閾値を利用した統計更新を実現できる。 このスキーマ更新ロック(Sch-M)はテーブルの削除や変更時だけでなく、SQLServerが内部で統計情報を更新するときにも発生します。 スキーマ ロック SQL Server は 2 種類のスキーマ ロックを使用しており、SQL Server がテーブルの統計情報を更新するときにその両方が使用されます。 SQL Server の統計保 … 例えば、「1つのレコードを一度に更新できるのは、1つのクエリだけ」といったルールを実現してくれる。, １．ロックには複数の粒度（階層とも呼ばれる）が存在する SQL で統計情報を更新する方法です。 クエリのパフォーマンスが低下した場合などのときには統計情報を更新することで改善することがあります。 UPDATE STATISTICS もしくは sp_updatestats ストアドプロシージャを使用して更新することができます。 1．ロックには複数の粒度（階層とも呼ばれる）が存在する 2．ロックには複数の種類が存在する（ロックモード） 3．各ロックモード間には「互換性」という関係性がある. したがってこのクエリによってCPU高負荷となった可能性が非常に高いと判断しました。 HOME >> Tips >> Microsoft SQL Server. ※with(nolock) / READ UNCOMMITTEDトランザクション分離レベルによって影響を受けるのはSELECT文のみ。UPDATE/INSERT/DELETE等は挙動が変わらない。, どんなロックとも競合する最強のロックである、Sch-Mロック(スキーマ修正ロック：Schema Modification Lock)がかかっている状態だと、with(nolock)をつけたSELECT文でも互換性が無いためブロックされる。, update文を実行すると、レコードに対してUロックとXロックが取得される。おそらくUロックを獲得した後にXロックへと昇格させていると思われる。, 全ての互換性を示した図は以下の通り。↑の互換性の加えて、Sch-S / Sch-Mロックのみ押さえておけばとりあえずはOK。 例：要件的に許されるのであれば、SELECT文をトランザクションの外に出すことで、TableBのロック保持期間を短縮できる。, 例えばテーブルの全レコードを更新する際、1レコードずつにXロックをかけるより、テーブルに1つだけXロックをかけてしまったほうが効率が良い。※1ロックあたり、粒度に関係なくメモリを96Bytes消費するためメモリリソースの観点からも効率が良い。, https://docs.microsoft.com/ja-jp/sql/database-engine/configure-windows/configure-the-locks-server-configuration-option?view=sql-server-2017, このように、大量のPAGEやKEYにロックをかける場合に、SQL Serverが自動的にロックの粒度をTABLEに昇格する場合がある。 By following users and tags, you can catch up information on technical fields that you are interested in as a whole, By "stocking" the articles you like, you can search right away. 「TABLEに対してXロックを獲得している場合は、TABLEへのIXロックが互換性が無いためブロッキングされる」, →ある程度なら意図的に粒度をいじることもできるが、基本的にはSQL ServerにまかせておけばOK。, ロックを保持する期間は、「明示的にトランザクションを開始しているかどうか」で変わる。, Sロック：クエリ実行直後にロックを開放。（既定のトランザクション分離レベルである「Read Committed」の場合の挙動） ポイント：ロックにはいろいろな種類がある。発行するクエリによって、SQL Serverが自動的に必要なロックをかけてくれる。（各ロックの違いは後述） クエリレベルだと、「次の二つのクエリは同時に実行できる」という意味。, 互換性が無いため、同時にかけられるロックは1つだけ。 それぞれについて完全に覚えておく必要は無いけど、「XロックとSロックに互換性がないから、UPDATEの実行中は該当レコードへのSELECTはブロックされるのだろうな。データの大量更新を実行するタイミングはブロッキングが起きていないかDMVを使ってチェックしよう」 こちらはクエリのコンパイルおよび最適化の際に、その時渡されたパラメータ値ではなく、統計データを使用するよう指定するヒントです。, 本記事では、実際に経験したSQL Serverに関するトラブルから学んだ統計情報の更新に関する方針について紹介しました。, 本記事に出てくる技術的な内容や調査用のクエリはほとんどWeb上で既出の内容かと思います。 -- Status of the request. 以下のようにKEY→PAGE→HoBT(Heap or BTree=Index)→TABLEの順番。KEYが最下層、TABLEが最上位。, ※HoBT：Heap or B-Treeの略。 補足.

以下を参考にSQLServerのチューニングを実施したが、その際の気づきを残しておく。, インデックス再構築によって更新される統計は、更新対象インデックスの統計であって、それ以外の統計 (更新対象ではないインデックスや列の統計) は更新されません。, インデックスを作成した列に対しては統計情報が一つ自動で作成されるが、それ以外の列にも自動生成されることがある*1。AUTO_CREATE_STATISTICSがONのデータベースでは、以下の通り統計情報の自動生成が行われうる*2：, 例えば、WHERE B=xxx という検索を行なったときに、B 列にインデックスが 作成されていない場合は、_WA_Sys_B_XXX という統計が作成されます。 この統計は、使われなくなった場合には、自動的に削除されるのでほっておいても 問題ありません。明示的に削除したい場合は DROP STATISTICS を使います。, すなわち、一つのヒープに対して複数のインデックスが存在でき、それぞれに統計情報が一つ対応して存在するが、それ以外にも自動生成された統計情報が存在しうる。インデックス再構築の際にサンプル率100%で更新される統計情報は前者のみで、後者は対象外となる*4。, 余談だが、統計情報の更新をサンプル率100%で定期的に実施するのであれば、その更新頻度次第では、既定のサンプリングレートで更新されてしまう自動更新による統計情報の劣化を防ぐため、AUTO_UPDATE_STATISTICS オプションをオフにしておくのを忘れないように気をつけたい。, SQL Server の統計情報作成について書いてみた、ちょっとだけマジメにまとめてみた - 都内で働くSEの技術的なひとりごと, 「【統計情報】_WA_SYS_XXXについて」（1） Database Expert − ＠IT, *4:ALTER INDEX において対象のインデックスを個別に指定せずALLとし再構築しても同様である。, souegg2さんは、はてなブログを使っています。あなたもはてなブログをはじめてみませんか？, Powered by Hatena Blog SQL Serverではデフォルトで自動統計情報更新が有効で、色々条件はあるがざっくり20%以上のデータ変動があったら自動で更新される。 統計情報を手動で取得しようと思ったら、ORA-20005エラーになってしまった。 どうやら、統計情報にロックがかかっているらしい。 解除方法は以下コマンドである。 統計情報ロック exec dbms_stats.lock_table_stats('スキーマ名', 'テーブル名'); 統計情報ロック解除 exec d… 「統計情報の更新」とパフォーマンス遅延の関係 統計情報はテーブルのデータ分布の状況を示します。では、アプリケーションがデータを更新し ※EXTENT：物理的に連続した8ページをひとまとめにしてエクステントと呼ぶ。ページの効率的な管理のために使用される。, 最も粒度の大きいロックリソース。クエリを実行すると必ず該当DBにSロックをかける。 MSのドキュメントには、ロックリソースの種類として以下の図が掲載されている。見方にポイントがあるので解説。 下記コマンドで対象のDBにある全テーブルの統計情報を更新します。 exec sp_updatestats 実行中はこんな感じのメッセージが表示されます。 実行中の様子. |

これまで普通に使えていた SQL Server が急に遅くなった、夜間の更新処理に異常に時間がかかるようになった。といったとき、統計情報が古くなっていて実行プランが正しく選 …