SQLServerのテーブル値関数(Table Valued Function:TVF)のパフォーマンスが突然悪化する件
やめて!
障害は突然に
安定稼働してたサービスのユーザーから一部の機能が急に遅くなったとクレームが。
試してみると確かに遅い。
Webサービス側を再起動や再デプロイしてみても状況は変わらず。
更にローカルの開発環境からSQLAzureに接続しても同様の現象が出た。
( ﾟДﾟ) あぁこれSQLAzure側や…
更に調査すると
- SQLAzureのパフォーマンスモニタを確認するとCPUPercentage と DTIPercentageが高い値で推移してる。
- ローカルのSSMS*1から直接クエリを実行すると普段通りのパフォーマンスが出てる。クエリキャッシュを考えてランダムのパラメータでロードしても速度は普通。
- データをローカルのSQLServerに同期してローカルで実行すると普段通りの速度が出てる。
- 問題になっている機能はEFを使用せず、テーブル値ユーザー関数を使用している。
- EFでデータを扱っている部分は通常通りのパフォーマンス。
- 別のSQLAzureサーバー建ててデータ移行してみるとこちらも通常通りのパフォーマンス
- ロックや待機は発生してない。
(; ﾟДﾟ) …もうこれわかんねぇな
ヒント?発見
いろいろ検索してると似たような現象を発見した。
ASP.NET + SQL Server2005で作成したアプリケーションで特定のストアドプロシージャを呼ぶとかなりの頻度でタイムアウトが発生していました。
ところが、SQL Server Management Studioで実行すると、数秒で戻ってきます。
Web.configのConnectionStringや TransactionScopeのタイムアウトを設定してみても改善しませんでした。そこで問題のあるストアドプロシージャを調べてみると、どれもテーブル変数に集計結果を保存していることがわかりました。
tauchi.net: テーブル変数は遅い？
このコメント欄
匿名 さんのコメント...
クエリプランのキャッシュが原因の気がしますが…
リコンパイルオプションを参考
2008年5月4日 3:38
( ･`ω･´) クエリプランキャッシュ!!
クエリプランキャッシュ
SQL Server の実行プランは、主に次の要素から構成されます。
- クエリ
プラン 実行プランの大部分は、任意の数のユーザーが使用できる再入可能な読み取り専用のデータ構造体です。これをクエリ プランといいます。クエリ プランにはユーザー コンテキストは格納されません。また、メモリに複数のクエリ プランのコピーが配置されることはありません。すべての直列実行に 1 つのコピーが使用され、すべての並列実行に 1 つのコピーが使用されます。並列実行用コピーは、並列処理の次数に関係なくすべての並列実行に適用されます。- 実行コンテキスト
クエリを現在実行しているユーザーごとに、パラメーター値など、実行に固有のデータを保持するデータ構造体が用意されています。このデータ構造体を実行コンテキストといいます。実行コンテキストのデータ構造体は再利用されます。ユーザーがクエリを実行したときに使用されていない構造体が 1 つある場合、新しいユーザーのコンテキストでその構造体が再初期化されます。実行プランのキャッシュと再利用
SQL Server で任意の SQL ステートメントを実行すると、まずリレーショナル エンジンにより、プロシージャ キャッシュが調査され、同じ SQL ステートメントの既存の実行プランが存在するかどうかが確認されます。検出された既存のプランは SQL Server によって再利用されます。これにより、SQL ステートメントを再コンパイルする際のオーバーヘッドが少なくなります。既存の実行プランが存在しない場合、SQL Server によってクエリの新しい実行プランが生成されます。
2. 実行プランを作成し直す
パラメータクエリ、ストアドプロシージャに有効です。 実行プラン生成時に使用されたパラメータ値が特殊だった場合、大半のパラメータ値にとっては最適な実行プランになっていないことがあります。 実行プランを作成し直すことによって、より適した実行プランが生成される可能性があります。
どうする？ SQL Server のクエリ パフォーマンスが低下した！ - Microsoft SQL Server Japan Support Team Blog - Site Home - MSDN Blogs
実行プランが無効になるのは、次の場合です。
実行プランのキャッシュと再利用
- クエリ (ALTER TABLE および ALTER VIEW) によって参照されるテーブルまたはビューに変更を加えた場合
- 実行プランで使用されるインデックスに変更を加えた場合
- UPDATE STATISTICS などのステートメントを使用して明示的に生成した実行プラン、または自動的に生成された実行プランによって使用される統計を更新した場合
- 実行プランで使用されるインデックスを削除した場合
- sp_recompile を明示的に呼び出した場合
- クエリによって参照されるテーブルを変更する他のユーザーが、INSERT ステートメントまたは DELETE ステートメントを使用して大量の変更をキーに加えた場合
- トリガーを含むテーブルで、inserted テーブルまたは deleted テーブルの行数が大幅に増加する場合
- WITH RECOMPILE オプションを使用してストアド プロシージャを実行する場合
sp_recompileで試せばよかったんだけど、先走ってALTER FUNCTIONで関数再コンパイルしたらパフォーマンスは通常に戻った。
しかし解せぬ
クエリプランキャッシュの説明を見る限りクエリの実行速度の最適化に作られてる感じあるんだけど、MVCから実行した時は遅くてSSMSで実行した時は早かったってのが引っかかる。
結果データのTVFの読み込みで遅いのかもとは思うけど、問題となったクエリの戻り値は高々30行程度だからそういう事じゃ無い気がする。
さらに言えば、この問題が発生しないようにするにはTVFを使わないって事になりかねんぐらい根治方法が見当たらないのが解せない。
ぬう。
参考
- ビューとストアドプロシージャの強力な代替手段「ユーザー定義関数」 - インターネットコム
- sql server - Query becomes drastically slower when wrapped in a TVF - Database Administrators Stack Exchange
- Tibor Karaszi : Execution plan re-use, sp_executesql and TSQL variables
- どうする？ SQL Server のクエリ パフォーマンスが低下した！ - Microsoft SQL Server Japan Support Team Blog - Site Home - MSDN Blogs
- DBCC FREEPROCCACHE (Transact-SQL) (SQLAzureでは権限が足りないので使えない)
- 蒼の王座SQL Azureパフォーマンス改善手段 - 蒼の王座
- 蒼の王座SQL Azure上のプロシージャキャッシュについて理解する - 蒼の王座
- Query Store: A flight data recorder for your database | Microsoft Azure Blog
*1:SQL Server Management Studio
