Azureはじめました

Windows Azureで業務システムを組んでみる日記

ストアドプロシージャがアプリケーションから実行すると遅いのにSSMSからだと高速なアレ

10.hateblo.jp

ローカルのSSMS*1から直接クエリを実行すると普段通りのパフォーマンスが出てる。クエリキャッシュを考えてランダムのパラメータでロードしても速度は普通。

これどういった話なんだろうと調べた結果そのものずばりな記事があった。

When I read various forums about SQL Server, I frequently see questions from deeply mystified posters. They have identified a slow query or stored procedure in their application. They cull the SQL batch from the application and run it in SQL Server Management Studio (SSMS) to analyse it, only to find that the response is instantaneous. At this point they are inclined to think that SQL Server is all about magic. A similar mystery is when a developer has extracted a query in his stored procedure to run it stand-alone only to find that it runs much faster – or much slower – than inside the procedure.

Slow in the Application, Fast in SSMS?

(;´д｀) しかしなんちゅう読みにくい英語だ…
(そのうち訳すつもり)


ざっくり要約すると、

アプリケーションで使うADO.net ODBC OLEDBなんかはARITHABORTオプションがデフォルトでOFF SSMSはデフォルトでONだからおんなじクエリでも違うクエリプランキャッシュが使われて比較できねーよバーカバーカ
f:id:twisted0517:20151021174644p:plain

という事らしい。

ARITHABORTって何か

MSDNから引く

SET ARITHABORT (Transact-SQL)

SET ARITHABORT (Transact-SQL)
クエリ実行中にオーバーフローまたは 0 除算のエラーが発生した場合に、クエリを終了します。

説明

ログオン セッションでは、ARITHABORT を常に ON に設定する必要があります。 ARITHABORT を OFF に設定すると、クエリ最適化に悪影響を与え、パフォーマンスに関する問題が発生する可能性があります。

注意
SQL Server Management Studio の ARITHABORT の既定値は ON です。 ARITHABORT が OFF に設定されているクライアント アプリケーションは異なるクエリ プランを受け取り、パフォーマンスに問題のあるクエリのトラブルシューティングが困難になる場合があります。 つまり、同じクエリでも Management Studio 内では高速実行できますが、アプリケーション内では実行速度が低下する可能性があります。 トラブルシューティングを行うとき、Management Studio を使用するクエリは常に、クライアントの ARITHABORT 設定と一致します。

SET ARITHABORT を ON にし、SET ANSI WARNINGS も ON にした場合、このエラー状態が発生するとクエリが終了します。

SET ARITHABORT を ON にし、SET ANSI WARNINGS を OFF にした場合、このエラー状態が発生するとバッチが終了します。 エラーがトランザクション内で発生した場合、トランザクションはロールバックされます。 SET ARITHABORT が OFF の場合に、次に示すいずれかのエラーが発生すると、警告メッセージが表示され、算術演算の結果には NULL 値が割り当てられます。

SET ARITHABORT が OFF で SET ANSI WARNINGS が OFF の場合に次に示すいずれかのエラーが発生すると、警告メッセージが表示され、算術演算の結果に NULL が割り当てられます。

注意
SET ARITHABORT および SET ARITHIGNORE の両方とも設定しない場合、SQL Server では NULL 値が返され、クエリの実行後に警告メッセージが返されます。

ANSI_WARNINGS を ON に設定すると、データベース互換性レベルが 90 以上に設定されている場合、暗黙的に ARITHABORT が ON に設定されます。 データベース互換性レベルが 80 以下に設定されている場合、ARITHABORT オプションを明示的に ON に設定する必要があります。

SET ARITHABORT を OFF に設定して式を評価中に、INSERT、DELETE、または UPDATE ステートメントで算術演算エラー、オーバーフロー、0 除算、またはドメイン エラーが検出されると、SQL Server では NULL 値が挿入または更新されます。 出力先の列で NULL 値が許容されない場合は、挿入または更新処理は失敗し、エラーが返されます。

SET ARITHABORT と SET ARITHIGNORE のいずれかが OFF でも、SET ANSI_WARNINGS が ON の場合は、SQL Server で 0 除算やオーバーフロー エラーが検出されるとエラー メッセージが返されます。

SET ARITHABORT を OFF に設定し、IF ステートメントのブール条件の評価中に中止エラーが発生すると、FALSE の分岐が実行されます。

計算列やインデックス付きビューのインデックスを作成または変更するときには、SET ARITHABORT を ON に設定する必要があります。 SET ARITHABORT が OFF の場合、計算列にインデックスが設定されているテーブルやインデックス付きビューに対して CREATE、UPDATE、INSERT、または DELETE ステートメントを実行すると失敗します。
SET ARITHABORT は、解析時ではなく実行時に設定されます。

ARITHABORT = OFFの状態だとクエリ中のInsert/updateでゼロ除算/オーバーフローが発生する可能性を考慮する(=nullデータが格納される)ので、最適化によってインデックスの統計がnullableで行われる結果望ましくない最適化がなされる、 みたいな話かな?

ということは

アプリケーション側の接続でもARITHABORTをONにすれば良いのか?


参考