+ CAST(@Table AS VARCHAR(500)) ', https://www.brentozar.com/archive/2015/01/updating-statistics-cause-recompile-no-data-changed/, PASS Summit 2019参加レポート:最新のSQL Server/SQL Databaseに…, Azure Automationを利用してSQL Databaseをオートスケールしコスト削減させた…, ユーザーの行動等によって自然に少しずつレコード数が変化していく性質をもったテーブルは、1日1回等、定期的に統計情報を更新, バッチ処理等で、大量にレコード削除/挿入/更新を行う場合は、処理完了直後に統計情報を更新. イメージ図は以下の通り。, トランザクションを張っている期間は必要最小限に留め、可能な限り短くする。(コード量の観点からも、実行時間の観点からも) 何故だろうとクエリと再度にらめっこしていたところ、気づいたことがありました。それはレコード更新の性質がテーブルによって異なるということです。 そのためたとえ1時間前に統計情報を更新したばかりでも、バッチ処理等で大量にデータ更新された後では、現在の統計情報は「古い」といえます。, 上記考察を踏まえて、定期的な統計情報の更新に加えて、バッチ処理等でレコード数に大きな変化がある場合は処理完了直後に統計情報の更新を実施するようにしました。 ※実行にはVIEW SERVER STATE権限が必要です。, 実行結果の一部を抜粋します。クエリの内容は伏せさせていただきますが、同一のクエリが多数実行中で、かつ最長で20秒間も実行中の状態でした。, また、lastwaittypeカラムの多くがCPU高負荷の際に発生することが多いSOS_SCHEDULER_YIELDとなっており、突然のCPU使用率高騰との関連性が考えられます。, このクエリの平均のCPU使用時間を確認するため、さらに別のDMVを使ったクエリを実行します。, 平均のCPU使用時間が約5秒と非常に長いです。 HOME >> Tips >> Microsoft SQL Server. 例:要件的に許されるのであれば、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に昇格する場合がある。 ③プロセスAがテーブルBのKEYロックを取得しようとするが、ブロッキングが発生。待ち状態になる。 以下を参考に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 したがってこのクエリによってCPU高負荷となった可能性が非常に高いと判断しました。

3.各ロックモード間には「互換性」という関係性がある, ロックはデータの整合性を保つために必要な仕組みだけど、ブロッキングの原因にもなりやすい。, そのため、クエリを実行する際に「どういったロックが、どの粒度でかけられるのか、そのロックの互換性はどうか」といったことを開発者が意識できるとブロッキングの発生を未然に防ぐ(または最小限に抑える)ことが可能となる。, ロックには粒度が存在する。ロックリソースとも呼ばれる。 自動統計の増分オプションの既定の設定を示します。 0 = 自動作成の統計は非増分です。 1 = 可能な場合は、自動作成の統計情報は増分されます。 適用対象: SQL Server 2014 (12.x) 以降。 is_auto_update_stats_on: bit: 1 = AUTO_UPDATE_STATISTICS は ON です。 …

