SQL Q&A: Rebuilding Indexes, Disk Queue Length, and More

[アーティクル]
08/25/2016

SQL に関する Q&Aインデックスの再構築、ディスクキューの長さなど

Nancy Michell

質問 - SQL Server のインデックスはどのようにして再構築されますか。DBCC DBREINDEX によってすべてのディスク領域が使用される理由と、そのコマンドが失敗したときにディスク領域が解放されない理由を教えてください。データベースのサイズは 90 GB で、最も大きなテーブルは 70 GB です。

ディスクの空き領域を 10 % 残して DBCC DBREINDEX を実行すると、すべての空き領域が使用され、コマンドが失敗します。最初のデータベースファイルのサイズは 90 GB ですが、DBCC コマンドが失敗するとそのサイズは 160 GB まで増加し、増加分の 70 GB は解放されません。そのため、領域を解放するために何度か手動でデータベースを圧縮する必要があります。

コマンドが成功した場合でも、データベースファイルのサイズは 160 GB になりますが、領域は自動圧縮によって数時間後に自動的に解放されます。使用される領域を減らすために、DBREINDEX の代わりにインデックスの最適化を実行することはできるでしょうか。クラスタ化インデックスは使用しておらず、単純復旧モードを使用しています。

回答 - 最も単純なレベルでは、インデックスの再構築は、インデックスのコピーを新しく構築して古いインデックスを削除することによって行われます。つまり実質的には、わずかな間、インデックスのコピーが 2 つ存在することになります。新しいインデックスの構築には、元のインデックスと同じサイズのデータベースファイルの領域が必要になる場合があります。また、再構築で並べ替え操作が必要な場合、並べ替えのためにさらにインデックスサイズの 20 % の領域が必要になります。

したがって、最悪の場合、インデックスの再構築には古いインデックスの 1.2 倍の空き領域が必要になります。データベースファイルに十分な空き領域がない場合、操作の進行に伴ってファイルを拡張する必要があります。自動拡張が無効になっていたり、ディスクボリュームに十分な空き領域がない場合、空き領域の不足によって再構築操作が失敗する場合があります。

操作が失敗したかどうかにかかわらず、再構築操作の完了後、データベースファイルに新しく割り当てられた領域は解放されません。その領域は、通常のデータベース操作に使用されることが想定されます。

(手動または自動で) 圧縮を実行すると、そのアルゴリズムのしくみが原因で、ほぼ確実にインデックスの断片化が発生します。詳細については、「SQL Server ストレージエンジン」を参照してください。特に自動圧縮は、データベースが通常の操作に空き領域を必要とする場合、パフォーマンスに悪影響を与える可能性があります。これは、自動拡張と自動圧縮の繰り返しにより、断片化が大量に発生し、パフォーマンスが大幅に低下する可能性があるためです。

DBCC INDEXDEFRAG (または SQL Server™ 2005 の ALTER INDEX REORGANIZE) を使用すると、追加のデータベースファイルの領域がほとんど使用されないというメリットがあります。ただし、インデックスの再構築よりも時間がかかり、より多くのトランザクションログが生成される場合があります。DBCC INDEXDEFRAG では、使用する復旧モードの種類にかかわらず、常に完全ログ記録が行われます。一方、単純復旧モードでインデックスを再構築した場合、一括ログ記録が行われます。それぞれの方法にはさまざまな長所と短所があります。詳細については「SQL Server 2000 インデックスの最適化に関するベストプラクティス」を参照してください。

断片化の解消方法を決定する前に、まず、断片化を解消するかどうかを決定します。インデックスが使用される操作の種類によっては断片化がパフォーマンスに影響を与えないことがあるため、その場合は断片化の解消によってリソースが浪費されます。詳細は、上記のホワイトペーパーに記載されています。

結論ですが、使用中の環境に最も適した断片化の解消方法を選択し、その断片化の解消がクエリのパフォーマンスの向上に役立つことを確認してください。

質問 - SQL Server 2005 の 2 つのインスタンス間でデータベースミラーリングを正しく構成しました。アプリケーションは、SQL Server ログインを使用して SQL Server に接続しています。また、このアプリケーションは ADO および SQL Native Client を使用して構築されています。接続文字列と接続設定には、適切なフェールオーバーパートナーなどの正しい情報が指定されています。また、プリンシパルサーバーに存在するものと同じログインをミラーサーバーに作成しました。データベース障害のテストを行うと、ミラーサーバーにプリンシパルロールが正しく割り当てられ、SQL Server インスタンスが正常に機能しているように見えます (Windows® ログインを使用してミラーサーバーに接続することもできます)。ところが、アプリケーションを再接続すると、接続に失敗し、次のエラーメッセージが表示されます。

