none
SQLServer2014 クエリタイムアウト散見について RRS feed

  • 質問

  • ■環境
    ・Windows Server 2012 R2 64bit
    CPU:12core
    メモリ:48GB
    ・SQLServer2014 Enterprise

    上記環境で、テーブルに対するクエリがタイムアウト(タイムアウト値30秒)が散見して、
    処理が滞留してしまう問題が発生しました。

    サポートと連携して調査しておりますが、いかんせん私の知識が足りず、理解が出来ていない部分が
    多いです。

    サポート曰く、パフォーマンスモニタ、OSのログ、SQLServerのエラーログを見る限り、
    OSやデータベース自体のパフォーマンスに問題がでていた事は確認できていないとの事でした。

    そこで教えて頂きたいのですが、テーブル(DB?)のパフォーマンス問題とはどのような事が考えられるの
    でしょうか。また、ログに情報がない場合、テーブル(DB?)のパフォーマンスはどのように確認すれば
    良いのでしょうか。

    正式回答ではなく、情報や皆様のご経験からの知見を頂けますと幸いです。
    2018年3月15日 1:18

回答

  • OSやデータベース自体にパフォーマンスの問題が発生していなかったのであれば、排他制御によるロックが発生していたと考えるのが自然です。ロックは説明するまでもないかと思いますが、データベースの整合性を保つために他の処理を待たせることであり、「待つ」という行為そのものはデータベースにとって正常な動作です。30秒以上待たされたことによりタイムアウトが発生したとしても、これもデータベースとしては指示された通りに正常動作したと言えます。

    30秒以上待たせたことによりタイムアウトが発生したというのが不本意な動作だとすればそれは指示ミス、アプリケーション側の問題です。データベースへの指示、つまりクエリ内容を見直す必要があります。まずはSQL Server Profilerにてどのようなロックが発生していたのか(もしくは発生していなかったのか)を確認してください。

    • 回答としてマーク nbosamohan 2018年3月21日 9:49
    2018年3月15日 1:52
  • 個人的な経験から回答したいと思います。
    クエリタイムアウトはselectなど参照系でしょうか?

    基礎的な回答となってしまいますが、SQL Serverに限らずデータベース製品では実行計画という、クエリを実行するために最適なプランを算出してSQLを実行しています。
    インデックスの有無や抽出するデータ量によって、データの取り出し方に効率・非効率があるためです。
    サーバリソースに空きがあっても非効率な実行計画でクエリが実行されることでSQLの実行速度が遅延する事もあります。
    「ある日突然SQLの結果が遅くなった」というケースでは、テーブルのデータが増減・更新されたことや統計情報が更新された影響である事があります。


    >そこで教えて頂きたいのですが、テーブル(DB?)のパフォーマンス問題とはどのような事が考えられるの
    >でしょうか。また、ログに情報がない場合、テーブル(DB?)のパフォーマンスはどのように確認すれば
    >良いのでしょうか。

    こういったタイムアウト問題では、私の場合は対象のSQL文を特定して実行計画を確認します。
    実行計画に問題がある場合に、チューニング出来る事もあれば出来ない事もあります。

    またInsert・Delete・Updateに関してもインデックスなどが断片化していれば、インデックスの再構築で解消する事もあります。



    ■インデックスの再構成と再構築
    https://docs.microsoft.com/ja-jp/sql/relational-databases/indexes/reorganize-and-rebuild-indexes

    出来ないケースとして、データ件数が単純に増加していてselect count(*)などを実施している場合です。(キャッシュに乗れば2回目以降は早いことも)
    また実行計画以外では、キャッシュヒット率などを確認するくらいです。



    2018年3月15日 2:21

