SE の雑記

昔々に途中前で書いて挫折したメモが出てきたので投稿しておきたいと思います。
まだまだいろいろと書くとはあるのですが続きは書かないです…。

元ネタはこちら

SQL Server 2005 でのパフォーマンス問題のトラブルシューティング
http://technet.microsoft.com/ja-jp/library/cc966540.aspx
http://download.microsoft.com/download/c/9/8/c985ab71-1cf5-40b4-bc2a-ea8f9555fb3e/TShootPerfProbs.doc

Troubleshooting Performance Problems in SQL Server 2008
http://msdn.microsoft.com/en-us/library/dd672789(v=sql.100).aspx
http://download.microsoft.com/download/D/B/D/DBDE7972-1EB9-470A-BA18-58849DB3EB3B/TShootPerfProbs2008.docx

■パフォーマンス取得の際に使用するツール

---

• パフォーマンス モニター

• SQL Server Profiler

• DBCC コマンド
• DMV (動的管理ビュー)
• Extended Events (拡張イベント)
• データコレクタ

• PSSDiag

http://support.microsoft.com/kb/830232/ja

• SQLDiag

http://msdn.microsoft.com/ja-jp/library/ms162833.aspx

• SQL Nexus

http://sqlnexus.codeplex.com/

• SQL Live Monitor

http://sqlmonitor.codeplex.com/

• Performance Analysis of Logs (PAL) Tool

http://pal.codeplex.com/

 

■CPU ボトルネック

---

パフォーマンス モニターで Processor:% Processor Time の状態を確認

CPU 使用率が 80% を超えている場合、CPU の使用率が高い状態と言える。

sys.dm_os_wait_stats から SOS_SCHEDULER_YIELD を確認し、複数のタスクが実行されていることによる、スケジューラーの解放待ちが発生していないかを確認するのが重要。

クエリーによる CPU の負荷状況を確認するためには、sys.dm_exec_query_stats の execution_count と total_worker_time の値に注視する。

実行回数が多く、CPU の負荷が高いクエリをチューニングすることで、全体の CPU 負荷を減らせる可能性がある。

 

■クエリコンパイルと解析

---

SQL Server はクエリコンパイル時にクエリ / プランのハッシュが設定される。

これらのハッシュに関しては、sys.dm_exec_query_stats の query_hash/query_plan_hash から確認をすることが可能。

ハッシュに関しては実行プランからも確認することが可能。

クエリハッシュ (query_hash) はリテラル (クエリのコンテキスト部) を除いたものを元に生成される。そのため、以下の 2 つのクエリのハッシュは同一となる。

select * from sys.objects where object_id = 200

select * from sys.objects where object_id = 100

これらのクエリは同一のハッシュ値を持つが、sql_handle としては異なるトークンが割り当てられている。そのため、別のクエリテキストとしてキャッシュがされている。

同様にプランにもコンパイル時に設定されるハッシュがあり、それがプランハッシュ (query_plan_hash) となる。

先ほどの 2 つのクエリは生成された実行プランは同一のため、プランハッシュも同一となる。

ただし、plan_handle は異なるトークンが割り当てられている。

そのため、同一の実行プランを持っているが別のプランとしてキャッシュがされている。

このようなクエリがキャッシュされている場合、同一のクエリハッシュ / プランハッシュを持つクエリが複数メモリ上にキャッシュされていることになる。

同一のクエリハッシュを持つクエリをパラメータ化クエリまたはストアドプロシージャ化し、一つのキャッシュとすることで、メモリの効率的な利用が可能となる。

強制パラメーター化

クエリプランを元にクエリの実行回数や CPU の使用時間を見る場合は以下のクエリで確認することが可能。

以下のクエリでは、plan_generation_num を取得する事で後述のリコンパイルにより実行プランがどの程度再生成されているのかも取得している。

select text, query_plan, execution_count, total_worker_time, plan_generation_num, query_hash, query_plan_hash from sys.dm_exec_query_stats cross apply sys.dm_exec_sql_text(sql_handle) cross apply sys.dm_exec_query_plan(plan_handle) order by sys.dm_exec_query_stats.execution_count desc

sys.dm_exec_cached_plans を使用することで、同様の情報を成型することが可能であるが、この場合 CPU の使用状況については確認をする事ができない。

 

■クエリのリコンパイル

---

既に実行しているプランを再生成する必要が発生した場合、クエリのリコンパイルが発生する。

リコンパイル中は COMPILE ロックが発生するため同時実行性の低下につながる。

リコンパイルが発生するタイミングとしては

• スキーマの変更
• 実行プランで使用しているインデックスの変更

• sp_recompile による手動リコンパイル

• 参照されるテーブルに大量の変更があった場合

