SQL に関する Q&Aロックの検出、大きなクエリ、入出力統計など

Nancy Michell

この記事で使用しているコードのダウンロード: SQLQandA2007_08.exe (151KB)

質問 - すべてのデータベース内でロックを保持しているオブジェクトの名前を表示する必要があります。このような方法はありますか。

回答 - SQL Server™ 2000 では、次のように master データベースの syslocks システム テーブルに照会するか、sp_lock を実行すれば、現在のロック情報を取得できます。

SELECT * FROM master..syslocks
EXEC sp_lock

ただし、今回の質問では sp_lock ストアド プロシージャの出力に含まれる ObjID (または master..syslocks の id 列) から、実際のオブジェクト名への変換が必要です。

SQL Server 2005 SP1 以前では、OBJECT_NAME 関数にはパラメータを 1 つしか渡せません。つまり、object_id を 1 つしか渡せません。したがって、オブジェクト名を正しく取得するには、作業する現在のデータベースを設定してから OBJECT_NAME を実行する必要があります。そのため、すべてのデータベースから適切なオブジェクト名を取得するには、各データベースをループするカスタム コードを記述する作業が必要になるため、現状のロックの監視は難しくなります。

USE DBNAME  
SELECT OBJECT_NAME(object_id)

SQL Server 2005 SP2 では、2 つ目のパラメータとして database_id が追加され、この問題に対処しています。この新しいパラメータを使用すれば、現在どのデータベースに接続しているかとは無関係に、オブジェクトの名前を照会できます。

OBJECT_NAME ( object_id [, database_id ] )

次のように、sys.dm_tran_locks に照会して、データベースごとにオブジェクト名を取得できるようになりました。

SELECT
DB_NAME(resource_database_id),
OBJECT_NAME(resource_associated_entity_id, resource_database_id)
FROM sys.dm_tran_locks
WHERE resource_type='OBJECT'

ただし、この方法は SQL Server 2005 SP2 以降でのみ機能します。このコードを以前のバージョンの SQL Server 2005 で実行すると、次のエラーが発生します。

Msg 174, Level 15, State 1, Line 1
The object_name function requires 1 argument(s).

図 1 に示すもう 1 つの例では、sys.dm_exec_sessions を結合して、ロックに関与しているサービス プロセス ID (SPID) に関する情報を取得しています。詳細については、technet.microsoft.com/library/ms130214(sql.90).aspx にある SQL Server Books Online を参照してください。

Figure 1 サービス プロセス ID の検索

SELECT
DB_NAME(resource_database_id) as DBName,
OBJECT_NAME(resource_associated_entity_id, resource_database_id) AS ObjectName,
request_mode,
request_type,
request_session_id,
es.host_name,
es.login_name,
es.login_time
FROM
sys.dm_tran_locks tl
INNER JOIN sys.dm_exec_sessions es
ON tl.request_session_id=es.session_id
WHERE resource_type='OBJECT'

質問 – 大きな動的 SQL クエリを使用していますが、このクエリの長さが NVARCHAR(max) を超える場合があるようです。これに関して、引き続き 1 つの大きな文字列を実行できる方法はありますか。

回答 - NVARCHAR(max) の長さを超えるということは、2 GB のクエリを実行しているということです。おそらく、連結する文字列すべてを NVARCHAR(max) に変換する必要があります。ただし、それよりも簡単な回避策は、小さな文字列をたくさん連結する方法です。この方法には、SQL Server 2005 以前のバージョンの SQL Server で効果的であるという利点もあります。次に例を示します。

DECLARE @q1 NVARCHAR(4000), @q2 NVARCHAR(4000), @q3 NVARCHAR(4000)
SET @q1 = 'SELECT...'
SET @q2 = 'FROM...'
SET @q3 = 'WHERE...'
EXEC (@q1 + @q2 + @q3)

ヒント : OUTPUT 句の使用

トリガを使用しなくても、データ操作言語 (DML) ステートメントを使用して行う変更を監査できるようになりました。SQL Server 2005 では、DML ステートメントの一部として OUTPUT 句が導入されました。これは、DML 操作中に行われた変更の追跡に役立ちます。OUTPUT 句では、結果セットをテーブルまたはテーブル変数に保存できます。