Cannot open database "<db name>" requested by the login. The login failed.

ログインが新しいプリンシパルデータベース (ミラーデータベース) のユーザーに関連付けられていないようです。このデータベースのユーザーとログインの同期を取るために sp_change_users_login を実行すると、複数の不明なユーザーが解決されたことを示すメッセージが表示されます。その後、アプリケーションは新しいプリンシパルサーバーに正常に再接続できます。何回かフェールオーバーを試行しましたが、毎回同じ動作が確認されます。つまり、ログインとユーザーの関連付けが行われません。

この問題が発生しないように、ミラーリングの設定を構成する方法はありますか。

回答 - はい、あります。この問題は、各サーバーの SQL Server ログインの名前が同じでも、セキュリティ識別子 (SID) が一致していないことが原因で発生します。Windows ドメインユーザーログインや Windows ドメイングループログインの場合、この問題は発生しません。これらのログインの SID は、ユーザーまたはグループが属するドメインの SID を基に作成されるため、ユーザーまたはグループがどの SQL Server に追加されるかに関係なく、同じユーザーまたはグループの SID は一致します。

sp_change_users_login による同期操作を不要にするには、プリンシパルサーバーのログインと同じ名前および同じ SID を持つ SQL Server ログインをミラーサーバーに作成する必要があります。これを行うには、次に示すように、ミラーサーバーにログインを作成するときに、CREATE LOGIN ステートメントで SID を指定します。

CREATE LOGIN <loginname> WITH PASSWORD = <password>, 
SID = <sid for 
same login on principal server>,...

各ログインの SID は、プリンシパルサーバーの sys.sql_logins カタログビューに照会することによって取得できます。図 1 は、特定のサーバーに SQL Server ログインまたは Windows ログインを実際に作成するための CREATE LOGIN ステートメントを生成するクエリの例です。

select 'create login [' + p.name + '] ' + 
case when p.type in('U','G') then 'from windows ' else '' end + 
'with ' +
case when p.type = 'S' then 'password = ' + master.sys.fn_varbintohexstr(l.password_hash) + 
' hashed, ' + 'sid = ' + master.sys.fn_varbintohexstr(l.sid) + ', check_expiration = ' +
case when l.is_policy_checked > 0 then 'ON, ' else 'OFF, ' end + 'check_policy = ' + 
case when l.is_expiration_checked > 0 then 'ON, ' else 'OFF, ' end +
case when l.credential_id > 0 then 'credential = ' + c.name + ', ' else '' end 
else '' end +
'default_database = ' + p.default_database_name +
case when len(p.default_language_name) > 0 then ', default_language = ' + p.default_language_name else '' end
from sys.server_principals p
left join sys.sql_logins l
on p.principal_id = l.principal_id
left join sys.credentials c
on l.credential_id = c.credential_id
where p.type in('S','U','G')
and p.name <> 'sa'

質問 - ディスクキューの長さの平均はどれぐらいにすればよいでしょうか。たとえば、RAID 01 で構成されたストレージエリアネットワーク (SAN) に 20 個の物理スピンドルが含まれている場合、ディスクキューの長さの平均はどのようにして計算すればよいでしょうか。Avg. Disk Queue Length の 20 分の 1 ですか、それとも Avg. Disk Queue Length の 2 分の 1 ですか。

回答 - まず、SAN 環境のディスクキューの長さの平均を把握することに時間を浪費する前に、ディスク待ち時間を確認しましょう。ただしそれも、何を検討しようとしているかによって異なります。理由はすぐにわかります。

(パフォーマンスモニタの) このカウンタの説明は、"サンプリング間隔中に選択したディスクのキューに入った読み取りおよび書き込み要求の数の平均値です" となっています。このカウンタは物理ディスクカウンタでも論理ディスクカウンタでもありますが、カウンタの値は、基になるストレージがどのようにしてオペレーティングシステムに提示されるかによって異なります。

では、今回の状況について考えてみましょう。RAID 01 構成に 20 個のスピンドルがあるということですが、この構成ではストライプ化が行われた後ミラー化が行われます (01 または 10 の解釈によっては、ミラー化が行われた後ストライプ化が行われます)。このストレージアレイで重要なのは、ストライプセットに 10 個のスピンドルが含まれるということです。

ただし、これだけでは、ストライプのサイズ、書き込みのサイズ、発行する I/O の種類 (読み取り、書き込み、順次、またはランダム) などの基本的な情報がわかりません。

