遅くなる - sqlserver パフォーマンス 改善




なぜSQL Serverは突然そのようなひどい実行計画を使用することにしましたか? (2)

バックグラウンド

私達は最近私達のより大きなテーブル(約175,000,000行)の一つでsql serverが使用していた問い合わせ計画に問題を抱えていました。 テーブルの列とインデックスの構造は5年以上変わっていません。

テーブルとインデックスは次のようになります。

create table responses (
    response_uuid uniqueidentifier not null,
    session_uuid uniqueidentifier not null,
    create_datetime datetime not null,
    create_user_uuid uniqueidentifier not null,
    update_datetime datetime not null,
    update_user_uuid uniqueidentifier not null,
    question_id int not null,
    response_data varchar(4096) null,
    question_type_id varchar(3) not null,
    question_length tinyint null,
    constraint pk_responses primary key clustered (response_uuid),
    constraint idx_responses__session_uuid__question_id unique nonclustered (session_uuid asc, question_id asc) with (fillfactor=80),
    constraint fk_responses_sessions__session_uuid foreign key(session_uuid) references dbo.sessions (session_uuid),
    constraint fk_responses_users__create_user_uuid foreign key(create_user_uuid) references dbo.users (user_uuid),
    constraint fk_responses_users__update_user_uuid foreign key(update_user_uuid) references dbo.users (user_uuid)
)

create nonclustered index idx_responses__session_uuid_fk on responses(session_uuid) with (fillfactor=80)

パフォーマンスがよくなかった(通常の1秒未満のパフォーマンスではなく、〜2.5分)クエリは次のようになります。

SELECT 
[Extent1].[response_uuid] AS [response_uuid], 
[Extent1].[session_uuid] AS [session_uuid], 
[Extent1].[create_datetime] AS [create_datetime], 
[Extent1].[create_user_uuid] AS [create_user_uuid], 
[Extent1].[update_datetime] AS [update_datetime], 
[Extent1].[update_user_uuid] AS [update_user_uuid], 
[Extent1].[question_id] AS [question_id], 
[Extent1].[response_data] AS [response_data], 
[Extent1].[question_type_id] AS [question_type_id], 
[Extent1].[question_length] AS [question_length]
FROM [dbo].[responses] AS [Extent1]
WHERE [Extent1].[session_uuid] = @f6_p__linq__0;

(クエリはエンティティフレームワークによって生成され、sp_executesqlを使用して実行されます)

低パフォーマンス期間中の実行計画は次のようになりました。

上記のクエリを実行しているデータの背景が400行を超えることは決してありません。 言い換えれば、session_uuidでフィルタリングすることは結果セットを本当に減らします。

スケジュールされたメンテナンスの背景 - スケジュールされたジョブはデータベースの統計を再構築し、テーブルのインデックスを再構築するために毎週実行されます。 ジョブは次のようなスクリプトを実行します。

alter index all on responses rebuild with (fillfactor=80)

パフォーマンス上の問題を解決するには、このテーブルに対して上記の再構築インデックススクリプトを実行します。

関連性のあると思われるその他の情報...最後のインデックスの再構築以降、データの分布はまったく変わりませんでした。 クエリに結合はありません。 私たちはSAASの店で、正確に同じスキーマを持つ50から100のライブ本番データベース、いくつかのデータを含むもの、少ないもの、すべて同じクエリを実行するものをいくつかのSQLサーバーに分散させています。

質問:

この特定のデータベースでSQL Serverがこのひどい実行計画を使用し始めるようになった原因は何でしょうか。

問題はテーブルのインデックスを再構築することで解決されたことを覚えておいてください。

たぶんもっと良い質問は、「SQL Serverがインデックスの使用をやめるような状況は何ですか?」です。

もう1つの見方は、「数日前に再構築されたインデックスをオプティマイザが使用せず、不適切なクエリプランに気付いたときにインデックスの緊急再構築を行った後に再び使用し始めるのはなぜでしょうか」です。


これはSQLに関する私の最も嫌いな問題の1つです - 私はこの問題が原因で複数の失敗がありました - 数ヶ月間動作していたクエリが最大250msからタイムアウトしきい値を超えるまで製造システムをクラッシュさせるコース。 クエリを分離してSSMSに貼り付けて、次にそれをばらばらにし始めるのにしばらく時間がかかりました - しかし私がしたことはすべて「うまくいった」だけでした。 最後に、クエリに "AND 1 = 1"という語句を数週間追加しました。最後のパッチは、オプティマイザを "盲目にする"ことでした - 基本的に、渡されたパラメータをすべてローカルパラメータにコピーします。 クエリがうまくいかない場合は、引き続き機能するようです。

このクエリがすでにプロファイリングされていて前回正常に実行されていて、関連する統計が大幅に変更されていない場合(たとえば、テーブルのさまざまな変更または新しいインデックスのいくつかの要因を考え出す)そして、「オプティマイザ」が新しい実行計画で物事を盛り上げることにしました。その新しい改善された計画が古い計画のX倍以上かかる場合はどうでしょうか。 テーブルが100〜100,000,000行になるか、キーインデックスが削除されるかはわかりますが、安定した運用環境ではクエリの持続時間が100倍から1000倍遅くなるのはそれほど難しくありません。計画にフラグを立て、前の計画に戻る。


これはコメントには長すぎる。

理由は簡単です。オプティマイザは、最適なプランが何であるかについて考えを変えます。 これは、データの配布における微妙な変更(またはjoinキーの型の不一致などの他の理由)が原因である可能性があります。 クエリの実行計画を提示するだけでなく、自分が別の実行計画にどれだけ近いかについてのしきい値を表示するツールがあったと思います。 あるいは、実行計画を隠して同じクエリが別の計画を使用し始めた場合に警告を出すことを可能にするツールです。

私は私自身にこの全く同じ質問を複数回行った。 あなたは、数ヶ月間、夜間に稼働するシステムを持っています。 非常に複雑なクエリを使用して大量のデータを処理します。 その後、ある日、午前中に入ってきて、通常午後11時までに終了するジョブはまだ実行中です。 矢印!

我々が思いついた解決策は、失敗した結合に対して明示的なjoinヒントを使うことでした。 ( option (merge join, hash join) ) また、複雑なクエリすべてに対する実行計画の保存も開始したため、ある夜の変化と次の変化を比較できます。 結局、これは実際的な興味よりも学術的な興味がありました - 計画が変わったとき、我々はすでに悪い実行計画に苦しんでいました。





performance