• 統計情報の更新
• 遅延コンパイル
• SET オプションの変更
• 一時テーブルの変更

• ストアドプロシージャ作成時に WITH RECOMPLIE オプションを指定

• クエリヒントとして、RECOMPILE オプションを指定

等がある。

SQL Server 2005 以降はストアドプロシージャ内のステートメントでクエリヒントとして、RECOMPILE を指定した場合は、ストアドプロシージャ全体ではなく、対象のクエリのみがステートメントレベルでリコンパイルされることになる。

クエリのコンパイル / リコンパイルの発生状況に関しては、パフォーマンスモニターで取得する事が可能である。

• SQL Statistics: SQL Compilations/sec

• SQL Statistics: SQL Compilations/sec

でコンパイル / リコンパイルの発生状況を確認することが可能。

SQL Statistics: Batch Requests/sec と合わせて情報を見比べることで、バッチの実行に合わせてクエリのコンパイルが発生しているのかを確認することが可能である。

SQL Server プロファイラを使用することでリコンパイルに注視して状況を把握することが可能となる。

• TSQL SQL:StmtRecompile
• Sotred Procedures SP:Recompile

StmtRecompile ではストアドプロシージャのステートメントレベルのリコンパイルに関しての情報を取得する事が可能である。

# アドホッククエリのリコンパイルも取得可能

リコンパイルの原因に関しては、EventSubClass を参照することで調査することが可能。

プランに関するクエリヒントとして KEEP PLAN/KEEPFIXED PLAN があり、再コンパイルの頻度 (実行プランの再生成) を多少調整することが可能となる。

また、オブジェクト修飾のあいまいさを回避するために完全修飾で実行することで再コンパイルの低減につながることもある。

SQL Server の compile ロックが発生したブロックの説明

http://support.microsoft.com/kb/263889/ja

 

■非効率なクエリプラン

---

ハッシュ/ソート操作は CPU/IO コストを高める要因となりえる。

CPU の使用率(sys.dm_exec_query_stats.total_worker_time) が高いクエリの実行プランで Hash Match/Sort 操作が行われていないかを確認することが重要となる。

以下のクエリでプランの中に特定の操作が含まれているかを確認することが可能である。

select text, query_plan, execution_count, total_worker_time, plan_generation_num, query_hash, query_plan_hash from sys.dm_exec_query_stats cross apply sys.dm_exec_sql_text(sql_handle) cross apply sys.dm_exec_text_query_plan(plan_handle, 0, -1) where query_plan LIKE ‘%Hash Match%’ order by sys.dm_exec_query_stats.execution_count desc

sys.dm_exec_query_plan で XML をパースしても取得する事が可能である。

特定の実行コンテキストが指定された状態の実行プランを使用したい場合には、クエリヒントで OPTIMIZE FOR を指定することで、初回に指定された実行コンテキストに依存しないプランを作成することが可能である。

クエリヒントに USE PLAN を指定することで特定のプランを強制することも可能である。

また、プランガイドを使用することで特定のクエリに対して実行プランを固定化することを既存のクエリを修正することなく後から関連付けすることも可能である。

 

■並列プランを使用したクエリの実行

---

SQL Server では複数の CPU を使用した並列プランを使用してクエリを実行することが可能である。

並列プランを使用するかの閾値はサーバーレベルで設定することが可能であり、cost threshold for parallelism オプションで指定を行う。

上記オプションはデフォルトでは 5 となっており、直列プランを実行するために必要な予測所要時間が 5 秒を超えた場合は、並列プランが使用される。

並列プランで使用する CPU は max degree of parallelism オプションを指定することで制御することが可能である。

また、MAXDOP クエリヒントを使用することでクエリレベルでも制御することが可能である。

デフォルトの設定では、max degree of parallelism は 0 に設定されているため、全 CPU を使用した並列プランが生成される。

並列プランで CPU を複数使されている状態で、他のクエリが実行され CPU の使用要求が発生した場合、CPU が身近な時間でタイムスライスされ共有されながら処理が実行されることになる。

そのため、制限をせずに複数の CPU を使用した並列プランでクエリが実行された場合、単一のクエリの応答時間は最速となるかもしれないが、CPU が共有される可能性が高くなるため、他のクエリの処理時間が圧迫され全体のスループットが低くなる可能性がある。

並列クエリを使用する場合、全体のトランザクションを考慮し並列プランで使用可能な CPU の数を設定していく必要がある。

# 並列プランでの実行は処理時間を短くするために必要となるため、並列プランで実行されること自体が悪いわけではない。

並列クエリで実行されているかの検出は実行プランの Parallel が 0 より大きいクエリが含まれているかで確認をすることが可能である。