SET ARITHABORT ONでクエリが劇的に高速化されるのはなぜですか？

少し古いですが、ここで終わる人にとっては同様の問題…

同じ問題が発生しました。私にとっては、パラメータスニッフィングであることが判明しましたが、最初は気にするほど理解できませんでした。問題を修正する「set arithabort on」を追加しましたが、その後戻ってきました。次に、次のように読みました。

http://www.sommarskog.se/query-plan-mysteries.html

すべてがクリアされました。Linqを使用していたためSQLで問題を修正するためのオプションが限られていたため、クエリプランガイド（リンクの終わりを参照）を使用して、必要なクエリプランを強制しました。

コメント

• 6年以上経った今でも、この回答に記載されているリンクは”必ず読む必要があります” …現在も、最新のリビジョンは12月’ 17です。

.NETアプリケーションは、デフォルトで無効になっているオプションで接続しますが、ManagementStudioではデフォルトで有効になっています。その結果、サーバーは実際にはほとんど/すべてのプロシージャに対して2つの個別の実行プランをキャッシュします。これは、サーバーが数値計算を実行する方法に影響を与えるため、手順によって大きく異なる結果が得られる可能性があります。これは実際には、procがひどい実行プランを受け取ることができる2つの一般的な方法の1つにすぎず、もう1つはパラメータスニッフィングです。

については、もう少し詳しく説明します。

コメント

• 半分に同意しますこの答え。しかし、数値計算の主張には非常に懐疑的です！
• @Martin：私は不明確だったと思います。 ARITHABORT ONを使用すると、div / 0または算術オーバーフローエラーでSQLServerがエラーになると言っていました。オフのときは動き続け、何らかの理由であらゆる種類の恐ろしい問題を引き起こす可能性があります。
• @ Ben-はい申し訳ありませんが’特に攻撃したくありませんでしたあなたの答え私が指摘したのは、SETオプションを変更するのは非常に簡単で、より良い計画を立てて、これをオプション自体に問題があると誤診することです。私は’あなたのリンクの男が’これを行っていないことを確信していません。
• @ Martin-そうではありません問題、私はあなたが私を攻撃しているとは思わなかった’。私がリンクした他の議論は少し不明確かもしれません。裏付けとなる証拠を提供しようとしていました。
• @Martin振り返ってみると、あなたは正しいと思います。

これはほぼ確実にパラメータスニッフィングであったと私は主張します。

SET OPTIONSはこのようにパフォーマンスに影響を与える可能性があるとよく言われますが、あなたがいる場合を除いて、この主張の信頼できる情報源はまだ1つもありません。インデックス付きビュー/永続化された計算列を使用します。

この場合（データベースがSQL2000互換モードでない限り、SQL2005 +およびの場合）。 ARITHABORTとANSI_WARNINGS OFFの両方がある場合は、インデックスが使用されていないことがわかりますそのため、目的のシークではなくスキャンが行われる可能性があります（また、永続的な計算結果を使用できないため、オーバーヘッドが発生します）。 ADO.NETは、私が行った簡単なテストから、デフォルトでANSI_WARNINGS ONを持っているようです。

クレーム 「サーバーが数値計算を実行する方法」が結果に数分を追加する可能性があるというベンの答えは、そうでなければ1秒もかからないだけで、私には信頼できないようです。パフォーマンスの問題を調査する際に、プロファイラーを使用して問題のあるクエリを特定することが起こりがちだと思います。これはManagementStudioに貼り付けられて実行され、結果を即座に返します。接続間の唯一の明らかな違いは、ARITH_ABORTオプションです。

Management Studioウィンドウでの簡単なテストでは、SET ARITHABORT OFFをオンにしてクエリを実行すると、パフォーマンスの問題が再発するため、明らかに名探偵コナンであることがわかります。実際、これは Gregg Stark リンクで使用されているトラブルシューティング方法のようです。

ただし、このオプションセットを使用すると、次のことができるという事実は無視されます。まったく同じ悪い計画をキャッシュから取得することになります。

このプランの再利用は、アプリケーション接続が使用するのとは異なるユーザーとしてログインしている場合でも発生する可能性があります。

最初にWebアプリケーションから、次にSET ARITHABORT OFFを使用してManagementStudioからテストクエリを実行することでこれをテストしました。以下のクエリから使用回数が増加していることがわかりました。

SELECT usecounts, cacheobjtype, objtype, text ,query_plan FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_sql_text(plan_handle) CROSS APPLY sys.dm_exec_query_plan(plan_handle) 

この共有pfプランを実際に実行するには、すべてのプランキャッシュキーが同じである必要があります。 arithabort自体だけでなく、実行中のユーザーには同じデフォルトスキーマが必要であり（クエリが暗黙の名前解決に依存している場合）、接続には同じセット。

プランキャッシュキーの完全なリストはこちら

このパーティーに遅れていることはわかっていますが、将来の訪問者にとって、マーティンは正確に正しいです。同じ問題が発生しました。SPの実行速度が非常に遅いということです。 .NETクライアントの場合、SSMSの場合は非常に高速でした。問題の調査と解決において、KennyEvittがMartinの質問に対するコメントで尋ねる体系的なテストを行いました。

