Comments
Transcript
インサイドMicrosoft SQL Server 2005 クエリチューニング
■ この電子書籍に関する Web サイトによる情報提供について この電子書籍に関するご質問方法や訂正情報は、最終ページに記載した Web ページ をご参照いただくようお願いいたします。本文「はじめに」などに記載している Web ページや FAX 番号は古い情報ですので、ご使用にならないようお願いいたします。 Inside Microsoft SQL Server 2005 : Query Tuning and Optimization © 2008 by Microsoft Corporation. All rights reserved. Original English language edition © 2007 by Kalen Delaney. All rights reserved. Published by arrangement with the original publisher, Microsoft Corporation, Redmond, Washington, U.S.A. Microsoft、Microsoft Press, MSDN, MSN, SQL Server, Visual Studio, Windowsは米国および/または他の国 のMicrosoft Corporationの登録商標または商標です。その他の社名および製品名は、それぞれの会社の 商標または登録商標です。なお、本文中には、™、®マークは明記しておりません。 本書の例題または画面で使用している会社名、氏名、ほかのデータは、すべて架空のものです。 ※ 本書は日経BPソフトプレスが、米国Microsoft Corporationとの契約により、 「Inside Microsoft SQL Server 2005 : Query Tuning and Optimization」（ISBN 978-0-7356-2196-1）を翻訳したものです。 v 目 次 序文 .....................................................................................................................................ix 謝辞 .....................................................................................................................................xi はじめに ..............................................................................................................................xv 第1章 パフォーマンスのトラブルシューティング法 1 1.1 パフォーマンスに影響を与える要素 ...............................................................................2 1.1.1 アプリケーションのアーキテクチャ ...................................................................2 1.1.2 アプリケーションのデザイン.............................................................................5 1.1.3 トランザクションと分離レベル..........................................................................8 1.1.4 Transact-SQLコード .................................................................................12 1.1.5 ハードウェアリソースの選定 ..........................................................................14 1.1.6 SQL Serverの構成 ......................................................................................14 1.2 トラブルシューティングの概要 ...................................................................................17 1.2.1 ワークロードに対するベースラインの作成 ......................................................18 1.2.2 ワークロードの監視 .......................................................................................22 1.2.3 よく発生するパフォーマンス問題の発見、トラブルシューティングと分離 ........24 1.3 まとめ .......................................................................................................................66 第2章 トレースとプロファイル 69 2.1 SQLトレースのアーキテクチャと用語........................................................................70 2.1.1 内部トレースコンポーネント ..........................................................................70 2.1.2 トレースI/Oプロバイダ..................................................................................71 2.2 セキュリティと権限 ...................................................................................................72 2.2.1 ALTER TRACE権限....................................................................................72 2.2.2 センシティブなイベントデータの保護 .............................................................73 2.3 はじめに：プロファイラ.............................................................................................73 2.3.1 プロファイラの基本 .......................................................................................74 2.3.2 トレースの保存と再生 ....................................................................................78 2.4 サーバー側のトレースとコレクション .........................................................................83 2.4.1 サーバー側トレースのスクリプト....................................................................83 2.4.2 サーバー側トレースメタデータのクエリ..........................................................87 2.4.3 サーバー側トレースからのデータの取得..........................................................89 2.4.4 トレースの停止と終了 ....................................................................................90 2.4.5 行セットプロバイダの調査 .............................................................................91 vi 目次 2.5 トレースによるトラブルシューティングと分析 ............................................................94 2.5.1 よく使われるSQLトレースのイベントクラス..................................................94 2.5.2 パフォーマンスのチューニング .......................................................................97 2.5.3 例外の識別..................................................................................................103 2.5.4 デッドロックのデバッグ ..............................................................................106 2.5.5 ストアドプロシージャのデバッグ .................................................................110 2.6 トレースについての考慮すべき事項とデザイン .........................................................113 2.6.1 SQL Server Profilerの問題点..................................................................114 2.6.2 トレースオーバーヘッドの最小化.................................................................115 2.6.3 ファイルの最大サイズ、ロールオーバー、データコレクション ......................116 2.7 監査： SQL Serverのビルトイントレース..............................................................117 2.7.1 既定トレース ..............................................................................................117 2.7.2 ブラックボックストレース...........................................................................118 2.7.3 C2監査とCommon Criteria監査..............................................................119 2.8 まとめ.....................................................................................................................120 第3章 クエリの実行 121 3.1 クエリの処理と実行の概要.......................................................................................121 3.1.1 イテレータ（反復子）...................................................................................122 3.1.2 イテレータのプロパティ ..............................................................................123 3.2 クエリプランの読み方 .............................................................................................126 3.2.1 クエリプランのオプション...........................................................................126 3.3 クエリプランの分析.................................................................................................135 3.3.1 スキャンとシーク ........................................................................................135 3.3.2 シーク可能な述語と対応列(カバーされる列).................................................138 3.3.3 ブックマーク参照 ........................................................................................141 3.3.4 結合............................................................................................................144 3.3.5 集計............................................................................................................159 3.3.6 ユニオン .....................................................................................................174 3.3.7 高度なインデックス操作 ..............................................................................179 3.3.8 サブクエリ..................................................................................................189 3.3.9 クエリの並列処理........................................................................................205 3.3.10 挿入、更新、削除処理 ..............................................................................225 3.4 まとめ.....................................................................................................................227 第4章 クエリパフォーマンスのトラブルシューティング 229 4.1 コンパイルと最適化.................................................................................................229 4.1.1 コンパイル..................................................................................................230 4.1.2 最適化 ........................................................................................................231 4.1.3 クエリオプティマイザの内部動作.................................................................233 4.2 実行プランの問題の検出 ..........................................................................................250 目次 vii 4.2.1 統計情報の基数推定(カーディナリティ)エラー .............................................251 4.2.2 その他の警告のサイン .................................................................................252 4.3 クエリパフォーマンスの監視....................................................................................254 4.3.1 STATISTICS TIME（実行時間とCPU使用時間）......................................259 4.4 クエリの性能改善 ....................................................................................................259 4.4.1 クエリの書き直し........................................................................................260 4.4.2 スキーマの改善（DDL）..............................................................................262 4.4.3 統計情報の管理 ...........................................................................................263 4.4.4 有効なインデックスの作成...........................................................................266 4.4.5 SQL Server 2005におけるオプティマイザへのヒント句..........................276 4.5 クエリ処理のベストプラクティス .............................................................................314 4.5.1 結果セット志向プログラミングを使用する ...................................................315 4.5.2 オプティマイザに正確な検索範囲と統計情報を与える...................................315 4.5.3 不必要な複雑さを避ける..............................................................................316 4.5.4 動的に生成するT-SQLに注意する ...............................................................316 4.6 まとめ.....................................................................................................................317 第5章 プランのキャッシュとリコンパイル 319 5.1 プランキャッシュ ....................................................................................................319 5.1.1 プランキャッシュのメタデータ ....................................................................320 5.1.2 プランキャッシュのクリーンアップ..............................................................320 5.2 キャッシュのメカニズム ..........................................................................................321 5.2.1 アドホッククエリのキャッシュ ....................................................................322 5.2.2 自動パラメータ化クエリ ..............................................................................324 5.2.3 プリペアードクエリ.....................................................................................330 5.2.4 コンパイル済みオブジェクト .......................................................................333 5.2.5 プランがリコンパイルされる理由.................................................................336 5.3 プランキャッシュの内部構造 ...................................................................................345 5.3.1 キャッシュストア ........................................................................................346 5.3.2 コンパイル済みプラン .................................................................................347 5.3.3 実行プラン..................................................................................................348 5.3.4 プランキャッシュのメタデータ ....................................................................348 5.3.5 ハンドル .....................................................................................................349 5.3.6 sys.dm_exec_sql_text ..........................................................................350 5.3.7 sys.dm_exec_cached_plans................................................................351 5.3.8 sys.dm_exec_cached_plan_dependent_objects.............................352 5.3.9 sys.dm_exec_requests ........................................................................353 5.3.10 sys.dm_exec_query_stats ................................................................353 5.3.11 キャッシュサイズの管理 ...........................................................................354 5.3.12 ローカルメモリ不足による圧迫 .................................................................356 5.3.13 キャッシュエントリのコスト.....................................................................357 viii 目次 5.4 プランキャッシュ内のオブジェクト：全体像 ............................................................358 5.5 キャッシュ内に存在する複数のプラン ......................................................................361 5.6 ストアドプロシージャの使用と他のキャッシュメカニズムの使用...............................362 5.7 プランキャッシュに関する問題のトラブルシューティング.........................................362 5.7.1 プランキャッシュの問題を示すWait Statistics 情報 ................................363 5.7.2 キャッシュに関する他の問題 .......................................................................365 5.7.3 キャッシュのトラブルシューティングとリコンパイルについてのまとめ .........366 5.7.4 プランガイドとオプティマイザへのヒント句 ................................................369 5.8 まとめ.....................................................................................................................382 第6章 同時実行の問題 383 6.1 同時実行をトラブルシューティングするための新しいツール .....................................384 6.1.1 新しいブロッキング検出ツール：sys.dm_os_waiting_tasks ..................385 6.1.2 新しいブロッキング解決ツール：行のバージョン管理に基づく分離レベル .....385 6.1.3 同時実行に関する問題の種類 .......................................................................386 6.2 ロックのトラブルシューティング .............................................................................387 6.2.1 ロックメモリのトラブルシューティング .......................................................387 6.2.2 ロックのタイムアウト .................................................................................388 6.2.3 ロックエスカレーション ..............................................................................388 6.3 ブロッキングのトラブルシューティング ...................................................................392 6.3.1 ブロッキングの問題の検出...........................................................................394 6.3.2 ブロッキングの原因の発見...........................................................................402 6.3.3 ブロッキングの問題の解決...........................................................................405 6.4 デッドロックのトラブルシューティング ...................................................................409 6.4.1 デッドロックの種類.....................................................................................410 6.4.2 デッドロックの検出.....................................................................................415 6.4.3 デッドロックの原因の特定...........................................................................416 6.4.4 デッドロックの解決方法 ..............................................................................421 6.5 行のバージョン管理に基づくスナップショット分離レベルのトラブルシューティング .427 6.5.1 スナップショット分離レベルによる共有（S）ロックの問題..........................428 6.5.2 フルSNAPSHOT分離レベルの競合の可能性 ..............................................437 6.5.3 スナップショット分離オプションの監視 ....................................................445 6.5.4 スナップショット分離問題の解決.................................................................449 6.5.5 スナップショット分離の適切な使用 ..........................................................452 6.6 まとめ.....................................................................................................................454 補足と参照 ......................................................................................................................455 著者紹介..........................................................................................................................459 索引 ................................................................................................................................461 ix 序文 リレーショナルデータベース管理システム（RDBMS）が研究プロジェクトやかろ うじて利用できる初期製品の段階から、現在見られるような商用アプリケーションで のデータの保存とアクセスの主要手段へと移行を始めて以来、20年が経ちました。こ れら初期のシステムに対するチューニングはもっぱらシステムリソースに向けられた ものであり、システムリソースの量を選択することを主体に、ストレージを割り当て、 インデックスを定義しました。この単純さが強みになると考えられることが多い一方 で、アプリケーションのこれらのシステムの使用法をチューニングや最適化できない ために、非常に要求の厳しいアプリケーションで使用されるには限界がありました。 今日のSQL Server 2005は2つの世界の最善のものを提供します。多くのアプリケー ションが書かれるときにこれまでどおりチューニングと最適化に当てられる労力がほ とんどあるいはまったく必要ではない世界と、同時に、最も要求の厳しいアプリケー ションをサポートするための拡張的なチューニングと最適化の便利な機能が提供され ている世界です。読者が現在、新しい高性能SQL Server 2005アプリケーションを作 成している立場であれ、既存のアプリケーションがなぜ期待どおりに実行できないの かを見つけ出そうとしている立場であれ、本書は必ず役立ちます。 本書のルーツはSQL Server 6.5からSQL Server 7.0への移行のときにあります。 SQL Server 6.5の非常に単純なクエリプロセッサが最先端の設計に入れ替えられた ことによって、チューニングと最適化を論じる場合、クエリ処理が中心課題になりま した。可能な実行プランの数は飛躍的に増加し、データベースシステムが優れた統計 を維持できる重要性が劇的に増え、クエリがコンパイルされキャッシュされた方法は データベースアプリケーションプログラマやデータベース管理者にとって突然に重要 になりました。パフォーマンス問題を特定し解決するための新しいツールがSQL Server 7.0で導入され、SQL Server 2000ではこれが強化され、SQL Server 2005で 再びさらに強化されました。インデックスビューなどの新しいクエリ処理ベースの機 能がSQL Serverに追加され、さらにトリガ処理における挿入テーブルや削除テーブ ルなどの多くの既存の機能が移植されて従来のストレージエンジンベースのメカニズ ムではなくクエリ処理が使用できるようになりました。今回、プランガイドや新しい ツール（動的管理ビューなど）のようなSQL Server 2005の新機能とともに、クエリの チューニングと最適化がそれだけで1冊の本として書かれることになったのは本当に喜 ばしいことです。 x 序文 Server 2005のリリースによって、Microsoft SQL Serverは3回のリリース改革を完 了して、純粋に部門のサーバーから、組み込みアプリケーションのためのストレージ から最も要求の厳しいエンタープライズ要求までのすべてを処理することのできる製 品へと移行しました。筆者のKalen Delaneyは、Microsoft SQL Serverを用いたシス テムを構築するデータベースプロを育成するうえで、重要なパートナーでした。彼女 は通常はコンピュータサイエンスph.Dの領域にある教材を取り上げ、データベースア プリケーションプロの人たちに対してそのような教材を提供することに秀でた才能を 持っています。Kalenはかって我々が『Inside Microsoft SQL Server 7.0』を執筆し ているときに我々のチームと親密に関わって作業しました。このため、クエリプロセ ッサ内部についての私のTechEdプレゼンテーションをMSDNアーティクルへと変更 するための協同執筆者が必要になったときに、私は彼女を迷わず選びました。そのと きの資料に最新の改訂を加えたものが、本書の「第5章 プランのキャッシュとリコ ンパイル」の中に生かされています。 クエリ処理がデータベースアプリケーションパフォーマンスで大きな役割を果たす 現在、そして、高いパフォーマンスが多くのデータベースアプリケーションに対して 優先される現状では、本書は私がすべてのMicrosoft SQL Serverプロに強く勧める 本です。 Hal Berenson Microsoft社 上級エンジニア xi 謝辞 いつも思うことですが、本書のような作品は個人の作業では決して成り立ちません。 本書の場合、これは特に言えることです。今回の執筆にあたり4人のSQL Server専門 家が参加してくれたことを誇りに思っています。実際に私一人では本書を完成させる ことはできなかったでしょう。本書を現実のものするために力を貸してくれたSunil Agarwal、Craig Freedman、Adam Machanic、Ron Talmageの4氏に心からお礼 申し上げます。優れた共同執筆者を得ることができたことに加え、多くの人たちの支 援と励ましをいただいて本書は完成の運びとなりました。 私がまずお礼を言いたいのは読者諸氏です。私がこれまで執筆したものを読んでも らえたことに何度お礼を言っても足りないでしょう。書籍についての感想やSQL Serverについて学びたいことを私に伝えるために時間を費やしてくれた人たちに感謝 します。どの質問にも詳しく答えることができるといいのですが、完璧な答えを送る ことができないとしても、読者から寄せられた情報のすべてが大いに参考になりまし た。私の前作『Inside Microsoft SQL Server : The Storage Engine』 （翻訳書は『イ ンサイド Microsoft SQL Server 2005 ストレージエンジン編』 、日経BPソフトプ レス刊）を読んでくれた1人の特別な読者にはとりわけ感謝します。私は優れた洞察 力のある読者としてBen Nevarez氏と親しくなりました。彼はまだ隠れているエラー や微妙な矛盾をいくつか見つけ、私のWebサイトを通して丁寧にそして簡潔にそれら について知らせてくれました。数十回のeメールの後、私はBenのeメールを楽しみに 待つようになり、2006年11月、PASSコンファレンスで会うことができたときはとて もうれしく感動しました。そして今回、Benは本書のテクニカル担当編集者の1人とな りました。1つ1つの章を非常に注意深く読んでくれたBenに心から感謝します。 Ron Soukup氏は『Inside SQL Server』の初版を執筆し、後続版を引き受ける私に 「 承 認 証 」 を 与 え て く れ た 人 で す 。 彼 に 感 謝 し ま す 。『 Inside SQL Server Professional Journal』の前編集者Karen Watterson氏にも感謝します。Karenは私 に多くチャンスを与えてくれました。その1つはMicrosoft Pressのすばらしい人たちに 私の名前を紹介してくれたことです。 いつものことながら、Microsoft社のSQL Serverチームには畏敬の念を抱かざるを えません。Lubor Kollar氏は今回、私のクエリプロセッサ研究の多くに直接は参加し ていませんが、精神的に私を支えてくれた心強い存在であり、私と顔を会わせるとい つも励ましの言葉をかけてくれました。Sunil Agarwal氏はStorage Engine執筆の ときと同じように本書の開発中ずっと刺激やひらめきを投げかけ、執筆者たちから出 る問題や質問を解決する上で私が適切な人たちを見つけ出せるように骨折ってくれま xii 謝辞 した。さらに、Sunil氏は本書の第1章の執筆を担当しています。その困難な仕事に対 し彼に深く感謝します。 Stefano Stefani氏とSangeetha Shekar氏は私と会う機会を作ってくれ、eメール で私の疑問に答えてくれました（時には、終わりがないほどメールのやり取りをしま した） 。Erik Ismert氏は最終段階で私が再編集した章の重要な部分をレビューし、貴 重な情報を提供してくれました。Eric Hanson氏は、私が他では見つけることができ ないプランガイド内部の詳細を提供し、プランガイドメタデータとともに働くスクリ プトも提供してくれました。Paul Randal、Ketan Duvedi、Giri Nair、Sameer Verkhedkar、Milind Joshi、Andrew Richardsonの各氏も、私のeメールやSunil 氏からの質問に応えて貴重な技術的洞察と情報を提供してくれました。私が受け取っ たこれらの情報すべてがどんなに参考になったことでしょう。貴重な情報を提供して くれたみなさんに感謝します。 SQL Server製品サポートチームのCindy Gross、Bob Ward、Bob Dorr、Keith Elmore、Ken Hendersonの各氏が、折に触れて質問に答えてくれただけではなく、 ホワイトペーパーコンファレンスプレゼンテーションおよび知識ベースのアーティク ルを通してSQL Serverについての多くの情報を入手してくれたことにも感謝します。 Alan Brewer、Gail Erickson、Buck Woodyの各氏とそのユーザー教育チームが Books OnlineにSQL Serverドキュメンテーションをまとめるという偉大な仕事してく れたことに感謝します。 Leona Lowry氏がSQL Serverチームのみんなと同じビル内に私のオフィススペース を作ってくれたことにも感謝の気持ちでいっぱいです。ありがとう、Leona。私をこ ころよく迎えてくれたことに感謝します。 Microsoft Pressの編集者諸氏にも感謝します。私が招いた編集者であるBen Ryan 氏はこのプロジェクトを立ち上げ、実施に移し、途中に出てきた多くの障害を克服し てくれました。開発担当編集者であるDevon Musgrave氏は各章が確実に開始でき るように、プロジェクト担当編集者であるMaureen Zimmerman氏は各章が最後ま で滞りなく継続するように、外部のテクニカル担当編集者であるAndy KellyとBen Nevarezの両氏はそれぞれの章がうまくまとまるようにそれぞれ努力してくれました。 しかし、もちろん、2人のBenと、Devon、Maureen、Andyは単独でこの仕事をし たわけではなく、原稿担当編集者であるErica Orloff氏を含めた編集チームのすべて の人たちの協力がありました。この本を現実のものにするためにみんなが限りなく時 間を費やしてくれたことに感謝します。私のエージェントであるClaudette Moore氏は 本書の編纂に伴なう契約を私が取るにあたり多くのことを我慢強く調整してくれまし た。今は、そのことすべてに対して私のめいっぱいの感謝を伝えます。 SQL Server MVPのみなさん、特に、Tibor Karaszi氏とRoy Harvey氏に心から 感謝します。両氏は私的なニュースグループに参加して、見事な情報交換を行い、挑 戦に値するアイディアを討論し、私に個人的な支援と励ましのすべてを与えてくれま した。本書の執筆中に私を勇気付けてくれたM V P は他にも多勢います。G r e g Linwood、Hugo Kornelis、Erland Sommarskog、Tony Rogerson、Steve Kass、 Tom Moreau、Linchi Sheaの各氏です。もちろん、Microsoft MVP前主任である xiii Ben Miller氏、現在の主任であるSteve Dybing氏も含まれます。SQL Server MVP の一部であることが私のプロとしての人生の大きな栄光と名誉の1つであることを申 し上げておきます。 SQL Server Internalsクラスの学生たちに深く感謝します。彼らはSQL Server製 品に熱心に取り組んでくれ、私が彼らに教え、彼らと共有しなければならなかったこ とはたくさんありましたが、彼ら全員が私と共有しなければならなかったこともたく さんありました。私は好奇心旺盛な学生たちの質問から刺激を受けて多くのことを学 びました。Lara Rubbelkeを含む学生の数人とは友人となり、彼らからは今も刺激を 受けています。 何よりも大事なのは、私が仕事をするために必要な確固とした基盤となるものを私 の家族が耐えず提供してくれていることです。私の夫Danは結婚してからの22年の人 生でずっと私を導いてくれる光です。娘のMelissaは私のお手本です。彼女は、私が この本を終えるのとほとんど同時にエジンバラ大学から言語学のPh.Dを受けました。 3人の息子、Brendan、Rickey、Connorは現在成長し、寛大で、愛すべき、情熱あ ふれる若者ですが、近い将来自分たちの人生がどんな道を進むのかをまだ決定しかね ています。私の人生で家族として彼らに出会えたことはこの上ない幸せです。 Kalen Delaney Kalen Delaney氏が彼女の本の一部として私に大きなチャンスを与えてくれたことに 感謝します。私は実際にこの本に取り組むことを楽しみましたが、執筆は最初に私が 考えていたよりもずっとたいへんな仕事でした。しかし、Karenは激励の言葉で常に 私を導いてくれました。3人の仲間、Jerome Halmans、Ron Dar Zivｍ、Mike Ruthruffの各氏が私の仕事をレビューしてくれたことと、他にも書籍担当編集者が手 配してくれた人たちのレビューがあったことを幸運に思っています。彼らみんなのフ ィードバックに感謝します。最後に、本章の執筆中私を支えてくれた私の妻Anjuと 息子Sahilに感謝します。最善を尽くすようにと常に励ましてくれた私の両親にも感謝 します。 Sunil Agarwal SQL ServerチームのMilind Joshi氏に感謝します。Milindは多くの疑問点の調査 と回答のためにたくさんの時間を費やし、根気強く対応してくれました。本書に対す る私の寄稿は彼とSQL Serverチームの他のメンバーの支援があったからこそ、より 優れたものになりました。名前をあげると、Jay Choe、Campbell Fraser、Lubor Kollar、Martin Neupauer、Shu Scott、Stefano Stefani、Aleksandras Surnaの諸 氏です。最後に、Alyssa、Natali、Gavin、愛と支援をありがとう。 Craig Freedman xiv 謝辞 最初に、そしてだれよりも、私がこのプロジェクトに参加するきっかけを与えてく れたAndy Kelly氏に感謝します。さらに、Kalen Delaney氏に感謝します。彼女は このすべてを可能にしてくれただけではなく、スケジュール上私には荷が重すぎて対 処できないと私が思い込んでいたときにも私の参加を支えてくれました。最後に、私 の妻Kateに感謝します。彼女は常に私を何らかの形で支援し、私が執筆に忙殺されて いたときにも家庭での私の足りない部分を補ってくれました。 Adam Machanic 本書の1つの章への寄稿を私に促し、私が執筆を順調に進めることができるように 支援してくれたKalen Delaney氏に感謝します。有益なコメントを寄せてくれたテクニ カル担当編集者のみなさんにも感謝します。同時実行の問題をトラブルシューティン グする例を得るために参考文献の中から多数のアーティクル、ホワイトペーパー、ブ ログを引用できたことに感謝します。私の仲間たちと顧客のみなさんにも感謝します。 彼らはここ数年の、珍しい、取り組みがいのあるデッドロックの例を数多く提供して くれました。 Ron Talmage xv はじめに 『Inside SQL Server 2005: The Storage Engine』の「Introduction」で記したよ うに、本を執筆し出版するにあたって、著者にとって最も喜ばしいことは、読者から フィードバックをいただけることです。これは、『Inside SQL Server 2005: The Storage Engine』の読者からフィードバックをいただいたときにも実感しています。 著者の記述をじっくりと読み、すべての内容を丹念に理解しようとしてくださる読者 がいらっしゃることを知り、大変心強く感じます。もし、誤りやあまり明確でない部 分があったとしても、読者がその箇所を見つけて教えてくださることでしょう。編集 者やレビューアが一丸となって、誤りや明確でない概念を排除しようと力を尽くしま したが、それでもそれらが出版物に残ってしまうことがあります。 もちろん、期待していたトピックが省かれていたことに落胆した読者からのフィー ドバックは、このうえもなく辛いものです。しかし、4冊目となる本書を執筆するにあ たって、すべての人を対象にすべてのものを書くことは、たとえそれが望ましいこと であっても無理であることがわかってきました。Microsoft SQL Server 2005は大規模 で複雑な製品ですから、複数の冊子に分けたとしてもすべての機能を網羅することは 不可能です。コップの半分が空になっているのではなく、コップに半分も中身が注が れていると思って、 『Inside Microsoft SQL Server 2005』の掲載内容を受け入れてい ただきたいと切望しています。記載されなかったトピックについては、別の情報源に て必要な情報が見つかることを願っています。 このシリーズは「Inside」という言葉が意味するように、特にクエリプロセッサや ストレージエンジンなど、SQL Serverの中核となるエンジンに焦点を当てています。 クライアントプログラミングインターフェイス、異機種間クエリ、ビジネスインテリ ジェンス、レプリケーションなどについては取り扱いません。実際に高可用性機能の ほとんどを対象外としていますが、ミラーリングなどいくつかの機能については、デ ータベースのプロパティ設定に関する部分で詳しく言及しています。セキュリティ関 連など、いくつかの内部動作についても掘り下げていません。何らかの境界線を引か ない限り、このシリーズだけで10冊もの本を書かなければならず、次期バージョンが リリースされるまでにそれらを完結させることはできないでしょう。 xvi はじめに 『Inside Microsoft SQL Server』の歴史 バージョン6.5に関して記述された『Inside Microsoft SQL Server』の第1冊目には、 ほとんどすべての機能が盛り込まれました。当時の製品は、現在と比べて非常に小規 模だったためです。SQL Serverに関する書籍が他にほとんど存在せず、内容によっ ては著者のRon Soukup氏が読者に参照先すら示せない部分もあったくらいです。と ころが、その第1冊目でも、レプリケーションやセキュリティなどに関する説明は省か れています。さらに、Ron氏はSQL Serverデータベースのバックアップと復元を対 象から外し、トランザクションログの使用と管理についてはまったく触れていません。 SQL Server 7.0ではストレージエンジン全体が変更されたため、このシリーズの執 筆を引き継いだ私はストレージエンジンの内部に関する節の多くを完全に書き直しま した。また、バージョン7.0ではページの構造、インデックスの編成、リソースロック の管理がまったく異なっています。 『Inside Microsoft SQL Server 7.0』では、トランザクション、ストアドプロシージ ャ、トリガに関する説明を1つの章にまとめています。ユーザー定義関数の新機能や 新しいトリガ機能が追加されたSQL Server 2000版では、これらのトピックを2つの 章に分割しました。また、バージョン7.0の版で同じ章にまとめていたクエリの処理と チューニングについては、SQL Server 2000版では個別の章に分けています。クエリの 内部処理とSQL Serverオプティマイザが動作するしくみを説明する章と、パフォー マンスを高めるクエリの記述方法について説明する章です。さらに『Inside Microsoft SQL Server 2000』では、トランザクションログのしくみとバックアップと復元におけ るログの使用方法について深く掘り下げています。 シリーズの構成 『Inside Microsoft SQL Server 2005』を企画する段階の早い時期に、解説したい内 容が1冊の本に収まりきらないことに気が付きました。当初の想定では、ストレージ エンジンのコンポーネントと実際のデータ管理を第1巻に、T-SQL（Transact-SQL） 言語の使用方法とクエリの最適化を第2巻に収録することにしていましたが、間もな くSQL Server 2005にはT-SQLについてあまりにも多くの新機能が追加されている ために第2巻の内容が膨大になることが判明したのです。プログラミングの新しい基 本構造を十分に網羅するには、それだけで1冊分の量が必要となります。そのため、私 はT-SQLの大家であるItzik BenGan氏にSQL Server 2005のT-SQLに関して別の1 巻を執筆することを依頼しました。Itzik氏はすさまじい速さで執筆をする人で、私が ストレージエンジンに関する巻の構想を練り上げる前に、すでに500ページを超える 原稿を書き上げていました。その時点で、彼は、T-SQL言語は1冊の本に納めるには 大きすぎるトピックであり、彼が必要と考える内容をすべて取り込むには2冊分のボ リュームが必要であることを認識していたのです。このような経緯から『Inside Inside Microsoft SQL Server 2005: T-SQL Querying xvii Microsoft SQL Server 2005』は4巻で刊行されることとなりました。 各巻で重複する部分を最小限にとどめて読者が余計な手間をかけることがないよう にするのが我々の目標の1つでありましたが、すべての読者が同じ巻から読み始める わけではないことも認識していました。Itzik氏と私はSQL Serverのクエリ処理、イ ンデックスの利用方法、チューニングについて異なるアプローチで説明しているため、 これらのトピックは複数の巻に記載されています。重複部分についてはボーナスと考 えていただきたいと思います。 Inside Microsoft SQL Server 2005: T-SQL Querying この巻はTransact-SQLクエリ言語の基本構造を説明し、クエリ処理を論理的かつ 物理的な面から完全に解説しています。また、クエリのチューニング方法を紹介して います。Itzik氏はCTE、PIVOT演算子、UNPIVOT演算子、順位付け関数など、 TSQLの新規クエリ構造のすべてについて使用方法や動作を詳しく解説していますが、 TOP句の拡張についても言及し、作成したクエリに集計を統合するための新しく便利 な方法を例示しています。さらに、データの変更処理（I N S E R T 、UPDATE、 DELETE）の新機能についても深く掘り下げています。 Inside Microsoft SQL Server 2005: T-SQL Programming この巻はT-SQL言語のプログラミングに関する機能に焦点を当て、SQL Severア プリケーションにおけるトランザクション、ストアドプロシージャ、関数、トリガの プランニングと使用について網羅しています。Itzik氏はプログラミングテクニックに ついて結果セットベースとカーソルとを比較し、どちらのテクニックが適しているか を判別する方法を示しています。また、CLR（共通言語ランタイム）とリレーショナ ルのプログラミングを対比して説明し、どちらのモデルがどのような場面に適してい るかについても説明しています。さらに、この巻では一時オブジェクトの使用方法と、 SQL Server 2005の新しいエラー処理機能が網羅されています。Itzik氏はXMLデータ やユーザー定義のCLRデータ型などさまざまなデータ型の扱い方を示し、最終章では データベースアプリケーションにおいて非同期処理の制御を実現するSQL Server Service Brokerを取り上げています。 Inside Microsoft SQL Server 2005: The Storage Engine 本書（翻訳書は『インサイド Microsoft SQL Server 2005 ストレージエンジン編』 、 xviii はじめに 日経BPソフトプレス刊）はSQL Serverデータベースエンジンを対象としています。 『Inside Microsoft SQL Server 2000』のストレージ関連の章を基にすることから始め、 その後、どの新機能を盛り込むべきかを検討しました。しかし、執筆を開始してすぐ に構成を見直す必要があることに気付き、最終的にSQL Server 2005エンジンのアー キテクチャとトランザクションログに関する章だけに絞ることにしました。以前の版 と同様に、データファイル内のデータとインデックスの両方に関する実際の物理スト レージについて極めて深く言及し、ファイル領域の割り当て方法と管理方法について 解説しています。公式には説明されていないトレースフラグとDBCCコマンドについ ても、ある特定の機能を説明するために必要な場合や、読者がSQL Serverの動作に 関する理解を確認するために必要な部分で紹介しています。 本書ではSQL Server 2005の新機能についても解説していますが、詳しく説明して いる最重要の新機能には次のようなものがあります。ここに挙げていない新機能につ いても取り上げていますが、すべてを詳細に説明しているわけではありません。 ● 互換性ビュー、カタログビュー、動的管理ビュー（および動的管理関数）など のSQL Server 2005メタデータビュー ● データベースのスナップショット ● ユーザーとスキーマの分離 ● 行オーバーフローデータ、varchar（MAX）データを含むLOB（大容量デー タオブジェクト）の格納方法 ● パーティションテーブルとインデックスの格納方法 ● オンラインインデックスの構築と再構築 ● スナップショットの分離レベルと行レベルのバージョン管理 Inside Microsoft SQL Server: Query Tuning and Optimization このシリーズの最終巻である本書は、SQL Server 2005を現実のアプリケーション で最大限に活用する方法を説明します。本書は、著者が初めて執筆チームの一員とな って記した『Inside』シリーズの本であり、 そのこと自体が非常に有意義な経験でし た。他の執筆者と一緒に作業を行うということは、すべてを自分だけで本を記すのと はとても異なるものでした。これには良い点と悪い点とがありましたが、共著者から 学ぶ機会を得たことにより、最終的にはこのプロジェクトは著者にとって非常に前向 きなものとなりました。 Sunil Agarwal氏が執筆した第1章では、基本的にパフォーマンスのトピックを非 常に広範囲にわたって紹介しており、さらにチューニングの手法について説明してい ます。この章では、パフォーマンス上の問題が発生する可能性があるSQL Serverの システムおよびアプリケーション内の複数の分野を取り扱います。 Adam Machanic氏が執筆した第2章では、SQL Serverのトレース機能を取り上 例とスクリプト xix げ、SQL Traceの内部作用と、トレースの作成、管理および再利用のためのベストプ ラクティスについて説明しています。 Craig Freedman氏が執筆した第3章では、クエリの実行プロセスについて説明し、 クエリプランの解釈に重点が置かれています。ここでは、多数のクエリプラン演算子 の意味について他の何よりも詳細に説明しています。 Craig Freedman氏と著者が共に作成した第4章には、T-SQLクエリのチューニン グに関する推奨事項と、SQL Serverで利用できる無数のオプティマイザのヒントを 使用するためのガイドラインが含まれています。 著者が執筆した第5章では、SQL Serverのプランキャッシュメカニズムの働きにつ いて説明しています。また、SQL Serverがどのように、いつ、そしてなぜ新規プラ ンの作成を選択するのかについて取り上げ、クエリがいつリコンパイルされ、既存の プランがいつ使用されるのかを知る方法を説明しています。さらに、この章では、新 しいSQL Server 2005 プランガイド機能についても取り上げています。 Ron Talmage氏が執筆した第6章では、同時実行に関する問題のトラブルシューテ ィングについて詳述します。この章では、オプティミスティック（楽観的）な同時実 行とペシミスティック（悲観的）な同時実行の両方について取り上げ、問題検出の方 法と、SQL Serverのメタデータを使用した多数のスクリプトなどの問題を緩和する ための推奨事項について説明しています。最終的に、アプリケーションをモデリング する同時実行に対して最良の選択を行うための、いくつかのベストプラクティスのガ イドラインが示されています。 例とスクリプト 本書では、T-SQLコードを使って多くの機能や動作を説明しています。わずか数行 のコードもあれば、非常に複雑なコーディングを必要とする例もあります。動的管理 ビューの多方向結合なども含まれますが、いずれも長くて入力するのが面倒な名前が 使われています。わずか数行のコードを除き、すべてのサンプルコードはコンパニオ ンWebサイトwww.InsideSQLServer.com/companionでダウンロードすることがで きます。 対象外のトピック 『Storage Engine』の巻の「Introduction」で述べたとおり、いくつかの機能と概念 をこのシリーズの4冊で網羅することができませんでした。これらの4冊は、データベ ース管理者やデータベースアプリケーションプログラマのためのハウツー本ではあり ません。SQL Serverの内部的なしくみの解説に重点を置いているため、読者がアプ リケーションの構築やトラブルシューティングのための確固たる基礎を身に付け、な xx はじめに ぜSQL Serverがそのような動作をするのかを理解できるようになることを目指して います。 本 書 で は 、 ビ ジ ネ ス イ ン テ リ ジ ェ ン ス （ Analysis Services、 Integration Services、Reporting Services）と高可用性（レプリケーション、データベースミラ ーリング、ログシッピング、クラスタリング）に加えて、次のトピックを扱っていま せん。 ● Notification Services ● セキュリティ ● XMLインデックス ● フルテキスト検索 ● クライアントプログラミングインターフェイス 免責事項 本書ではSQL Serverのいくつかの動作を解説するために、内部テーブルなど公式 には説明されていない製品機能やオブジェクトを取り上げています。それらの一部は、 サポートされる関数、プロシージャ、ビューの定義を参照する場合などにユーザーが 自ら見つけ出す可能性もあります。したがって本書では、読者の無駄な時間を省くた めにも、読者が結果的に発見してしまうような情報をあらかじめ提供することにして います。さらに、公式に紹介されていない機能としてDBCCコマンドやトレースフラ グなどがあります。本書では、これらを特定の動作についてより細かい分析や完全な 解説をするためだけに紹介しています。それらのほとんどは、誰かが指摘しない限り 気付かないはずです。公式に説明されていないということは、サポート対象外である ことを意味します。その点に十分注意してください。つまり、本書で説明している非 公式の機能に対する質問をMicrosoft社のカスタマーサポートサービスに問い合わせ ることはできません。また、非公式の機能がSQL Serverの次のバージョンでも同じ 動作をする保証はありません。場合によっては、サービスパックでも動作が変更され る可能性もありますし、M i c r o s o f t 社にはr e a d m e ファイルやサポート技術情報 （Knowledge Base）の記事などで、そのような変更について通知する義務はありま せん。本書で非公式の機能やツールについて言及する場合は、そのことがわかるよう に示しています。また、Microsoft社がサポートを提供していないことを繰り返し表 記している箇所もあります。ただし、ここでの注意事項がすべての非公式機能につい て言及していることを考慮していてください。 サポートの受け方 xxi サポートの受け方 本書の内容を正確なものにするためにあらゆる努力がなされていますが、もし問題 が生じた場合は次のいずれかの手段を活用してください。 コンパニオンWebサイト 数人の優秀なテクニカルレビューアおよびMicrosoft社SQL Serverチームメンバー によるレビューと著者の細心の注意にも関わらず、本書は完璧であるとは言い切れま せん。変更や訂正事項は、コンパニオンWebサイトのエラーログ www.InsideSQLServer.com/errorlog.htmlに掲載しています。また、間違いと思わ れる部分を発見した場合は、サイトのフィードバックフォームを使って問題を報告し てください。 Microsoft Learning M i c r o s o f t 社 では、次 のW e b アドレスでも訂 正 内 容 を提 供 しています。 http://www.microsoft.com/learning/support Microsoft Learning Knowledge Baseで問題に関する質問を入力するには、 http://www.microsoft.com/learning/support/search.aspにアクセスしてくださ い。 著者へのフィードバックに加えて、次の手段によってMicrosoft社宛てにコメント や質問を送ることができます。 郵便 Microsoft Learning Attn: Inside Microsoft SQL Server 2005 Editor One Microsoft Way Redmond, WA 98052-6399 電子メール [email protected] xxii はじめに 上記のアドレスでは製品サポートを受けることができません。SQL Serverのサポ ートはwww.microsoft.com/sqlで提供されています。電話による標準サポートは平 日の午前6時から午後6時（太平洋標準時）まで425-635-7011（米国） で受け付けて います。また、http://support.microsoft.com/default.aspxではMicrosoft社の Support Onlineを検索できます。 本書はSQL Serverに関して読者が期待するトピックをすべて網羅していないかも しれません。たとえそうであっても、本書が読者にとって価値あるものとなることを 願ってやみません。より詳しく知りたい分野についてお知らせください。それらにつ いて、他の書籍やホワイトペーパーなどを参照先としてお知らせすることができます。 場合によっては著者のブログhttp://sqlblog.com/blogs/kalen_delaneyに書き込ん だり、 『SQL Server Magazine』誌に記事を書いたりすることも検討します。どうぞ Webサイトhttp://www.InsideSQLServer.comを通じてご連絡ください。 1 1 第 章 パフォーマンスのトラブルシューティング法 SQL Serverは、ミッションクリティカルなアプリケーションを世界規模で実行し ようとする組織によって使用されてきた企業レベルのデータベースサーバーです。こ れらのアプリケーションは可用性、パフォーマンス、拡張性という点で最も厳しく難 しい要求を突きつけます。もしあるとしてもほとんどの企業ワークロードにはこれら をしのぐほどの要求はありません。Microsoft SQL Server 2005は企業ワークロード が求める厳しい条件を十分に満たせる能力を備えています。SQL Serverのこの種の 展開の1つは、誰もが知っているNASDAQです。NASDAQはそのMDDS（Market Data Dissemination System：市場データ伝送システム）をサポートするために、 SQL Server 2005を2つの4ノードDell PowerEdge 6850クラスタ上で実行しています。 NASDAQ市場で処理されるすべての取引はMDDSを経由し、SQL Server 2005は市 場が開いている間1秒間に5,000のトランザクションを処理します。このため、現在使 用中のアプリケーションでパフォーマンスや拡張性の問題があるようなら、おそらく それはSQL Server の問題ではなく何か他の問題である可能性があります。解決策 は、それが何なのか（診断）とそれをどのように修復するのか（トラブルシューティ ング）を特定して、アプリケーションが再びスムーズに動くようにすることです。パ フォーマンス問題の診断とトラブルシューティングはその場しのぎのやり方ではうま く進みません。アプリケーションが利用できない時間やユーザーが減速を経験する時 間を最小限に抑えるにはパフォーマンスに関する問題をすばやく診断しトラブルシュ ーティングする戦略と方法が必要です。 パフォーマンスという言葉は人によってさまざまな意味を持つために、この言葉が いったい何を意味するのかと疑問をお持ちかもしれません。このため、本書ではパフ ォーマンスを次の3つの言葉に絞って説明します。 ◆ 応答時間 サーバーに処理要求が送られるときと、その応答の最初の文字が返されるときとの 2 第1章 パフォーマンスのトラブルシューティング法 間隔。 ◆ スループット サーバーが一定の時間単位で処理できるトランザクションの数。 ◆ 拡張性 ハードウェアリソースの追加とともに、スループットや応答時間がどのように変化 するかということ。簡単に言うと、拡張性とはハードウェアのボトルネックにぶつか っているとき、リソースを追加することによってその障害を簡単に軽減できることで す。 1.1 ｜ パフォーマンスに影響を与える要素 ほとんどのユーザーはアプリケーションの応答性からSQL Serverのパフォーマン スを認識します。SQL Serverそれ自体はアプリケーションの応答性では大きな役割 を果たしていますが、パフォーマンス問題の原因としてSQL Serverだけを考えるの ではなく、その前にアプリケーションのパフォーマンスに影響を与える要素をまず理 解する必要があります。これらの要素を完全に理解してから、SQL Serverのパフォ ーマンス問題のトラブルシューティングに視点を切り替えましょう。 高いレベルで考えると、多くの要素がアプリケーション内で到達可能なパフォーマ ンスと拡張性に影響を与えています。次の要素があります。 ● アプリケーションのアーキテクチャ ● アプリケーションのデザイン ● トランザクションと分離レベル ● Transact-SQLコード ● ハードウェアリソース ● SQL Serverの構成 1.1.1 ｜ アプリケーションのアーキテクチャ 今日のほとんどのアプリケーションは多層アーキテクチャを取り入れており、可用 性、パフォーマンス、拡張性が優れています。この後に説明する多層アーキテクチャ は、1980年代や1990年代前半に普及した2層（クライアントとサーバー）モデルより も大幅に改良されています。これらの多層はアプリケーションの論理境界を定義して おり、同一の物理ハードウェアや複数ハードウェア上で実行できますが、アプリケー ション自体のハードウェア（複数の場合もある）上でこれらの層を実行するのが最も 一般的な展開です。アプリケーションがスムーズに実行されるには、3層のすべてが何 1.1 パフォーマンスに影響を与える要素 3 のボトルネックもなく機能することが必要です。どこかにボトルネックがあるとパフ ォーマンスと応答時間の低下を経験することになります。 図1−1は3層のアプリケーションです。 第1層 プレゼンテーション ▲図1−1 第2層 ビジネス 第3層 データベース 通常の3層アプリケーション クライアント層として知られている第1層は、アプリケーションと交信するためのイ ンターフェイスをユーザーに提供するプレゼンテーション層です。アプリケーション と交信する一般的な方法はWebブラウザ（カスタムアプリケーションプログラムイン ターフェイス）を使用するか、あるいはコマンドラインを使います。アプリケーショ ンに応じて、少人数のユーザーから膨大な数のユーザーまでが同時にアプリケーショ ンと交信できます。たとえば、MSNのホームページの場合、数百万人ものユーザーが 同時にアプリケーションと交信できます。通常、ユーザーがこの層のパフォーマンス 問題にぶつかるのは以下の理由です。 ● ネットワーク帯域幅がコンテンツ配信には十分ではない。コンテンツのダウ ンロードの際に時間がかかることがあり、この原因はクライアントのネット ワーク帯域幅（つまり、第1層と第2層間のパフォーマンスのボトルネック） である可能性があります。 ● クライアントマシーンはコンテンツを効率的に配信するだけのパワーがない。 第1層のパフォーマンス問題については本書の対象外なので詳しい説明は省きます。 中間／アプリケーション層として知られる第2層はアプリケーションのビジネス論 理を主に担っています。通常の中間層には2つの主要コンポーネントがあります。 HTTPベースのコンテンツを作成するWebサーバーと、ビジネス論理を主に担うアプ リケーションサーバーです。アプリケーションの種類に応じて、ビジネス論理は簡単 なものから非常に複雑なものまであり、複雑なアプリケーションが200万∼300万行の コードを持っているのは珍しいことではありません。この層は第3層であるストレージ 層と交信して、データを格納したり、既に格納済みのデータにアクセスします。たと 4 第1章 パフォーマンスのトラブルシューティング法 えば、発注管理アプリケーションはデータベース内に発注されたデータを格納します。 ユーザーが発注についての情報にアクセスしたいときそれが特殊な発注であっても、中 間層はデータベースにクエリを実行してこの情報を取得します。ビジネス論理に加え て、中間層はアプリケーションによりよいパフォーマンスと拡張性を提供するために 使用できます。次のような利用法があります。 ● 一般的なクライアント要求の結果をキャッシュすることによって、再計算を 最小限に抑えます。これは2層アーキテクチャでは不可能です。 ● この層のホストを務める複数のサーバーを使用することによって、ワークロ ードを分散して拡張性と可用性を高めることができます。これは2層アーキ テクチャでは不可能です。 ● SQL Serverへの複数の接続（セッション）をプールし、それらを再利用す ることによって、アプリケーションユーザーに代わってデータベース要求を 実行します。これが可能なのは、すべてのアクティブユーザーが同時にデー タベースにアクセスする必要があるとは限らないからです。接続のプールは 2つのレベルでパフォーマンスの向上をもたらします。その1つは、接続のプ ールによって、データベースサーバー（ここではSQL Server）が管理しなけ ればならない接続／セッションの数が最小限に抑えられます。それぞれの接 続はおよそ50KBのメモリが必要です。ご想像どおり、アプリケーション層 が数百万人のアクティブユーザーそれぞれに対して個別のデータベース接続 を開くとすると、アプリケーション層はSQL Serverのメモリ上で大きな領域 を使い切ってしまいます。もう1つは、SQL Serverにアクセスするために 毎回セッションを開いたり閉じたりすると、時間とCPUリソースの両方が 必要になります。接続のプールを使うことによって、必要なSQL Server接 続の数を最小限に抑えることができ、接続のメモリオーバーヘッドも最小限 に抑えることができます。アプリケーションのワークロードを分析してプー ルのための適切な数の接続を見つけることが必要でしょう。接続のプール は、アプリケーションのユーザーそれぞれが個別の接続を確立する必要があ る2層アーキテクチャでは不可能です。個別の接続では、アプリケーション がサポートできる同時ユーザーの数が制限されます。 第3層はデータ層です。この層はアプリケーションによって必要とされるデータを格 納し、取得し、操作する役割を担っています。この層はデータベースサーバーである 必要はありませんが、通常はデータベースサーバーです。この後、この層をデータベ ース層と呼びます。SQL Server上で実行しているアプリケーションのパフォーマン スに現在焦点を当てているので、この言葉をSQL Serverと同義語としても使います。 他の層と同様に、データベース層はアプリケーションのパフォーマンスには非常に重 要です。もしアプリケーション層がデータベース層のデータにアクセスしている間に 停滞すると、ユーザーは減速を経験します。データベース層については本章の後半で 詳しく説明します。 1.1 パフォーマンスに影響を与える要素 5 1.1.2 ｜ アプリケーションのデザイン 他のソフトウェアと同様に、アプリケーションの設計はパフォーマンスという点で 非常に重要です。アプリケーションの良し悪しを決める尺度はありませんが、通常、 ハードウェアリソースが十分に働いているのにアプリケーションが停滞しているとき は問題があります。わかりやすくするために、アプリケーションの論理を2つの部分に 分けます。最初の部分にはアプリケーションとデータベース層との交信が含まれ、も う1つの部分には中間層で実行されるアプリケーション内のすべてのもの（ビジネス論 理、ワークフロー、セキュリティ、セッション管理、キャッシュなど）が含まれます。 おわかりのように、両方の部分ともアプリケーションのパフォーマンスと拡張性には 非常に重要ですが、今はデータベース層（ここではSQL Server）に関連するパフォ ーマンス問題について説明しているので最初の部分に焦点を当てます。アプリケーシ ョンのパフォーマンスに影響を与える最初の部分についてアプリケーションの一般的 な設計のいくつかを考察していきます。 ■■ データベーススキーマとその物理設計 データベーススキーマの設計が十分でないと、クエリとデータ変更操作の効率が悪 くなります。3つの主要な要因について見ていきましょう。 最初の要因はデータベーススキーマの正規形です。データベース論理設計理論はデ ータベーススキーマに対して主として5つの正規形を定義しています。正規形が高く なるほど、データベースの一貫性を維持しやすくなります。データベースの5つの正規 形の最終目標はデータの冗長性を取り除くことです。同じデータの繰り返しや複数コ ピーを取り除くことによって、データ変更操作のパフォーマンスを向上できます。デ ータを変更するのは1箇所だけでよくなるのでロックやログの数を減らすことができる からです。完全に正規化されたデータベーススキーマを持つことが望ましいのですが、 そうするとアプリケーションのデザインに複雑さが増えます。高い正規形というのは テーブルが多いことです。したがって、アプリケーションは同じ情報を取得するため に多くの結合操作が必要です。多くの結合操作を伴なうクエリは比較的複雑であり、 最適化や実行のためにより多くのシステムリソースが必要です。この場合、最終的に はクエリのパフォーマンスが低下し、データベースシステム内の他の同時操作のパフ ォーマンスも低下します。 スキーマ正規化がどのようにデータ変更操作に影響を与えるかを理解するために、 次の例で単純なスキーマを考えてみましょう。この場合、データベース内の複数の学 生について同じ情報が保存されている2つのスキーマがあります。最初のスキーマで は、１つのテーブルだけでStudentを表していますが、スキーマ2では、テーブルは StudentとDeptの2つに分かれています。 Schema-1: Student ( 6 第1章 パフォーマンスのトラブルシューティング法 student_id student_name student_status dept_name dept_bldg_no dept_phone Schema-2: Student ( student_id student_name student_status dept_id int) int, varchar(100), int, varchar(100) int, char(10)) int, varchar(100), int, Dept ( dept_id int, dept_name dept_bldg_no dept_phone varchar(100), int, char(10)) 2つのスキーマのそれぞれにおいて次の2つの操作のパフォーマンスを調べます。 ◆ 学生と学部の情報を参照する（たとえば、選択する） スキーマ1では、結合を再クエリせずに単にStudentテーブルをスキャンすることに よってこの情報を得ることができます。スキーマ2では、StudentとDeptのテーブル を結合する必要があります。 ◆ 学部の電話番号を更新する スキーマ1では、より多くのロックとログレコードが必要でStudentテーブルの中で 複数行を更新しなければならないでしょう。スキーマ2では、Deptテーブルの中で1行 更新するだけです。 データの冗長性があるため、スキーマ1のデータベースのサイズはスキーマ2より大 きくなっているので、アプリケーションのパフォーマンスにも影響が出ます。では、ア プリケーションはどのスキーマを選択すべきなのでしょう。答えは実際にはアプリケ ーションが実行する操作の種類によって違います。たとえば、アプリケーションが主 にデータを読み込むような場合（たとえば、データウェアハウスアプリケーション） 、 スキーマ1がよいでしょう。 第2の要因は役立つインデックスの可用性です。役立つインデックスは、アプリケ ーション内のDML（Data Manipulation Language：データ操作言語）ステートメ ントを処理するためにオプティマイザが選択可能なインデックスであると定義されま す。役立つインデックスがないとSQL Serverはテーブル全体をスキャンすることに なり、ステートメントのパフォーマンスにマイナスの影響が出ます。たとえば、上記 のスキーマ1の中のstudent_name列上にインデックスがあると、SQL Serverはテー 1.1 パフォーマンスに影響を与える要素 7 ブル全体のスキャンを実行しないで学生をその名前で捜すことができます。一方、利 用されないインデックスが存在する場合、テーブル内の変更を把握しておくためにイ ンデックスエントリが維持される必要があるので、インデックスはデータ変更操作に 対して不必要なオーバーヘッドが発生します。すなわち、役立つインデックスを見分 けたり、欠落しているインデックスを追加したり、役立たないインデックスを消去し たりなどの修正操作が必要です。 第3の要因は、SAN（Storage Area Network：ストレージエリアネットワーク） 環境の場合のように、物理ディスクやLUN（Logical Unit Number：論理装置番号） へのテーブルとデータベースのマッピングです。ここでの説明では「物理ディスク」と いう言葉を使いますが、これはSAN環境でのLUNと同じようなものです。データのア クセスパターンを理解し、次に複数の物理ディスクにデータベースとテーブルを分散 して、利用可能なI/O帯域幅が最大限使用できるようにしてください。最適ではない マッピングの例をいくつか見ていきましょう。 ■ ログとデータを同一の物理ディスク上で混合する ファイルをログ記録するほとんどの操作は連続した順アクセスです。一方、データ へのアクセスはユーザーのクエリとデータ変更操作に応じて、本質的にランダムアク セスです。ログレコードは連続的に書き込まれ、バックアップ時に連続的にアクセス されます。逆順でログレコードにアクセスしなければならないのはトランザクション をロールバックするときだけですが、これは通常の操作ではありません。SQL Server 2005の場合、トリガは行のバージョン管理を使用して実行されます。SQL Serverの 以前のバージョンでは、トリガはログレコードを逆順でスキャンして実行されました。 この場合、ディスクヘッドが前後に動き、ログファイルを含む物理ディスクのI/Oス ループットに影響を与えました。お勧めの方法はログファイルをそれ自体の物理ディ スク上に置くことです。これが不可能なら、待機時間は応答時間に直接影響を与える ので、ログ書き込み時に正常な待機時間が必ず維持されるように工夫してください。 ■ tempdbと複数のユーザーデータベースの間で物理ディスクを共有する tempdbは、SQL Serverのインスタンス1個に1つある共有データベースです。これ はアプリケーションとSQL Serverの両方によって一時データを保存／操作するため に使用されます。必ず、ワークロードに必要なI/O帯域幅をサポートするディスクI/O サブシステム上にこのtempdbを作成してください。遅いディスクにtempdbを作成す ると、アプリケーションのパフォーマンスに深刻な影響が出ます。アプリケーション 自体の物理ディスク上にtempdbを作成することをお勧めします。 ■ 頻繁にアクセスされるテーブルを同じ物理ディスク上にマッピングする 複数の物理ディスクにI/Oロードを分散することをお勧めします。この場合、これ らのテーブルを異なる物理ディスクにマッピングしてI/Oボトルネックの可能性を減 らすことを考えるとよいでしょう。 8 第1章 パフォーマンスのトラブルシューティング法 1.1.3 ｜ トランザクションと分離レベル アプリケーションは1つあるいは複数のトランザクションを使ってSQL Serverと交信 します。トランザクションはBEGIN TRANSACTION Transact-SQLステートメント を実行して明示的に開始されます。あるいはそれぞれのステートメントがトランザク ションにより明示的にカプセル化されない場合はSQL Serverによって暗黙的に開始 されます。トランザクションはデータベースシステムにおいては基本となるものです。 トランザクションは次の4つの基本プロパティを持つ作業の単位を表しています。 ◆ 原子性(Atomicity) 完全にコミットされるかロールバックされるかのいずれかのトランザクションの下 で実行される変更。部分的な変更は許可されていません。これはオール（全て処理さ れる）またはナッシング（まったく処理されない）のどちらかの状態です。 ◆ 一貫性(Consistency) 1つの一貫性のある状態から別の一貫性のある状態へトランザクションデータベー スの下で実行される変更。トランザクションは1つの一貫性のある状態から別の一貫 性のある状態へデータベースを保持します。 ◆ 分離性(Isolation) トランザクションによって実行された変更は、トランザクションのコミット時まで 別の同時トランザクションから分離されます。 ◆ 永続性(Durability) コミットされたトランザクションによって実行された変更は、永久的にデータベー スで保持されます。 SQL Serverはロックを使ってトランザクションの分離を実行し、データを変更す る前にそのデータを排他的にロックします。排他（X）ロックによって2つのトランザ クションはそのデータを同時に変更できなくなります。排他ロックはトランザクショ ンが持続している間保持されるので、同時トランザクションは最初のトランザクショ ンがコミットした後あるいはロールバックした後であってもデータの読み込みや変更 が可能です。同様に、トランザクションはデータを読み込む前に共有（S）ロックを 取得します。その名前が示すように、共有ロック（share lock）によって複数のトラ ンザクションがリソースを同時に共有できます。言い換えると、同時トランザクショ ンはお互いをブロックすることなくそれ自体の共有ロックを取得することによって同 じデータを読み込むことができます。表1-1に簡単なロック互換性テーブルを示しま す。2つの共有ロックはお互いに競合していませんが、排他ロックは共有ロックと排 他ロックの両方と競合しています。 1.1 パフォーマンスに影響を与える要素 ▼表1-1 9 簡単なロック互換性 ロックモード 共有（S） 排他（X） 共有（S） あり なし 排他（X） なし なし ご想像のとおり、トランザクション間のブロッキングはアプリケーションのパフォ ーマンスに大きな影響を与えますが、前述のトランザクションの分離性のプロパティ を保証する必要があります。このため、SQL Serverでは次のことが提供されていま す。 ● さまざまな粒度（たとえば、行、ページ、テーブル）でのより多くのロックモー ド（インテントロックなど） ● アプリケーションがオブジェクトレベルでロック動作を制御するようなロックヒ ント句 ● ロックの取得／解放が効率的になるような最適化されたコードの実行パス アプリケーションのデザイナとしては、ブロッキングを最小限に抑えるために特別 な注意を払う必要があります。分離性の保証に関して興味深い点は分離のレベルです。 SQL-99標準はトランザクションを実行するために使用できる4つの分離レベルを定義 しています。このトランザクション分離レベルはアプリケーションに一貫性と同時実 行を選択するオプションを提供します。一貫性が高くなるほど、同時性は低くなりま す。これらの分離レベルについてこの後簡単に説明しますが、詳しくは「第6章 同 時実行の問題」で説明します。 ◆ READ UNCOMMITED この分離レベルで実行するトランザクションはユーザーデータを読み込むためにロ ックを取得する必要はありません。つまり、トランザクションはブロックされること なくアクティブな同時トランザクションによって変更される可能性のあるデータを読 み込む（ダーティリード）ことができます。データを変更したトランザクションの状 態や成り行きをユーザーが知ることはないので、この分離レベルを使用するアプリケ ーションデザイナはこの事実に対して弾力的に対応する必要があります。 ◆ READ COMMITTED これはSQL Serverの既定の分離レベルです。この分離レベルで実行するトランザ クションはコミットされたデータだけを読む込むことができ、同時トランザクション がデータを変更するとブロックされます。これはデータを読み込む前にそのデータに 共有（S）ロックを要求することによって達成されます。しかし、SQL Serverはデー タが読み込まれた後に共有（S）ロックを解放し、トランザクションが持続している 間共有（S）ロックを保持しません。これによって、最初のトランザクションがまだ コミットしていなくても、同時トランザクションはデータを変更できます。共有（S） 10 第1章 パフォーマンスのトラブルシューティング法 ロックはトランザクションが持続している間も保持されないので、同じデータが再び 読み込まれても、それが同じものであるという保証はありません。たとえば、同じデ ータは、最初の読み込みの後に開始し、2回目の読み込みの前にコミットした他の同 時トランザクションによって変更されてしまう可能性があります。つまり、読み込み がリピート可能であるという保証はありません。 ◆ REPEATABLE READ この分離レベルはトランザクションによって読み込まれたデータがトランザクショ ンの持続中に変更されないことを保証します。SQL Serverはトランザクションが持 続している間共有（S）ロックを保持することによってこれを実現します。おわかり のように、この分離レベルはアプリケーションに高い一貫性をもたらしますが、同時 実行が犠牲になります（つまり、ブロッキングが増えます） 。現在のところ、同時トラ ンザクションがデータを変更しようとする場合、最初のトランザクションが完了する のを待たなければなりません。この分離レベルはリピート可能な読み込みを保証しま すが、バグを防ぐことはできません。たとえば、もし新しい行が同時トランザクショ ンによって挿入され、それが最初のトランザクションの検索条件に合うと、この新し い行は、新しい行を挿入したトランザクションがコミットした時点で再びクエリが実 行されたとき、結果セットの中に現れます。 ◆ SERIALIZABLE これは分離レベルによって提供される最も高いレベルの一貫性です。この分離レベ ルで実行されるトランザクションはトランザクションが持続している間変更がないコ ミットされたデータだけを読み込み、バグは発生しません。純粋な視点からは、直列 化可能トランザクションは複数の同時トランザクションを連続して実行することを表 します。SQL Serverはキー範囲ロックを使用するか、より大きな粒度で（たとえば、 テーブルレベルで）ロックを取得するかのいずれかの方法でこれを実行します。この 分離レベルで実行されるトランザクションは最も多くのブロッキングを引き起こしま す。 ここで注意してほしいのは、トランザクションはデータを変更するとき排他（X） ロックを取得し、このロックはトランザクションの分離レベルとは関係なくトランザ クションが持続している間保持されることです。 アプリケーションを設計するとき、トランザクションが開いている時間の量を認識 する必要があります。トランザクションの持続時間は排他（X）ロックの持続時間を 決定します。これらのロックはトランザクションが終了するまで保持されるからです。 共有（S）ロックの場合、トランザクション分離レベルがロックの持続時間とその粒 度を決定します。アプリケーションが既定のREAD COMMITTEDより高い分離レ ベルでトランザクションを実行していると、共有（S）ロックはトランザクションが終 了するまで保持されます。ロックが保持される時間が長いほど、ロックが同時トラン ザクションをブロックする機会が多くなります。したがって、アプリケーションのス ループットと応答時間に影響が出ます。 1.1 パフォーマンスに影響を与える要素 11 一見したところ、ほとんどのアプリケーションは最も多くの一貫性を提供する分離 レベルを使用すべきであると思えるかもしれません。しかし現実には、一般的に使わ れる分離レベルはREAD COMMITTEDであり、実際のところ、これはSQL Server が提供する既定の分離レベルです。高い分離レベルを選択する場合は事前に注意深い 検討が必要です。 ここで、簡単なホテル予約アプリケーションを取り上げて、異なるトランザクショ ン分離レベルを使ってこのアプリケーションを実行する2つの方法を調べてみましょ う。 宿泊予約アプリケーションは、空室状況や1泊料金を含む部屋情報を表示するWeb ベースのアプリケーションであるとしましょう。このアプリケーションによってオン ラインの顧客は部屋を予約することができ、ホテルのスタッフは季節や現在の空室率 に応じて部屋の1泊料金を変更できます。決まった年の1日の1部屋につき1行あるとし ましょう。設計時に主に考慮すべき点は2人の顧客が同じ部屋を予約しないようにす ることです。ソリューションを実行するための2つの方法を調べていきましょう。 ■■ 最初の実行方法 ユーザーは自分の条件に合う部屋を検索します。アプリケーションはデータベース にクエリを発行し、条件を満たす部屋を表示します。部屋についての情報が変更され ないようにするため、トランザクションは、部屋を表す行に共有（S）ロックを取得 するREPEATABLE READ分離レベルで実行されます。ユーザーは1部屋を予約す ることを決定し、支払い情報を入力し、次にWebフォームを送信します。共有（S） ロックは部屋を表す行に保持されているので、他の人がこの部屋を予約することはな く、アプリケーションはその部屋の予約状況を更新し、そのトランザクションをコミ ットします。顧客は予約がとれたことを通知されます。 この方法にはいくつかの問題があります。まず、トランザクションは顧客が部屋情 報を閲覧して更新するのを待っている間はアクティブです。つまり、トランザクショ ン実行時間は顧客がいつフォームを送信するかに依存するので、制限がありません。 長時間トランザクションが実行されていると、取得されたロックが長時間保持される ことになり、このデータを変更する必要がある同時トランザクションがあるとブロッ キングが増えます。第2に、2人の顧客が同じ部屋を参照してその部屋を予約する場 合、顧客を表わすトランザクションが行で共有（S）ロックを保持し、2人の顧客が行 上で排他（X）ロックを取得することになるので、デッドロックが発生します。顧客 はお互いに待つことになり、デッドロックにつながります。UPDLOCKヒント句によ ってSELECTを実行してデッドロックを除去できますが、ブロッキングがなおも存在 します。 ■■ 第2の実行方法 ユーザーは第1の実行方法と同じようにアプリケーションと交信しますが、アプリ 12 第1章 パフォーマンスのトラブルシューティング法 ケーションはREAD COMMITTED分離レベルにあり、ロックを保持しないまま検 索条件に合う部屋を返し、トランザクションを完了します。しかし、複数の顧客が同 時に予約するのを防ぐために、最後の更新時間を追跡するスキーマに新しい列 timestampが追加されます。このため、顧客が部屋予約要求を提出すると、アプリケ ーションは更新される行のtimestampがなおも同じかどうかを確認します。同じであ れば、部屋予約は成功です。同じでなければ、部屋予約は失敗し、顧客には「申し訳 ありませんが、ご希望の部屋は埋まりました」というメッセージが送られます。この 実行方法で興味深い点は、低い分離レベルで短い実行時間でトランザクションを実行 しながら、更新の喪失（つまり複数の顧客が同じ部屋を予約する）を避けることがで きることです。このため最初の実行よりはお勧めの設計です。 この例は不必要なブロッキングを引き起こす不十分なアプリケーション設計の1つ です。アプリケーションで多くのブロッキングが発生するようなら、トランザクショ ンの実行時間と使用している分離レベルに注意してください。SQL Server 2005で は、SNAPSHOT ISOLATIONあるいはREAD_COMMITTED_SNAPSHOTを使 用してreaderとwriter（たとえば、SELECTとUPDATE）の間のブロッキングを取 り除くことも検討できます。詳細については「第6章 同時実行の問題」を参照して ください。 1.1.4 ｜ Transact-SQLコード SQL Serverオプティマイザは最適化を高めるためにクライアントから送信された Transact-SQLを再調整しますが、設計の不十分さまでを補うことはできません。こ のような例として、カーソルを使用して複数行を更新します。カーソルは一度に1行 を操作するので、対応するセット操作よりは著しく遅くなります。次の例でこれを説 明します。 CREATE TABLE t1 (c1 int PRIMARY KEY, c2 int, c3 char(8000)) GO -- 次のように、このテーブルの中へ6000行をロードします。 DECLARE @i int SELECT @i = 0 WHILE (@i < 6000) BEGIN INSERT INTO t1 VALUES (@i, @i + 1000, 'hello') SET @i = @i + 1 END -- Method-1： このテーブルの中のすべての行を1つのステートメントで更新します。 BEGIN TRAN UPDATE t1 SET c2 = 1000 + c2 COMMIT TRAN 1.1 パフォーマンスに影響を与える要素 13 -- Method-2: このテーブルの中のすべての行をカーソルを使って更新します。 DECLARE mycursor CURSOR FOR SELECT c2 FROM t1 OPEN mycursor GO BEGIN TRAN FETCH mycursor WHILE (@@FETCH_STATUS = 0) BEGIN UPDATE t1 SET c2 = 1000 + c2 WHERE CURRENT OF mycursor FETCH mycursor END COMMIT TRAN -― ここで合計のワーカー時間をクエリします。 SELECT TOP 10 total_worker_time/execution_count AS avg_cpu_cost, execution_count, (SELECT SUBSTRING(text, statement_start_offset/2 + 1, (CASE WHEN statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max), text)) * 2 ELSE statement_end_offset END - statement_start_offset)/2) FROM sys.dm_exec_sql_text(sql_handle)) AS query_text FROM sys.dm_exec_query_stats ORDER BY [avg_cpu_cost] DESC -DMVクエリの出力です。 avg_cpu_cost exec_count ------------------------4913844 1 58634 1 8437 6000 5641 1 5268 6000 query_text ----------------------------UPDATE [t1] SET [c2] = @1+[c2] SELECT top 10 total_worker_tim FETCH mycursor FETCH mycursor UPDATE t1 SET c2 = 1000 + c2 w この場合、カーソルを使う更新は（8,437 * 6,000 + 5,268 * 6,000）=81,690,000行を 取得しています。これは結果セット指向の更新によって取られる4,913,844回に比べる とおよそ20倍です。前述のTransact-SQL UPDATEステートメントを代わりに使う ことができれば、アプリケーションは大きなセットの行を更新するためにカーソルを 使う必要はないようです。しかし、大事なのは、設計が不十分なTransact-SQLはア プリケーションのパフォーマンス問題につながることです。アプリケーションの設計 を検討する価値は十分あります。 14 第1章 パフォーマンスのトラブルシューティング法 1.1.5 ｜ ハードウェアリソースの選定 ハードウェアリソースはアプリケーションを実行するために必要な原動力です。き ちんと設計されたアプリケーションを使っているのに、ワークロードの要求に応えら れないハードウェア上でアプリケーションが実行されていると、大きな問題が起こり ます。ほとんどの場合、アプリケーションは模擬のワークロードを使って小規模環境 で試験されます。これは機能面でのテストをするという目的にはかなうのですが、ワ ークロードのパフォーマンスと、実際にアプリケーションが実稼働で実行されている ハードウェアのパフォーマンスを測定するためにはそのまま使えないのは明らかです。 Morse Consultancy Group（http://www.morse.co.uk）による調査では、組織の 89％がパフォーマンスを試験するために不適なプロセスとハードウェアを使っている ことが示されています。したがって、アプリケーションがパフォーマンスの問題にぶ つかるときは、必ずしもそうとは言い切れないのですがハードウェアを一度は疑って みるべきです。アプリケーション層の中でその操作能力を超えてしまうハードウェア リソース（CPU、I/O、メモリ、ネットワーク）はアプリケーションの機能低下を招 きます。ボトルネックはアプリケーションの設計が不十分なときにも発生し、最終的 に処置が必要になることにも注意してください。ボトルネックが発生した場合、短期 的にはハードウェアの更新が最善のソリューションです。 1.1.6 ｜ SQL Serverの構成 SQL Serverはワークロードの課題を満たすように自己チューニングできる設計で す。ほとんどの場合、SQL Serverは独創的な構成設定によってスムーズに機能しま すが、ある場合には、最大パフォーマンスを出すために構成パラメータを調整する必 要があるかもしれません。 『インサイドMicrosoft SQL Server 2005 ストレージ編』 （日経BPソフトプレス刊）には構成オプションについての詳細情報が追加されていま す。この節では、パフォーマンス問題を追跡するために監視する必要のあるオプショ ンを取り上げます。関連する構成オプションはCPU関連オプションあるいはメモリ関 連オプションのいずれかです。 ■■ CPU関連構成オプション CPU関連構成オプションを使用するのは、たとえば、SQL Serverインスタンスに よって使用可能なCPUやソケットの数、並列処理の最大限度、ワーカースレッドの最 大数を制御するためです。このカテゴリでよく使われるオプションには次のものがあ ります。 1.1 パフォーマンスに影響を与える要素 15 ◆ 関連マスク(Affinity Mask) このオプションを使うとSQL Serverプロセスへの利用可能なCPUのマッピングを 制御できます。既定では、SQL ServerはServerハードウェア上で利用可能なすべて のプロセッサを使用します。SQL Serverは既定の設定で最上に機能するので、関連 マスクオプションを使用しないことをお勧めしますが、次の2つの状況下ではこのオプ ションを使用する方がよいでしょう。 ● サーバーハードウェア上で他のアプリケーションを実行しているようなとき、高 い負荷を受けてWindows OSはプロセススレッドをさまざまなCPUに移動させる ことがあります。関連マスクを使用すると、それぞれのSQL Serverスケジュー ラをそれ専用のCPUに結合できます。これによって各プロセッサへのスレッド の移行がなくなり、コンテキスト切り替えが減るのでパフォーマンスが向上しま す。 ● このパラメータを使うと、SQL Serverが実行されるCPUの数を制限できます。 同じサーバーボックス上で複数のSQL Serverインスタンスを実行していて、そ れぞれのSQL Serverによって取られるCPUリソースを制限したいときや、リ ソースの干渉を最小限に抑えたいとき、これは役立ちます。 ◆ 簡易プーリング(Lightweight Pooling) この構成を有効にすると、SQL ServerはWindowsファイバを利用します。ワーカー はWindowsスレッドやファイバへのマッピングができます。ファイバはスレッドに似 ていますが、2つのワーカー（ファイバースレッド）間の切り替えがカーネルモードで はなくユーザーモードで実行可能なので、これは通常のスレッドよりは安価です。こ のため、カーネルモードでおよびコンテキスト切り替えに大量の時間が使われるCPU ボトルネックがワークロードに発生するようなときは、このオプションを有効にする と恩恵が大きいでしょう。このオプションがパフォーマンスの低下を頻繁に引き起こ さないように、実稼働システムでこのオプションを有効にする前にこのオプションを 実際のワークロードでテストしてください。CLR統合が簡易プーリングではサポート されていないことにもくれぐれも注意してください。 ◆ 最大ワーカースレッド(Max Worker Threads) ワーカーはユーザーやバッチの要求を実行するSQL Serverスレッドと考えるとよ いでしょう。ワーカーは完了するまでバッチに結合されます。このため、ワーカーの 最大数は同時に実行できるバッチの数を制限します。既定の最大ワーカースレッド数 は、表1-2のとおりです。 ▼表1-2 既定の最大ワーカースレッド数 CPUの数 <= 4プロセッサ 8プロセッサ 16プロセッサ 32プロセッサ 32ビットコンピュータ 256 288 352 480 64ビットコンピュータ 512 576 704 960 16 第1章 パフォーマンスのトラブルシューティング法 ほとんどの場合、表1-2に示したこの既定の設定で十分です。それぞれのワーカーは 32ビットコンピュータで512KB、x64で2MB、IA64で4MBのメモリを使います。メ モリを維持するために、SQL Serverは少数のワーカーで開始します。ワーカーのプ ールは必要に応じて拡張したり縮小したりします。2つの状況下ではこの構成を変更 した方がよいでしょう。1つは、アプリケーションが少ない数のワーカーを使用してい ることがわかっているときです。これをもっと低い数値に設定することによってSQL Serverは最大数のワーカーに対してメモリを予約しておく必要がありません。もう1 つの状況は、必要とされるワーカーの数が既定の構成を越える場合などの、長時間実 行のバッチ（おそらくロック待機を伴なう）がたくさんあるときです。これは通常の ケースではないので、アプリケーションの設計を調べて分析する方がよいでしょう。 ◆ 並列処理の最大限度(Max DOP : Max Degree of Parallelism) この構成パラメータは並列のクエリを実行するために展開できるプロセッサやコア の最大数を制御します。並列クエリはより速い応答時間を提供しますが、CPUリソー スを多く使います。CPUのボトルネックが発生するときは、本章の後半で説明するよ うに、この構成パラメータを調べる必要があります。 ■■ メモリ関連の構成オプション メモリ関連構成オプションはSQL Serverによって使用されるメモリを制御するた めに使います。このカテゴリでよく使われる構成オプションには次のものがあります。 ◆ 最大および最小サーバーメモリ(Max and Min Server Memory) おそらく、パフォーマンスの観点からはこれは構成オプション（特に32ビット構成） の中で最も重要です。この構成パラメータはSQL Serverに対して設定される総メモ リとよく混同されますが、同じではありません。これはバッファプールに対して設定 されたメモリを表します。SQL Serverは（はっきり言えば、どのデータベースサー バーも）メモリを多く必要とするアプリケーションです。できるだけ多くのメモリを SQL Serverが利用できるようにする方がよいでしょう。64ビットの場合、OSと、バッ ファプールの外側での割り当てに対してメモリを予約するために適切な上限を定める ことをお勧めします。他のアプリケーション（SQL Serverの他のインスタンスを含 む）が同じサーバーボックス上で実行されているときにはSQL Serverによるメモリ 使用の上限を定めることも必要です。 ◆ AWE Enabled 32ビットアドレス方式のハードウェアでSQL Serverプロセスが処理できるのは 2GBのバーチャルメモリだけです。あるいは、/3 GBパラメータをboot.iniファイル に追加してコンピュータを再起動し、/3 GBパラメータを有効にした場合は3GBのバ ーチャルメモリです。4GBを超える物理メモリがあり、この構成オプションを有効に した場合、通常SQL Serverプロセスは最大64GBのメモリを利用でき、/3 GBパラメー タが使用されていれば16GBまでを利用できます。その上、SQL ServerはAWE 1.2 トラブルシューティングの概要 17 Enabledと連結したメモリ特権のロックページを要求します。SQL Server SKUと、 現在実行中のWindows OSのバージョンに関してはいくつかの制限があります。詳細 についてはTechNetのSQL Server 2005 Books Onlineを参照してください。AWEオ プションを使うときには注意すべき重要な点があるので知っておいてください。まず、 このオプションによってSQL Serverプロセスはバッファプールに対して最大64GBのメ モリ（クエリプランに対して利用可能なメモリ）にアクセスできますが、接続、ロッ ク、他の重要な機能は2GBまでになおも制限されています。第2に、AWEによってマ ッピングされたメモリはページングが不可能であり、同じサーバーハードウェア上で 実行されている他のアプリケーションに問題を引き起こすことがあります。SQL Server 2005からは、AWEメモリは動的に解放できますが、なおもページアウトでき ません。SQL Serverは物理メモリが圧迫されるとこのメモリを解放することがあり ます。第3に、このオプションは64ビット環境では利用できません。しかし、SQL Serverプロセスがメモリ特権でロックページを許可されていれば、バッファプールが メモリ内でページをロックし、これらのページはページアウトできません。 ここまで、データベースを設計し展開するときに考慮すべき重要な点をいくつか説 明してきました。これらは特にSQL Serverについての説明ですが、他のデータベー スサーバーについても該当します。 1.2 ｜ トラブルシューティングの概要 理想的にはデータベースアプリケーションはきちんと設計されています。アプリケ ーションが実行されているハードウェア構成はワークロードの要求を満たすか超えて います。この環境を制御することは十分に可能です。アプリケーションとユーザーは 適格に作られたTransact-SQLクエリを使用してSQL Serverと交信し、ミスも起こ しません。しかし、なかなかこのようには行かないでしょう。膨大な数のITショップ が独自のソフトウェアベンダ（ISV）アプリケーションを実行し、その設計をほとん ど管理していません。時間とともに、ワークロードはハードウェアの能力より大きく なります。多くのアプリケーションが同じハードウェアで実行され、SQL Serverに 干渉することがあります。ワークロードは短い期間で大幅に変化します。最終的に、 インデックスの削除などのユーザーミスによってパフォーマンスが低下します。DBA （データベース管理者）やシステム管理者としてこのような問題にぶつかるのは日常的 なことです。こんなとき頼りになるのは何でしょう。どのようにしてこれらのパフォ ーマンス問題を積極的に特定し、タイムリーにそれらをトラブルシューティングする のでしょう。問題はアプリケーションにあるのでしょうか、それともハードウェアに、 ワークロードに、ユーザーのアクションにあるのでしょうか？可能性が数多くあり、考 え抜かれた戦略もないまま、時間は無駄に過ぎていきます。パフォーマンス問題の優 れた戦略について要点を大まかにあげると次のとおりです。 18 第1章 パフォーマンスのトラブルシューティング法 ● ワークロードに対するベースラインの作成 ● ワークロードの監視 ● パフォーマンス問題の検出、分離、トラブルシューティング この後の節では、これらの戦略について詳細に説明します。問題の範囲は非常に大 きく、ある一定のインストレーションでは固有の問題が関わることもあるので、すべ ての状況に適した普遍的な戦略を提供するのは不可能ですが、次にあげる戦略はよく 起こるパフォーマンス問題に適用できると考えられます。 1.2.1 ｜ ワークロードに対するベースラインの作成 ほとんどのユーザーにとってパフォーマンスのトラブルシューティングは事前に策 を講じることではなく、実際にぶつかってから対処することです。アプリケーション のユーザーがパフォーマンス問題を経験するとき、システムやデータベースの管理者 は手がかりを得るためにパフォーマンスカウンタを通常調べます。パフォーマンスカ ウンタは数多くあるので、これらのカウンタのほとんどは大きな操作範囲を持ち、特 定のカウンタの値が正当かどうかを知るのは不可能です。たとえば、現在表示されて いるSQL Server:Locks:lock waits/Secが許容限界内であるかどうかはどのようにして わかるのでしょう。データへの同時アクセスを許可するアプリケーション内ではいく つものロック待機があると考えるのが普通ですが、問題なのは、実行している特定の ハードウェア上のその時のワークロードでは使用中のアプリケーションに対してどん な値が正当かということです。どんなパフォーマンスカウンタの場合も、複数のディ メンションがその値に影響を与えるので、分析はさらに複雑になります。それではど うすればいいのでしょう。端的な答えは、比較するためのベースライン（つまり、ア プリケーションが正常に実行されているときのパフォーマンスカウンタのベースライ ン）を持つことです。実際のところ、パフォーマンス問題を取り除くために使用でき るデータ（必ずしもパフォーマンスカウンタである必要ありません）のベースライン を持つことが必要です。たとえば、アプリケーション内の主要クエリに対する最適化 クエリプランのベースラインを持つのがよいでしょう。ベースラインを使うと、収集 された現在のパフォーマンス問題を、アプリケーションがパフォーマンス問題を起こ していないときのデータと比較できます。この情報を使って、通常の操作範囲にはな い数値をすばやく発見でき、パフォーマンス問題の原因の範囲を限定することができ ます。突然のパフォーマンス低下が起こるには多くの理由があります。一般的な理由 は次のとおりです。 ◆ 役立つインデックスが誤って削除される この場合、メモリとI/Oの帯域不足を引き起こすテーブルスキャンが必要な、高コ ストのクエリプランとなってしまいます。影響を受けるクエリに対するクエリプラン がベースラインにあれば、簡単に原因を特定できます。 1.2 トラブルシューティングの概要 19 ◆ ワークロード内の無計画な変更 この種の例は、感謝祭の後の日などの月販売や季節販売が終わるときのレポートの 実行です。SQL Serverが実行されているハードウェアがそのような変更を処理する ように設計されていない場合、応答時間が若干遅くなると予測される中で、ユーザー はパフォーマンスの低下を経験します。この場合、CPUが100％利用されるようなハ ードウェアリソースの不足が見られるか、ページングが増えてその結果I/Oに影響を 与えるようなメモリ不足が見られます。おもしろいことに、この徴候（たとえば、ハ ードウェアリソースの不足）はインデックスが誤って削除される上記の例に似ていま すが、その原因はまったく異なっています。パフォーマンス問題をハードウェアのせ いにするのは必ずしも最善のソリューションではないと強調するにはこんなぴったり の例はないでしょう。もう一度言いますが、パフォーマンスデータとベースラインを 比較することによってパフォーマンス問題の原因の範囲を限定することができます。 ベースラインはパフォーマンスのトラブルシューティング法として基本的な部分で す。ほとんどのアプリケーションでは、1つのベースラインでは十分ではなく、複数の ベースラインが必要になります。たとえば、株取引アプリケーションを考えてみまし ょう。このアプリケーションのワークロードはその日の時間によって異なります。取 引時間帯にはOLTP （Online Transaction Processing：オンライントランザクショ ン処理）ロードが多くなり、取引時間が終わった後はレポートを作成するDW（Data Warehouse：データウェアハウス）のようなワークロードが多くなります。同様に、 ワークロードは四半期の最後や営業年度の最後でも異なります。このため2つの主要 ポイントが浮上します。まず、問題点をすばやく特定するためにワークロードに対す るベースラインを作成する必要があることです。第2に、アプリケーションを使用する 際、複数のベースラインを作成しそれぞれが異なる種類のワークロードを表すように することです。複数のベースラインを持ったときは、アプリケーションのパフォーマ ンス問題をトラブルシューティングするにあたり、対応するベースラインあるいは代 表的なベースラインを使用してください。 ワークロードのベースラインを作成することがなぜ重要なのかについてある程度理 解していただけたと思いますが、1つの疑問が残ります。 「どんなベースラインを作る べきか」という疑問です。これは最初に考えたよりも難しい質問です。明らかに一般 的な答えは、パフォーマンス問題を特定しやすいデータをベースラインにすることで す。しかし、それは正確にどんなものなのでしょう。この問題は、SQL Serverのパ フォーマンスを監視するために使用できるツールがたくさんあるという事実から、さ らに複雑になります。すべての利用可能なツールを使い、それらが提示する情報をす べて使用すると、膨大な量のデータを収集し分析することになります。さらに、すべ てのツールを実行するとワークロードのパフォーマンスに影響が出ます。十分な情報 を収集することと、ワークロードへの影響を抑えることの間に適切なバランスを見つ けることが必要になります。ワークロードの測定とベースラインの確立のためによく 使われるツールのいくつかをご紹介します。 20 第1章 パフォーマンスのトラブルシューティング法 ■■ システムモニタ このツールはパフォーマンスモニタ（Performance Monitor）とも言われ、CPU、 メモリ、ディスク、ネットワークリソースなどの使用情報を、他の多くの役立つカウ ンタに提供するWindows監視ツールです。このツールはWindowsプラットフォーム で実行されているアプリケーションに対してプロセスレベルでの情報を提供します。し かし、Windows OSで実行されているアプリケーションは、アプリケーションプロセス 自体の内部に役立つ情報を提示するためにこのツールと統合することができます。ご 想像のとおり、SQL Serverアプリケーションはシステムモニタと統合されて、SQL Serverに関連する多くの役立つカウンタを提供します。一般的なのがPage Life Expectancy、Buffer cache hit ratio、Free Space in tempdb（KB）です。カウン タを監視するオーバーヘッドはカウンタの数と監視の頻度に依存します。システムモ ニタを使うと毎秒の頻度で監視できます。 ■■ SQL Server Profiler SQL Server Profilerは、Microsoft SQLトレースに対するGUI（Graphical user interface：グラフィカルユーザーインターフェイス）であり、Microsoft SQL Serverデータベースエンジンのインスタンスを監視するためのものです。これは対象 となるイベントについてのデータをキャプチャします。このデータは後から分析する ためにファイルあるいはテーブルに保存できます。SQL Serverテーブルではなくフ ァイルにデータを収集することをお勧めします。監視されているイベントに応じて、こ のテーブルは膨大な数の挿入があり、アプリケーションを実行するために利用できる 貴重なリソースに負担をかけるからです。SQL Serverデータファイルや実行可能フ ァイルと共有されないローカルディスクサブシステムにこのファイルを作成すること もお勧めします。ポーリングをベースとするシステムモニタとは違って、SQL Server Profilerはイベントベースです。ポーリングシステムでは、ポーリング間隔の中間で イベントが発生したときに、監視している情報を見失う可能性があります。このこと はSQL Server Profilerを使用していればイベントを見失うことはないということで はありません。重い負荷のシステムでは、サーバー側トレースを作成し、トレースデ ータを直接サーバー側ファイルへ送信することをお勧めします。サーバー側トレース を使用するとイベントが削除されることはありませんが、システムがひどく混んでい る場合、プロファイラ定義のトレースにイベントを送るときイベントが削除される可 能性があります。SQL Server Profilerの実行は多くのオーバーヘッドが必要です。 ワークロードのパフォーマンスにプロファイラが及ぼす影響は選択されるイベントと、 これらのイベントが生成される頻度に依存します。このため、イベントの削除を防ぐ ためだけではなく、SQL Serverへの影響を最小限に抑えるためにも、サーバー側ト レースをお勧めします。SQL Server Profilerを通常に使用するシナリオでは、短い 間隔でイベントデータを収集し、次にイベントと実行中のクエリを関連付けて問題を 特定します。SQL Serverのトレースとプロファイラについては「第2章 トレースとプ 1.2 トラブルシューティングの概要 21 ロファイル」で詳しく説明します。 ■■ データベースエンジンチューニングアドバイザ（DTA） DTA（Database Engine Tuning Advisor：データベースエンジンチューニングア ドバイザ）はMicrosoft SQL Server 2000のITW（Index Tuning Wizard：インデ ックスチューニングウィザード）に代わるものでありさらに強力です。これは、SQL プロファイラを使用して収集された通常のアプリケーションワークロードと物理スキ ーマを分析して、役立つインデックスの推奨設定のアイディアを得るために使われま す。役立つインデックスの使用を推奨することに加え、これはパーティション、オン ライン操作、他の多くの状況についての推奨設定を提供します。これはSQL Server Profilerトレースを使用して静的にアプリケーションを分析するので、初期にアプリ ケーションを設定しているときには優れたツールですが、システムがいったん稼働す ると使用が制限されます。アプリケーションが変更された場合（たとえば、新しいバ ージョンのアプリケーションをインストールする、あるいはワークロードが大きく変 化する） 、このツールを再度実行する必要があります。 ■■ DBCCコマンド DBCCはデータベースコンソールコマンドを表します。ほとんどのDBCCコマンド はデータベースの一貫性のチェックと関連があります。一例はDBCC CheckDBコマ ンドです。しかし、SQL Serverのメモリの使用を監査するために使われるDBCC Memorystatusや、トランザクションログ空間の使用を監査するために使われるDBCC SQLPerfのようなDBCCコマンドもいくつかあります。DBCCはパフォーマンス関連 情報を提示するために適したツールではないので、SQL Server 2005からは、次に説明 するような新しいDMVやDMFがこのような情報を提示するにはお勧めの方法です。 ■■ 動的管理ビュー（DMV）と動的管理関数（DMF） これらのビューと関数はSQL Serverの内部データ構造とパフォーマンス問題の診 断とトラブルシューティングのための関連情報を提示します。DMVとDMFは非常に 似ているので、この後、DMVとDMFの両方の説明にDMVという言葉を使います。 DMVはこの情報をリレーショナル形式で提示するので、この情報は、おなじみの パラダイムを使って、Selectステートメントを使用してアクセスできます。また、意 味のあるデータを提供するために結合操作を使用して他のDMVと結合できます。提 示されるほとんどの情報はいずれにせよSQL Serverによって維持される必要がある ので、SQL Serverの内部でこの情報を収集する追加のオーバーヘッドはありません。 しかし、インメモリ構造はリレーショナル行セットとして提示される必要があるので、 この情報を取得するためにDMVを使用するときに小さなオーバーヘッドがあります。 同様に、複数のDMVを含む複雑なクエリを使用する場合は、クエリ実行のコストは 22 第1章 パフォーマンスのトラブルシューティング法 比例して増えます。しかし、DMVを実行するオーバーヘッドはSQL Server Profiler に比べるとかなり低くなります。このオーバーヘッドはほとんどのDMV ※1 の場合、主 にCPUオーバーヘッドを2％未満含んでおり、少数の例外はありますが、追加の物理 あるいは論理I/Oを含みません。それに加えて、DMVはSQL Server Profilerよりもも っと多くの役立つ情報を提示します。しかし、システムモニタカウンタと同様に、 DMVによって取得される情報はポーリングに基づいており、役立つ情報が欠落して いる可能性があります。たとえば、DMV sys.dm_os_waiting _tasksはそのときブ ロックされているタスクを追跡します。このため、ポーリング間隔の間でブロックさ れて、次にブロック解除されたタスクは欠落している可能性があります。 興味深いことに、DMVの概念はSQL Server 2005に新しく登場したものではありま せん。たとえば、アクティブなセッションについての情報と、このセッションがリレ ーショナル行セット形式で実行している要求についての情報を返すsysprocessesシス テムテーブルは既におなじみのことでしょう。実際、これがDMVなのです。同様に、 リレーショナル行セット形式で内部情報を提示するストアドプロシージャがいくつか あります。たとえば、リレーショナル行セットでロック関連情報を提示するsp_lock です。SQL Server 2005での主な変更点は、DMVが正式なものになっていることと、 より多くのDMVがあることです。しかし、この変更はDMVが提示する情報をどのよ うに効果的に使用するかという課題を投げかけています。このことを理解し、可能で あれば、この後パフォーマンス問題の説明の中でDMVを使う例を取り上げます。 DMVとシステムモニタカウンタは自由自在に使える非常に強力なツールだと考え られます。この後の節では、パフォーマンス問題のトラブルシューティングにこれら を何度か使ってみましょう。 1.2.2 ｜ ワークロードの監視 ワークロードを定期的に監視してベースラインからの大きなズレを見つけられなか ったら、ベースラインは何の役にも立たないでしょう。ベースラインからの大きなズ レは、理解し分析する必要のある変化であり、その変化がワークロードのパフォーマ ンスに影響を与えるということです。ワークロードのパフォーマンスが低下するには 多くの理由があります。SQL Serverに関する一般的な原因は次のとおりです。 ◆ 統計情報の更新や役立つインデックスの削除が原因でクエリプランが変更されている 変更されたプランはより多くのリソースを使うので、一般的なパフォーマンス低下 が起こります。 ◆ ロック粒度の変更 SQL Serverは帰納的手法を使って、述語の選択性と、テーブルのサイズおよび同 ※1 sys.dm_tran_version_storeやsys.dm_os_buffer_記述のようないくつかのDMVの実行は高負荷 になりますが、通常はパフォーマンス監視のためにこれらを使います。 1.2 トラブルシューティングの概要 23 時実行ワークロードを考慮して適切なロック粒度を選択します。もしSQL Serverが 粗い単位のカーソルロック粒度（たとえば、テーブルロック）を選択すると、大きな ブロッキングを起こし、より低い粒度（たとえば、行）を選択すると、ロックのオー バーヘッドを増やします。 ◆ 逸脱したセッション ユーザーはクエリに述語を加えるのを忘れることがあります。そんなときは大きな テーブルのテーブルスキャンにつながります。同様に、ユーザーは複雑なクエリを実 行することがあり、クエリの複雑さ（たとえば、結合のネストの深さと数）に応じて クエリはシステムリソースを大量に浪費します。 ◆ ワークロードの変化 たとえば、ニュースコンテンツプロバイダの場合、国際的なイベントによってWeb サイトへユーザーが一挙に押し寄せると、著しい遅延が起こります。 ◆ ビジネスの通常の成長と関連ワークロード ワークロードを監視することによって、トレンドを特定し、変更を計画するために その情報を使用できます。 ◆ 新しいバージョンのSQL Serverへの更新 ほとんどのアプリケーションでは、新しいバージョンのSQL Serverに更新すると そのパフォーマンスが向上します。しかし、新バージョンのSQL Serverに対してア プリケーションをなおも監視し、ベースラインからのズレがないかどうかを識別する 必要があります。 ◆ ハードウェアリソース 新しいアプリケーションやソフトウェアがSQL Serverと同じシステムハードウェ アで実行されると、ハードウェアリソースを獲得しようと競合するので、SQL Server プロセスはCPUやワークロードのメモリを十分に得ることができない事態が起こりま す。 ほとんどのパフォーマンス問題は最終的にはリソースのボトルネックとなって現れ ます。定期的にワークロードを監視することによってリソースのボトルネックを早期 に特定できます。DMV／システムモニタカウンタによって監視を行うには一定間隔 でこれらをポーリングする必要があります。では、どれくらいの間隔で監視すればよ いのでしょうか。それはワークロードがどの程度動的に変化しているか、どんな監視 オーバーヘッドが受容されるかによって違ってきます。頻繁に監視（すなわち、ポー リング）して、膨大な数のシステムモニタカウンタやDMVを監視すれば、この情報 をどこに保存し、それをどのように処理すべきかに頭を悩ますことになるでしょう。こ れについては本章の後半で説明します。 一般的には、リソースのボトルネックはそれらが明確であっても、まず徴候として 処置すべきであり、必ずしもパフォーマンス問題の原因として処置すべきではありま せん。トラブルシューティングは順次、リソースの種類ごとに3つのステップを取りま 24 第1章 パフォーマンスのトラブルシューティング法 す。最初は、リソースボトルネックがあることを検出することです。2番目は、リソー スのボトルネックの原因を分離することです。3番目そして最後のステップは、修正 行動を取ってパフォーマンス問題を解決することです。 1.2.3 ｜ よく発生するパフォーマンス問題の発見、トラブルシューティン グと分離 SQL Serverを展開してデータベースアプリケーションを実行してきた数百万人の ユーザーが遭遇するすべてのパフォーマンス問題について説明するのはほとんど不可 能でしょう。ここでは頻繁に発生する一般的なパフォーマンス問題のいくつかをトラ ブルシューティングすることに焦点を当てます。しかし、パフォーマンスを特定しト ラブルシューティングするために取るステップはほとんどのデータベースアプリケー ションに対するものと非常に似かよっており、関連があると考えられます。 アプリケーションにパフォーマンス問題が発生するとき、なんらかのリソース（CPU、 メモリ、I/O、ネットワーク帯域幅のいずれか）に必ずボトルネックが生じます。さ らに、SQL Serverに関連しては、過度のロック（つまり、ブロッキング）によって 起こることもあれば、tempdbと呼ばれる1つの特殊なリソース内の競合によって起こ ることもあります。tempdbはSQL Serverインスタンス内のすべてのデータベースの 共有リソースであり、tempdb内の競合や領域の問題はSQL Serverインスタンス上で 実行されているすべてのアプリケーションに影響を与える可能性があることに注意し てください。ここでは次のリソース内でのボトルネックに絞って説明します。 ● CPU ● メモリ ● I/O ● tempdb ● ブロッキング これらのリソースそれぞれについて、ボトルネックがあることをどのように検出す るか、その原因をどのように分離するか、ボトルネックをどのようにトラブルシュー ティングし解決するかについて説明します。 ■■ CPUボトルネック CPUボトルネックについて説明する前に、SQL Server内部の実行モデルの理解を 深めるためにいくつかの基本概念を見て行きましょう。 SQL Serverプロセスには、ユーザークエリを実行するために使用するワーカーや、 レイジーライター（ダーティページをディスクへ更新する）のような内部バックグラ ウンドタスクを実行するために使用するワーカーのセット（プール）があります。ワ 1.2 トラブルシューティングの概要 25 ーカーは、Windowsスレッドに、あるいはlightwait pooling 設定がONの場合はフ ァイバに、内部的にマップ（１対１）されるSQL Server内の論理スレッドです。ユ ーザーやアプリケーションは実行のためにクエリあるいはTransact-SQLステートメ ントの一群をSQL Serverに提出します。この作業単位はバッチあるいは要求と呼ば れます。SQL Serverはバッチを受け取ると、実行のためにバッチにワーカーを割り 当てます。バッチへのワーカーの関連付けはバッチの実行が完了する間維持されます。 この関連付けは、たとえワーカーがロック要求上やI/O上でブロックされても維持さ れます。バッチが完了すると、ワーカーは自由に別のバッチを実行できます。SQL Serverは、バッチを並列で実行すると決定した場合には、そのバッチを実行するため に複数のワーカーを割り当てることに注意してください。同時実行のユーザー要求／ バッチが提出されると、SQL Serverはワーカーをバッチのそれぞれに関連付け、次 にワーカーはそのバッチを実行して完了します。ご推測どおり、アクティブワーカー （バッチを実行しているワーカー）の数はSQL Server上のCPU負荷を示しています。 しかし、すべてのワーカーがリソース上でブロックされたらどうなるでしょう？その ような場合、多数のアクティブワーカーがあるとしても、CPUのオーバーヘッドが非 常に低くなることがわかるでしょう。これは興味深い点であり、ほとんどのパフォー マンス問題について結論を引き出す前に、複数の情報を調べる必要があります。 ワーカーの状態はたくさんありますが、おもしろいのはRUNNING、RUNNABLE、 SUSPENDEDの状態です。 ● RUNNING ● RUNNABLE ワーカーは現在CPU上で実行中です。 ワーカーは現在CPU待ちキュー上でその順番を待ってい ます。 ● SUSPENDED ワーカーはリソース（ロックやI/O）上で待機中です。 RUNNABLE状態で多数のワーカーがある場合、CPUボトルネックの徴候です。一 方、ワーカーがSUSPENDED状態でほとんどの時間を過ごす場合、SQL Server内に 過度のブロッキングがあることを示しています。 ■ CPUボトルネックの検出 システムモニタカウンタProcessor:% Processor Timeを調べるとCPUボトルネック を特定できます。このカウンタの値が高ければ、つまり80％（15∼20分の間での一般 的 な許 容 値 ）を超 えていれば、C P U ボトルネックがあるということです。 System:Processor Queue Lengthを監視することもできます。持続値が2以上であれ ばCPU圧迫を示します。しかし、同じハードウェア上で実行されている他のアプリケ ーションがあるので、これは必ずしも現在のSQL ServerプロセスによってCPUボト ルネックが引き起こされているということではありません。SQL Serverがボックス の外で実行されている唯一のアプリケーションのとき、プロセス特定カウンタ Process:%Processor Timeを調べることでSQL Serverプロセスが取っているCPU リソースを見つけることができます。CPUボトルネックを引き起こしているのが他の 26 第1章 パフォーマンスのトラブルシューティング法 アプリケーションであるとわかれば、解決のためにSQL Serverの内部を調べても無 駄なことは明らかです。SQL Serverハードウェアを他のアプリケーションと共有す る必要があるとき、SQL Serverや他のプロセスのための適切なCPUの割合をどのよ うにして知ればよいのでしょう。このようなときこそベースラインを使います。どん なときも、ハードウェア上のワークロードとベースラインを比較し、どのプロセスが その処理範囲を超えているかを見極めることができます。 CPU圧迫を検出するもう1つの方法は、RUNNABLE状態のワーカーの数を数える ことです。次のDMVクエリを実行してこの情報を得ることができます。 SELECT COUNT(*) AS workers_waiting_for_cpu, t2.Scheduler_id FROM sys.dm_os_workers AS t1, sys.dm_os_schedulers AS t2 WHERE t1.state = 'RUNNABLE' AND t1.scheduler_address = t2.scheduler_address AND t2.scheduler_id < 255 GROUP BY t2.scheduler_id 次のクエリを実行してRUNNABLE状態のワーカーによって使われた時間を使用す ることもできます。 SELECT SUM(signal_wait_time_ms) FROM sys.dm_os_wait_stats signal wait (ms) は、ワーカーがRUNNABLE状態に入った時間とそれが実際に実行 を開始した時間との差を表します。ベースラインからの極端なズレは、ワークロード にCPU圧迫を引き起こしているいくつかの変化があることを示しています。ここで注 意してほしいのは、上記DMVはSQL Serverが開始してからの待機を返すことです。 したがって、意味のあるデータを得るには、対象となる期間の差を調べてください。 ■ CPUボトルネックのトラブルシューティングと分離 アプリケーションのパフォーマンス低下の原因がCPUのボトルネックであることが わかれば、その原因を分離する必要があります。CPUのボトルネックは複数の要因に よって引き起こされますが、通常は次の理由です。 ■ 効率の悪いクエリプラン このような場合にまず始めるのは、最も多くCPU時間を使っているクエリを識別 し、それらをベースラインの中で使われている時間と比較することです。大きなズレ があるようなら、そのクエリを調べてそれを引き起こしているのが何かを見極めます。 統計情報の更新によってクエリプランが変更される可能性があります。SQL Server オプティマイザはコストベースであり、統計情報に全面的に依存して結合順序や結合 戦略を決定する中間結果セットのサイズを計算します。統計情報の大きな更新はクエ リプランの変更を引き起こし、時として悪い方向に変更が起こります。同様に、役立 1.2 トラブルシューティングの概要 27 つインデックスが間違って削除されると、このインデックスに依存しているクエリプ ランはリコンパイルされる必要があり、結果としてクエリプランは高価になります。 次に示すのは、実行のたびに最も多くCPUを使っている上位10位のクエリを得るた めに使用できるDMVクエリです。SQLステートメント、そのクエリプラン、このプ ランが実行された回数もリストアップします。高価なクエリの実行頻度が低いような ら、それほど重要なことでもないでしょう。 SELECT TOP 10 total_worker_time/execution_count AS avg_cpu_cost, plan_handle, execution_count, SELECT SUBSTRING(text, statement_start_offset/2 + 1, (CASE WHEN statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max), text)) * 2 ELSE statement_end_offset END - statement_start_offset)/2) FROM sys.dm_exec_sql_text(sql_handle)) AS query_text FROM sys.dm_exec_query_stats ORDER BY [avg_cpu_cost] DESC ここで注意してほしいのは、このDMVは現在キャッシュされているクエリに対す る集計情報だけを示すことです。メモリ不足が原因していくつかの高価なクエリがキ ャッシュから削除されてしまう可能性があります。しかし、一定間隔でこのDMVを ポーリングしていれば、これらのクエリをキャプチャできる可能性が高くなります。ク エリのavg_cpu_costを対応するベースラインと比較すると、潜在的な問題を探す開 始点が直ちにわかります。これらのクエリプランのそれぞれを調査し、変更されたク エリプランが実行のコストを減らすことになるかどうかを見分ける必要があります。さ らに、これらのプランに関連するTransact-SQLを簡素化してその実行コストを減ら すことができるがどうかを調べることも必要です。上記DMVのクエリ出力を簡素化 したものを次に示します。ワークロードに関して興味深い観察はほとんどありません。 たとえselect sum(c3) from t1を実行するCPUコストが大きくないとしても、これは 11,002回実行されています。このため、このクエリプランがそれを最適化するための 機会を見つけることが重要です。たとえば、列c3に非クラスタ化インデックスを作成 すると、前出のクエリは非クラスタ化インデックスをスキャンするだけで計算でき、デ ータページにアクセスする必要はなくなります。第2に、最も高価なクエリは結合を含 んでいます。さらに最適化する方法があるかどうかを知るためにはそのクエリプラン を調べてください。 avg_cpu_cost ----------29589011 83021 62786 plan_handle ---------0x06000500E401FC06B8015704000000000000000000000000 0x06000500201C8F03B8E1BB0B000000000000000000000000 0x06000500334B0B05B8610807000000000000000000000000 28 第1章 パフォーマンスのトラブルシューティング法 Execution_count --------------10 query_text ----------select c1, c5 from t1 INNER HASH JOIN t2 ON t1.c1 = t2.c4 order by c2 11002 1 select sum(c3) from t1 select count(*) from t2 上記DMVを使うと、ワークロードの中で最も頻繁に実行されたクエリを発見でき ないかもしれません（それらのクエリのCPUコストが前出の上位10位のクエリよりか なり低い場合です） 。ワークロードの中で最も頻繁に実行されたクエリを見つけるには 次のDMVクエリを実行します。上記DMVクエリを若干変えたものです。 SELECT TOP 10 total_worker_time, plan_handle,execution_count, (SELECT SUBSTRING(text, statement_start_offset/2 + 1, (CASE WHEN statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max),text)) * 2 ELSE statement_end_offset END - statement_start_offset)/2) FROM sys.dm_exec_sql_text(sql_handle)) AS query_text FROM sys.dm_exec_query_stats ORDER BY execution_count DESC ■ 過度のコンパイルとリコンパイルの発生 クライアントから送られたクエリやバッチは、最適化されたクエリプランを生成す るためにコンパイルされ、次に実行されます。最適化されたプランはプロシージャキ ャッシュとして知られるメモリ上のキャッシュに維持されます。同じクエリやバッチ が再び実行されると、SQL Serverはまずクエリの最適化されたプランがあるかどう かプロシージャキャッシュをチェックします。もし見つかると最適化プランは再利用 され実行されます。これは、多くの結合操作を通常含む複雑なクエリにとっては非常 に重要です。このような場合、オプティマイザが探索しなければならない解決空間が 指数関数的に大きくなるからです。最適化されたクエリプランを再利用することによ って、同じクエリやバッチを複数回呼び出すよりも最適化のコストを削減できます。 多くのリコンパイルがやむをえない場合、コンパイルやリコンパイルはCPUに負担を かけるアクティビティなので、CPUの利用率が高くなるかもしれません。SQL Server が自動的にクエリをリコンパイルするには多くの理由があります。一般的な理由は次 のとおりです。 ◆ スキーマ変更 参照オブジェクトのメタデータが変更されたとき、リコンパイルが起こります。 DDLとDMLを混合したバッチがあると、リコンパイルが起こります。 1.2 トラブルシューティングの概要 29 ◆ セットオプション 変更されると、リコンパイルを引き起こすセットオプションがいくつかあります。 ANSI_NULLS、ANSI_PADDINGS、ANSI_NULL、ARITHABORTです。バッチ 内でこれらのオプションを変更すると、そのたびにリコンパイルが起こります。 ◆ 統計情報の更新 SQL Serverはコストベースのオプティマイザを使用します。中間結果のサイズを 計算するためにテーブルやインデックスに対する統計情報を使用します。統計情報に 大きな変更があるとリコンパイルが起こります。 ◆ リコンパイルオプション リコンパイルオプション（with recompile）を持つストアドプロシージャは実行の たびにリコンパイルされます。ストアドプロシージャの最適化プランがパラメータ値 に依存しているようなら、リコンパイルオプションを加えるのがよいでしょう。 SQL Server 2005ではリコンパイルはステートメントのレベルで実行されます。この ため、影響を受けるステートメントだけがリコンパイルされます。SQL Server 2000の 場合はこれと違って、バッチ全体がリコンパイルされます。次のシステムモニタパフ ォーマンスカウンタを使うとコンパイルとリコンパイルの割合がわかります。 ● SQLServer: SQL Statistics: Batch Requests/Sec ● SQLServer: SQL Statistics: SQL Compilations/Sec ● SQLServer: SQL Statistics: SQL Recompilations/Sec 1秒あたりのバッチ要求の数に対するコンパイル／リコンパイルの数を調べること が重要です。もしベースラインがあれば、これらの数を対応するベースラインと比べ ることもできます。また、現在、より多くのコンパイル／リコンパイルがあるかどう かもわかります。コンパイルはクエリが初めてコンパイルされることですが、リコン パイルはメモリ常駐クエリプランの後続のコンパイルであることに注意してください。 クエリプランの最適化にSQL Serverがどのくらい時間を使うかを知ることも有益 です。次のDMVを使うとこの情報を得ることができます。 SELECT * FROM sys.dm_exec_query_optimizer_info WHERE counter = 'optimizations' OR counter = 'elapsed time' これらのカウンタはインスタンスが開始されたときからの累積カウンタです。この DMVの2種類のスナップショットを取ることによって、特定数の最適化と経過時間を 見つけることができます。カウンタoptimizationsは2つのスナップショットの間のク エリ／バッチの全数と、秒単位での各最適化に対する平均経過時間を表します。クエ リ最適化はCPUに負担をかけるアクティビティなので、クエリ最適化のCPUコストに 30 第1章 パフォーマンスのトラブルシューティング法 ついて何らかのよいアイディアを得てください。クエリ最適化オーバーヘッドが著し く大きいようなら、その理由を特定し、対処する必要があります。 頻繁にリコンパイルされているクエリ／バッチを識別するためには、もちろん、こ の情報を得るためのSQLプロファイラを使用します。しかし、先に説明した理由でこ のオプションはお勧めしません。SQL Server 2005では、DMVを使って最も頻繁に リコンパイルされている上位10位のクエリプランを見つけことができます。 SELECT TOP 10 plan_generation_num, execution_count, (SELECT SUBSTRING(text, statement_start_offset/2 + 1, (CASE WHEN statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max),text)) * 2 ELSE statement_end_offset END - statement_start_offset)/2) FROM sys.dm_exec_sql_text(sql_handle)) AS query_text FROM sys.dm_exec_query_stats WHERE plan_generation_num >1 ORDER BY plan_generation_num DESC プランと関連SQLテキストのそれぞれを調べることによって、リコンパイルを引き 起こす理由をおそらく特定できるでしょう。リコンパイルの一般的な理由については 既にいくつか説明したので、これ以上知る必要もないでしょう。 「第5章 プランのキ ャッシュとリコンパイル」ではコンパイルとリコンパイルについてもっと詳しく説明 します。 コンパイルの数が増えてくるときは、SQL Serverにメモリ不足が発生し、メモリ からクエリプランを追い出している可能性もあります。これは、SQL Serverのメモ リがCPUボトルネックに直面している典型的な例です。必ず、他のリソースとの関連 において1つのリソースのリソースボトルネックを評価してください。したがって、コ ンパイルの数が増えていることがわかったら、プロシージャキャッシュ（最適化され たプランを保存するために使用されるキャッシュ）に割り当てられたメモリを調べて ください。次のDBCCコマンドを実行してその情報を得ることができます。 DBCC MEMORYSTATUS 次は、他の情報を消した後のサンプル出力です。TotalPagesは最適化されたプラン を保存するために使われる、奪われたバッファプールページです。 Procedure Cache -------------TotalProcs TotalPages InUsePages Value -----37 899 20 これをベースラインと比べると、プロシージャキャッシュサイズが縮小しているか 1.2 トラブルシューティングの概要 31 どうかを予測することができ、これによってSQL Serverにメモリ不足が発生してい ることが示されるでしょう。なぜSQL Serverがメモリ不足になっているのかを分析 する必要はありません。次の節でメモリ不足とそれを管理する方法を説明します。 ■■ メモリボトルネック ほとんどの実稼働データベースはデータベースサーバープロセスが利用できるメモ リよりもずっと大きなサイズのものです。SQL Serverはユーザークエリを処理する ためにデータベースページにアクセスするので、要求されたページがSQL Serverプ ロセスメモリで見つからないと、物理I/Oが発生します。SQL Serverはバッファプー ルとして知られる内部構造の中のメモリのページをキャッシュし、LRU（Least Recently Used：よくアクセスされるページがバッファプールの中に最大限保存され るようにするキャッシュアルゴリズム）を展開します。ディスクから読み込まれたペ ージを保存することに加えて、SQL Serverはロック、接続、ワークロードなどの内 部構造に対してメモリを使用し、最適化されたクエリプランを保存するためにメモリ を使用します。このため、SQL Serverが適切な量のメモリで構成されていないとき、 アプリケーションのパフォーマンスが著しく低下します。不十分なメモリの影響はし ばしば他のリソースのボトルネックに出てきます。たとえば、クエリプランはメモリ 不足の状態ではメモリから追い出されてしまいます。実行のために再提出されたクエ リは再び最適化される必要があります。こうなると、クエリ最適化はCPUに負担をか ける操作なので、CPUに圧迫がかかります。同様に、データベースページはメモリ不 足の状態ではバッファプールから削除されかねません。その後すぐにこれらのページ の参照が必要になると、より多くの物理I/Oが起こります。 一般的には、メモリはサーバーハードウェア上で利用できる物理メモリ（RAM）で すが、VAS（Virtual Address Space：仮想メモリ）という別のメモリがあります。 これはRAMと同じように重要です。Windows OSシステムを使用しているすべての 32ビットアプリケーションは、最大4GBの物理メモリにアクセスするために使用でき る4GBプロセスアドレス空間を持っています。アドレス可能な全メモリのこの4GBの うち、VASの2GBはプロセスがユーザーモードでアクセスするために利用可能であり、 他の2GBはカーネルモードでアクセスするために予約されています。boot.iniファイ ルの中のa/スイッチを使用してこの構成を変更できます。このスイッチによってアプ リケーションはユーザーモードで3GBのVASにアクセスできるようになります。一方、 カーネルモードでのVASのアクセスは1GBに制限されます。プロセスは4GBのアドレ ス空間にアクセスできますが、かといって4GBの物理メモリが必要になるということ ではありません。これを実現するための通常のOSの仕組みはページングと言われま す。これはスワップファイルを使用して、最近参照されていないプロセスメモリの一 部を保存します。再び参照されるとこのメモリはスワップファイルから物理メモリの 中に透過的に再度読み込まれます（すなわち、ページインされます） 。このため、4GB の物理メモリ（RAM）と8GBのスワップファイルがあれば、サーバーハードウェア上 のすべてのアクティブなプロセスによってコミットされるVASの総量は8GBです。し 32 第1章 パフォーマンスのトラブルシューティング法 かし、参照されるメモリの4GBだけが実際にRAMにあり、残りの他のメモリは“ペ ージアウト”されると考えられます。たとえば、しばらくの間アクティブではないプ ロセスがあると、OSはメモリ不足の状態ではそれをページアウトします。後からこの プロセスがアクティブになると、そのプロセスに関連のあるメモリをページインする ために時間がかかるので、プロセスは遅くなるか、応答しません。同様に、現在実行 中のプロセスは利用可能なメモリのほとんどをコミットしてしまうと、たとえば前述 の8GBの場合、新しいメモリがそのプロセスに割り当てられることは不可能です。お わかりのように、2種類のメモリ不足があります。物理メモリ不足と仮想メモリ不足 です。これらはアプリケーションのパフォーマンスに影響を与えます。それぞれにつ いて次の節で詳しく説明します。 ■ 物理メモリ不足 SQL Serverは外部コンポーネントあるいはSQL Server自体の中から物理メモリ 不足を起こします。SQL Serverが利用できる物理メモリ（RAM）が十分にないと き、外部物理メモリ不足です。これが起こるのは、サーバーハードウェア上の物理メ モリが非常に制限されているか、サーバーハードウェアがSQL Serverの別のインス タンスを含む他のアプリケーションに共有されているかのいずれかです。このような 状況は、SQL Serverのワーキングセット（SQL Serverによって現在使用される物 理メモリ）が小さいか、そのワーキングセットには縮小する圧力が常にかかっている かのいずれかです。SQL Serverプロセスはサーバーボックス上で物理メモリ不足を 監視し、バッファプールを縮小することによってその割り当てられたメモリを解放す ることに注意してください。 物理メモリ不足はSQL Serverプロセスそれ自体の内部から起こることもあります。 たとえば、SQL Serverインスタンスのメモリ設定（たとえば、最大サーバーメモリ 構成パラメータ）を変更したり、内部コンポーネントのメモリ分散を変更したり（た とえば、バッファプールからの予約ページや奪われたページの割合を高くする）と、内 部メモリ不足が起こります。内部コンポーネントに対するメモリの割り当ては2種類 あります。 ■ 単一ページ割り当て これは単一ページアロケータを使って割り当てられたメモリを表します。単一ペー ジアロケータはバッファプールから直接ページを奪います。たとえば、プロシージャ キャッシュはバッファプールからページを奪うことによってメモリを割り当てます。 ■ 複数ページ割り当て これは複数ページアロケータを使って割り当てられたメモリを表します。このメモ リはバッファプールの外側で割り当てられます。 ■ 仮想メモリ不足 SQL Serverプロセスは外部コンポ−ネントから、あるいはそれ自体の内部から仮 1.2 トラブルシューティングの概要 33 想メモリ不足を起こします。SQL Serverプロセスに外部仮想メモリ不足が発生する のは、サーバーハードウェア上で利用できる仮想アドレスがその限界に近づいて、サ ーバーハードウェアが仮想メモリ不足の状態になったと見られるときです。たとえば、 サーバーハードウェア上で実行中の他のプロセスが、利用できるほとんどのメモリ（ス ワップファイルを含む）を使い切ったとき、SQL Serverプロセスは開始できなくな るか、ワークロードの要求に応じるためのより多くのメモリをコミットできなくなり ます。この状態になると、全システムの応答が非常に遅くなります。 仮想メモリ不足はSQL Serverプロセスの内部からも起こります。たとえば、フラ グメンテーション（多くのVASが利用可能だが小さなブロックになっている）や使用 量（直接割り当て、SQL Server VASに読み込まれたDLL、多数のスレッド）が原 因して、少ないVASで実行されている時です。SQL Serverはこの状態を検出し、 VASの予約領域を解放し、キャッシュの縮小を開始します。 次の節ではメモリ不足の検出とトラブルシューティングの方法を説明します。 ■ 物理メモリ不足の検出 物理メモリ不足を識別する簡単な方法はパフォーマンスビューのタスクマネージャ と、物理メモリセクションのtotal値とavailable値を開くことです。totalはサーバー ボックス上で利用できる実際の物理メモリ（RAM）を示し、availableは新しいプロ セスが利用できるメモリを示します。availableが低いと物理メモリが不足していま す。正確な値は多くの要素で異なってきますが、この値が50∼100MBに下がるような ときにはこの値を詳しく調べてください。この容量が10MB未満ときは、明らかに外 部メモリ不足です。 物理メモリ不足を識別するためのシステムモニタカウンタがいくつかあります。一 般的な疑問は、これらのカウンタの通常値はどれくらいかということです。前述のよ うに、すべてのカウンタにとって1つの答えはありません。大きなズレを見つけるには ワークロードのベースラインを参考にしてください。メモリ不足を識別するために次 のシステムモニタカウンタを使用できます。 ◆ Memory: Available Bytes これはコンピュータ上で実行されるプロセスが利用できる、バイト単位の物理メモ リの量です。これは、Zeroed、Free、Standbyのメモリリストのメモリ空間を加算 することによって計算されます。Freeメモリはメモリ使用の準備が整っているメモリ です。Zeroedメモリはゼロで満たされたメモリのページで構成されていて、後続のプ ロセスが前のプロセスによって使用されたデータを見ることができないようにします。 Standbyメモリは、ディスクへのルート上でプロセスのワーキングセット（プロセス の物理メモリ）から削除されているメモリですが、再呼び出しのためになおも利用可 能です。このカウンタは最後に観察された値だけを表示しますが、それは平均値では ありません。 34 第1章 パフォーマンスのトラブルシューティング法 ◆ SQL Server:Buffer Manager: Buffer Cache Hit Ratio これは、ディスクからの読み込みが必要ではないバッファプール内で見つかったペ ージの割合を示します。ほとんどの実稼働ワークロードでは、この値は高く、90を超 えます。 ◆ SQL Server:Buffer Manager: Page Life Expectancy これは、ページが参照されていない場合にバッファプール内に保持される秒数です。 値が低いと、バッファプールはメモリ不足です。 ◆ SQL Server:Buffer Manager: Checkpoint Pages/Sec これは、フラッシュされるすべてのダーティページを要求するチェックポイントや 他の操作によってフラッシュされたページの数です。これはワークロードのバッファ プールアクティビティの増加を示します。 ◆ SQL Server:Buffer Manager: Lazywrites/Sec これは、バッファーマネージャのレイジーライターによって書き込まれたバッファ の数を示します。前述の、1秒あたりのチェックポイントページと類似しています。 通常、バッファプールはSQL Serverによってコミットされたメモリのほとんどを 占めます。バッファプールに属するメモリ量を決めるには、D B C C MEMORYSTATUS出力を調べることができます。この出力では、buffer countsセク ションを見て、targetとcommittedの値を調べてください。次に示すのは、SQL Serverが通常の負荷に到達した後のDBCC MEMORYSTATUS出力の一部です。 Buffer Counts ---------------------Committed Target Hashed Stolen Potential External Reservation Min Free Visible Available Paging File Buffers ----------201120 201120 166517 143388 0 256 201120 46040 ◆ Committed この値は、コミットされている全バッファを示します。コミットされているバッフ ァはそれに関連する物理メモリを持っています。Committedの値はバッファプールの 現在のサイズです。この値にはAWEサポートが有効な場合に割り当てられる物理メ モリが含まれます。 ◆ Target この値はバッファプールの対象サイズを示します。SQL Serverがページングを起 こさずにコミットできるのは8KB単位のページ数なので、この値はSQL Serverによ 1.2 トラブルシューティングの概要 35 って定期的に計算されます。SQL ServerはWindows OSシステムからのメモリ通知が 低いとそれに答えてこの値を低くします。通常に読み込まれたサーバー上の対象ペー ジの数の減少は、外部物理メモリ不足に反応していることを示します。 物理メモリ不足はSQL Serverそれ自体の内部からも起こります。たとえば、プロ シージャキャッシュが非常に大きくなると、バッファプールを圧迫します。内部メモ リ不足はSQL Server自体によるものなので、論理ステップは、バッファ分散に不調 和がないかどうかチェックしてSQL Server内部のメモリ分散を調べることです。 DBCC MEMORYSTATUS出力から、奪われたページ数を調べることもできます。 Buffer Distribution ------------------Stolen Free Cached Database (clean) Database (dirty) I/O Latched Buffers ----------32871 17845 1319 148864 6033 0 0 全コミット済みページのうち奪われたページの割合が高い（75∼80％を超える）と きは内部メモリ不足の徴候です。 同様に、内部コンポーネントがバッファプールの外側で大量のメモリを割り当てる 場合、バッファプールを再び圧迫します。システムモニタカウンタprocess: Private Bytesから、バッファプールの現在のサイズを減算すれば、その不足分を計算できま す。高い値は内部メモリ不足を表します。通常、SQL Serverプロセスにロードされ ているコンポーネント（COMオブジェクト、リンクサーバー、拡張ストアドプロシー ジャ、SQLCLR、その他）はバッファプールの外側でのメモリ使用の一因になります。 特に、コンポーネントがSQL Serverメモリインターフェイスを使用していない場合、 これらのコンポーネントによって使用されるメモリを追跡する簡単な方法はありませ ん。 ■ 仮想メモリ不足の検出 仮想メモリ不足を識別する簡単な方法は、スワップページファイルに現在のメモリ 割り当てを収納できるだけの十分な空間があるかどうかチェックすることです。この ためには、パフォーマンスビューの中のタスクマネージャを開き、コミットチャージ セクションを調べます。TotalがLimitに近ければ、ページファイル空間が少ないまま 実行されている可能性があります。Limitはページファイル空間を拡張せずにコミッ トできるメモリの最大量を表しています。タスクマネージャの中のコミットチャージ トータルが示しているのは、ページファイルがどれくらい使用できるかという可能性 であり、RAMの中にあるコミットされたメモリの一部としての実際の使用量ではな 36 第1章 パフォーマンスのトラブルシューティング法 いことに注意してください。ページファイルの実際の使用量は物理メモリが不足して いる状態では増えます。仮想メモリ不足を識別するために次のシステムモニタカウン タも使用できます。 ◆ Paging File: %Usage これは、ページファイルインスタンスの使用量をパーセントで表します。 ◆ Memory: Commit Limit これは、ページングファイルを拡張せずにコミットできる仮想メモリの量を表し、バ イト単位で測定されます。コミットされたメモリはディクスページングファイル上に 予約されている空間を持つ物理メモリです。このカウンタは最後に観察された値だけ を表示します。平均値ではありません。 ■ メモリ不足のトラブルシューティングと分離 SQL Serverインスタンスにメモリ不足が発生していることを確認した時点で、次 のような一般的なステップを取ることができます。外部メモリ不足を減らす、より多 くの物理メモリを追加する、32ビットインストレーションでAWEモードを有効にす るなどです。 外部物理メモリ不足が発生したら、システムの物理メモリの主要コンシューマを特 定する必要があります。このためにはProcess:Working Setパフォーマンスカウンタ、 あるいはタスクマネージャの［Processes］タブのMemUsage列を調べて、最も大き いコンシューマ（利用しているプロセス）を特定してください。システム上の物理メ モリの総使用量は次のカウンタを合計することによって概算できます。 ◆ Process: Working Set 各プロセスに対するカウンタ ◆ Memory: Cache Bytes システムワーキングセットに対するカウンタ ◆ Memory: Pool Nonpaged Bytes ページングされないプールのサイズに対するカウンタ ◆ Memory: Available Bytes （タスクマネージャのAvailable値と同じ） 外部物理メモリ不足がない場合、Process: Private Bytesカウンタあるいはタスクマ ネージャのVMサイズはProcess: Working Setサイズに近くなるはずです。つまり、 ページアウトされるメモリがないということです。大きなワーキングセットを持つ、 SQL Server以外のプロセスを分析する必要があります。ソリューションの1つはSQL Server が稼働するハードウェア上でこのようなアプリケーションを実行しないこと です。 1.2 トラブルシューティングの概要 37 AWEメカニズムを使うと32ビットアプリケーションは固有の32ビットアドレス限 界を超える物理メモリを操作できます。64ビットプラットフォームでは、より多くの 物理メモリにアクセスするためにAWEメカニズムは不要です。ただし、これは利用 可能ですが、32ビットプラットフォームとは違って、AWE Enabled構成オプション を設定する必要はありません。AWEメカニズムによって割り当てられたメモリペー ジは64ビットプラットフォームではロックされたページと言われます。32ビットと64 ビットのプラットフォーム両方とも、AWEメカニズムによって割り当てられたメモ リはページアウトできません。このことはアプリケーションには有利です（これが、64 ビットプラットフォームでAWEメカニズムを使う理由の1つです） 。これは、システ ムと他のアプリケーションが利用できるRAMの容量に影響を与え、不利な影響にな ることもあります。このため、AWEを使用するためには、メモリ特権の中のロック ページをSQL Serverを実行するアカウントに対して有効にしてください。トラブル シューティングの観点から重要なのは、SQL ServerバッファプールはAWEによって マップされたメモリを使用することです。しかし、データベースの（ハッシュされた） ページだけがAWEによって割り当てられたメモリを最大限利用できます。AWEメカ ニズムによって割り当てられたメモリはタスクマネージャによってレポートされず、 Process: Private Bytesパフォーマンスカウンタの中にもレポートされません。しか し、DMVとDBCCメモリステータスコマンドはAWE対応です。たとえば、次のDMV クエリを使うとバッファプールによって使用されたメモリの総量（AWEを含む）を 知ることができます。 SELECT SUM(multi_pages_kb + virtual_memory_committed_kb + shared_memory_committed_kb + awe_allocated_kb) AS [Used by BPool, Kb] FROM sys.dm_os_memory_clerks WHERE type = 'MEMORYCLERK_SQLBUFFERPOOL' 次はサンプル出力です。 Used by BPool, Kb -------------------------8269684 (1 row(s) affected) 内部コンポーネントがバッファプールからページのほとんどを奪ったために内部メ モリ不足が発生していることを（DBCC MEMROYSTATUSを使用して）決定した ら、次のDMVクエリを使ってバッファプールからほとんどのページを奪っている内部 コンポーネントを特定できます。 SELECT TOP 10 type, SUM(single_pages_kb) AS stolen_mem_kb FROM sys.dm_os_memory_clerks 38 第1章 パフォーマンスのトラブルシューティング法 GROUP BY type ORDER BY SUM(single_pages_kb) DESC 次のような出力になります。 Type -------------------CACHESTORE_PHDR MEMORYCLERK_SOSNODE CACHESTORE_SYSTEMROWSET CACHESTORE_SQLCP MEMORYCLERK_SQLGENERAL MEMORYCLERK_SQLSTORENG USERSTORE_SCHEMAMGR CACHESTORE_BROKERTBLACS OBJECTSTORE_LOCK_MANAGER USERSTORE_DBMETADATA OBJECTSTORE_SERVICE_BROKER stolen_mem_kb ------------20020 1104 1040 880 832 816 400 368 352 288 256 内部コンポーネントによって使われるメモリの制御はありませんが、ほとんどのメ モリを使用している内部コンポーネントを決定すると問題究明の範囲を限定できます。 複数ページアロケータを持つ次のクエリを使用すると、バッファプールの外側でメモ リを割り当てた内部コンポーネントを特定できます。 SELECT type, SUM(multi_pages_kb) AS memory_allocated_KB FROM sys.dm_os_memory_clerks WHERE multi_pages_kb != 0 GROUP BY type 出力は次のとおりです。 type -----------------------MEMORYCLERK_SQLSTORENG OBJECTSTORE_SNI_PACKET MEMORYCLERK_SQLOPTIMIZER MEMORYCLERK_SQLGENERAL MEMORYCLERK_SQLBUFFERPOOL MEMORYCLERK_SOSNODE CACHESTORE_STACKFRAMES MEMORYCLERK_SQLSERVICEBROKER MEMORYCLERK_SNI CACHESTORE_XPROC memory_allocated_KB ------------------56 96 72 1696 256 8352 16 192 32 50120 こ こ で 、X P R O C （拡 張 ス ト ア ド プ ロ シ ー ジ ャ ）が バ ッ フ ァ プ ー ル の 外 側 （MemToLeave領域）で50MBのメモリを使用しているのがわかります。複数ページ 1.2 トラブルシューティングの概要 39 アロケータによって大量のメモリ（100∼200MB、それ以上）が割り当てられている ときは、さらに調査が必要です。 仮想メモリ不足の場合、次のような一般的な推奨事項があります。1）ページファ イルのサイズを増やす。2）妥当なら、/3GBオプションを使用する。3）32ビットハ ードウェアを実行している場合、VASの大きなチャンクを取るのでAWEの使用を避 ける。しかし、一方では、物理メモリ不足を最小限に抑えるにはAWEを使用する必 要がある。4）8テラバイト（Itaniumシステムの場合は7テラバイト）のユーザーモー ドアドレス領域を提供する64ビットハードウェアに切り替える。 メモリ関連情報を得るために使用できるDMVはもっとたくさんあります。注目す べ き は s y s . d m _ o s _ r i n g _ b u f f e r で す 。詳 細 に つ い て は 、ホ ワ イ ト ペ ー パ ー 『Troubleshooting Performance Problems in SQL Server 2005』を見てくださ い。連携Webサイトおよびhttp://www.microsoft.com/technet/prodtechnol /sql/2005/tsprfprb.mspxでも閲覧できます。 ■■ I/Oボトルネック SQL ServerのパフォーマンスはそのI/Oサブシステムのパフォーマンスに大きく依 存します。データベースが物理メモリやアプリケーションアクセスにまったく合わな いときや、メモリのサイズに合ったデータのサブセットだけを処理するのではないと き、SQL Serverはページを次々とメモリから出し入れする必要があります。こうな ると、大きなI/Oトラフィックが発生します。I/Oはデータページだけに限定されませ ん。DDL／DMLアクティビティに応じて、SQL Serverは膨大な数のログレコードを 生成できます。いくつかのログ最適化は別として、データベースに対するほとんどす べての変更はログ記録され、これらのログレコードはトランザクションがコミットさ れたことをSQL Serverが宣言する前にディスクへフラッシュされる必要があります。 同様に、SQL Serverはtempdbを使用してクエリ処理やDDL操作の中間結果を保存 します。たとえば、SQL ServerはOrder By句が指定されたときやインデックスを作 成／再構築するときにtempdbを使用してデータを並び替えます。また、SNAPSHOT ISOLATION、オンラインインデックス構築、複数アクティブ行セット（MARS） 、 トリガなどをサポートするために行バージョンを保存する（SQL Server 2005のみ） 目的でもtempdbを使用してデータを並び替えます。さらに、アプリケーションは tempdbを使用して中間結果を保存します。したがって、I/Oサブシステムはアプリケ ーションワークロードの要求をサポートするように構成されることが重要です。I/O システムがワークロードの変動や変更に対応できないと、アプリケーションにパフォ ーマンス低下が起こります。I/Oボトルネックは必ずしもI/O帯域幅を増やす必要が あることを示唆しているわけではありません。この節の後半で説明しますが、I/Oボ トルネックはSQL Serverの他の部分に問題がある徴候です。 ■ I/Oボトルネックの検出 システムモニタカウンタはI/O問題を識別するための非常に役に立つセットを提供 40 第1章 パフォーマンスのトラブルシューティング法 します。これらのカウンタを使うとI/Oサブシステムや個別の物理ディスクの全体的 なパフォーマンスを知ることができます。I/Oボトルネックを特定するためによく使 われるカウンタは次のとおりです。注意してほしいのは、これらの数値は平均値であ り、ポーリング間隔内の値にはスパイクが隠れていることです。これらのカウンタの 多くを調べて検証するとよいでしょう。 ◆ PhysicalDisk Object: Avg. Disk Queue Length サンプリング期間に選択された物理ディスク上でキューに登録された物理読み込み 要求と物理書き込み要求の平均値を示します。I/Oシステムがオーバーロードされる と、多くの読み込み／書き込み操作が待ち状態になります。ディスクキューの長さが、 SQL Serverのピーク使用時に1個の物理ディスクあたり2の値を頻繁に超えるような ら、I/Oボトルネックが発生しています。 ◆ PhysicalDisk Object: Avg. Disk Sec/Read or Avg. Disk Sec/Write ディスクからの読み込みとディスクへの書き込みの秒単位での平均時間です。一般 的な指針は次のとおりです。 ● 10ミリ秒未満は非常によい ● 10∼20ミリ秒はよい ● 20∼50ミリ秒は遅い、要注意 ● 50ミリ秒を超える場合、深刻なI/Oボトルネックが考えられる ◆ PhysicalDisk: Disk Reads/Sec or Disk Writes/Sec ディスク上での読み込みあるいは書き込み操作の割合です。この数値が必ずディス ク容量の85％未満になるようにしてください。85％の容量を超えるとディスクアクセ ス時間は指数関数的に増えます。 システムモニタカウンタは物理ディスクレベルでI/Oパフォーマンス情報を提供し ますが、ファイルレベルではありません。ファイルレベルの情報は、ログ、データ、 tempdbファイルが同じディスクで混合している場合に役立ちます。このような場合、 たとえばファイルレベルの情報を利用すると、I/Oボトルネックを引き起こしている のがログ記録あるいはtempdbであるとき、それを分離できます。同様に、この情報 が役立つのは、さまざまなファイルグループからのファイルに同じ物理ディスクを共 有させていて、特殊なファイルにマップされたオブジェクトがI/Oボトルネックの原 因かどうかを知りたいときです。この情報を使うと、異なる物理ディスクにオブジェ クトを再マップする選択ができ、I/O待機時間を最小限に抑えることができます。 このようなファイルを識別するために使用できるDMVクエリがあります。2つの列、 io_stall_read_msとio_stall_write_msによって、SQL Serverが開始したときから ファイル上で発行された読み込みと書き込みをSQL Serverが待った時間が示されま す。意味のあるデータを得るためには、短い実行時間のこれらの数値のスナップショ ットを作り、これらの数値のスナップショットとベースラインの数値を比べてくださ い。大きなズレがあれば、分析する必要があります。 1.2 トラブルシューティングの概要 41 SELECT database_id, file_id, io_stall_read_ms, io_stall_write_ms FROM sys.dm_io_virtual_file_stats(NULL, NULL) ラッチ待機を調べることによって全体的なI/Oボトルネックも識別できます。これ らのラッチ待機は、ページが読み込みや書き込みのためにアクセスされるときやペー ジがバッファプールの中で利用できないとき、物理I/O待機の原因になります。ペー ジがバッファプールの中で見つからないとき、非同期I/Oが記録され、次にそのI/Oの 状態がチェックされます。I/Oが既に完了していれば、ワーカーは正常に開始します。 完 了 していなければ、要 求 の型 に応 じてワーカーはP A G E I O L A T C H _ E X や PAGEIOLATCH_SHで待機します。次のDMVクエリを使用してI/Oラッチ待機統計 値を見つけることができます。 SELECT wait_type, waiting_tasks_count, wait_time_ms, signal_wait_time_ms FROM sys.dm_os_wait_stats WHERE wait_type LIKE 'PAGEIOLATCH%' ORDER BY wait_type 興味深いラッチ待機はPAGEIOLATCH_SHとPAGEIOLATCH_EXです。これら の待機は、I/Oの中にあるバッファに対するラッチ上でタスクが待機しているときに 発生します。この種の待機が長いときはディスクサブシステムに問題があります。 wait_time_ms列はワーカーがSUSPENDED状態とRUNNABLE状態で費やす時間 を含みます。一方、signal_wait_time_ms列はワーカーがRUNNABLE状態で費や す時間を表します。したがって、この2つ（wait_time_msとsignal_wait_time_ms） の差は、実際にはI/Oが完了するのを待って費やされる時間を表します。ここで注意 してほしいのは、上記DMVはSQL Serverが開始されてからの待機を返すことです。 したがって、意味のある情報を得るには、対象となる期間の差を計算し調べてくださ い。 ■ I/Oボトルネックのトラブルシューティングと分離 I/Oボトルネックが発生したとき、ディスクI/Oサブシステムが原因であると想定さ れがちです。実際、そうかもしれませんが、結論を出す前に最終的にI/Oボトルネッ クにつながる2つの要因を検討する必要があります。 最初に、SQL Serverメモリがワークロードに対して適切に構成されているかどう かをチェックしてください。十分な物理メモリがないと、バッファプール内のページ 42 第1章 パフォーマンスのトラブルシューティング法 は無理矢理に再利用されることになり、これは物理I/Oそして最終的にはI/Oボトル ネックにつながります。メモリボトルネックの検出とトラブルシューティングの方法 については前の節を参照してください。SQL Serverが物理メモリに対して適切に構 成されていないことや、SQL Serverとボックスを共有している他のアプリケーショ ンによって引き起こされた物理メモリ不足がSQL Serverに発生していることがわか ったら、この問題にまず取り組み、これがI/Oボトルネックにつながっていないかど うか検証してください。 第2に、最も多くのI/Oを発生させているクエリ／バッチを調べてください。そのク エリプランを調べて、多数のI/Oが不適切なクエリプランの結果なのか欠落インデッ クスの結果なのかを確かめる必要があります。次のDMVクエリは最も多くのI/Oを生 成している上位10位のクエリ／バッチを返します。このクエリのバリエーションも使 用すると、1秒あたり最も多くのI/Oを実行する上位10位のクエリを見つけることもで きます。 SELECT TOP 10 (total_logical_reads/execution_count) AS avg_logical_reads, (total_logical_writes/execution_count) AS avg_logical_writes, (total_physical_reads/execution_count) AS avg_phys_reads, execution_count, (SELECT SUBSTRING(text, statement_start_offset/2 + 1, (CASE WHEN statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(MAX),text)) * 2 ELSE statement_end_offset END - statement_start_offset)/2) FROM sys.dm_exec_sql_text(sql_handle)) AS query_text, plan_handle FROM sys.dm_exec_query_stats ORDER BY (total_logical_reads + total_logical_writes) DESC 上記クエリはSQLテキストと対応するクエリプランを出力します。前述のように、 クエリプランとSQLステートメントを評価してI/Oの数を減らすことができることを 確かめてください。上記DMVの簡素化されたクエリ出力を示します。ワークロード について興味深い観察がいくつかあります。まず、JOIN演算子を伴なうクエリが最大 数のI/Oを生成していることがわかります。第2に、集計クエリは物理I/Oをまったく 発生していません。つまり、このクエリが参照するすべてのページは既にバッファプ ールの中にあるということです。したがって、このクエリアはおそらく、t1テーブル の6,008ページすべてをバッファプールの中へ運ぶJOINクエリの後に実行されたので しょう。第3に、物理I/Oの平均値は1.256であり、これは論理I/Oの平均値である 16,536未満です。つまり、t1テーブルとt2テーブルからのサブセットページは既にバ ッファプールの中にあり、そして、必要なページのいくつかを前もってフェッチする ことによってSQL Serverは発生した物理I/Oを減らしたということです。あるいはそ のいずれかです。第4に、プランハンドルが示されています。これを使用すると実際の 1.2 トラブルシューティングの概要 43 クエリプランを生成し、多数のI/Oの原因を理解できます。 avg_logical_reads ----------------16536 6008 avg_logical_writes ---------------4 0 avg_phys_reads -------------1256 0 Execution_count --------------3 query_text -----------select c1, c5 from t1 INNER HASH JOIN t2 ON t1.c1 = t2.c4 order by c2 select sum(c1) from t1 1 Plan_handle ----------0x06000500E401FC06B8E1A003000000000000000000000000 0x060005005B9B8F16B841B703000000000000000000000000 I/Oの数を減らすことによってクエリパフォーマンスを改善するために実行できる ことが多くありますが、その中でまず、役立つインデックスが存在するかどうかをチ ェックします。SQL Server 2005では、DMVのセットが欠落インデックスを識別す るために提供されています。これにはインデックスの潜在的有用性（欠落インデック スが何回使われたか、など）も含まれます。DMVを使用して欠落インデックスを識 別する方法の一例を見て行きましょう。 t_sampleには5列があるとします。最初に、2つのインデックス（1つのクラスタ化 インデックスをc1列に、1つの非クラスタ化インデックスをc4列に）を作成した後に このテーブルに1,000行を読み込みます。次はスクリプトです。 CREATE TABLE t_sample (c1 int, c2 int, c3 int, c4 int, c5 char(5000)) GO -- データを挿入し、インデックスを作成します。 DECLARE @i int SELECT @i = 0 WHILE (@i < 1000) BEGIN INSERT INTO t_sample VALUES (@i, @i + 1000, @i+2000, @i+3000, 'hello') SET @i = @i + 1 END -- インデックスを作成します。 CREATE CLUSTERED INDEX t_sample_ci ON t_sample(c1) CREATE NONCLUSTERED INDEX t_sample_nci_c4 ON t_sample(c4) ここで、次のようにループの中で複数の選択を実行します。 DECLARE @i int 44 第1章 パフォーマンスのトラブルシューティング法 SELECT @i = 0 WHILE (@i < 100) BEGIN SELECT SUM(c1 + c2 + c3) FROM t_sample WHERE c1 BETWEEN @i AND @i+50 SELECT SUM(c2) FROM t_sample WHERE c2 BETWEEN @i+1000 AND @i + 1100 SELECT SUM(c3) FROM t_sample WHERE c3 BETWEEN @i +2000 AND @i+2400 SET @i = @i + 1 END c2列とc3列にインデックスがないことにお気付きでしょう。したがって、2番目と3 番目のSELECTステートメントに対して、SQL Serverはテーブルスキャンを実行す る必要があります。c2列とc3列のインデックスは実際のところ欠落インデックスです。 次は、欠落インデックスとその有用性を識別するために実行できるDMVクエリです。 -- DMVを使用して、どのインデックスが存在していないのか、どのインデックスが役立つのかを決定しま す。 SELECT t1.object_id, t2.user_seeks, t2.user_scans, t1.equality_columns, t1.inequality_columns C01621961.fm Page 36 Tuesday, August 14, 2007 4:41 AM FROM sys.dm_db_missing_index_details AS t1, sys.dm_db_missing_index_group_stats AS t2, sys.dm_db_missing_index_groups AS t3 WHERE database_id = DB_ID() AND object_id = OBJECT_ID('t_sample') AND t1.index_handle = t3.index_handle AND t2.group_handle = t3.index_group_handle 次の出力は、c2列とc3列にインデックスがあると、それぞれはインデックスによる 100回のシークで利用されていたことを示しています。欠落インデックス関連のDMV はより多くの役立つ情報を提供できます。詳細についてはBOLを参照してください。 object_id user_seeks ----------- ---------1989582126 100 1989582126 100 user_scans ---------0 0 equality_columns ---------------NULL NULL inequality_columns -----------------[c2] [c3] さまざまなクエリによって実行されたメモリとI/Oに関連する問題に対処した後で、 なおもI/Oボトルネックが発生するようなら、ワークロードの要求を満たすためにI/O サブシステムを更新することを検討してください。 I/Oサブシステムを更新するとき、まずI/Oシステムの容量をチェックしてくださ い。I/Oシステムが適切に構成されていないと、配信が可能なI/O帯域幅を得ること ができないでしょう。Microsoftが提供するSQLIO.exeツールを使用すると、一定の 1.2 トラブルシューティングの概要 45 I/O構成のI/O容量を決定できます。このツールを次のサイトからダウンロードできま す。http://www.microsoft.com/downloads/details.aspx?familyid=9a8b005b84e4-4f24-8d65-cb53442d9e19&displaylang=en ■■ tempdbボトルネック tempdbデータベースはSQL Serverインスタンスの共有リソースです。アプリケー ションはtempdbを使用して後続の処理のために一時テーブルあるいはテーブル変数 の中に中間データや一時データを保存します。たとえば、複雑なクエリの結果を複数 回反復する必要があるとき、その結果を一時テーブルに実際に保存できたらとお考え でしょう。こうすると複雑なクエリを複数回実行する手間を減らすことができます。 通常のデータベースのテーブルの中に中間結果セットをどうして保存できないのかと 不思議に思うかもしれませんが、簡単に答えると、できます。しかし、次のような問 題が出てきます。 ● ユーザーはデータベースの中にテーブルを作る権限を持つことが必要です。 CREATE TABLE権限を持つことができるユーザーはほんの少数です。 ● 2つの理由でユーザーデータベースにログオーバーヘッドが増えます。第1に、 ユーザーデータベースの復旧モデルはFULLに設定されます。第2に、ユーザー データベースの中のログレコードはUNDOとREDOの両方の情報を持たなけれ ばなりません。SQL Server 2005からは、UNDO情報はtempdbの中のログに記 録されません。 ● これらの中間テーブルが削除されないと、ユーザーデータベースが乱雑になりま す。実際、クエリが作成しようとしている中間テーブルが既に存在していると、 中間テーブルはクエリエラーを起こします。 ● 中間テーブルはユーザーデータベースに対してI/Oパスをオーバーロードする可 能性があります。 明示的に#<table>および##<table>と名づけられた一時テーブルがtempdbの中に 作られます。これらのオブジェクトの両方ともセッションのスコープが設定されてい ますが、##<table>はそれを使用しているすべてのセッションの有効期限が切れるま で、あるいはセッションが終了するまで有効です。一方、#<table>は中でそれが作ら れたスコープ（たとえば、ストアドプロシージャやセッション）の有効期限が切れる とき、あるいはそのスコープが終了するときに破棄されます。このような理由で、 #<table>と##<table>はそれぞれローカル一時テーブル、グローバル一時テーブルと 言われます。最終的にユーザーはtempdbの中でCREATE TABLE権限が不要になり ます。このために、ほとんどのアプリケーションは中間結果を保存するために一時テ ーブルを使用します。一時テーブルのほかに、テーブル変数を使用して中間結果を保 存することもできます。テーブル変数はローカルテーブルのようなもので、tempdbの 中に保存されます。しかし、そのスコープは、テーブル変数が中で定義されていて、ロ 46 第1章 パフォーマンスのトラブルシューティング法 ックとログのための低いオーバーヘッドを持つバッチ／要求に限られます。テーブル 変数にはいくつかの制限があります。たとえば、テーブル変数にインデックスを作成 できません。また、統計値は維持されません。テーブル変数が通常使われるのは、テ ーブル値関数の行セットとして返される一時行セットを表すためです。ユーザーテー ブルと同様に、メタデータ情報は一時テーブルとテーブル変数に対して作成されます。 テーブル変数の使用の詳細については、SQL Server 2005 BOLを参照してください。 これらのオブジェクトはユーザーによって明示的に作成されるので、ユーザーオブジ ェクトとして分類されます。 SQL Serverはtempdbを使用してSQLステートメントの処理中に中間行セットを 保存します。たとえば、 ● ORDER BY句を処理するために外部ソートを実行するとき ● インデックスを作成、あるいは再構築するとき ● ハッシュテーブルがインメモリに適合しない場合にハッシュ結合を実行するとき ● カーソル結果セットを保存するとき、データをスプールするとき、あるいはLOB 変数を保存するために ● さらにもっと多くの場合 SQL Serverはこのデータを#<table>や##<table>ではなく、ワークテーブル、ワ ークファイル、ソートファイルのような内部オブジェクトの中に保存します。内部オ ブジェクトはいろんな意味でユーザーオブジェクトとは異なります。まず、これらの オブジェクトはカタログの中に表現されないので、DDLオーバーヘッドはありません。 第2に、これらのオブジェクトはTransact-SQLステートメントにスコープが設定され ており、ステートメントが完了した後は破棄されます。第3に、これらのオブジェクト についての操作は、割り当てがログされている場合にはソートファイルの例外によっ てログされません。このため、これらのオブジェクトはユーザーオブジェクトよりも かなり低いオーバーヘッドを持ちます。 SQL Server 2005からは、tempdbの中にバージョンストアと呼ばれる新しい種類の エンティティが入っています。このバージョンストアは古いけれどもトランザクショ ン的には一貫しているデータ／インデックス行を保存するために使われます。これら の行バージョンを使用して次のことを実行できます。 ● 削除された／挿入された行セットを生成するためのトリガ。SQL Server 2005以 前は、挿入された／削除された行セットはログチェーンを逆方向に横断するこ とによって生成されました。主な欠点は、ログへのアクセスが無作為になるの で、ログのスループットに影響が出る可能性があることでした。 ● SNAPSHOT ISOLATIONとREAD COMMITTED SNAPSHOT ISOLATION ● オンラインインデックス構築 ● 複数アクティブ行セット（MARS） 1.2 トラブルシューティングの概要 47 行バージョンは必要がないとき削除することさえ可能です。バックグラウンドスレ ッドが毎秒実行されて、行バージョンをクリーンアップします。行バージョンに依存 する長期実行トランザクションはバージョンストアのクリーンアップを阻止するので、 tempdb内で領域問題を引き起こすことがあります。tempdb内のオブジェクトとバー ジョンストアの詳細については、ホワイトペーパー『Working with tempdb in SQL Server 2005』を見てください。連携Webサイトおよびhttp://www.microsoft.com/ technet/prodtechnol/sql/2005/workingwithtempdb.mspxでも閲覧できます。 tempdbを頻繁に使用するクエリ／バッチや、ワークロードに対して適切に構成さ れていないクエリ／バッチがある場合、アプリケーションはtempdb関連のパフォー マンス問題に入ることがあります。tempdbに関連する一般的なパフォーマンス問題 のいくつかをあげます。 ◆ I/Oボトルネック tempdbの中の内部オブジェクトとユーザーオブジェクトの両方が作成され、設定 されます。これらのオブジェクトは次に、グローバル一時テーブルを除いたステート メントやセッションスコープ内のDML操作に対してアクセスされます。したがって、 これらのオブジェクトに対するページはバッファプールの中にある可能性が高くなり ます。しかし、これらのオブジェクトに割り当てられたページの総数とバッファプー ルのサイズに応じて、tempdb上に大きなI/Oが発生することがあります。たとえば、 大きなテーブル上にインデックスを作成するとき、tempdb内のソート操作は複数パ スで実行される必要があり（すなわち、外部ソート） 、読み込み／書き込み操作に多く の物理I/Oが発生します。データとインデックスページのほかに、ユーザーオブジェ クトのDML操作はtempdb内にログレコードを生成し、物理I/Oの別の原因になるこ ともあります。そして最終的に、物理I/Oを引き起こすバージョンストアがあります。 特に、長いバージョンチェーンがあるとき、通常は長時間実行のトランザクションに なります。 ◆ 割り当てボトルネック tempdbの中でデータを操作するデータ変更操作はページの割り当てと割り当て解 除につながることがよくあります。tempdb内の過度のページ割り当て／割り当て解 除アクティビティによって、割り当て情報を追跡する、割り当て構造（GAMページ およびSGAMページ）の中で競合が起こります。 ◆ DDLボトルネック DDL操作の競合はtempdb内のユーザーオブジェクトに対してのみ起こります。ワ ークテーブルのような内部オブジェクトのメタデータはキャッシュされ、システムカ タログの中に作成されません。しかし、ローカルおよびグローバル一時テーブルやテ ーブル変数のようなユーザーオブジェクトのメタデータは、システムカタログの中に 作成されます。ワークロードやアプリケーションがtempdbの中に多数のユーザーオ ブジェクトを作成したり削除したりする場合、システムカタログテーブルの中に競合 があるとワークロードのパフォーマンスに影響が出ます。 48 第1章 パフォーマンスのトラブルシューティング法 ◆ tempdbの拡張 SQL Serverは開始するたびに、tempdbを再作成します。tempdbの中のすべてのオ ブジェクトは明らかに一時的であり、復旧や再保存は実行されません。tempdbが再 作成されるとき、そのサイズは最後に構成されたサイズあるいはtempdbが最初に作 られたときの既定サイズになります。ワークロードが安定した状態でもっと大きな tempdbを必要とするとき、2つの問題が出てきます。最初に、アプリケーションは tempdbが拡張する間、一時停止する必要があります。2つ目は、頻繁な自動拡張はフ ァイルの物理フラグメンテーションにつながります。これは特に、物理ディスクが他 のデータベース／アプリケーションに共有されている場合です。物理フラグメンテー ションというのは、ファイルが物理ディスク上で複数の非連続フラグメントで構成さ れていることであり、このような場合、ディスクにアクセスするときの待機時間が増 えます。同様に、ワークロードが最後に構成された値よりもずっと大きなサイズの tempdbログファイルを必要とすると、アプリケーションはログファイルが拡張して いる間一時停止する必要があります。こうなるとデータファイルの場合と同じような 物理フラグメンテーションも起こります。 ここまで、tempdbに関連するパフォーマンス問題のタイプを説明しました。次の 節ではこれらの問題の検出とトラブルシューティングについて説明します。 ■ tempdbボトルネックの検出 前述のI/Oボトルネックの節では、システムモニタカウンタを使用して物理ディス クレベルで、あるいはsys.dm_io_virtual_file_statsDMVを使用してファイルレベル でI/Oボトルネックを特定する方法を説明しました。tempdbをそれ自体の物理ディス クに作成すれば、システムモニタカウンタを使用してtempdb特定I/Oボトルネックを 検出できます。作成しない場合には、前述のI/Oボトルネックの節で説明したDMVを 使います。一般的には物理ディスクのtempdb自体のグループの中にtempdbを作成す るのがお勧めです。それは次のような理由からです。 ● tempdb が過度に使われると、tempdb 内のI/Oボトルネックはtempdb と物理 ディスクを共有するデータベースにアクセスするすべてのアプリケーションのパ フォーマンスに影響を与えます。 ● tempdb はウォームスタート時のクラッシュリカバリを必要としないので、ト ランザクションがコミットされるときそのログレコードをフラッシュしたり物理 メディアに保存したりする必要はありません。このためtempdb に対してどん な種類のディスクを使用するかについては多くの選択があります。たとえば、 RAMディスクや大きなキャッシュを持つディスクも使用できます。詳細につい ては、KB 917047情報を参照してください。 データファイルとログファイル両方のtempdb自動拡張の検出に、SQLプロファイ ラとトレース、DATA FILE AUTO GROWイベント、LOG FILE AUTO GROWイ ベントを使用できます。tempdb内のデータファイルとログファイル両方の現在のサ 1.2 トラブルシューティングの概要 49 イズを返すsys.database_filesカタログビューを定期的にポーリングすることもでき ます。これらのファイルのサイズが時間とともに変化するようなら、自動拡張が原因 か、ALTER DATABASEコマンドを使用するファイルのサイズが明示的に変化して いることが原因かのいずれかの理由でファイルが拡張しています。しかし、どんな原 因でファイルが拡張しているかとは関係なく、物理フラグメンテーションが起こりま す。これはできるだけ避けなければなりません。 割り当てボトルネックは、次に説明する割り当て構造のラッチを取得するために複 数のスレッドが待機中であることによって発生します。割り当て構造の詳細について は『インサイドMicrosoft SQL Server 2005 ストレージエンジン編』で説明してあ ります。 ● GAMページはエクステント割り当て情報を追跡します。各エクステントはビッ トによって表されます。ビットの値が1の場合、エクステントは割り当てのため に空いています。 ● SGAMページは混合エクステントの割り当て情報を追跡します。混合エクステ ントは複数オブジェクトからのページを含みます。 ● PFSページはエステント内のページ割り当て状態とページ上で利用できる空き 領域も追跡します。ページの割り当てや割り当て解除が行われるとき、SQL Serverは対応するPFS情報を更新してページの割り当て状態を示します。同様 に、行が挿入されたり削除されたりすると、SQL Serverは対応するPFS情報 を更新する必要があります。 おわかりのように、ワークロードが、ページの空き領域を変化させるようなページ 変更操作とデータ変更操作の割り当て／割り当て解除を過度に実行していると、SQL Serverに割り当てボトルネックが発生します。次のDMVクエリを使用して割り当て ボトルネックがあるかどうかを判断できます。このクエリは1つあるいは複数のスレッ ドがtempdb内のページでラッチを取得するために待機状態であるかどうかを調べま す。このDMVは待機中の現在のワーカーを示すことに注意してください。割り当て ボトルネックを識別するためにこのDMVを頻繁にポーリングする必要はありません。 SELECT session_id, wait_duration_ms, resource_description FROM sys.dm_os_waiting_tasks WHERE wait_type LIKE 'PAGE%LATCH_%' AND resource_description like '2:%' tempdbのデータベースIDは2なので、検索引数2:%はどのファイルであっても tempdb内のページを表します。そのページがGAMなのか、SGAMなのか、PFSなの かを知ることが秘訣です。PFSページはデータファイル（第1ページ）の中のファイル ヘッダーページの後の最初のページです。この後にGAMページ（第2ページ）が来て、 50 第1章 パフォーマンスのトラブルシューティング法 次にSGAMページ（第3ページ）が来ます。最初のPFSページに続いてサイズが約 8,000ページのPFSページがあります。最初のGAMページに続いて別のGAMページ 64,000エクステントがあり、最初のSGAMページに続いて別のSGAMページ64,000エ クステントがあります。 tempdb内のユーザーオブジェクトと内部オブジェクトの割り当て／割り当て解除 が異常に増える場合は次のシステムモニタカウンタを監視してください。 ◆ SQLServer:Access Methods: Worktables Created/Sec 1秒あたりに作成されるワークテーブルの数。ワークテーブルは一時オブジェクトで あり、クエリスプール、LOB変数、カーソルの結果を保存するために使われます。通 常、この数値は200未満ですが、もう一度、ベースラインと比べてください。 ◆ SQLServer:Access Methods: Workfiles Created/Sec 1秒あたりに作成されるワークファイルの数。ワークファイルはワークテーブルに似 ていますが、操作をハッシュすることによってのみ作成されます。ワークファイルは ハッシュ結合と集計されたハッシュの一時結果を保存するために使われます。 ◆ SQLServer:Access Methods: Worktables from Cache Ratio ワークテーブルの最初の2ページは割り当てされていないが、ワークテーブルキャッ シュから直ちに利用可能である場合に作成されたワークテーブルの割合。SQL Server 2000では、一時テーブルのキャッシュはありません。 ◆ SQLServer:General Statistics: Temp Tables Creation Rate 1秒あたりに作成される一時テーブルや変数の数。 ◆ SQLServer:General Statistics: Temp Tables for Destruction クリーンアップシステムスレッドによって破棄されるために待機している一時テー ブルや変数の数。 割り当て競合を識別するのと同じような方法でDDLボトルネックを検出できます。 次のDMVクエリによってDDLボトルネックを識別できます。このクエリはtempdb内 のラッチが掛かったページを取得するために多くのスレッドが待機中であるかどうか を調べます。このDMVは待機中の現在のワーカーを示すことに注意してください。 DDLボトルネックを識別するためにはこのDMVを何度もポーリングしてください。 SELECT session_id, wait_duration_ms, resource_description FROM sys.dm_os_waiting_tasks WHERE wait_type LIKE 'PAGE%LATCH_%' AND resource_description like '2:%' このクエリは割り当てボトルネックを検出するために使用したものと同じです。唯 1.2 トラブルシューティングの概要 51 一の違いは、ラッチ競合を起こしているページがシステムカタログに属するページで あるかどうかです。つまり、DDL競合があるということです。 ■ tempdbボトルネックのトラブルシューティングと分離 前出の節でI/Oボトルネックの状況下での推奨事項について説明しましたが、これ らはメモリ不足の検出と解放に関する推奨事項も含めてここでも同じように当てはま ります。しかし、特にtempdbの場合、どんなクエリ／バッチがtempdbの中で最も多 くI/Oを生成するかを識別し、次に、I/Oの生成を最小限に抑えるためにTransactSQLあるいはクエリプランのいずれかを変更できるかどうか確かめることが必要です。 次のDMVクエリを使用してtempdbの中で最も多く割り当てと割り当て解除を引き起 こしている現在実行中のクエリを識別できます。 SELECT TOP 10 t1.session_id, t1.request_id, t1.task_alloc, t1.task_dealloc, t2.plan_handle, (SELECT SUBSTRING (text, t2.statement_start_offset/2 + 1, (CASE WHEN statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(MAX),text)) * 2 ELSE statement_end_offset END - t2.statement_start_offset)/2) FROM sys.dm_exec_sql_text(sql_handle)) AS query_text FROM (SELECT session_id, request_id, SUM(internal_objects_alloc_page_count + user_objects_alloc_page_count) AS task_alloc, SUM(internal_objects_dealloc_page_count + user_objects_dealloc_page_count) AS task_dealloc FROM sys.dm_db_task_space_usage GROUP BY session_id, request_id) AS t1, sys.dm_exec_requests AS t2 WHERE t1.session_id = t2.session_id AND (t1.request_id = t2.request_id) AND t1.session_id > 50 ORDER BY t1.task_alloc DESC このクエリによってtempdbの中で最も多くページ割り当てを起こしている上位10 位のクエリがわかります。それぞれのクエリのクエリプランを分析し、Transact-SQL やクエリプランを変更できるか、あるいはtempdb内のI/Oを最小限に抑えるために欠 落インデックスを作成できるかどうかを調べてください。次の例は、前出のDMVク エリがtempdb内で最も多く割り当てと割り当て解除を引き起こしているクエリを識 別するためにどのように使用されているかを示しています。 CREATE TABLE t1 (c1 int PRIMARY KEY, c2 int, c3 char(8000)) GO 52 第1章 パフォーマンスのトラブルシューティング法 CREATE TABLE t2 (C4 int, c5 char(8000)) GO -- テーブルの1つ1つに多数の行をロードします。 DECLARE @i int SELECT @i = 0 WHILE (@i < 6000) BEGIN INSERT INTO t1 VALUES (@i, @i + 1000, 'hello') INSERT INTO t2 VALUES (@i,'there') SET @i = @i + 1 END -- 個別のセッション内でハッシュ結合によってクエリを実行します。 SELECT c1, c5 FROM t1 INNER HASH JOIN t2 ON t1.c1 = t2.c4 ORDER BY c2 次は上記クエリの出力です。tempdb内で最も多く割り当てと割り当て解除を引き 起こしているクエリを示しています。 Session_id request_id task_alloc ----------------------------54 0 6016 Task_dealloc plan_handle ----------------------672 0x06000A001DBE3835B841B303000000000000000000000000 Query_text ----------SELECT c1, c5 FROM t1 INNER HASH JOIN t2 ON t1.c1 = t2.c4 次のDMVクエリを使用してクエリの実行プランを調べることができ、tempdb内で 過度の割り当てと割り当て解除を引き起こしている原因を特定できます。 SELECT * FROM sys.dm_exec_query_plan(<plan-handle>) アプリケーションを分析し、tempdb内で起こっているI/Oが予想通りであることと SQL Serverがメモリ不足ではないことを決定したら、唯一の選択はI/Oシステムを 更新することです。 tempdb内のI/Oを減らすためにアプリケーションを変更する場合、割り当てオーバ ーヘッドを間接的に減らします。しかし、たいていの場合、アプリケーションを変更 するのはほとんどのユーザーにとって少なくとも当面利用できる選択ではありません。 前述のように、割り当て競合は複数のスレッドがGAM、SGAM、PFSページに同時 にアクセスすることによって発生します。この競合を最小限に抑えるには2つの方法 があります。 1.2 トラブルシューティングの概要 ● 1つ目は、SQL 53 Serverが使用するCPUのコア数と同じだけのデータファイルを 作成します。これらのファイルは必ずしも異なるディスクサブシステム上に置く 必要はありませんが、そうした場合、tempdb のI/O帯域幅も増やすことにな り、これによってI/Oボトルネックは最小限に抑えられます。どうしてCPUの 数と同じ数のファイルなのかと聞きたくなるでしょう。CPUの数はtempdb 内 のページにアクセスしたり割り当て／割り当て解除したりすることが可能な同 時スレッドを決定します。したがって、１つのスレッドで1つのファイルだけに アクセスしているときは、割り当て競合を最小限に抑えることができます。SQL Serverは比例割り当てアルゴリズムを実装することによってこれら複数ファイ ルを使用します。このアルゴリズムではファイル内の利用可能領域に基づいて 全ファイルにページが割り当てられます。このため、ファイルに領域があれば、 SQL Serverはこのファイルを使ってその割り当てにバイアスをかけます。こう なると割り当て競合が起こります。したがって、割り当てワークロードが均一 に分散されるようにすべてのファイルは同じサイズにすることをお勧めします。 おもしろいのは、tempdb 内の領域からの実行中に新しいファイルを追加する とどうなるかです。この場合、この新しいファイルは空なので、比例割り当て アルゴリズムはこのファイルを使用して多くの新しい割り当てを実行し、たと え新しいファイルを追加したところであっても、アプリケーションに割り当てボ トルネックが増えます。ちょっと直感では理解しにくいかもしれません。ワーク ロードに対して適切にtempdb のサイズを決めることによってI/Oボトルネック の増加を積極的に抑えることができます。tempdb のサイズを増やすのは普通 というより例外です。 ● トレースフラグ1118を使用して複合ページ割り当てを削除できます。このトレ ースフラグを有効にすると、SQL Serverは混合エクステント割り当てを停止 し、均一のエクステントだけを使います（すなわち、エクステント内のすべての ページが同じオブジェクトに属します） 。したがって、1ページしか必要としな い小さなテーブルの場合、SQL Serverは8ページを予約します。こうなると SGAMの競合はなくなりますが、GAMやPFSの競合はなくなりません。トレ ースフラグ1118を使う欠点は、それがSQL Serverインスタンス全体に有効で あり、ユーザーデータベースにも影響を与えることと、tempdb と他のデータ ベースのサイズ膨張を引き起こすことです。詳細については、http://support. microsoft.com/kb/328551を見てください。 アプリケーションに一時オブジェクトに対するDDL競合が発生している場合、 Transact-SQLステートメントとストアドプロシージャを分析して、これらのオブジ ェクトをどこで作成、削除しているかを確かめ、そのDDL操作を最小限に抑えること ができるかどうかを調べてください。たとえば、ループ内部に一時テーブルを作成し ている場合、それをループから移動できるかどうかを確かめます。SQL Server 2005は 一時オブジェクトを可能な限りキャッシュするので、一時オブジェクトの削除と作成 は高速です。SQL Serverは一時オブジェクトを削除するとき、オブジェクトのカタ 54 第1章 パフォーマンスのトラブルシューティング法 ログエントリを削除しないので、それを次回使用する場合は、一時オブジェクトを再 作成する必要はありません。SQL Serverは一時オブジェクトの1データページと1 IAMページをキャッシュし、残りのページを割り当て解除します。一時オブジェクト のサイズが8MBより大きい場合、割り当て解除はバックグラウンドで非同期的に実行 されます。 SQL Server 2005は、次の条件が満たされたときに、一時オブジェクトをキャッシ ュします。 ● 名前付き制約が作成されていない。 ● CREATE INDEXやCREATE STATISTICSステートメントのような、一時テ ーブルに影響を与えるDDL（Date Definition Language：データ定義言語） ステートメントが、テーブルが作成された後に実行されていない。 ● sp_executesql N'CREATE #t(a int)'のような動的SQLを使用して一時オブジ ェクトが作成されていない。 ● 一時オブジェクトが別のオブジェクト（ストアドプロシージャ、トリガ、ユーザ ー定義関数など）の内部で作成されている。あるいは、ユーザー定義のテーブ ル値関数の戻りテーブルである。 次のスクリプトを使用して、一時オブジェクトがストアドプロシージャの中でキャ ッシュされているかどうかを確認できます。このスクリプトは、特定のストアドプロ シージャを呼び出すときに作成された一時オブジェクトの数を示します。 DECLARE @table_counter_before_test bigint SELECT @table_counter_before_test = cntr_value FROM sys.dm_os_performance_counters WHERE counter_name = 'Temp Tables Creation Rate' DECLARE @i int SELECT @i = 0 WHILE (@i < 10) BEGIN -- <execute your stored procedure> SELECT @i = @i+1 END DECLARE @table_counter_after_test bigint; SELECT @table_counter_after_test = cntr_value FROM sys.dm_os_performance_counters WHERE counter_name = 'Temp Tables Creation Rate' PRINT 'Temp tables created during the test: ' + CONVERT(varchar(100), @table_counter_after_test - @table_counter_before_test) 次の例を考えてみてください。ストアドプロシージャtest_temptable_cachingが #<table>をその内部で作成しています。前出のスクリプトを使って、この一時テーブ 1.2 トラブルシューティングの概要 55 ルが複数の呼び出しでキャッシュされるかどうかをチェックするとよいでしょう。 CREATE PROCEDURE test_temptable_caching AS CREATE TABLE #t1 (c1 int, c2 int, c3 char(5000)) -- CREATE UNIQUE CLUSTERED INDEX ci_t1 ON #t1(c1); DECLARE @i int SELECT @i = 0 WHILE (@i < 10) BEGIN INSERT INTO #t1 VALUES (@i, @i + 1000, 'hello') SELECT @i = @i+1 END PRINT 'done with the stored proc' GO 次は出力です。 ストアド プロシージャの実行 ... ストアド プロシージャの実行 ストアド プロシージャの実行 Temp tables created during the test: 1 ストアドプロシージャの内部にインデックスnci_t1 on #t1を作成すると、出力は 一時オブジェクトが10回作成され削除された（すなわち、キャッシュされない）こと を示すことに注意してください。このストアドプロシージャが何回も実行されると、 tempdb内に大きなDDL負荷を発生する可能性があります。 ストアド プロシージャの実行 ... ストアド プロシージャの実行 ストアド プロシージャの実行 Temp tables created during the test: 10 一時テーブルにインデックスを実際に作成しなればならないとき、次のDDLを使用 し、キャッシャされた一時テーブルをなおも維持します。固有の制約がクラスタ化イ ンデックスを暗黙的に作成することに注意してください。 CREATE TABLE #t1 (c1 int UNIQUE, c2 int, c3 char(5000)) ■ tempdbの自動的および明示的な拡張 ワークロードの中のtempdbの最大サイズがどのくらいかを知り、そのサイズに tempdbを前もって割り当てることが必要です。こうすると前述のように2つの利点が 56 第1章 パフォーマンスのトラブルシューティング法 あります。第1に、ファイルの物理フラグメンテーションを最小限に抑えることができ ます。第2に、ユーザートランザクションはtempdbが拡張するのを待つ間も停止しま せん。例外状況を処理するには、自動拡張を有効にしたまま、tempdb用のディスク サブシステム上の領域を空にするとよいでしょう。Windows XPとWindows 2003で 実行されているSQL Server 2005での改良点の1つは、データベースの作成時や1つあ るいは複数のファイルの拡張時に、ファイルの内容消去をスキップできることです（瞬 間ファイル初期化と呼ばれます） 。瞬間ファイル初期化は、何も書かれていない領域に 書き込むのではなく、使用されたディスク領域を再要求します。ディスクコンテンツ はファイルに新しいデータが書き込まれるとき上書きされます。 ■■ ブロッキング SQL Serverは、ユーザーテーブル、システムテーブル、内部データ構造、バッフ ァ類、クエリプランなどの共有リソースにアクセスする数千人のユーザーによる同時 アクセスをサポートしています。データとその内部構造の整合性を保証するために、 SQL Serverは、論理ロック、スピンロック、ラッチなどのさまざまな同期メカニズ ムを展開してこれらのオブジェクトへのアクセスを制御します。アクセスが互換モー ドである場合、複数のユーザーが同時にオブジェクトにアクセスできます。たとえば、 複数ユーザーが同じ行を読み取りできますが、その行を変更できるのは一度に1人の ユーザーだけです。したがって、ブロッキングは通常のことであり、SQL Server内 で当然発生すると予想されます。注意すべきは、アプリケーションのパフォーマンス に影響を与える可能性のある過度のブロッキングです。過度のブロッキングの理由は いろいろあります。ワークロードに対してハードウェアシステムの構成が不適切であ ることや、アプリケーションの設計の不備や、最終的にはSQL Serverの設計の限界 があげられます。これらについて詳しく説明して、ブロッキングの問題をトラブルシ ューティングするためにはどんな行動を取るべきかについて理解していきましょう。 システム構成とはサーバーハードウェア（CPUとメモリ） 、ネットワーク、I/O装置 を指します。これらリソースのボトルネックはシステムのスループットに影響を与え ます。ユーザーが要求／バッチを提出すると、SQL Serverはワーカーを割り当て、 実行のためにそれをCPU上にスケジューリングします。クライアントからの処理要求 の数がそれを処理するCPUの容量をはるかに超えると、エンドユーザーは要求がブロ ックされていることや、実行が遅くなっていることを感じます。同様に、SQL Server に十分なメモリがないと、SQL Serverは後から必要になるページを破棄／フラッシ ュするためにバッファを圧迫することもあれば、コンパイル済みプランをメモリから 強制的に追い出してI/OサブシステムとCPUを圧迫することもあります。したがって、 完璧に設計されたアプリケーションであっても、ユーザーはハードウェア構成の不備 が原因するパフォーマンス低下にぶつかります。しかし、前述のように、ワークロー ドを満たすためのハードウェアの更新は有益ですが、解決策にはなりません。 一般的なレベルでは、データベースアプリケーションによって複数ユーザーがテー ブル内のデータへアクセスし、それを操作することが可能です。裏では、それぞれの 1.2 トラブルシューティングの概要 57 ユーザーインターラクションは、アプリケーションによって明示的にあるいはSQL Serverによって暗黙的に開始されたトランザクションのコンテキストに従って実行さ れます。トランザクションは4つのプロパティ（原始性、一貫性、分離性、持続性）を 持つと考えられます。これらのプロパティのいくつか（原始性と持続性）は厳格に実 行されますが、トランザクションの分離性のレベルに応じて他の2つには余裕がありま す。たとえば、トランザクションがREAD UNCOMMITTED分離レベル下で実行され るとすると、データ読み込みが一貫しているという保証も他の同時トランザクション から分離しているという保証もありません。アプリケーション設計の一部として、ど んなトランザクション一貫性がアプリケーションによって許可されているかを考慮し、 次にトランザクション分離レベルを適切に選択する必要があります。たとえば、アプ リケーションの一部として、販売データを使用してトレンド分析を行っている場合、 低いレベルの一貫性でこのトランザクションを実行してもかまいません。同様に、デ ータベース設計と、アプリケーションの要求に最適のサポートインデックスを考慮す る必要があります。これはアプリケーションを設計するための実に簡単で理にかなっ た指針に聞こえるかもしれませんが、実際のところアプリケーションは複雑なので、ア プリケーションが実稼働で展開されると、いろんな問題が出てくるでしょう。大事な のは、そのときの環境で何がブロッキングを起こしているのかを理解し、その次にブ ロッキングにぶつかったときにアプリケーションを改善するための糧とすることです。 ほとんどの顧客はあらかじめパッケージになったアプリケーションをI S V （Independent Software Vendor：独立系ソフトウェアベンダー）から購入する傾向 が増えています。これらのISVは初期には異なるデータベースプラットフォームに対 するアプリケーションを開発していましたが、後にSQL Serverに移行しました。た とえば、アプリケーションがOracleプラットフォーム用に開発され、現在SQL Server に 移 行 し た と し ま す 。 こ の よ う な 場 合 、 SQL Server内 の 既 定 の READ COMMITTED分離レベルはロックを使用して実行されてきたのですが、Oracleでは コミット済み読み取りの実行はロックを必要としないので、アプリケーションにはブ ロ ッ キ ン グ が 増 え る 可 能 性 が あ り ま す 。 SQL Server 2005は 、 READ COMMITTED分離レベルのロックに基づく実行と行のバージョン管理に基づく実行 の両方を提供することによってこの問題に対処しています。アプリケーションを変更 することはできないかもしれませんが、一部のトラブルシューティングのためにはこ の節で説明した情報を使うことができます。たとえば、欠落インデックスを追加した り、ロックエスカレーションを防いだりできます。 ■ ブロッキングの検出 ブロッキングというと、ほとんどのユーザーは共有のような論理ロックや、データ 行、ページ、テーブル上の排他ロックを考えます。論理ロックは最も数多く出現する ので、データベースアプリケーションのブロッキング問題をトラブルシューティング する際の一般的なブロッキング要素ですが、必ずしもそれがすべてではありません。前 述のように、SQL Serverは内部構造の整合性を保護するために多くの内部非同期メ カニズムを採用しています。この節では、ブロッキングとは論理ロックを含むあらゆ 58 第1章 パフォーマンスのトラブルシューティング法 る種類のブロッキングのことです。ワークロード内の一定の時間で、予想以上の多く のブロッキングがあるかどうか（ベースラインと比べて）を確認してください。SQL Server 2005からは、DMV sys.dm_os_wait_statsを使用してSQL Serverが開始して からスレッドやワーカーに発生した累積待機を確認できるようになっていますが、 DBCC SQLPERF（ [sys.dm_os_wait_stats], clear）を使用してそれらをリセットで きます。DMVは200種類以上の待機を追跡するので、これらの待機統計を調べること によって、アプリケーションで多くのブロッキングを起こしている待機の種類を特定 できます。これらの待機の種類には論理ロックの待機、I/Oの待機、メモリ許可の待 機などがあります。これらの待機統計は累積的なので、一定間隔でこのDMVをポー リングでき、ポーリング期間内の各待機ごとにミリ秒単位で待機の合計を特定できま す。この情報を使用して待機やブロッキングの原因を調べることができます。 次のDMVクエリはアプリケーションで発生する上位10位の待機を示します。 SELECT TOP 10 wait_type, waiting_tasks_count AS tasks, wait_time_ms, max_wait_time_ms AS max_wait, signal_wait_time_ms AS signal FROM sys.dm_os_wait_stats ORDER BY wait_time_ms DESC 次はこのDMVの出力です。 wait_type -------------------LAZYWRITER_SLEEP SQLTRACE_BUFFER_FLUS LCK_M_S IO_COMPLETION PAGEIOLATCH_SH ASYNC_NETWORK_IO SOS_SCHEDULER_YIELD BROKER_TASK_STOP SLEEP_TASK PAGEIOLATCH_EX tasks -----166202 41562 587 14462 3150 2525 87964 9 295304 1560 wait_time_ms -----------344796328 344794437 492205 68703 51281 34187 32687 20359 18937 16171 max_wait ---------55732593 55733359 149031 609 1328 2015 3734 10000 1015 515 signal -----21578 4546 93 265 359 1500 32687 0 10812 46 上記出力について簡単に説明します。 ● LCK_M_S：この待機が発生するのは、共有ロックを取得するためにタスクが 待機中のときです。この場合、タスクが共有ロックを得るために待った最大時 間は149,031ミリ秒です。もう1つ役に立つ情報は、合計587のタスクが共有ロッ クを得るために待機したことです。 ● LAZYWRITER_SLEEP：これはレイジーライタスレッドによる待機を表しま 1.2 トラブルシューティングの概要 59 す。このレイジーライタスレッドは定期的に実行され、ダーティページをディス クに書き込むことを思い出してください。したがって、レイジーライタによる待 機は通常のことで、問題ではありません。 ● PAGEIOLATCH_SH：この待機が発生するのは、I/O要求の中にあるバッフ ァに対するラッチ上でタスクが待機しているときです。ラッチ要求は共有モー ドです。この待機が長いのはディスクサブシステムに問題があることを示してい ます。 ● PAGEIOLATCH_EX：この待機が発生するのは、I/O要求の中にはないバッ ファに対するラッチ上でタスクが待機しているときです。ラッチ要求は排他モ ードです。 ● signal waitは、ワーカーがリソースへのアクセスを許可されていたときと、ワ ーカーがCPU上でスケジュールされたときとの間の時間です。signal wait (ms) が長いのはCPUの競合が高いことを表します。 他の種類の待機についてはBOLを見てください。アプリケーションに多くの待機が 発生していることや、ベースラインよりも多く待機が発生していることを確認したら、 次に取るステップは何が待機を引き起こしているかを特定することです。 システムモニタカウンタはブロッキングを検出するためのカウンタを豊富に提供し ています。主要なカウンタは次のとおりです。 ◆ SQLServer:Locks: Average Wait Time (ms) 待機を引き起こすそれぞれのロック要求の平均待機時間（ミリ秒）を表します。 ◆ SQLServer:Locks: Lock Requests/Sec ロックマネージャから要求された新しいロックとロック変換を表します。 ◆ SQLServer:Locks: Lock Wait Time (ms) 最後の1秒間にロックを待った合計待機時間（ミリ秒）を表します。 ◆ SQLServer:Locks: Lock Waits/Sec 他のユーザがロックを保持中で、ロックを許可される前に呼び出し元に待機するよ うに要求したロック要求の数を表します。 ◆ SQLServer:Locks: Number of Deadlocks/Sec デッドロックを引き起こしたロック要求の数を表します。 ◆ SQLServer:General Statistics: Processes Blocked 現在ブロックされているプロセスの数を表します。 ◆ SQLServer:Access Methods: Table Lock Escalations/Sec ロックがテーブルレベルのロック粒度へエスカレートされた回数を表します。 これまでの節では、CPU、I/O、メモリに関連する待機と、tempdbが原因で起こ 60 第1章 パフォーマンスのトラブルシューティング法 る待機のトラブルシューティングの方法を説明しました。次の節ではアプリケーショ ン内の論理ロックが引き起こす待機に焦点を当てます。 ■ ブロッキングのトラブルシューティングと分離 前述のように、ブロッキングは通常のことであり、どんな同時実行アプリケーショ ンでも発生すると考えられます。大きなブロッキングは長時間実行のトランザクショ ンやトランザクションが実行されている分離レベルの結果です。トランザクションが 行を変更すると、そのトランザクションの実行時間の間にその行の上に排他ロック （X ）が保持されます。同様に、トランザクションが高い分離レベル（つまり、 REPEATABLE READとSERIALIZABLE）で実行中の場合、共有ロックさえもト ランザクションが持続している間保持されます。ブロッキング問題をトラブルシュー ティングするための指針のいくつかをあげます。 ● トランザクションの実行時間を短縮し、より低い分離レベルでトランザクショ ンを実行してください。トランザクション内でのユーザーインターラクションを 避けてください。ユーザーインターラクションは際限のないほど長い時間を取る 可能性があります。こうなるとアプリケーション内の重要なリソースが停止し、 他のトランザクションが開始できなくなります。 ● トランザクションによってアクセスされる必要があるデータを最小化してくださ い。時として、役立つインデックスが欠落すると、実際には必要ではないデー タをトランザクションが読み込んでしまいます。 ● オブジェクト上でDML操作を実行するとき、同じ順序でオブジェクトにアクセ スするようにアプリケーションを設計してください。2つのストアドプロシージ ャがあるとしましょう。1つのストアドプロシージャがt1テーブルを更新し、次 に同じトランザクション内でt2テーブルを更新します。その一方で、別のスト アドプロシージャが最初にt2テーブルを更新し、次に別のトランザクションで t1テーブルを更新すると、デッドロックが発生する可能性があります。 ● 多数の行に対してDML操作を実行している場合、ロックエスカレーションを防 ぐにはその操作を小さなトランザクションに分けてください。 ブロッキングの問題をトラブルシューティングするには、どのトランザクションが ロックを保持しているのかと、どのトランザクションがブロックされているのかを知 る必要があります。次のDMVクエリを使用すると、許可されているすべてのロック や、現在実行中のトランザクションによって待たれたすべてのロックをいつでも見つ けることができます。このクエリはストアドプロシージャsp_lockによく似た出力を提 示します。 SELECT request_session_id AS spid, resource_type AS rt, 1.2 トラブルシューティングの概要 61 resource_database_id AS rdb, (CASE resource_type WHEN 'OBJECT' then object_name(resource_associated_entity_id) WHEN 'DATABASE' THEN ' ' ELSE (SELECT object_name(object_id) FROM sys.partitions WHERE hobt_id=resource_associated_entity_id) END) AS objname, resource_description AS rd, request_mode AS rm, request_status AS rs FROM sys.dm_tran_locks 次は、セッション56がセッション53によってロックされたことを示すサンプル出力 です。セッション53はRID 1:143:3に排他ロック（X）を保持しています。 spid ----56 53 56 53 53 56 53 53 53 56 rt --------DATABASE DATABASE PAGE PAGE PAGE OBJECT OBJEC KEY RID RI rdb -----9 9 9 9 9 9 9 9 9 9 objname ----------- t_lock t_lock t_lock t_lock t_lock t_lock t_lock t_lock rd -------- rm rs ------ ----S GRANT S GRANT 1:143 IS GRANT 1:143 IX GRANT 1:153 IX GRANT IS GRANT IX GRANT (a400c34cb X GRANT 1:143:3 X GRANT 1:143:3 S WAIT もっと詳しく知りたいときは、sys.dm_tran_locksDMVと他のDMVを結合する と、ブロックの持続時間や、ブロックされたトランザクションによって実行されてい るTransact-SQLステートメントなどの詳細情報を得ることができます。 SELECT t1.resource_type, 'database' = DB_NAME(resource_database_id), 'blk object' = t1.resource_associated_entity_id, t1.request_mode, t1.request_session_id, t2.blocking_session_id, t2.wait_duration_ms, (SELECT SUBSTRING(text, t3.statement_start_offset/2 + 1, (CASE WHEN t3.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max),text)) * 2 ELSE t3.statement_end_offset END - t3.statement_start_offset)/2) FROM sys.dm_exec_sql_text(sql_handle)) AS query_text, 62 第1章 パフォーマンスのトラブルシューティング法 t2.resource_description FROM sys.dm_tran_locks AS t1, sys.dm_os_waiting_tasks AS t2, sys.dm_exec_requests AS t3 WHERE t1.lock_owner_address = t2.resource_address AND t1.request_request_id = t3.request_id AND t2.session_id = t3.session_id 次は、１つのトランザクションがオブジェクト上に排他（X）ロックを保持し、別 のトランザクションがそれを読み込むために待機中であるときのこのクエリのサンプ ル出力です。出力は1行だけですが、見やすくするために行を分けて記述しました。 resource_type ------------RID request_session_id -----------------54 database --------foo blocking_session_id ------------------53 blk object request_mode ----------------- -------------72057594039631872 S wait_duration_ms --------------------7585953 query_text --------------------SELECT * FROM [t1] WHERE [c1]=@1 resource_description -------------------------ridlock fileid=1 pageid=167 dbid=7 id=lock36673c0 mode=X associatedObjectId=72057594039631872 ここでおわかりのように、ロックを要求しているセッションはt1テーブルでSELECT を実行し、行で共有ロック（S）を要求しました。別のトランザクションがその行で 排他ロック（X）を保持しているので、共有ロック（S）ロックは許可されず、要求元 セッションは待たなければなりません。これは典型的なwriter／readerブロッキング です。この例では、トランザクションが合計7,585,953ミリ秒のやや長い時間待機して いることにお気付きでしょう。これはブロッキングトランザクションが長時間実行さ れていることを示しているので、どうしてそうなるのかを調べてください。この問題 をトラブルシューティングする他の方法はREAD UNCOMMITTED分離レベルで SELECTを実行することです。あるいは、新しいSNAPSHOT ISOLATION（SI） レベルを使 うか、データベースレベルでREAD_COMMITTED_ SNAPSHOT （RCSI）を有効にするかのいずれかの方法でreader／writerブロッキングをトラブル シューティングできます。これは、コミット済み読み取り分離レベルの、行のバージ ョン管理に基づく非ブロッキングです。ここで注意してほしいのは、SIを使うとアプ リケーションの変更が必要になることです。その一方、RCSIはreaderがREAD UNCOMMITTED分離レベル下で実行されているときだけ有効です。これらの分離 レベルが役立つことに注意してください。ただし、インデックスが見つからないなど の他の理由があるかもしれません。このような場合、writerやreaderのトランザクシ 1.2 トラブルシューティングの概要 63 ョンは粗い（より大きな単位）粒度ロックを得ることになります。 一般的には、ブロッキングはほとんどの場合、少数のオブジェクトが関連していま す。sys.dm_db_index_operational_statsDMV関数は、インデックスにアクセスす るときに発生するブロッキングなどの総合的なインデックス使用統計値を提供します。 この関数を、データベースレベルで、個別のテーブルやインデックスのレベルで、パ ーティションレベルでさえも呼び出すことができます。ブロッキングという点では、こ の関数は以下に述べるようにテーブル単位、インデックス単位、パーティション単位 でロック統計値の詳細な試算結果を提供します。このDMVが提供する情報のいくつ かを次に挙げます。 ● このインデックスにアクセスしている間に発生した行とページのロック総数。こ の数値が高いときは、このインデックスが過度に使用されていて、ロック競合 が起こっている可能性があります。 ● 要求が行やページロックを取得するために待たなければならなかった回数。も う一度言いますが、この数字が高いと、このインデックスへのアクセスに実際 に競合があります。 ● SQL Serverがこのインデックスのロックをテーブルレベルにまでエスカレート した回数。 ● このインデックスのリーフノード上の挿入、削除、更新の数。 この情報はインスタンス開始のときからの累積的なものであり、インスタンスが再 開されるときには保持されず、これを明示的にリセットする方法はありません。DMV によって返されたデータは、ヒープやインデックスを表すメタデータキャッシュオブ ジェクトが利用できる場合にだけ存在します。それぞれの列の値は、ヒープやインデ ックスのメタデータがメタデータキャッシュの中に送られるときに0に設定され、統計 値はキャッシュオブジェクトがメタデータキャッシュから削除されるまで蓄積されま す。しかし、このテーブルを定期的にポーリングすることができ、さらにクエリを出 すことが可能なテーブルにこの情報を収集することができます。ここで気をつけなけ ればならないのは、どんな種類の操作がインデックスについて実行されているかとい うことです。インデックスが単一スキャンや範囲スキャンにほとんど使われないよう なら、このインデックスは役に立たないので削除する方がよいでしょう。同様に、1つ あるいはそれ以上の欠落インデックスがある場合、いくつかの他のスキャンパス（す なわち、インデックス）をオーバーロードできます。次のDMVクエリはemployeeと いうテーブル上のすべてのインデックスについての操作統計です。 SELECT index_id, range_scan_count, row_lock_count, page_lock_count FROM sys.dm_db_index_operational_stats(DB_ID('<db-name>'), OBJECT_ID('employee'), NULL, NULL) 64 第1章 パフォーマンスのトラブルシューティング法 次はこのクエリからの出力です。このクエリは非クラスタ化インデックス（index_id = 2）が今まで1回だけ使われたことを示します。このテーブルが全面的に読み取り専 用であるときにはこれは問題ではありませんが、そのテーブル上で膨大な数のINSERT、 DELETE、UPDATE操作を実行しているときには、このインデックスを削除する方 がよいでしょう。ここで注意してください。INSERTとDELETE操作を実行するに あたっては、テーブル上で定義されているすべてのインデックスから行を挿入あるい は削除してください。UPDATE操作の場合、1つまたはそれ以上のインデックスキー 列あるいは付加列が更新中であるときだけ、インデックスを変更してください、 index_id range_scan_count ---------- -------------------1 1500 2 1 row_lock_count page_lock_count ---------------- ---------------1025500 92780 100 20 欠落インデックスを検出するには、I/Oボトルネックの節を参照してください。 この他のブロッキング問題で特別な注意が必要なのはデッドロックとロックエスカ レーションです。 ■ デッドロック デッドロックが発生するのは、2つまたはそれ以上のトランザクションやタスクが完 了に必要なリソースを取得するためにそれぞれが待機中であるときです。SQL Server はさまざまな種類のリソースについてデッドロックを検出できます。たとえば、リソ ースは論理ロックであったり、メモリ許可であったり、ワークスレッドであったりし ます。しかし、通常はトランザクションT1がリソースにアクセスするためにロックを 要求すると、他のトランザクションT2が競合モードでそのリソースを既にロックして いるので、トランザクションT1はブロックされます。この場合、T1は進行する前に 解放されるためにロックを待ちます。通常の操作では、T2がロックを解放すると、次 にT2はT1によって取得されます。ここでは2つのブロッキングシナリオが考えられま す。1つは、T2がロックを解放しないとしたらどうでなるでしょう。この場合、ロッ ク中断が指定されなければ、T1はいつまでも待機することになります。これを解決す るには外部の介入が必要です。通常は最初のトランザクションを強制終了します。 もう1つは、T2が、競合モードにあるT1によって既にロックされている別のリソー ス上でロックを要求したらどうなるでしょう。T1はT2を待機し、今やT2がT1を待機 しているので、デッドロックが発生します。この場合、どれだけ待っても何の役にも 立ちません。SQL Serverはバックグラウンドタスクを通してリソース内のこのよう なサイクルを検出する論理を持っています。これはロックモニタと呼ばれ、定期的に サイクルをチェックします。その頻度は通常5秒に1回ですが、デッドロックがどのく らい検出されるかによって変わります。デッドロックが検出されると、SQL Server は対象としてプロセスの1つを選択することによってそのデッドロックを破棄します。 対象となるのは通常今までの最も低いリソースに対して使われたプロセスです。対象 1.2 トラブルシューティングの概要 65 セッションは1205エラーを受け取り、トランザクションは中止されます。SQL Server は、タスクがデッドロック対象として選択されるときに、デッドロック優先セッショ ンの設定によって、いくつかの制御を提供します。セッションが1205エラーを得ると き、デッドロックが発生した原因や、どのセッションやリソースがデッドロックサイ クルに入っているかについての情報は提供されません。この情報がないと、デッドロ ックの原因を理解することや、今後デッドロックを最小限に抑えることは困難です。 デッドロックについての詳細な診断情報を得るには、トレースフラグ1222（SQL Server 2005で登場）を使ってSQL Serverを実行することをお勧めします。これは、 同じ目的で使用できる関連トレースフラグ1204よりもずっと詳細な情報を提供しま す。 デッドロックを防ぎ、最小限に抑え、トラブルシューティングする詳細については 「第6章 同時実行の問題」で説明します。 ■ ロックエスカレーション アプリケーションが行とページ粒度で多くのロックを取得するとき、SQL Server はそのロックに必要なメモリを減らし、ロックを取得・解放するオーバーヘッドを最 小化するために、これらのロックをテーブルレベルへとエスカレートすることを決定 します。ロックエスカレーションが起こるには2つの条件があります。SQL Serverがロ ックエスカレーションをトリガするのは、ステートメント内のインデックスやヒープ 上のステートメントによって保持されるロックの数（取得された数とは異なる）がし きい値（現在は約5,000に設定）を超えるときです。これらのロックにはインテントロ ックも含まれます。もう1つ、SQL Serverがロックエスカレーションをトリガするのは、 lock構成オプションが0（既定値）に設定されているときに、ロックリソースが非AWE （32ビット）や通常（64ビット）の40％を超えてメモリを取り、そのメモリが有効に なったときです。この場合、ロックメモリは必要に応じて動的に割り当てられます。 このlockが構成されていると、ロックリソースが使用するメモリがロックの構成メモ リの40％を超えたとたん（すなわち、lock構成オプションが0ではない値のとき） 、ロ ックエスカレーションはトリガされます。 ロックエスカレーションがトリガされると、SQL Serverはロックをテーブルレベ ルへとエスカレートしようとしますが、この試みは競合ロックがあると失敗します。た とえば、共有（S）ロックがテーブルレベルへエスカレートされる必要があり、対象 テーブルの1つまたはそれ以上の行／ページ上に同時に排他（X）ロックがあると、ロ ックエスカレーションの試みは失敗します。しかし、SQL Serverは、ロック所有者 （トランザクション）によって取得された1,250の新しいロックのすべてに対して定期 的にロックのエスカレーションを試みます。ロックエスカレーションが成功すると、 SQL Serverは低い粒度のロックと関連ロックメモリをインデックスやヒープ上で解 放します。成功したロックエスカレーションは、競合ロックモードにあるトランザク ションがインデックスやヒープへ将来同時アクセスするときにブロッキングを引き起 こす可能性があります（ロックエスカレーション時には競合アクセスはないからです） 。 したがって、ロックエスカレーションはロックタスクの数を減らしますが、すべての 66 第1章 パフォーマンスのトラブルシューティング法 アプリケーションにとって必ずしもよい方法ではないでしょう。 次の2つのトレースフラグを使用するとロックエスカレーションを無効にできます。 ◆ トレースフラグ1211 このトレースフラグはステートメント単位で1つのインデックス／ヒープごとに現在 のしきい値（5,000）でロックエスカレーションを無効にします。このトレースフラグ が有効なとき、ロックはエスカレートされません。このフラグはSQL Serverに対し て、ロックマネージャによって取得されたメモリを、静的に割り当てられたロックメ モリの最大まで、あるいは動的に割り当てられたメモリの非AWE（32ビット）／通 常（64ビット）メモリの60％をまでを無視するように指示も出します。このとき、ロ ックメモリ不足エラーが発生します。こうなると、多数のロックを取得することによ って誤作動アプリケーションがSQL Serverメモリを使い切るような被害になる可能 性があります。最悪の場合、SQL Serverを停止させることもあれば、そのパフォー マンスを受け入れがたいレベルまで低下させます。このような理由で、このトレース フラグを使うときは十分に注意してください。 ◆ トレースフラグ1224 このトレースフラグはトレースフラグ1211に似ていますが、重要な違いが1つあり ます。このフラグは、静的に割り当てられたロックメモリの40％、あるいは非AWE （32ビット）／通常（64ビット）の動的に割り当てられたメモリ（40％）をロックマ ネージャが取得するときにロックエスカレーションを有効にします。さらに、他のコ ンポーネントがより多くのメモリを占めていることが原因でこのメモリが割り当てさ れることができないと、ロックエスカレーションは早期にトリガされます。ロックマ ネージャに割り当てられたメモリが、静的に割り当てられたメモリを超えるときや、動 的割り当てのための非AWE（32ビット）／通常（64ビット）メモリの60％を超える ときに、SQL Serverはメモリ不足エラーを発生します。 ロックエスカレーションを防ぎ、最小限に抑え、トラブルシューティングする詳細 については「第6章 同時実行の問題」で説明します。 1.3 ｜ まとめ 本章では、SQL Serverで実行されているデータベースアプリケーションでよく発 生するパフォーマンス問題をどのように診断しトラブルシューティングするかについ て一般的な戦略を説明しました。ワークロードのすべてが変化する可能性のあるアプ リケーションが数多くあり、SQL Serverで展開できるハードウェア構成が数多くあ ることを考えると、すべてのパフォーマンス問題を予測し、トラブルシューティング の指針となるものを確実に総合的なまとまりとして提供するのはほとんど不可能です。 これまで説明してきたことからシステムモニタカウンタやDMVについてよい着想を得 1.3 まとめ 67 て、開始していただければ幸いです。アプリケーションを使う経験が増えるとともに、 監視が必要な自分自身のデータを作ることができるでしょう。しかし、収集するのが どんなデータであっても、ワークロードのベースラインがどれくらいかを知ることが 重要です。ワークロードの代表的なベースラインがないと、パフォーマンス問題のト ラブルシューティングにあたりほとんど闇の中で走り出すようなものです。 DMVクエリの使用にあたっては、DMVの理解を深めるためにSQL Serverアーキ テクチャについての知識を増やしてしてください。その知識を武器にして、最も役に 立つ情報を提供するDMVクエリを作成できます。 本書が発行された後になってMicrosoftがリリースするツールを参照したいことも あるでしょうが、DMVをベースにする連携Webサイトでこれを閲覧できるので、よ く発生するパフォーマンス問題のトラブルシューティングに利用してください。この ようなツールの基本となる考えは、一定間隔で主要なDMVをポーリングし、データ ベースの中にそのデータを読み込むことです。そのデータはパフォーマンスウェアハ ウスとして使用できます。ここで、このデータを分析するためにSQL Serverの持つ 力を活用します。このツールは、SQL Serverのトップレベルビューから始まるレポ ートを使用して構築され、さらに特定の問題をクエリレベルにまで掘り下げる能力を 提供します。このツールの威力は、データの監視と分析の複雑さをユーザーから隠し、 ユーザーがパフォーマンスのトラブルシューティング問題に集中できるようにしてく れることです。