SQL に関する Q&ACPU ボトルネック、データベースの復元と移動など

Nancy Michell

質問 - 変更は一切加えていないのですが、SQL Server™ の CPU 使用率が突然急激に上昇し始めました。新しいユーザーを追加したり、ハードウェアが故障したり、新しいテーブルを作成したりしたわけではありません。一体何が起きているのでしょうか。

ヒント : インデックス作成中のアクセス

大規模なテーブルでは、インデックスの作成に非常に時間がかかる場合があります。そのようなテーブルのインデックスを作成しているときに、そのテーブルのデータを利用できるようにすることが必要な場合があります。どのようにすれば、この両方の目的を達成できるでしょうか。

SQL Server では、クラスタ化インデックスの作成、削除、または再構築を行うときに、必ず、テーブルにスキーマ修正 (SCH-M) ロックがかけられます。このロックにより、インデックスの操作中はどのユーザーも、基になるデータにアクセスすることができなくなります。これはテーブルにインデックスを作成しているときに発生する問題です。クラスタ化インデックスとは異なり、列の非クラスタ化インデックスの作成時には、テーブルには共有 (S) ロックがかけられます。このロックでは基になるテーブルのデータを更新することはできませんが、SELECT ステートメントを実行することはできるので、データを読み取ることができます。

クラスタ化インデックスの作成中にテーブルに対して読み取り操作を実行できるようにする必要がある場合、オンライン操作としてテーブルのインデックスを作成する方法があります。このコマンドは次のとおりです。

CREATE UNIQUE CLUSTERED INDEX CLUST_IDX_SQLTIPS 
ON SQLTips (tip) with (ONLINE=ON) Go;

テーブルに対してオンライン インデックス操作を行う場合も、クラスタ化インデックスの場合には SCH-M ロックが、非クラスタ化インデックスの場合には S ロックが基になるテーブルにかけられますが、その期間は非常に短く、インデックス操作の開始フェーズと終了フェーズのみです。したがって、オンライン インデックス操作を使用すると、インデックスの作成処理中も基になるテーブルにアクセスしてクエリや更新を行えるようになります。ただし、インデックスの作成などの操作をオンラインで行えるのは、SQL Server 2005 Enterprise Edition のみであることに注意してください。

回答 - 変更を行ったわけでもなくサーバーの負荷が増えたわけでもないのに突然前触れもなく CPU ボトルネックが発生する場合には、サーバーの中核になんらかの原因がある可能性があります。一般的な原因としては、最適ではないクエリ プラン、SQL Server の構成が不十分であること、アプリケーションやデータベースのデザインが不適切であること、ハードウェア リソースの不足などが考えられます。

このような場合には、まず、サーバーが CPU を占有しているかどうかを確認し、占有している場合は、ローカルの SQL Server システムで CPU を最も多く使用しているステートメントを特定する必要があります。パフォーマンス モニタの PROCESSOR:% PROCESSOR TIME カウンタを確認すると、サーバーが CPU を占有しているかどうかを判断できます。1 つの CPU の時間あたりの値が 75％ 以上の場合は、CPU ボトルネックが発生しています。

また、SYS.DM_OS_SCHEDULERS と呼ばれるシステム動的管理ビュー (DMV) をクエリして SQL Server スケジューラを監視し、実行可能なタスクの値を確認する必要もあります。ゼロ以外の値は、そのタスクが、割り当てられているタイム スライスを実行するまで待機しなければならないことを意味します。また、このカウンタの値が大きい場合も CPU ボトルネックの兆候になります。

次のクエリを使用してすべてのスケジューラを一覧表示し、実行可能なタスクの数を確認することができます。

SELECT
 Scheduler_ID,
 Current_Tasks_Count,
 Runnable_Tasks_Count
FROM
 SYS.DM_OS_SCHEDULERS
WHERE
 Scheduler_ID < 255

CPU 使用量の多い SQL ステートメントの上位 50 件を取得するには、図 1 のクエリを使用します。

Figure 1 CPU の使用量が多い上位 50 件のステートメント