ここではこれらの情報を無視することにし、Avg. Disk Queue Length が 10 だとすると、OS はディスクアレイに対する 10 個の I/O をキューに入れたことになります。理論的には、ストライプ内のミラー化された 10 個のセットにそれぞれ 1 個ずつ I/O が行われるか、10 個すべての I/O が 1 個のディスクに対して行われるかのいずれかです。どちらになるかを知る方法はありません。

ここで、不足している情報が必要になります。それではストライプのサイズが 64 KB、書き込みサイズが 8 KB で、一連の順次書き込みを実行するとしましょう。これは、SQL Server のストレージ操作では一般的なシナリオです。この場合最も考えられるのは、8 個の I/O までが 1 番目のディスクに対して行われ、次の 2 個の I/O が 2 番目のディスクに対して行われる可能性です。そのため、このシナリオで各ディスクのディスクキューの長さを考えると、アレイ内の 1 番目のディスクが 8、2 番目のディスクが 2、残りの 8 個のディスクが 0 になります。

ではシナリオを変更し、理論的なストライプのサイズが 8 KB、書き込みブロックのサイズが 64 KB、ディスクキューの長さが 10 とします。この場合、64 KB のブロックは 8 個のディスクに分散されるため、1 個の I/O は 8 個のディスクに書き込まれます。つまり、キューに入った 10 個の I/O は、アレイの 10 個のディスク全体に合計 80 回のディスク書き込みとして行われます。アレイのディスクごとのディスクキューの長さを計算する場合、それぞれ 8 となります。

現実的に考えて、別のレベルの不確定要素をシナリオに追加しましょう。ほとんどの場合、SAN ストレージは、サーバーの 1 つ以上の HBA、HBA を SAN に接続するための多数のファイバ、SAN のフロントエンドの多数のポート、およびサーバーと SAN との間のファブリックのファイバスイッチを使用してサーバーに接続されます。また、SAN 内のバスの内部アーキテクチャと、ディスクが SAN 前部のポートに接続される方法もかかわってきます。

I/O がキューに入れられたことがパフォーマンスモニタで報告されるということは、長い待ち時間が発生しているか、OS がディスクキューの長さを測定してから I/O がディスクに書き込まれるまでに、I/O がキューに入れられている可能性があります。ディスクキューの長さの平均ではなく、待ち時間を確認してそのカウンタを基に判断を行う必要があるのはこのためです。

質問 - トランザクションレプリケーションを使用しており、サブスクライバでテーブル内の多数の行が手動で変更されたため、パブリッシャがサブスクライバに存在しない行を更新しようとするとエラーが発生します。

レプリケーションで、スナップショット全体を再適用するのではなく、パブリッシャからこのテーブルの再初期化のみを実行する方法はありますか。tablediff ユーティリティについて調べたところ、目的の操作に適しているようですが、このユーティリティとレプリケーションはどのような関係がありますか。

たとえば tablediff は、パブリッシャのテーブルのスナップショットを、それと同じ時点のサブスクリプションのテーブルと比較しますか。tablediff ユーティリティを使用するときは、データの一貫性を保つためにレプリケーションを停止する必要がありますか。また、他に何か気を付けることはありますか。

回答 - まず、tablediff は、パブリッシュまたはサブスクライブするテーブルのスナップショットを使用しません。この特定のシナリオに関しては、考えられるシナリオが 2 つあります。

1 つは、レプリケーションを一時的に停止して tablediff ユーティリティを実行する方法です。ユーザーがデータを変更する可能性がある場合は、-sourcelocked パラメータおよび -destinationlocked パラメータを使用できます。これらのパラメータによって、ユーティリティの実行中、両方のテーブルに排他ロックがかけられます。この方法を使用できない場合は、レプリケーションを実行したまま -rc パラメータおよび -ri パラメータを使用します。この方法では、tablediff を一度実行し、エラーが検出された場合、再度 tablediff を実行して、レプリケーションの反映が遅れたことが原因で発生したエラーを解決します。ただし、レプリケーションの遅延時間によっては、サブスクライバで変更されたすべての行が必ずしも検出されない場合があることに注意してください。

**次の Microsoft IT 技術支援スタッフに心より感謝します。**Sunil Agarwal、Chad Boyd、David Browne、Gilles Comeau、Emmanuel Dreux、Amanda Foote、Matt Hollingsworth、Paul Mestemaker、Uttam Parui、Paul Randal、Dennis Tighe、Steven Wort。

Figure 1 CREATE LOGIN ステートメントの生成

その他のリソース