この機能は、トリガで INSERTED テーブルおよび DELETED テーブルが使用されていたのと似ています。これらのテーブルは、DML 操作中に変更された行へのアクセスに使用されていました。この動作を確認するために、下図に示すように住所テーブルの住所を変更し、元の住所文字列を逆順の文字列にしてみましょう。また、このコードに示すロジックを使用して、データに加えた変更を追跡し、その変更をテーブルに保存することもできます。

このクエリからの出力は、次のようになります。

Original Value:'1234 One Microsoft Way, Redmond, Wa.' has been changed to: '.aW,dnomdeR 
,yaW tfosorciM enO 4321'

住所の変更

--Create the address table
Create Table Address (ProductID Int, SupplierID Int, Address Varchar(255))

--Insert data
Insert into Address Values (234,567,'1234 One Microsoft Way, Redmond, Wa.')


--Declare a table variable
Declare @Recordchanges table (change Varchar(255))

--Update the address
Update Supplier.Address
Set Address=reverse(address)

--Record the updates into the table variable
OUTPUT 'Original Value:' + DELETED.Address+' has been changed to: '+ INSERTED.Address+'' 
into @RecordChanges

--Query the changes from table variable
Select * from @RecordChanges

質問 - Windows Server® 2003 で、ミッション クリティカルな SQL Server 2005 クラスタ インストールを使用しています。Microsoft® 分散トランザクション コーディネータ (MS DTC) は、クラスタ クォーラムと同じグループに "クラスタ化" されていますが、専用のディスク リソースを使用しています (MS DTC は、クォーラム クラスタ グループと同じネットワーク名および IP アドレスを共有しています)。この構成をマイクロソフトのベスト プラクティスに合わせようと考えています。そのため、MS DTC を専用のクラスタ グループに移動することに関してアドバイスが必要です。クラスタ アドミニストレータ ツールを使用して、MS DTC サービスを削除し、専用のグループに MS DTC サービスを再作成するだけでよいのでしょうか。

回答 - 既に MS DTC 用に専用のディスク リソースがあるので、単純に、そのリソースを削除し、新しいグループとしてそのリソースを再作成できます。新しいグループには、新しいネットワーク名と仮想 IP アドレスも作成する必要があります。

もう 1 つの方法として、クラスタ グループ内に新しいネットワーク名と IP アドレスを作成し、依存関係を新しいリソースに変更することもできます。その後、MS DTC を新しいグループにドラッグすれば、専用のディスクとそのディスクに関連付けられたリソースを使用できるようになります。

質問 – あるデータベース内の物理データベース ファイルの入出力統計を表示する必要があります。何を使用したらよいでしょう。

回答 - fn_virtualfilestats システム関数を使用します。これは、SQL Server 2000 と SQL Server 2005 のどちらでも使用できます。また、sys.dm_io_virtual_file_stats (SQL Server 2005 のみ) を使用すると、目的の操作を正確に行えます。この関数は、SQL Server のインスタンスが前回起動されてから収集された統計情報を返します。図 2 にサンプルの結果を示します。

Figure 2 データベースの入出力統計の表示

基礎となるデータ ファイルに対する入出力の影響を理解することは、ファイルやファイル グループのデータ ボリューム上への物理的な配置、入出力の潜在的ボトルネックの検出、ファイルレベルのデータベース メンテナンスの実行など、さまざまなタスクをより適切に計画する際に役立ちます。この関数は、特に、複数のファイルおよびファイル グループが存在するような大規模データベースへの入出力の影響を調査する場合に有効です。

SQL Server 2000 でのファイル入出力情報を表示するクエリは、次のようになります。

SELECT *
FROM ::fn_virtualfilestats(default,default)
GO

特定の databaseID を表示するには、データベースの ID を次のように渡します。

SELECT *
FROM ::fn_virtualfilestats(7,default)
GO

サーバー上のすべてのデータベースについてファイル統計を表示する SQL Server 2005 コードを次に示します。

SELECT *
FROM ::fn_virtualfilestats(NULL,NULL)
GO

次のクエリでは、現在のデータベースのみのファイル統計が返されます。

SELECT *
FROM ::fn_virtualfilestats(NULL,NULL)
WHERE DBID=db_id()
GO