Martinのクエリでは、プロシージャキャッシュでSPを探し、そのうちの2つを見つけました。プランを見ると、実際には、1つはARITHABORT ONで、もう1つはARITHABORTOFFでした。ARITHABORTOFFバージョンにはインデックスシークがありました。 ARITHABORTONバージョンはインデックススキャンを使用しましたその同じ出力。関連するパラメータを考えると、インデックスシークでは、出力用に数千万のレコードを検索する必要があります。

キャッシュから2つのプロシージャをクリアし、.NETクライアントにSPを再度実行させました。同じパラメーター（アクティビティが多い顧客向けに幅広い日付範囲が特徴）。 SPは即座に戻りました。キャッシュされたプランは、以前にARITHABORT ONプランで取り上げられていたものと同じインデックススキャンを使用しましたが、今回のプランはARITHABORTOFF用でした。 SSMSで同じパラメーターを使用してSPを実行したところ、すぐに結果が得られました。これで、ARITHABORT ONの2番目のプランがインデックススキャンでキャッシュされたことがわかりました。

次に、キャッシュをクリアし、狭い日付範囲でSSMSでSPを実行すると、すぐに結果が得られました。結果としてキャッシュされたプランにはインデックスシークがあることがわかりました。これは、同じ出力が以前にスキャンで処理されていたためです（これは、ARITHABORT OFFの元のプランのシークでもありました）。再びSSMSから、今回は同じ広い日付範囲でSPを実行し、元の.NETリクエストと同じひどいパフォーマンスを確認しました。

要するに、格差は何の関係もありませんでした。 ARITHABORTの実際の値-オンまたはオフのどちらのクライアントからでも、許容できるパフォーマンスまたはひどいパフォーマンスを得ることができます。重要なのは、プランのコンパイルとキャッシュに使用されるパラメーター値だけでした。

MSDN は、ARITHABORT OFF自体がクエリの最適化に悪影響を与える可能性があることを示しています。テストでは、Martinが正しいことを確認しています。原因はパラメータのスニッフィングであり、結果の計画が最適ではありません。パラメータのすべての範囲に対して。

コメント

• そのフレーズSetting ARITHABORT to OFF can negatively impact query optimization leading to performance issues.が何を意味するのか疑問に思います。計算列とビューでインデックスを使用できないことについて話しているだけなのか（ANSI_WARNINGSもオフの場合）、それとも実際に他の効果があるのか。
• ‘わかりません。 ‘は、MSDNの誰かが同様の状況に遭遇し、ARTIHABORTをONに設定し、パフォーマンスの向上を確認し、他の人と同じ結論に飛びついたという単純なケースではないでしょうか。インデックス付きビューと計算列に関しては、’不明です。ある時点で、INSERT、UPDATE、またはDELETE操作がそこに格納されているデータ値を変更する場合、SETオプションには特定の値が必要であると記載されています。他の場所では、オプティマイザーは、インデックス付きビューまたは計算列を参照する”クエリ”のインデックスを無視すると述べています。両方とも正しいですか、それとも本当に”データを変更するクエリ”ですか？

この問題が発生しました。ここで人々が言ったように、根本的な原因は複数のクエリプランであり、そのうちの1つは最適ではありません。 ARITHABORTが実際にそれ自体で問題を引き起こす可能性があることを確認したかっただけです（問題が発生したクエリにはパラメーターがなく、方程式からパラメータースニッフィングが削除されるため）。

これは、SQL Server2008で発生したのとまったく同じ問題を思い出させます。私たちの場合、1つのSQLジョブが突然遅くなり（通常は数秒、現在は9分以上）、ジョブはリンクサーバーにアクセスする必要があり、ジョブのステップでsetARITHABORTを追加しました。問題は数日間解決されてから戻ってきました。

後でMSサポート付きのチケットを開きましたが、最初はどちらも見つかりませんでした。チケットは非常に上級のPFEチームにエスカレーションされました。サポートPFEはこの問題を理解しようとしました。

最後の理由は、（ジョブステップを実行するための）ユーザー資格情報が（リンクサーバー側の）基になるテーブルの統計にアクセスできないため、実行プランが最適化されないことです。

詳細には、ユーザーには DBCC SHOW_STATISTICS に対する権限がありません（ただし、ユーザーはから選択できます）テーブル）。 MSDN によると、このアクセス許可ルールはsql 2012SP1の後に変更されます

SQLServerおよびSQLデータベースの権限

統計オブジェクトを表示するには、ユーザーがテーブルを所有している必要がありますまたは、ユーザーはsysadmin固定サーバーロール、db_owner固定データベースロール、またはdb_ddladmin固定データベースロールのメンバーである必要があります。

SQL Server 2012SP1はアクセス許可の制限を変更します。 SELECT権限を持つユーザーがこのコマンドを使用できるようにします。コマンドを実行するのに十分なSELECT権限には、次の要件があることに注意してください。

この問題を確認するには、リンクサーバー側のインスタンスでプロファイラーを実行し、「エラー」のいくつかのイベントをオンにする必要があります。および警告」セクションを以下に示します。

この経験がコミュニティに何らかの形で役立つことを願っています。