SELECT TOP 50 (a.total_worker_time/a.execution_count) AS [Avg_CPU_Time],
 Convert(Varchar,Last_Execution_Time) AS 'Last_execution_Time',
 Total_Physical_Reads,
 SUBSTRING(b.text,a.statement_start_offset/2,
 (case when a.statement_end_offset = -1 then len(convert(nvarchar(max), b.text)) * 2
 else
 a.statement_end_offset end - a.statement_start_offset)/2) AS Query_Text,
 dbname=Upper(db_name(b.dbid)),
 b.objectid AS 'Object_ID'
 FROM sys.dm_exec_query_stats a
 CROSS APPLY
 sys.dm_exec_sql_text(a.sql_handle) AS b
 ORDER BY
 [Avg_CPU_Time] DESC

質問 - SQL Server 2005 データベースをテープから復旧する必要がありました。データベースの復元後、ユーザーがデータベースに対して持っていたほとんどの権限が失われました。データベースの復元中に、私が行った間違った操作は何だったのでしょうか。データに問題はありませんでしたが、権限の方は散々です。

回答 - おそらく master データベースを同じ場所に復元していなかったのでしょう。その場合、ユーザー データベースのログイン ID と現在の master データベースのログイン ID は一致しません。ログイン ID を同期する必要があります。「データベースの移動に関する詳細情報」に、データベースの復元時または移動時に発生した問題の原因を特定するための適切なリソースを紹介します。

質問 - SQL Server 2005 SP1 の OpenXML を使用して XML データをリレーショナル テーブルに細分化するストアド プロシージャを使用したアプリケーションを開発しました。XML ドキュメントのサイズは最大 5 KB (平均 2.5 KB) です。ストアド プロシージャは、最大 50 回まで並列処理で呼び出されます。

現在、深刻なロックの競合問題が発生しています。私は OpenXML に原因があると考えていますが、どう思われますか。

回答 - OpenXML は、単一のスレッドでデータの細分化や変換を行う場合には nodes メソッドよりも高速ですが、特に並列処理の場合は nodes メソッドによる処理の方が拡張性があります。ただし、それでも OpenXML を使用する場合は、以下のガイドラインに従って OpenXML 全体のパフォーマンスを上げる必要があります。

開発したアプリケーションについては、既に認識されているようですが、同じ行パターンについて OpenXML を 5 回呼び出すのではなく、同じ行パターンを持つすべてのデータを一時的なテーブルに抽出し、そのテーブルを使用して SELECT 文を実行します。また、sp_xml_removedocument を使用してできるだけ早い段階でメモリを解放します。可能であれば、* や // などのワイルドカードを使用しないようにしてください。パスを明示的に指定すると、クエリのパフォーマンスが向上します。

質問 - サーバーで実行している DBCC SHRINKFILE が非常に低速です。マルチプロセッサ コンピュータで実行すればパフォーマンスは向上しますか。この状況を改善するために何ができるでしょうか。

回答 - DBCC SHRINKFILE は、シングル スレッドの操作です。つまり、複数の CPU を活用することはできません。DBCC SHRINKFILE では、ページがファイルの末尾から先頭に 1 ページずつ移動されます。そして、圧縮と呼ばれることの多いこの操作では、最適化は実行されません。実際のところ、多くの場合、圧縮操作によって論理的な断片化は増加します。

圧縮操作のパフォーマンスを向上する方法はいくつかありますが、その １ つにクラスタ化インデックスでページを移動する方法があります。ヒープを使用しており、そのヒープに多数の非クラスタ化インデックスが作成されている場合、処理速度は、クラスタ化インデックスが作成されている場合に比べて著しく遅くなります。

また、ラージ オブジェクト (LOB) データのページの移動も、LOB データのルートを見つけるために行データを読み取る必要があるので、処理速度は遅くなります。

インデックスとテーブルのほとんどの内容がファイルの末尾に格納されている場合、インデックスを再構築して、その内容をファイルの先頭に移動できます。インデックスの再構築では、複数の CPU が活用され、bulk_logged モードで使用するログの領域を削減することができます。その後、圧縮を実行すると、実行速度が速くなります。