このクエリの1分当たりのクエリ実行回数を計算してみたところ、他のクエリと比べて実行頻度が高いようです。 インデックス再構築時には統計情報の更新も行われるので、そのタイミングで統計情報の更新を行う必要はありません。, [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で「現在のセキュリティ設定では、このファイルをダウンロードできません」が表示される.

このスキーマ更新ロック(Sch-M)はテーブルの削除や変更時だけでなく、SQLServerが内部で統計情報を更新するときにも発生します。 スキーマ ロック SQL Server は 2 種類のスキーマ ロックを使用しており、SQL Server がテーブルの統計情報を更新するときにその両方が使用されます。 SQL Server の統計保 … 2016/7/2. 今回のトラブルでは、自動更新が走る前に統計情報が古くなってしまったことで実行プランが狂ったと判断しました。そのため恒久的な対応策として1日1回、定期的に全テーブルの統計情報を更新するジョブを作成しました。, 後日、またCPU高負荷な状態に陥ってしまいました。しかも犯人は同じクエリです。

②プロセスBがテーブルBのKEYロックを取得 正確には「統計情報が古い」とは、「統計が実際のデータ分布と大きく乖離している」状態を指します。 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. | 本記事がトラブルシューティングの実例として参考になれば幸いです。, スタートトゥディテクノロジーズでは、一緒にサービスを作り上げてくれるエンジニアを大募集中しています。, vasilyjpさんは、はてなブログを使っています。あなたもはてなブログをはじめてみませんか?, Powered by Hatena Blog 原因として、統計情報が古くなっていたことを疑い、該当クエリで使用しているテーブルの統計情報を更新してみました。, ※今回は統計情報の更新後にクエリがリコンパイルされることを期待し、期待通りリコンパイルされました。ただし、統計情報の更新=必ずリコンパイル、というわけでもないようです。

こちらは効果的ですが、毎回コンパイルの分だけ実行時間とCPU使用時間が増大してしまい、ユーザーおよびサーバーにとってマイナスの側面もあります。そのためできる限り使用は避けるべきと考えています。, その他の対策としてはOPTIMIZE FOR UNKNOWNというクエリヒントを使用する方法もあります。 この挙動をロックエスカレーションという。, ロックエスカレーションが起きると、該当テーブルへの更新がすべてブロックされてしまうため注意が必要。, ポイント:エスカレーション先のリソースはテーブルのみ。(KEY→PAGEといったエスカレーションは無い。必ずKEY→TABLEやPAGE→TABLEとなる。), ①プロセスAがテーブルAのKEYロックを取得 これまで普通に使えていた SQL Server が急に遅くなった、夜間の更新処理に異常に時間がかかるようになった。といったとき、統計情報が古くなっていて実行プランが正しく選 … 本記事では、過去に経験したSQL Server関連のトラブル及びその調査内容をご紹介し、最後にトラブルシューティングを通して策定した統計情報の更新に関する方針をまとめます。, ある日突然、SQL Serverが稼働しているサーバーのCPU使用率が高騰し、100%に張り付く状態が一定期間続きました。, CPU使用率のグラフを見ただけでは、なぜ突然高騰したのか原因は分かりません。 この時に渡されたパラメータがたまたま非典型パラメータの場合、それ以外の典型的なパラメータにとっては遅い実行プランになってしまう恐れがあります。, Microsoftのブログの中で、ストアドプロシージャであればwith recompileを指定するなど、実行時に強制的に毎回コンパイルさせることで非典型パラメータに関する問題を回避させる案が紹介されています。 https://docs.microsoft.com/en-us/archive/blogs/jpsql/on-12, デフォルトでは統計情報の自動更新はInsert/Update文の一部として実行されるが、"AUTO_UPDATE_STATISTICS_ASYNC"をONにする事により非同期に実行する事ができる。 例えば、「1つのレコードを一度に更新できるのは、1つのクエリだけ」といったルールを実現してくれる。, 1.ロックには複数の粒度(階層とも呼ばれる)が存在する Why not register and get more from Qiita? ※EXTENT:物理的に連続した8ページをひとまとめにしてエクステントと呼ぶ。ページの効率的な管理のために使用される。, 最も粒度の大きいロックリソース。クエリを実行すると必ず該当DBにSロックをかける。 以下を参考にSQLServerのチューニングを実施したが、その際の気づきを残しておく。 blogs.msdn.microsoft.com インデックス再構築によって更新される統計は、更新対象インデックスの統計であって、それ以外の統計 (更新対象ではないインデックスや列の統計) は更新されません。  →この時点で、相互にブロックし合う関係になってしまい、このままだとプロセスA、プロセスB共に無限に待ち続けることに。これがデッドロック状態。, ⑤数秒後、SQL Serverが自動でデッドロックを検出し、プロセスAまたはプロセスBのどちらかを強制終了し、デッドロックを解消。 https://docs.microsoft.com/ja-jp/sql/2014-toc/sql-server-transaction-locking-and-row-versioning-guide?view=sql-server-2014, MSのドキュメントから、よく出てくるロックの種類(ロックモード)を抜粋。 (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. 「統計情報の更新」とパフォーマンス遅延の関係 統計情報はテーブルのデータ分布の状況を示します。では、アプリケーションがデータを更新し というように今回の内容を業務で使用するクエリレベルの話に自分で変換して考えられるようになると、ブロッキングなどのトラブルの軽減につながると思います。, 株式会社ZOZOテクノロジーズ テックリード。Microsoft MVP for Data Platform (August 2020-) SQL ServerをメインにDBに関してつぶやきます。得意領域はチューニング/トラブルシューティング。SQL Server User Groupにて毎月登壇中。https://github.com/masaki-hirose. http://tech.kou.asia/?p=1672, この場合、件数が増えると20%に達するまでに時間がかかる。 わかり辛かったらすみません!, DB上でデータを操作(SELECT/INSERT/UPDATE/DELETE等)する際、データの整合性を保つために使われる排他制御の仕組み。 弊社のサービスではDBMSとしてMicrosoft社のSQL Serverを使用している箇所があります。 一方、テーブルにIXロックがあると、それだけでテーブルにXロックをかけられないことが分かるため、ロックの可否の判断効率が良い。, 先ほどの「上位の階層に互換性の無いロックがかかっていると、下位のリソースに対してロックをかけられない」というルールは、 Help us understand the problem.

SQL Serverではデフォルトで自動統計情報更新が有効で、色々条件はあるがざっくり20%以上のデータ変動があったら自動で更新される。 |

もしIXロックがなければ、例えば他のクエリがテーブル全体にXロックをかけたいときに、全ページと全レコードに互換性の無いロックがかかっていないか調べる必要がある。 公式ドキュメントだと文字だけの情報なので、図解することで分かりやすく理解してもらえるように説明してみました。 ポイント:最低限覚えておくと良いのは、「SELECTはS、INSERT/UPDATE/DELETEはX、with(nolock)つきのSELECTはSch-S」 ※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。 SQL で統計情報を更新する方法です。 クエリのパフォーマンスが低下した場合などのときには統計情報を更新することで改善することがあります。 UPDATE STATISTICS もしくは sp_updatestats ストアドプロシージャを使用して更新することができます。 Microsoft SQL Server Japan Support Team Blog さんのブログの 統計情報の自動更新が ON の時には統計情報を手動で更新する必要はない? を読んで、以前自分で書いた SQL Server 2008 R2 SP1 で追加された統計情報の自動更新に関してのトレースフラグ でリコンパイルをする必要があったことにつながる … 該当のクエリは、かなり前から実行されているクエリだったため、突然実行プランが狂った可能性を疑います。, DMVを使ったクエリでは推定実行プランは取得できますが、実際の実行プランもみれると嬉しいです。 既にS Lockをかけていた場合は、X Lockはかけられない。このとき、X Lockをかけるためにクエリが待ち状態になる。 https://docs.microsoft.com/en-us/archive/blogs/jpsql/on-12 しかしながら個々の知識を組み合わせて実際に起きたトラブルを調査し、解決まで至ったというプロセスを紹介する記事はあまり無いように思います。 MSのドキュメントには、ロックリソースの種類として以下の図が掲載されている。見方にポイントがあるので解説。 インテントロックを用いると以下のように説明できる。 SQL で統計情報を更新する方法です。 クエリのパフォーマンスが低下した場合などのときには統計情報を更新することで改善することがあります。 UPDATE STATISTICS もしくは sp_updatestats ストアドプロシージャを使用して更新することができます。