Clever Geek Handbook

MS SQL Serverで定期的に長時間実行されるクエリの問題

まえがき



私が管理している情報システムがあります。 システムは、次のコンポーネントで構成されています。



1. MS SQL Serverデータベース

2.サーバーアプリケーション

3.クライアントアプリケーション



これらの情報システムは、いくつかのサイトにインストールされています。 情報システムは、各施設で2〜20人のユーザーから同時に使用され、24時間体制でアクティブに稼働しています。 したがって、一度に定期的なメンテナンスを実行することはできません。 つまり、インデックスの最適化を終日塗り付ける必要があり、必要なすべての断片化されたインデックスを一度に最適化する必要はありません。 他の作品でも同様です。



自動更新統計は、データベース自体のプロパティで設定されます。 また、統計は最適化されたインデックスによって更新されます。



問題



約1年前、次の問題に遭遇しました。



定期的に、すべてのリクエストが長時間実行されました。 さらに、制動時間はランダムでした。 これはランダムな日にすべての施設で起こりました。 さらに、ブレーキが発生する頻度（プロファイラーによる）を分析し始めたとき、毎日ランダムな時間に発生していることがわかりました。 ユーザーが常にそれに注意を払うのではなく、それを唯一のランダムな遅延として認識し、システムがすぐに再び機能するようになるだけです。



救いへの道



リクエスト自体がレビューされ、完了するまでに長い時間がかかりました。 しかし、最も興味深いのは、ランダムな時間にすべてのリクエストが長時間実行されたことです。 最も単純なタイプでも、テーブルから最後のレコードを数千行で引き出します。



さらに、次の作業が実行されました。



  1. MS SQL ServerおよびWindows Serverのログが分析されます-ブレーキの原因が見つかりませんでした
  2. 分析されたインデックス（断片化など）-不足しているインデックスが追加され、未使用のインデックスが削除されました
  3. 分析されたクエリ-一部のクエリが改善されました
  4. SQL Agentのジョブが分析されます-タスクをブレーキの問題にリンクできませんでした
  5. タスクスケジューラのタスクが分析されます-タスクはブレーキの問題にリンクできませんでした
  6. プロファイラーは、阻害の原因ではなく、効果も生み出しました。
  7. デッドロックの確認-長いロックはまったく検出されませんでした


その結果、定期的な禁止の原因の検索の失敗に3か月以上が費やされました。 ただし、興味深い事実を特定することは可能でした。すべてのリクエストにより、ワーカーの完了インジケーターではなく経過期待率が増加しました。 ディスクで何かが可能になるという事実につながったもの。 私もそれらをチェックしました-すべてが正常です。



解決策



驚くべきことは、アプリケーションのリクエストが遅い場合、SSMS自体で高速であることをランダムに確立できたことです。 その後、 次の記事が問題の解決に役立ちました（少なくとも続編では、アイデアを思い付きました）。



この記事から次の段落を引用します。

実際には、このオプションのデフォルト値はアプリケーションとSQL Server Management Studioで異なるため、最も重要なSETオプションはARITHABORTです。 これは、アプリケーションで遅い要求を検出し、SSMSでそれを行うことで十分な速度を得ることができる理由を説明しています。 アプリケーションは、現在の正しい値とは異なる値のセット用に構築されたプランを使用します。 一方、SSMSでクエリを実行する場合、キャッシュにはARITHABORT ONの実行計画がまだない可能性が高いため、SQL Serverは現在の値の計画を作成します。


実行の違いは、 SET ARITHABORTパラメーターにありました。 SSMSで行われたすべての要求では、このオプションが有効になり、外部（アプリケーションから）からの要求では無効になります。 そして、アプリケーションへの単純なリクエストがあってもオンにすることはできません。 



SET ARITHABORT ON;


その後、クレイジーなアイデアが続きました。フリーズ時に、プロシージャキャッシュをクリアします：をクリックします 。



その後の手動検証では、SSMSにリクエストを書き込む前に、次のように記述する必要があります。 



 SET ARITHABORT OFF;


その後、要求はアプリケーションから来たかのように実行されます。 リクエストに時間がかかったとき、手続き型キャッシュをクリアしました。 そして、それは常に癒されました。 つまり、手続き型キャッシュをクリアする前に、リクエストは最大20〜30秒実行され、その後は-0秒実行されます。



この後、別の実験が行われました-SQL Agentを使用して、データベース全体のプロシージャキャッシュ全体を1時間ごとにクリーニングします。 



 --   id  DBCC FLUSHPROCINDB(@db_id);


その後、すべての要求が非常に迅速に実行され始め（0.05秒未満）、最大5〜10秒の単一の異常値しかありませんでしたが、ユーザーはフリーズに気付きませんでした。 さらに、統計を更新しても結果が改善されなかったため、統計の更新を削除しました。



数か月の調査の結果、キャッシュがサーバー自体で使い尽くされ、1 GB未満のRAMが残っているか残っていない場合、またはMS SQL Serverサービスが割り当てられたすべてのRAMを使い果たした場合に、単一のハングが発生することを確認できましたタスクマネージャー）。 しかし、2回目は調査全体で2回しか発生しませんでした。



実際には、文字通りすべてがキャッシュに書き込まれますが、キャッシュは必ずしも時間通りに解放されるわけではありません。 キャッシュの問題は、 EmptyStandbyList.exeプログラムを使用して解決されました。



このアプリケーションは、タスクスケジューラを介して1時間に1回実行するように構成されています。 作業が6か月以上行われた後、すべての施設で要求にブレーキはかかりません。



不明な点は、1つのリクエストがランダムな日にランダムな時間に1か月に1回5〜10秒フリーズするまれなケースのみです。 合計で、6か月で4つのケースがあり、2つのサイトであり、まったくありませんでした。 同時に、MS SQL Serverサービスは、割り当てられたすべてのRAMを短時間消費します。



記事で説明した手順を実行しましたが、この解決策は役に立ちませんでした。



原則として、ユーザーはハングに気付かず、すべてが機能するため、深く掘り下げることはできませんでしたが、誰かが何か考えがある場合は共有してください、私は感謝します。



インターネットで包括的な答えを見つけることができず、問題の研究と解決に多くの時間を費やしたため、このような問題に出くわした人々を助けることを目的としてこの記事を書きました。



ソース



»1⬝2⬝3⬝4⬝5⬝6⬝7⬝8


More articles:


All Articles