SQL Server 2005 では、sys.dm_io_virtual_file_stats という新しいシステム関数も使用できます。この関数は、最終的には従来の fn_virtualfilestats 関数に取って代わる予定です。

sys.dm_io_virtual_file_stats( 
{ database_id | NULL },
{ file_id | NULL }
)

使用方法を次に示します。

SELECT * FROM sys.dm_io_virtual_file_stats(NULL,NULL)

この出力から、実際のデータベース名とファイル名を使った読みやすいレポートを作成するには、SQL Server 2000 または SQL Server 2005 の付属ダウンロードに含まれているコードを使用できます。このコードは、TechNet MagazineWeb サイトから入手できます。

質問 – 削除のトランザクションによってトリガが起動されたかどうかを確認する簡単な方法を必要としています。よい方法があれば、教えてください。

回答 – 削除操作、挿入操作、および更新操作を処理するトリガを扱っているとき、削除のトランザクションによってトリガが起動されたかどうかを判断する場合、一般に、さまざまなテクニックが使用されます。最も一般的な方法は、INSERTED 仮想テーブルと DELETED 仮想テーブルの数を比較して、一致するかどうかを確認する方法です。ただし、これよりも簡単な方法として、Columns_Updated 関数を使用する方法があります。

この方法を使用できるのは、削除のトランザクションによってトリガが起動された場合、Columns_Updated が必ず varbinary 値 0x を返すためです。次のチェックを行うと、削除によってトリガが起動されたかどうかを確認できます。

IF Columns_Updated() = 0x

ヒント : データ ファイルとログ ファイルの初期化

以前に削除されたファイルのデータがディスク上に残っている場合、そのデータに上書きするためにデータ ファイルとログ ファイルが初期化されることをご存知でしたか。データベースを作成したとき、ログ ファイルまたはデータ ファイルを既存のデータベースに追加したとき、既存のファイルのサイズを増加したとき (自動拡張操作を含む)、データベースまたはファイル グループを復元したときに、データ ファイルとログ ファイルはゼロ (0) を埋め込むことで初期化されます。こうした操作では、ファイルの初期化によって余分な時間がかかります。ただし、ファイルに最初にデータが書き込まれるときに、オペレーティング システムがこれらのファイルにゼロ (0) を埋め込む必要がなくなります。

SQL Server 2005 では、データ ファイルを瞬時に初期化できます。この機能により、先に説明したファイル操作を高速に実行できます。ファイルの瞬時初期化では、使用中のディスク領域にゼロ (0) が埋め込まれることなく再要求されます。代わりに、新しいデータがファイルに書き込まれるときに、ディスクの内容が上書きされます。ログ ファイルについては、瞬時に初期化することはできません。ファイルの瞬時初期化は、Windows XP Professional および Windows Server 2003 またはそれ以降のバージョンでのみ使用できます。

削除されたディスクの内容は、新しいデータがファイルに書き込まれたときにのみ削除されるため、削除されたディスクの内容が、未承認のプリンシパルからアクセスされる可能性があります。データベース ファイルが SQL Server のインスタンスにアタッチされている間は、そのファイルの随意アクセス制御リスト (DACL) によって、このような情報漏えいの脅威は低減されます。この DACL では、ファイルへのアクセスを SQL Server サービス アカウントとローカル管理者のみに制限できます。ただし、ファイルをデタッチすると、SE_MANAGE_VOLUME_NAME のないユーザーやサービスからアクセスされる可能性があります。データベースのバックアップ時にも同様の脅威が存在します。バックアップ ファイルが適切な DACL を使用して保護されていなければ、未承認のユーザーやサービスが、削除された内容にアクセスできるようになる可能性があります。

削除された内容が漏えいする可能性が問題になる場合は、デタッチされたデータ ファイルおよびバックアップ ファイルに、常に、制限付き DACL を設定する必要があります。また、SQL Server の SE_MANAGE_VOLUME_NAME を無効にすることにより、SQL Server のインスタンスでのファイルの瞬時初期化も無効にしてください。

Thanks to the following Microsoft IT pros for their technical expertise: Christian Bolton, Dan Carollo, Robert Davis, Jorge Guzman, Saleem Hakani, Ward Pond, Kalyan Yella, and Paolo Zavatarelli.

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