WhiskyStudios

【4】http://www.yahoo.co.jp/ 弊社ではサービスの一部にSQL Serverを使用しています。先日、「普段は数10ミリ秒で実行完了するクエリが、たまに5秒間実行され続けて最終的にタイムアウトするので調査して欲しい」という依頼を受けました。調査方法を整理して最終的に原因の特定とタイムアウト発生の防止まで実現できたので、一連の流れとハマった点、今回のようなケースでの調査に使える汎用的な調査手法をご紹介したいと思います。, SQL Server以外のRDBMSをお使いの方にも、「SQL Serverではこんな情報がとれるのか。MySQLだったら〇〇でとれる情報だな」というように比較しながら読んでいただけると嬉しいです。, 依頼を受けて最初に思い浮かんだのは、「ブロッキングが起きているのでは」という仮説でした。そこで、拡張イベントを使ってブロッキングを検出できる状態にしました。取得するように設定したイベントは以下の4つです。, sql_batch_completedとRPC_completedの違いについては、sql_batch_completedはクエリがTransact-SQLバッチとして実行されたときに発生します。一方で、RPC_completedはクエリがリモート プロシージャ呼び出しとして実行されたときに発生します。blocked process reportについてはこちらに詳しく解説されています。今回はプログラム側で設定するタイムアウト値が5秒であるため、blocked process thresholdには3秒を設定しました。また、sql_batch_completedとRPC_completedについてはResult=Abort（異常終了）でフィルタを設定しました。Result=Abortでフィルタしたsql_batch_completedとRPC_completedイベントが起きた同一時間帯でblocked process reportイベントが発生していれば、ブロッキングが原因でタイムアウトした可能性があります。また、blocked process reportイベントの中身を見ることで、Result=Abortで終了したクエリがブロッキングされていたかどうかを特定できます。, 調査した結果、Result=Abortでフィルタしたsql_batch_completedとRPC_completedイベントが起きた同一時間帯で、blocked process reportイベントは起きていませんでした。したがって、今回のタイムアウトの原因はブロッキングではないことが分かりました。, ブロッキングではないことが分かったので、次の仮説を立てようと、考えられる原因について考えました。しかし、「いつもは高速なクエリが突然遅くなる事象」の原因としては考えられるパターンが複数存在します。そのため、次のステップとして、いきなり仮説を立てるのではなく、次の調査に移るためにどういった情報を取得すれば良いかを考えました。, タイムアウトしたクエリが5秒間実行され続けるとき、何らかの「待ち状態」になっていると考えられます。ブロッキングは数ある待ち状態の一つでしかなく、どういった待ち状態であったかを後追いできる仕組みを作ることで調査に必要な情報が取得できるのではと考えました。, そこで、実行中のリクエストを取得できるDMVである、dm_exec_requestsの実行結果を1秒ごとにテーブルにダンプする仕組みを作りました。このテーブルには、実行中クエリの現在の待ち状態と前回の待ち状態が「wait_type」、「last_wait_type」としてそれぞれ格納されています。そのため、1秒ごとにこの情報をダンプしておくことで、タイムアウトにいたるまでの待ち状態の遷移を後から確認することができます。, 次に、以下のクエリをジョブなどで実行します。dm_exec_requestsをSELECTした結果を、無限ループで1秒ごとにテーブルにINSERTしていきます。, あとは、タイムアウトしたクエリのセッションIDとタイムアウト時間帯でテーブルをフィルタします。タイムアウトしたクエリのセッションIDは、Result=Abortでフィルタしたsql_batch_completedかRPC_completedイベントを確認することで取得できます。, タイムアウトしたクエリは上図のように、常にPAGEIOLATCH_SHで待っている状態でした。ここで、PAGEIOLATCH_SHという待ち状態について説明します。SQL Serverでは、必ずメモリからデータを読むのですが、以下のいずれかの流れをとります。, PAGEIOLATCH_SHは「ディスクからメモリに読む」ときに発生する待ちです。そのため、物理読み取りによる待ち時間が起因して普段よりもクエリ実行に時間がかかっている状況だったといえます。ブロッキングのように他のリクエストと競合しているわけではありませんでした。, 実行プランが突発的におかしくなり、通常よりも大量のIOを発生させている可能性を考え、タイムアウトしたクエリの実行プランのプロパティを確認してみました。その結果、「十分な数のプランが見つかりました」という理由でクエリの最適化が途中で終わっていました。最適化が途中終了する程度にシンプルなクエリなので、実行プランが突発的におかしくなるわけでもなさそうでした。, 別の可能性として、バッチ処理等で大量にデータを読み取る処理が発生したタイミングで、メモリからデータがキャッシュアウトされたことで通常時よりも多くのデータのディスク読み取りが発生する状況を考えました。そこで、[SET STATISTICS IO ON]をつけてタイムアウトしたクエリ実行し、各テーブルの読み取り状況を確認しました。, 多少物理IOは発生していますが、通常時は10ミリ秒程度で実行完了します。そのため、仮にキャッシュアウトされたことで全データが物理読み取りになったとしても、5秒もかかるものなのだろうかと疑問に思いました。, 以上を踏まえると、キャッシュアウトによる物理ディスク読み取り量が増えたことが原因というよりは、別のクエリが大量のデータを読み取ることで急激な物理ディスク負荷がかかり、ディスクキューの数値が上昇し、1IOあたりの物理読み取りの時間が伸びているのでは、と考えました。, タイムアウトしたときのdm_exec_requestsのダンプ結果を再掲します。上図から、wait_timeが毎回数10ミリ秒になっていることが分かります。これは普段の傾向なのか、タイムアウト時の傾向なのか判断するために、今までの累計値（≒通常時）から、物理ディスク読み取り時の平均待ち時間を確認しました。, PAGEIOLATCH_SHの平均待ち時間（avg_wait_time_ms）は2ミリ秒でした。このことから、タイムアウトが発生するタイミングでは、1IOあたりの待ち時間が通常時よりも大幅に長くなっていることが分かりました。ディスクまわりのメトリクスを採取することで、このあたりの情報をより詳細に確認できます。したがって、次のステップとしてperfmonで採取したメトリクスを解析してみました。, 20:05:24ごろタイムアウトしたクエリがあったので、その時間帯付近に注目して各メトリクスを確認しました。, メモリ内のデータ保持予測期間を示す「SQL Server:Buffer Manager\Page life expectancy」の値が定期的に大幅に減少しています。これはデータのキャッシュアウトが定期的に発生していると読み取ることができますが、ある程度規則性があり、タイムアウト発生時だけ特異な形状というわけではありませんでした。, 「PAGEIOLATCH_SH」待ちがエラー発生の時間帯で突出して高いことが分かります。タイムアウトしていないクエリも、一時的に実行時間が伸びている可能性がありそうです。, ディスクキューも確認しました。データファイルが格納されているドライブのディスクキューが突出して高くなっていました。タイムアウト時間帯以外もスパイクはみられました。, 秒間のディスク読み取り量についても、データファイルが格納されているドライブが突出して高く、タイムアウトが起きた時間帯では高い値のまま一定期間推移しているように見受けられます。この値がDBサーバーの物理ディスク性能の上限値である可能性が高そうです。, 物理読み取りサイズと、ディスクキューの発生状況の関係を見るため重ねてみると、両者には相関関係がありそうです。, 物理読み取りサイズとPage IO Latch Waitsにも関係があるようでした。「大量にディスク読み取りを行うプロセスが1つ以上存在し、ディスク性能上限に達するほどの読み取りを行っている状況下で、物理ディスクにアクセスが必要なクエリが実行されることでPage IO Latch Waitsがスパイクする」と解釈しました。, 定期的に物理ディスク読み取り性能の上限に達しており、そのタイミングで物理読み取りが必要なクエリが出てくると「Page IO Latch Waits」が発生するようです。この時点で、「普段は数10ミリ秒で実行完了するクエリが、たまに5秒間実行され続けて最終的にタイムアウトする」原因としては、そのクエリ自身にはなんの落ち度もないことが分かりました。かわりに、別の何らかのクエリがディスク性能の上限に達するほどの大量の物理読み取りを行っているため、他のクエリがわずかな物理読み取りを行う際でも通常時よりかなり時間がかかってしまい、場合によってはタイムアウトに達することもある、という状況でした。, 最後に、実際に大量の物理読み取りを発生させているクエリを特定すれば、実際に対策を実施できる可能性があります。, タイムアウトが発生した時間帯における物理読み取り量が多い順にsession_idを並び替えるクエリを以下の通り作成しました。, このクエリの実行結果です。該当の時間帯で最も総物理読み取りサイズが大きいセッションでも29541ページ（約230MB）しか読み取っていないという結果になりました。この程度のディスク負荷では、性能上限には達しない印象で、作成したクエリが間違っていそうです。そこで、次に「タイムアウト発生直前の20:05ごろに開始したクエリの中で、総物理読み取り数が多い順」で並び替えてみたところ、以下のクエリをみつけました。, ①と④に着目すると、クエリがタイムアウトした時間帯（2020/04/14 20:05:24ごろ）では、物理読み取りページ数（reads）がカウントアップされておらず、ずっと0になっていました。そのため、最初に作成したクエリにはヒットしていませんでした。そして、タイムアウトとは時間的には関係がない時間帯（図中②）において、いきなり「reaeds」が5358249（約41GB）まで跳ね上がっていました。, 今回のDBサーバーのディスク性能を考慮すると、数秒で41GBを読み取ることは不可能です。したがって、「readsは常に読み取ったサイズをカウントアップするわけではなく、あるタイミングで一気に累積読み取り数を計上することがある」といえます。この仕様を知らなかったのでクエリの書き方を間違い、結果として根本原因のクエリを見つけることができない状態になっていました。readsが計上されない条件ですが、少なくともlast_wait_type=CXPACKET（③）である場合、つまり並列処理している間はreadsに限らず、writesなど複数項目がカウントアップされないようです。, 以上の調査を踏まえ、以下のクエリで大量の物理読み取りを行っているクエリを特定することができました。, 修正版のクエリの実行結果です。修正前は、「タイムアウト発生タイミングでreads=0だけどディスクに高負荷をかけているセッションが存在する場合がある」ということを考慮せずに作成したクエリでした。一方で、修正版ではそれを考慮したうえで作成しているため、こちらのクエリの方が信頼できる結果を得られています。, ディスク性能限界まで達するほどの物理読み取りが数秒～数10秒継続した状態において、物理読み取りが必要なクエリが実行されたとき、通常よりも物理読み取りに時間がかかり、タイムアウトに達することもある状況になっていました。, 理論的には、性能限界まで物理読み取りを発生させなければ大丈夫です。そのためには、大量の物理読み取りを行っているクエリを特定し、特定結果によって以下のAかBいずれかの場合に応じた対応をとれば良いはずです。, 各クエリをチューニングするか、定期的な周期で実行しているクエリがある場合は実行タイミングをずらす。, 物理読み取り削減という観点でのチューニング（圧縮・クエリチューニング・インデックス等）を実施する。, 今回特定した結果はBに該当し、シンプルにインデックスを張ることで該当クエリのIOを劇的に削減できました。結果的にこのクエリ起因でのクエリタイムアウト発生を防ぐことができるようになりました。, ステップ3については、待ち状態によって手順が多岐にわたりますが、ステップ1と2については調査の初期段階として汎用的に使っていただけると思います。, 今回調査したサーバーでは使えませんでしたが、SQL Server 2017以降ではクエリストアを使って各クエリの待ち事象を取得することができるようになっています。そのため、今回ご紹介した調査手法をクエリストアだけで完結させることができます。なお、クエリストア自体は2016から提供されていますが、待ち事象が取得できるようになったのは2017からです。, 調査依頼を受けたときに最初に思いついた原因はブロッキングでしたが、結果としてまったく違う原因でタイムアウトが起きていました。原因調査では、仮設を立てる前に可能な限り必要な情報を集めることが重要だと感じました。, ZOZOテクノロジーズでは、一緒にサービスを作り上げてくれる仲間を募集中です。ご興味のある方は、以下のリンクからぜひご応募ください！, vasilyjpさんは、はてなブログを使っています。あなたもはてなブログをはじめてみませんか？, Powered by Hatena Blog 私も外資系企業に勤めていた時に、毎日為替レート表をWebから拾ってプリントアウトして保存していました。, しかし、こういったデータはリアルタイムにデータ保存しておく方が後から加工できるので便利です。, そこで、WebのデータをExcelに直接取り込む「Webクエリ」機能を紹介します。, 為替レートを直接取り込まない場合には定期的に為替レートの確認が必要となるでしょう。, そこで、自動的にWebの為替データを取りに行き、Excel上で更新することができたら便利です。, アドレスに取り込みたい表の存在するサイトのアドレスを入力し、移動をクリックします。（下の例では三菱東京UFJの為替サイトを指定しています）, 取り込みたい表の矢印マークをクリックし、範囲選択します。（見づらいですが、選択すると矢印マークが緑色に変わります。, 取込みボタンをクリックし、インポートする位置のセルを指定すると、Excelに表が取り込まれます。, Webから表を取り込んだシートを再度開くと、「外部データ接続が無効になっています」というメッセージが出てくるので「コンテンツの有効化」をクリックします。, ファイルを開く際に自動更新をしたい場合には、外部データ範囲のプロパティを開き、「ファイルを開くときにデータを更新する」にチェックを入れます。, 例えば国内で仕入れた商品をAmazon US やAmazon UKなどで販売する輸出業を行っている場合。, 先ほどの例の為替レート表をリアルタイムでExcelに取り込むことによりそれが可能となります。, あらかじめRateのシートを作っておき、そこにWebから為替データを自動取込みします。, 次に、別途管理している利益管理表に通貨に応じてVlookupでレートを反映させ、利益を更新する方法です（下記の表はあくまで一例です）。, 該当するWebサイトを開いて、コピペして・・という操作でも数分かかりますし、何よりもその作業自体を忘れる可能性もあります。, この他、グラフやピボットテーブルなど組み合わせてオリジナルの分析表を作ってみても面白そうです。, Webサイトのデータを自動的に取得できる、Excelの「Webクエリ」機能を紹介しました。, 定期的にWebサイトからデータを手動で取得されている方は自動取込みをすることによって業務効率化を図ることができます。, 開発者向けですが、Amazonや楽天など大量のデータを扱う業者が公開しているWebサービスを使えば、商品企画やマーケティングなどより実践に役立つ情報収集を行うこともできます。（こちらは未体験ですが・・）, 独立してから辞められたことの一つに、意味のない〇〇会（新年会、忘年会、懇親会・・）に出席することがあります。, 税理士。横浜市在住。 クラウド会計専門の税務顧問を行っています。IT効率化・ネットビジネス・海外取引・非居住者の税務が得意です。主に独立・起業して間もないフリーランス、社長、副業を行っているサラリーマンの方のお手伝いをさせていただいております。 既存の考え方にとらわれず、常にゼロベースから考えお客様に貢献します。 フリーランスの働き方・生き方を応援すべく、セミナー活動も行っています。 オフィシャルページはこちら, 2020年5月15日発売 『図解でわかる！税理士が知っておきたいネットビジネスの仕組みと税務』, 税理士がAI・ICT・キャッシュレスとどう向き合っていくのかを書きました。キャッシュレスの座談会にも出席しています。, 参考記事： 『税務弘報』2020年1月号　「税理士のためのAI・ICT・キャッシュレスに強くなる掟」に執筆・座談会出席しました, 【雑誌執筆のお知らせ】『税経通信　2019年1月号』　ネットビジネスの所得区分と必要経費について記事を執筆しました, 株式会社そると様の依頼により、普段の仕事、副業している方の税務上の留意点などのインタビューを受けました。, 巻頭特集「Life is precious！」 拡大しないひとり税理士として、インタビューを受けました。, 【取材協力】小学館発行の雑誌「Precious 2018年11月号」に取材協力しました.

ポケモンgo 背景 紫 8, Synoke 時計 説明書 8, 三協アルミ ダブルフェース 取説 6, トマト マリネ 作り置き 7, ガルバルディα β 違い 38, 西川 ベビー布団 アンパンマン 4, ドリル ビット シャープナー おすすめ 7, Bts キャラクター チミー 6, イニシャル Go 歌手 7, Bmw R69s 中古車 6, 背中 リンパ 痛い 5, 美容系ユーチューバー アンチ 68 27, エクスペリア 5 キーボード 設定 13, 川崎市 高校 偏差値 13, Gmail で Ymobileメール 6, 英 検 3級 熟語テスト 19, Nd ロードスター マツダコネクト 取り外し 6, 抱き枕 綿 詰め方 17, 離婚後 同棲 いつから 5, Bmw 走行中 異音 6, Dahon K3 ハンドル交換 14, 塾 引き止め しつこい 9, No Mean 意味 4, Fgo 絆クエスト 廃止 10, Access2016 メモリ不足 3035 5, パジャマ 毎日洗う 風水 12, メタルギアソリッド5 クワイエット シャワー 5, 5g 基地局数 予測 4, Ipad Pro フィモーラ 4, Wps Writer ページ設定 4,