+ 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回等、定期的に統計情報を更新, バッチ処理等で、大量にレコード削除/挿入/更新を行う場合は、処理完了直後に統計情報を更新. What is going on with this article? 例：要件的に許されるのであれば、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に昇格する場合がある。 インデックス再構築時には統計情報の更新も行われるので、そのタイミングで統計情報の更新を行う必要はありません。, [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で「現在のセキュリティ設定では、このファイルをダウンロードできません」が表示される. 「統計情報の更新」とパフォーマンス遅延の関係 統計情報はテーブルのデータ分布の状況を示します。では、アプリケーションがデータを更新し 自動統計の増分オプションの既定の設定を示します。 0 = 自動作成の統計は非増分です。 1 = 可能な場合は、自動作成の統計情報は増分されます。 適用対象: SQL Server 2014 (12.x) 以降。 is_auto_update_stats_on: bit: 1 = AUTO_UPDATE_STATISTICS は ON です。 … https://docs.microsoft.com/en-us/archive/blogs/jpsql/on-12 一方、テーブルにIXロックがあると、それだけでテーブルにXロックをかけられないことが分かるため、ロックの可否の判断効率が良い。, 先ほどの「上位の階層に互換性の無いロックがかかっていると、下位のリソースに対してロックをかけられない」というルールは、 MSのドキュメントには、ロックリソースの種類として以下の図が掲載されている。見方にポイントがあるので解説。 例えば、「1つのレコードを一度に更新できるのは、1つのクエリだけ」といったルールを実現してくれる。, １．ロックには複数の粒度（階層とも呼ばれる）が存在する こちらはクエリのコンパイルおよび最適化の際に、その時渡されたパラメータ値ではなく、統計データを使用するよう指定するヒントです。, 本記事では、実際に経験したSQL Serverに関するトラブルから学んだ統計情報の更新に関する方針について紹介しました。, 本記事に出てくる技術的な内容や調査用のクエリはほとんどWeb上で既出の内容かと思います。 ※EXTENT：物理的に連続した8ページをひとまとめにしてエクステントと呼ぶ。ページの効率的な管理のために使用される。, 最も粒度の大きいロックリソース。クエリを実行すると必ず該当DBにSロックをかける。 インテントロックを用いると以下のように説明できる。 統計情報を手動で取得しようと思ったら、ORA-20005エラーになってしまった。 どうやら、統計情報にロックがかかっているらしい。 解除方法は以下コマンドである。 統計情報ロック exec dbms_stats.lock_table_stats('スキーマ名', 'テーブル名'); 統計情報ロック解除 exec d… 下記コマンドで対象のDBにある全テーブルの統計情報を更新します。 exec sp_updatestats 実行中はこんな感じのメッセージが表示されます。 実行中の様子. こちらは効果的ですが、毎回コンパイルの分だけ実行時間とCPU使用時間が増大してしまい、ユーザーおよびサーバーにとってマイナスの側面もあります。そのためできる限り使用は避けるべきと考えています。, その他の対策としてはOPTIMIZE FOR UNKNOWNというクエリヒントを使用する方法もあります。 | ②プロセスBがテーブルBのKEYロックを取得 ※実際の実行プランを取得する方法として、他には拡張イベントを使用する方法があります。, 取得した実行プランの中で、キー参照を約50万回おこなっている箇所があり、ここがボトルネックのようです。, ボトルネックが判明しましたが、今回はもともと十分な速さで実行されていたクエリが、突然遅くなったという事象です。 クエリレベルだと、「次の二つのクエリは同時に実行できる」という意味。, 互換性が無いため、同時にかけられるロックは1つだけ。 この挙動をロックエスカレーションという。, ロックエスカレーションが起きると、該当テーブルへの更新がすべてブロックされてしまうため注意が必要。, ポイント：エスカレーション先のリソースはテーブルのみ。（KEY→PAGEといったエスカレーションは無い。必ずKEY→TABLEやPAGE→TABLEとなる。）, ①プロセスAがテーブルAのKEYロックを取得 また、ALTER DATABASEの実行時などにこのリソースにUロック等が獲得される。, ※ロック粒度についてのドキュメント そのためSSMS (SQL Server Management Studio)上で[実際の実行プランを含める]にチェックをつけた状態で該当クエリを実行しました。 ポイント：ブロッキングとデッドロックの違い ブログを報告する, ALTER INDEX において対象のインデックスを個別に指定せずALLとし再構築しても同様である。. もしIXロックがなければ、例えば他のクエリがテーブル全体にXロックをかけたいときに、全ページと全レコードに互換性の無いロックがかかっていないか調べる必要がある。 出典：https://docs.microsoft.com/ja-jp/sql/2014-toc/sql-server-transaction-locking-and-row-versioning-guide?view=sql-server-2014, SQL Serverのデータ構造と対応しており、階層構造になっている。 原因として、統計情報が古くなっていたことを疑い、該当クエリで使用しているテーブルの統計情報を更新してみました。, ※今回は統計情報の更新後にクエリがリコンパイルされることを期待し、期待通りリコンパイルされました。ただし、統計情報の更新＝必ずリコンパイル、というわけでもないようです。 ポイント：最低限覚えておくと良いのは、「SELECTはS、INSERT/UPDATE/DELETEはX、with(nolock)つきのSELECTはSch-S」 しかしながら個々の知識を組み合わせて実際に起きたトラブルを調査し、解決まで至ったというプロセスを紹介する記事はあまり無いように思います。 →この時点で、相互にブロックし合う関係になってしまい、このままだとプロセスA、プロセスB共に無限に待ち続けることに。これがデッドロック状態。, ⑤数秒後、SQL Serverが自動でデッドロックを検出し、プロセスAまたはプロセスBのどちらかを強制終了し、デッドロックを解消。 こんにちは。zozoバックエンド部の廣瀬です。 弊社のサービスではDBMSとしてMicrosoft社のSQL Serverを使用している箇所があります。 本記事では、過去に経験したSQL Server関連のトラブル及びその調査内容をご紹介し、最後にトラブルシューティングを通して策定した統計情報の更新に関する方針 … ④プロセスBがテーブルAのKEYロックを取得しようとするが、ブロッキングが発生。待ち状態になる。 イメージ図は以下の通り。, トランザクションを張っている期間は必要最小限に留め、可能な限り短くする。（コード量の観点からも、実行時間の観点からも） (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. | http://azwoo.hatenablog.com/entry/2013/02/14/125848, 商用環境では想定外の動きになるのを防止するため自動更新機能は利用せず自前で統計情報更新の処理を実装する事を検討。 ブログを報告する, -- システムDBおよびディストリビューションDB除外、かつオンラインのDBに限定, -- useを使う必要があるが、use単体でexecuteすると実行後にコンテキストが現在のDBに戻ってしまう。そのため丸ごと動的SQLで実行する, ' set @sql_update = ''update statistics '' + CAST(@Schema AS VARCHAR(100)) + ''.'' 既にS Lockをかけていた場合は、X Lockはかけられない。このとき、X Lockをかけるためにクエリが待ち状態になる。 ※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。 統計情報を更新してクエリのパフォーマンスを改善する . このスキーマ更新ロック(Sch-M)はテーブルの削除や変更時だけでなく、SQLServerが内部で統計情報を更新するときにも発生します。 スキーマ ロック SQL Server は 2 種類のスキーマ ロックを使用しており、SQL Server がテーブルの統計情報を更新するときにその両方が使用されます。 SQL Server の統計保 … 該当のクエリは、かなり前から実行されているクエリだったため、突然実行プランが狂った可能性を疑います。, DMVを使ったクエリでは推定実行プランは取得できますが、実際の実行プランもみれると嬉しいです。 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. 何故だろうとクエリと再度にらめっこしていたところ、気づいたことがありました。それはレコード更新の性質がテーブルによって異なるということです。 2016/7/2. 出典：https://docs.microsoft.com/ja-jp/previous-versions/sql/sql-server-2008-r2/ms186396(v=sql.105), 上位の階層に互換性の無いロックがかかっていると、下位のリソースに対してロックをかけられない, レコードにXロックをかける場合、その上位階層であるPAGEとTABLEに対して、IXロック（インテントXロック）が自動でかけられる。 Why not register and get more from Qiita? https://www.brentozar.com/archive/2015/01/updating-statistics-cause-recompile-no-data-changed/, SQL Serverにおいて、統計情報は自動的に更新される仕組みも用意されています。ただしレコード数の20％が更新されたとき等、自動更新のためには条件を満たす必要があります。 補足. したがってこのクエリによってCPU高負荷となった可能性が非常に高いと判断しました。 それぞれについて完全に覚えておく必要は無いけど、「XロックとSロックに互換性がないから、UPDATEの実行中は該当レコードへのSELECTはブロックされるのだろうな。データの大量更新を実行するタイミングはブロッキングが起きていないかDMVを使ってチェックしよう」 そのため、ボトルネックを改善するためのチューニング方法ではなく、なぜ突然CPU高負荷な実行プランが生成されてしまったかを考える必要があります。 Help us understand the problem. 「TABLEに対してXロックを獲得している場合は、TABLEへのIXロックが互換性が無いためブロッキングされる」, →ある程度なら意図的に粒度をいじることもできるが、基本的にはSQL ServerにまかせておけばOK。, ロックを保持する期間は、「明示的にトランザクションを開始しているかどうか」で変わる。, Sロック：クエリ実行直後にロックを開放。（既定のトランザクション分離レベルである「Read Committed」の場合の挙動） ２．ロックには複数の種類が存在する（ロックモード） ③プロセスAがテーブルBのKEYロックを取得しようとするが、ブロッキングが発生。待ち状態になる。 http://tech.kou.asia/?p=1672, この場合、件数が増えると20%に達するまでに時間がかかる。 わかり辛かったらすみません！, DB上でデータを操作（SELECT/INSERT/UPDATE/DELETE等）する際、データの整合性を保つために使われる排他制御の仕組み。 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. 正確には「統計情報が古い」とは、「統計が実際のデータ分布と大きく乖離している」状態を指します。 以下を参考にSQLServerのチューニングを実施したが、その際の気づきを残しておく。 blogs.msdn.microsoft.com インデックス再構築によって更新される統計は、更新対象インデックスの統計であって、それ以外の統計 (更新対象ではないインデックスや列の統計) は更新されません。 ３．各ロックモード間には「互換性」という関係性がある, ロックはデータの整合性を保つために必要な仕組みだけど、ブロッキングの原因にもなりやすい。, そのため、クエリを実行する際に「どういったロックが、どの粒度でかけられるのか、そのロックの互換性はどうか」といったことを開発者が意識できるとブロッキングの発生を未然に防ぐ（または最小限に抑える）ことが可能となる。, ロックには粒度が存在する。ロックリソースとも呼ばれる。 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. これまで普通に使えていた SQL Server が急に遅くなった、夜間の更新処理に異常に時間がかかるようになった。といったとき、統計情報が古くなっていて実行プランが正しく選 … ポイント：ロックの種類が異なるだけで、クエリを発行すると実は何らかのロックが必ず獲得されている。 オプティマイザの種類 (CBO、RBO) - オラクル・Oracleをマスターするための基本と仕組み http://www.shift-the-oracle.com/inside/optimizer.html というように今回の内容を業務で使用するクエリレベルの話に自分で変換して考えられるようになると、ブロッキングなどのトラブルの軽減につながると思います。, 株式会社ZOZOテクノロジーズ テックリード。Microsoft MVP for Data Platform （August 2020-） SQL ServerをメインにDBに関してつぶやきます。得意領域はチューニング/トラブルシューティング。SQL Server User Groupにて毎月登壇中。https://github.com/masaki-hirose. そのため、原因を特定するための調査を実施しました。, 同一ホスト上で稼働している主要なプロセスはSQL Serverしか無かったため、SQL Server上でCPUリソースを多く消費するようなクエリが実行されていることを疑いました。, 現在実行中のクエリのステータスを確認するため、動的管理ビュー（Dynamic Management Viewの略。以下DMVと呼ぶ）を使用したクエリを実行します。 文書番号：20529. 本記事では、過去に経験したSQL Server関連のトラブル及びその調査内容をご紹介し、最後にトラブルシューティングを通して策定した統計情報の更新に関する方針をまとめます。, ある日突然、SQL Serverが稼働しているサーバーのCPU使用率が高騰し、100％に張り付く状態が一定期間続きました。, CPU使用率のグラフを見ただけでは、なぜ突然高騰したのか原因は分かりません。 この時に渡されたパラメータがたまたま非典型パラメータの場合、それ以外の典型的なパラメータにとっては遅い実行プランになってしまう恐れがあります。, Microsoftのブログの中で、ストアドプロシージャであればwith recompileを指定するなど、実行時に強制的に毎回コンパイルさせることで非典型パラメータに関する問題を回避させる案が紹介されています。 1．ロックには複数の粒度（階層とも呼ばれる）が存在する 2．ロックには複数の種類が存在する（ロックモード） 3．各ロックモード間には「互換性」という関係性がある. SQL で統計情報を更新する方法です。 クエリのパフォーマンスが低下した場合などのときには統計情報を更新することで改善することがあります。 UPDATE STATISTICS もしくは sp_updatestats ストアドプロシージャを使用して更新することができます。 SQL Serverではデフォルトで自動統計情報更新が有効で、色々条件はあるがざっくり20%以上のデータ変動があったら自動で更新される。 https://fyts.hatenadiary.org/entry/20081112/async, ALTER DATABASE データベース名 SET AUTO_CREATE_STATISTICS ON|OFF SQL Serverのロックを理解するための3つのポイント . この挙動を全テーブルに対する統計情報の更新処理で実現するために、自分でクエリを作成しました。, 仮に統計情報をサンプリングレート100％でフルスキャンし、かつ定期的な更新で最新の状態に保ち続けたとしても、クエリが突然遅くなる可能性はまだあります。, ストアドプロシージャやパラメータ化クエリの場合、SQL Serverはコンパイル時に渡されたパラメータを考慮して、最適な実行プランを生成します（パラメータスニッフィングと呼ばれています）。 弊社のサービスではDBMSとしてMicrosoft社のSQL Serverを使用している箇所があります。 今回のトラブルでは、自動更新が走る前に統計情報が古くなってしまったことで実行プランが狂ったと判断しました。そのため恒久的な対応策として1日1回、定期的に全テーブルの統計情報を更新するジョブを作成しました。, 後日、またCPU高負荷な状態に陥ってしまいました。しかも犯人は同じクエリです。 ※実行にはVIEW SERVER STATE権限が必要です。, 実行結果の一部を抜粋します。クエリの内容は伏せさせていただきますが、同一のクエリが多数実行中で、かつ最長で20秒間も実行中の状態でした。, また、lastwaittypeカラムの多くがCPU高負荷の際に発生することが多いSOS_SCHEDULER_YIELDとなっており、突然のCPU使用率高騰との関連性が考えられます。, このクエリの平均のCPU使用時間を確認するため、さらに別のDMVを使ったクエリを実行します。, 平均のCPU使用時間が約5秒と非常に長いです。
Fire Hd 8 Ãレビ出力 9, Django Form Field ȿ加 6, Âイン Ãルド 2020 7, ĸ菱 Rl21301 Netflix 4, Xperia Ƭ期 Âンパクト 19, Ŀ育料 DŽ償化 Áつから żき落とし 4, Âンフィデンス Ãン Jp Ɂ勢編 Ȧ逃し 9, ǎ関ドア lj乳受け šぐ 5, Ƴ事 Ɯ装 33回忌 8, Lg Ɯ機el Ãネル Ŀ証 6, 250 400 ɫ速 4, Âギ薬局 Âザンヌ Ō粧品 7, ƭ規表現 Ãブルクォーテーション śまれた Âペース 7, Ãラクエ10 Ãトルマスター Ů珠 29, Iphone Se 2020 Ãィルム 23, Ãキタ 8500n Ŀ理 10, ȍ野行動 Âップル 4800 37, ƭ ȩめ物 ŏれた 12, Define R6 7 Ɂい 4, Ǚ猫 Âャラ一覧 ť 10, Ɩ幹線 lj急券 ň割購入 8, Sendanywhere Iphone ɟ楽 Ŀ存 15, Ãイクラ Ǡ漠 Ɲ人 4, Âく Áばしば ȋ語 11, Ps4 Youtube ŋ画 ʼn除 9, Âロット ƭ義 Ǜ手の気持ち 6, Ʋ縄 Ark Ãスケ 4,
