SQL に関する Q&A: インデックス カードを操作する
SQL Server のインデックスは問題になりがちですが、インデックスに注意して問題が発生しているかどうか警戒すれば、管理しやすくなります。
Paul S. Randal
成り行き上の DBA
Q. 私は社内の "非公式 DBA" として働いています。弊社では SQL Server の使用頻度が上がっており、社内の SQL Server のインスタンス数が急増しているので、専門家の DBA の支援を受ける必要を感じています。このような DBA に作業を任せるつもりですが、私はインスタンスの実行速度が低下している原因を特定できる必要があります。どこから着手すればいいか、全般的なアドバイスをいただけませんか。
A. これは良い質問ですね。パフォーマンスがあまり高くない SQL Server のインスタンスがあって、原因を特定するにはどこから着手すれば良いかわからないと、フラストレーションが溜まることもあります。パフォーマンスの低下にはさまざまな原因があるので、いつも私は SQL Server 自体に確認するのがもっとも簡単な解決策だと感じています。
SQL Server では、入出力統計および待機の統計という 2 つの情報が追跡されています。これらの情報から問題の原因を特定する有用な手掛かりを得られます。
現在、ほとんどの SQL Server は、I/O の制約を受けています (つまり、データの読み書きに関する処理によってパフォーマンスが制限されています)。パフォーマンス低下の原因としては、I/O サブシステムの処理速度が遅い、SAN をサーバーに接続するネットワークが遅い、サーバーのメモリが不足しているために SQL Server でページをメモリの内外でスワップする必要がある、インデックス計画に問題があることなどが挙げられます。
sys.dm_io_virtual_file_stats という動的管理ビュー (DMV) を使用すると、データやログ ファイルへのすべての I/O に関する I/O ボリューム、ストール、および遅延について SQL Server で認識している情報を確認できます。I/O サブシステム自体がホット スポットでないことが判明する場合もありますが、その場合でも I/O が問題になることはあります。I/O サブシステムで I/O 負荷が適切に処理されていなかった可能性があるためです。
ここで役に立つのが、SQL Server で追跡しているもう 1 つの情報である待機の統計です。SQL Server では、使用可能なリソースを実行スレッドで待機する必要が発生したすべての日時と、スレッドの待機時間を記録しています。また、リソースの空きを通知されてから CPU で実行できるようになるまでスレッドで待機する必要があった時間もわかります。このようなデータをまとめると、SQL Server で待機が発生する原因となっている可能性が高い領域を簡単に特定できます。この情報は、問題の原因特定に着手するためのヒントとして役立ちます。
ここでは、この手法の概要を説明したに過ぎません。使用できるスクリプトなど、詳しい説明については、私のブログ記事「Wait statistics, or please tell me where it hurts (待機の統計: どこが悪いか教えてください、英語)」を参照してください。この記事では、1,800 個を超える SQL Server インスタンスを保持しているユーザーを対象とした読者アンケートの結果を紹介し、多く発生した待機の種類について説明しています。ご質問への回答としては、SQL Server の周辺調査に時間を無駄に費やすのではなく、SQL Server で問題がどのように認識されているのかを直接確認することをお勧めします。
不足しているインデックスの分析
Q. 不足しているインデックスを確認できる DMV を発見しました。この一連の DMV によると、1 つの SQL Server インスタンスで数百ものインデックスが不足していることが判明しました。不足しているインデックスをすべて作成する必要がありますか。また、インデックスを作成した場合に、問題が発生することはありますか。
A. すべてのインデックスをただちに作成するのではなく、まずインデックスを分析してください。SQL Server 2005 以降のクエリ プロセッサでは、クエリ (バッチ、ストアド プロシージャを含む) のプランにインデックスが役立つ条件を特定できます。この分析はクエリ プランのコンパイル中に実行されます。
クエリ プロセッサでは、インデックスの不足が検出されると、その情報が通知されます。また、不足している各インデックスが使用された回数や、クエリ プランのコンパイル時にそのインデックスが存在していた場合に達成されたクエリ プランの向上予測も記録されます。
このすべての情報には、3 つの DMV (sys.dm_db_missing_index_groups、sys.dm_db_missing_index_group_stats、および sys.dm_db_missing_index_details) を使用してアクセスできます。また、インデックスが不足しているテーブルの列を示す DMV (sys.dm_db_missing_index_columns) もあります。最初の 3 つの DMV は、4 つ目の DMV より一般的です。データを取得する最も簡単な方法は、DMV をまとめてクエリすることです。Bart Duncan が「Are you using SQL’s Missing Index DMVs? (SQL Server で不足しているインデックスを確認できる DMV を使用していますか、英語)」で公開し、広く使用されているスクリプトも役立ちます。
DMV の情報は重要ですが、完全に信頼することはできません。まず、不足しているインデックスを確認できる DMV には潜在的な問題があります。このため、実際に存在するインデックスが不足していると示されることがあります。このバグは SQL Server の次のバージョンで修正される予定です。詳細については、私のブログ記事「Missing index DMVs bug that could cost your sanity (不足しているインデックスを確認できる DMV に関する信じられないようなバグ、英語)」を参照してください。
不足しているインデックスを特定するクエリ プロセッサのメカニズムでは、コンパイル中のクエリでインデックスが有効かどうかしか考慮されません。そのインデックスを管理する必要がある、挿入操作、更新操作、および削除操作のパフォーマンスへの影響は考慮されません。テーブルで行われる変更が読み取りより大幅に多い場合、パフォーマンスに大きな影響が及ぶことがあります。また、作成するインデックスのサイズも考慮されません。これは DBA だけが判断できるトレードオフです。
特定されるのは、コンパイル中のクエリ プランで確実に役立つインデックスだけです。たとえば、30 個の列と 1 つのクラスター化インデックスを含むテーブルがあり、クエリで、このテーブルの 25 列を要求するとします。不足しているインデックスを特定するメカニズムでは、非クラスター化インデックスを 1 つ作成して 25 列のクエリに対応することが推奨されます。ほとんどの場合、このような対応をしても意味がありません。
このような場合には、Duncan のスクリプトを使用して、不足しているインデックスを集計した出力を確認します。上位 10 ～ 20 個のインデックスを確認し、分析して、本当にインデックスを作成する価値があるかどうか判断します。多くの場合、作成する価値のないインデックスがあるので、この分析は必ず役立ちます。
仲良くできないものでしょうか
Q. 私は社内の DBA チームの一員として、さまざまなアプリケーション開発チームとやり取りしています。各チームは、いつも互いに敵意を持っています。これは作業環境に悪影響を与えています。チーム間の関係を円滑にする良い方法があれば、教えてください。
A. これは、敵意、不信感、および恨みによって作業環境の快適さが失われる、よくある問題です。どの感情も、生産性の向上にはつながらず、企業に悪影響が及びます。さいわい、解決策はあります。ただし、「言うは易し、行うは難し」という解決策です。
- お互いから学ぶ必要があります。各チームは、他のチームの行動理由や他のチームが考えている責任の範囲について、理解する必要があります。各チームが他のチームの作業として考えている内容を知ると、驚かれるかもしれません。
- 各チームは、他のチームが苦労している問題について理解する必要があります。個人を特定しなくても、匿名で問題を明らかにできます。
- 各チームは、他のチームの作業がそのチームに及ぼす影響について他のチームに知らせる必要があります。たとえば、開発チームが、コードを記述して少量のデータでテストしただけで運用環境にコードを移行すると、大きな問題が発生します。開発チームが、コードのトラブルシューティングを行って修正するのは DBA チームの役目だと考えている場合、この手順には明らかに問題があります。
問題の認識と理解が、双方が歩み寄って作業環境の平和と生産性を取り戻す解決策を探るための唯一の方法です。
厄介なインデックス作成
Q. 私は SharePoint の管理者で、SQL Server についても詳しい知識があります。私が管理する SharePoint データベースをホストしている SQL Server 2008 では、大量のインデックスの断片化が発生しています。そのために、SharePoint のパフォーマンスが低下しています。自分でインデックスを変更できないことはわかっていますが、インデックスを絶えず再構築する以外に、解決策はありませんか。
A. インデックスを絶えず再構築すると、SQL Server の I/O リソース、CPU リソース、トランザクション ログの生成などに大きな負荷がかかり、場合によっては他の処理がブロックされることがあります。sys.dm_db_index_physical_stats という DMV を実行して断片化したインデックスを特定する処理でも、大量のリソースが消費されます。
SharePoint データベース スキーマでは GUID 形式のクラスター化インデックス キーを使用しているので、SharePoint 環境では多くのインデックスが断片化します。この問題の詳細については、私の妻 Kimberly がブログ記事「GUIDs as PRIMARY KEYs and/or the clustering key (主キーやクラスター化キーとしての GUID、英語)」で説明しています。
インデックスにランダムなキーが存在すると、インデックスの挿入がランダムに行われ、最終的にはページ分割と呼ばれる処理が発生します。ページ分割は断片化の原因になる負荷の高い処理です (詳細については、私のブログ記事「How expensive are page splits in terms of transaction log? (トランザクション ログに関するページ分割の負荷について、英語)」を参照してください。ページ分割は、ページの空き領域がまったくないときに、そのページで領域が必要になった場合に発生します (たとえば、そのページに格納する必要があるランダムなキー値が含まれるインデックスで挿入が発生した場合にページ分割が発生します)。新しいページが割り当てられ、いっぱいになったページのレコードの約半分が新しいページに移動されて、新しい領域が確保されます。これがページ分割の基本的なしくみです。
サポート契約に違反するので、SharePoint データベースではインデックスを変更できませんが、データベースの FILL FACTOR の既定値は変更できます。インデックスの作成時や再構築時には、ランダムに挿入できるように、インデックス ページに一定量の空き領域を残すよう SQL Server に指示できます。このようにすると、負荷の高いページ分割を行わなくてもインデックス ページに新しいレコード用の領域が存在する可能性が高まります。FILL FACTOR の値を 80 に設定すると、インデックスの再構築時にページの 80% までが使用され、20% の空き領域が確保されます。
ここで、最適な FILL FACTOR の値はいくつかという疑問が生じます。残念ながら、確実な答えはありません。データが変更されずオンライン トランザクション処理 (OLTP) の挿入が発生しないデータ ウェアハウスの場合、通常、最適な FILL FACTOR の値は、SQL Server の既定値である 100 (空き領域なし) です。
OLTP 環境の場合、断片化の速度と断片化解消のためにインデックスを再構築する頻度によって、最適な値は異なります。最初は 70 (30% の空き領域) に設定して断片化の状況を監視し、値を増減したりインデックス メンテナンスの頻度を変更したりする必要があるかどうか判断することをお勧めします。
.jpg)
Paul S. Randal は SQLskills.com の代表取締役であり、Microsoft Regional Director でもあり、SQL Server MVP でもあります。1999 年から 2007 年までは、マイクロソフトの SQL Server ストレージ エンジン チームに所属していました。また、SQL Server 2005 では DBCC CHECKDB/repair コードを記述し、SQL Server 2008 の開発時にはコア ストレージ エンジンを担当していました。Paul は障害回復、高可用性、およびデータベース メンテナンスの専門家であり、世界中のカンファレンスで定期的に講演を行っています。彼のブログは、SQLskills.com/blogs/paul (英語) で公開しており、Twitter は twitter.com/PaulRandal (英語) でフォローできます。