圧縮操作の詳細については、まず、blogs.msdn.com/sqlserverstorageengine/archive/2007/03/29/how-does-your-schema-affect-your-shrink-run-time.aspx と blogs.msdn.com/sql-serverstorageengine/archive/2007/04/15/how-to-avoid-using-shrink-in-sql-server-2005.aspx の記事を参照してください。

データベースの移動に関する詳細情報

• SQL Server を実行しているコンピュータ間でデータベースを移動する方法
• SQL Server のデタッチとアタッチ機能を使用して SQL Server データベースを新しい場所に移動する方法
• [INF] RESTORE ステートメントでの WITH MOVE オプションの使用
• [HOWTO] SQL Server を実行しているサーバー間でデータベースを移動するときに、権限の問題を解決する方法
• SQL Server のインスタンス間でログインおよびパスワードを転送する方法
• [INF] SQL Server 2000 のデータベース コピー ウィザードの説明とトラブルシューティング
• データベースの復元後にデータベース上のユーザー ログインと権限が無効になる
• システム データベースのバックアップを別のビルドの SQL Server に復元できない
• [HOWTO] データベース バックアップまたはトランザクション ログ バックアップからテーブルまたは行を取得する方法

ヒント : ポートの変更

SQL Server に詳しいユーザーであれば、SQL Server がリッスンする既定のポート番号が、ポート 1433 であることはご存知でしょう。SQL Server の名前付きインスタンスは動的ポートを使用するように構成されますが (SQL Server インスタンスは、起動時に、使用可能な任意のポートを選択できます)、SQL Server の既定のインスタンスは常にポート 1433 でリッスンします。そのため、サーバーが既定のポートでリッスンしている場合、そのポートのセキュリティが厳重に確保されていなければ、サーバーは危険にさらされることになります。ただし、既定のポートを変更することで攻撃を阻止できます。その方法を以下で説明します。

SQL Server 構成マネージャを開き、[SQL Server 2005 ネットワークの構成]、[<InstanceName> のプロトコル] を順に展開します。次に、[TCP/IP] をダブルクリックします。下記の TCP/IP のプロパティと各プロパティの説明の一覧に従って設定します。

SQL Server データベース エンジンは、複数のポートで同じ IP アドレスをリッスンできるので、使用するポートを 1433,1500,1501 のようにコンマ区切りの形式で指定します。1 つの IP アドレスを複数のポートでリッスンするように構成する場合は、[TCP/IP のプロパティ] ダイアログ ボックスの [プロトコル] タブで、[すべて受信待ち] パラメータを [いいえ] に設定する必要があります。

次に、各アドレスを右クリックし、[プロパティ] をクリックして構成する IP アドレスを特定します。[TCP 動的ポート] ダイアログ ボックスに 0 が設定されている場合はデータベース エンジンが動的ポートでリッスンしていることを表すので、0 を削除します。IP のプロパティの [TCP ポート] ボックスに、この IP アドレスをリッスンするポート番号を入力し、[OK] をクリックします。コンソール ウィンドウで [SQL Server 2005 のサービス] をクリックし、詳細ウィンドウで [SQL Server (<instance name>)] を右クリックして [再起動] をクリックし、SQL Server を停止して再起動します。

特定のポートでリッスンするように SQL Server を構成すると、サーバーで SQL Server Browser サービスを実行してデータベース エンジンのインスタンスに名前を使用して接続する方法、クライアント上に別名を作成してポート番号を指定する方法、およびカスタム接続文字列を使用して接続するようにクライアントをプログラムする方法という 3 つの方法でクライアントとポートを接続できます。

**今月の質問に回答してくれた Microsoft IT 技術支援スタッフに心より感謝します。**Chad Boyd、Cindy Gross、John Hadden、Saleem Hakani、Stephen Jiang、Mahesh Nayak、Paul Randal、Wayne Yu

© 2008 Microsoft Corporation and CMP Media, LLC. All rights reserved; 許可なしに一部または全体を複製することは禁止されています.