すべての返信

  • OSやデータベース自体にパフォーマンスの問題が発生していなかったのであれば、排他制御によるロックが発生していたと考えるのが自然です。ロックは説明するまでもないかと思いますが、データベースの整合性を保つために他の処理を待たせることであり、「待つ」という行為そのものはデータベースにとって正常な動作です。30秒以上待たされたことによりタイムアウトが発生したとしても、これもデータベースとしては指示された通りに正常動作したと言えます。

    30秒以上待たせたことによりタイムアウトが発生したというのが不本意な動作だとすればそれは指示ミス、アプリケーション側の問題です。データベースへの指示、つまりクエリ内容を見直す必要があります。まずはSQL Server Profilerにてどのようなロックが発生していたのか(もしくは発生していなかったのか)を確認してください。

    • 回答としてマーク nbosamohan 2018年3月21日 9:49
    2018年3月15日 1:52
  • 個人的な経験から回答したいと思います。
    クエリタイムアウトはselectなど参照系でしょうか?

    基礎的な回答となってしまいますが、SQL Serverに限らずデータベース製品では実行計画という、クエリを実行するために最適なプランを算出してSQLを実行しています。
    インデックスの有無や抽出するデータ量によって、データの取り出し方に効率・非効率があるためです。
    サーバリソースに空きがあっても非効率な実行計画でクエリが実行されることでSQLの実行速度が遅延する事もあります。
    「ある日突然SQLの結果が遅くなった」というケースでは、テーブルのデータが増減・更新されたことや統計情報が更新された影響である事があります。


    >そこで教えて頂きたいのですが、テーブル(DB?)のパフォーマンス問題とはどのような事が考えられるの
    >でしょうか。また、ログに情報がない場合、テーブル(DB?)のパフォーマンスはどのように確認すれば
    >良いのでしょうか。

    こういったタイムアウト問題では、私の場合は対象のSQL文を特定して実行計画を確認します。
    実行計画に問題がある場合に、チューニング出来る事もあれば出来ない事もあります。

    またInsert・Delete・Updateに関してもインデックスなどが断片化していれば、インデックスの再構築で解消する事もあります。



    ■インデックスの再構成と再構築
    https://docs.microsoft.com/ja-jp/sql/relational-databases/indexes/reorganize-and-rebuild-indexes

    出来ないケースとして、データ件数が単純に増加していてselect count(*)などを実施している場合です。(キャッシュに乗れば2回目以降は早いことも)
    また実行計画以外では、キャッシュヒット率などを確認するくらいです。



    2018年3月15日 2:21
  • 疑問なのですが、挙げられたような問題が発生した場合、CPU / Memory / Disk IOなど何らかのパフォーマンス指標値に現れませんか? 例えば、データの取り出し方が非効率になり大量のデータを読み込んでしまった場合はDisk IOなど。
    そしてその点について質問文では「OSやデータベース自体のパフォーマンスに問題がでていた事は確認できていない」と言及されているわけですが、どうお考えでしょうか?

    根本の原因を特定せず、断片化解消で一時的に回復できたとしても、完治しておらず再発するだけであり、また「断片化を解消するとなぜか回復する」というバッドノウハウが出回ってしまうことも危惧しています。

    2018年3月15日 5:39
  • 佐祐理様

    明示的に返信先を記載されていないので、誰に対しての返信か不明ですが「断片化」に関して書かれているので私だと判断して返信致します。

    >そしてその点について質問文では「OSやデータベース自体のパフォーマンスに問題がでていた事は確認できていない」と言及されているわけですが、どうお考えでしょうか?

    もちろん、物理的な読込が大量に発生する場合はI/Oなどに変化がある事が多いでしょう。
    しかし、「問題ない」の基準は何になりますでしょうか。
    私の経験則では、異常や変化を100%ログや性能情報から全て把握する事が可能であるとは思っていません。
    むしろ、「異常が無いように見える」から困っているのであって、他の確認方法を探されているような質問に見受けられます。
    そもそも、目の前にないシステムに対して「絶対〇〇である」と私は言い切れませんので他の観点で調査するための情報を回答しています。


    >根本の原因を特定せず、断片化解消で一時的に回復できたとしても、完治しておらず再発するだけであり、また「断片化を解消するとなぜか回復する」というバッドノウハウが出回ってしまうことも危惧しています。

    断片化の解消はDBメンテナンスの基本です。
    性能劣化に対する一時対応として「断片化を解消」するのが「バッドノウハウ」であると認識されているのであればSQL Serverに限らずOracleDBやDB2でも性能劣化に対する対応として「バッドノウハウ」が出回っている状況なので他のフォーラムでも啓蒙すべきではないでしょうか。(何も断片化については私だけが回答しているわけでもありません)

    また、断片化の解消が「バッドノウハウ」であり「根本原因を解消するためにアプリを改修する」が正解であれば非現実的なシステムも多いのではないでしょうか。(そもそも改修する権利もない可能性もある)
    断片化の解消をせずとも、断片化が発生していないかなど、OSやDBだけではなくテーブル単位などについて確認する方法について質問しているのではないでしょうか。


    佐祐理様の回答が正解であろうが、私の回答が不正解であろうが、質問者様が原因を突き止めるために必要な情報が見つかることが最優先事項だと思っています。
    「不要かつ混乱を招く情報である」のであれば「不適切な発言として報告」して頂ければと思います。
    もし私を含めた他者の回答に疑問を持つのであれば、質問者様へ間違いのない「グッドノウハウ」を回答して頂ければと思います。


    2018年3月15日 7:24
  • >そしてその点について質問文では「OSやデータベース自体のパフォーマンスに問題がでていた事は確認できていない」と言及されているわけですが、どうお考えでしょうか?

    もちろん、物理的な読込が大量に発生する場合はI/Oなどに変化がある事が多いでしょう。
    しかし、「問題ない」の基準は何になりますでしょうか。

    質問者さんが問題ないと判断されている以上、それを前提に回答するしかないと思います。「サポート」がどういう立場の方なのかも気になるところですが…。

    私の経験則では、異常や変化を100%ログや性能情報から全て把握する事が可能であるとは思っていません。

    なるほどパフォーマンスの指標値には現れなくても30秒タイムアウトするような経験がおありということで理解しました。ちなみにその際の原因はどのようなものだったのでしょうか? 質問者さんも同じ問題にあたっている可能性もありますし、参考までにお聞かせいただけたら幸いです。

    >根本の原因を特定せず、断片化解消で一時的に回復できたとしても、完治しておらず再発するだけであり、また「断片化を解消するとなぜか回復する」というバッドノウハウが出回ってしまうことも危惧しています。

    断片化の解消はDBメンテナンスの基本です。

    改修する権利がないのであれば悩む必要はなく受け入れるだけのことかと思います。以降、改修する権利がある前提で。

    盲目的な断片化解消(ALTER INDEX REORGANIZEやALTER INDEX REBUILDの実行)を危惧してのコメントでした。断片化が原因であれば断片化を引き起こすクエリを改善すべきですし、それを変更できないのであれば断片化を緩和させるFILL FACTORを設定したり手立てはあると思います。そこまで含めての断片化解消という意味でしたら誤解していました、すみません。

    2018年3月15日 9:12
  • いくつか質問を頂いておりますので1つだけ回答しますが、まず私が回答をする前提を書かせて頂きます。

    先ほども記載しましたが「質問者様が原因を突き止めるために必要な情報が見つかることが最優先事項」としているだけで、あえて不毛なやり取りを開始する必要性に疑問があります。(せめて質問者様も含めるなどすべき)
    明らかに私の回答が技術的に間違っており、不利益に直結する原因になるのであれば指摘頂く事が正しい在り方であるのは間違いないです。

    今回は質問者様の「テーブル」に関する質問に対して、影響が出ている可能性のある事項を書いたまでで、これ以上蛇足である情報を長々と書くつもりもありません。

    そもそも論ではありますが、質問者様のための場であることをご承知ください。

     
    >質問者さんも同じ問題にあたっている可能性もありますし、参考までにお聞かせいただけたら幸いです。
     
     具体的な原因ですと、守秘義務に抵触する可能性があるのですが、そもそもデータベースの種類が異なるので参考にはならないでしょう。
     それこそ、佐祐理様のおっしゃるロックの話やログ周りの話であれば性能面としては異常がない(異常な変化に見えない)事もあるでしょう。
     また、ネットワークの問題であればDBサーバは正常に処理をしていてもAPがデータを受け取るまでに遅延が発生するケースもあります。

     

    くどいようですが、私は質問者ではないのですし指摘が済んだのであれば、有益な情報は「質問者」様へ回答して下さい。


    2018年3月15日 10:50