SQL Server Profiler–ストアド・プロシージャのデバッグ

SQL Server Profiler�Cストレージプロセスのデバッグ
 
SQL Serverには、複雑なストレージ・プロシージャのデバッグに役立つさまざまなツールがあります.Visual Studioで使用可能なTransact-SQLデバッガが含まれています.印刷文を埋め込んだり、ストレージ・プロシージャからデバッグ・ステータスを返したり、ストレージ・プロシージャのカスタム・エラーを呼び出してステータス情報を記録したりすることができます.SQLトレースには、ユーザーがイベントを構成できるツールも用意されています.
 
ユーザ構成可能なイベントはsp_という名前にほかならない.trace_generateeventのシステムストレージ・プロシージャの呼び出し.このストレージ・プロシージャには3つのパラメータがあります.
1.@eventidは82~91の整数値です.各値は、10個のユーザ構成可能なイベントクラスのうちの1つに対応し、この10個のクラスの番号は0~9である.値が82の場合は「UserConfigurable:0」イベント、83の場合は「UserConfigurable:1」イベントなどが呼び出されます.
2.@userinfoは、イベントを埋め込むためのTextData列のnvarchar(128)値です.
3.@userdataは、イベントを埋め込むためのBinaryData列のvarbinary(8000)値です.
 
特に対処しにくい場合、テスト環境でコピーしにくい場合にたまに失敗するストレージ・プロシージャを処理しなければなりません.これにより、ステータスを実際にデバッグするには文レベルの収集が必要になる可能性があり、実行時間が長すぎると、このような収集は大量のデータを生成します.より良い選択は、ストレージ・プロシージャ・レベルとバッチ・レベルのみを追跡し、ユーザーがイベントを構成して変数値を収集し、問題が発生した場合にデバッグ障害の他のデータを決定するのに役立ちます.この方法では,追跡が過剰なデータを収集することを心配する必要がなく,システムを自分で実行させることができる.
 
それを構築するには、まずsp_を知る必要があります.trace_generateeventストレージ・プロシージャを実行するには、ALTER TRACE権限が必要です.ユーザのストレージ・プロシージャがこの権限を持つ可能性は低いため、対応する権限を持ちsp_を呼び出すように作成することが望ましい.trace_generateeventのパッケージ・ストレージ・プロシージャ.これを行うには、SQLサーバのモジュール署名機能を使用する必要があります.最初のステップは、プライマリ・データベースに証明書を作成することです.
 

USE master
GO
 
CREATECERTIFICATE ALTER_TRACE_CERT
ENCRYPTION BYPASSWORD = '-USE_a!sTr0Ng_PWD-or-3~'
WITH
 SUBJECT = 'Certificate for ALTER TRACE',
 START_DATE = '20000101',
 EXPIRY_DATE = '99990101'
GO

 
次に、証明書ベースのログインを作成します.このログインには、ALTER TRACEとAUTHENTICATE SERVERの両方の権限が付与され、サーバレベルの権限をデータベースレベルのモジュールに送信する権利(例えば、パッケージング・ストレージ・プロシージャ)が付与されます.
 

CREATE LOGINALTER_TRACE_LOGIN
FROM CERTIFICATEALTER_TRACE_CERT
GO
 
GRANT ALTER TRACETO ALTER_TRACE_LOGIN
GO
 
GRANTAUTHENTICATE SERVER TO ALTER_TRACE_LOGIN
GO

 
この作業が完了したら、鍵を含む証明書をバックアップします.このバックアップは、カプセル化ストレージ・プロシージャを使用するユーザー・データベースと同じ証明書をリカバリするために使用できます.
 

BACKUP CERTIFICATE ALTER_TRACE_CERT
TO FILE = ‘C:\ALTER_TRACE.cer’
WITH PRIVATE KEY
(
FILE = ‘C:\ALTER_TRACE.pvk’,
ENCRYPTION BY PASSWORD = ‘-USE_a!sTr0Ng_PWD-or-3~’,
DECRYPTION BY PASSWORD = ‘-USE_a!sTr0Ng_PWD-or-3~’
)
GO

 
この章のサンプルコードをよりよく理解するために、tempdbでカプセル化プロセスを確立する方法について説明しますが、実際にはどのユーザー・データベースでも可能です.次のコードは、バックアップバージョンのtempdbに証明書を作成し、sp_trace_generateeventストレージ・プロシージャは、単純にカプセル化されています.
 

USE tempdb
GO
 
CREATE CERTIFICATE ALTER_TRACE_CERT
FROM FILE = ‘C:\ALTER_TRACE.cer’
WITH PRIVATE KEY
(
FILE = ‘C:\ALTER_TRACE.pvk’,
ENCRYPTION BY PASSWORD = ‘-USE_a!sTr0Ng_PWD-or-3~’,
DECRYPTION BY PASSWORD = ‘-USE_a!sTr0Ng_PWD-or-3~’
)
GO
 
CREATE PROCEDURE ThrowEvent
@eventid INT,
@userinfo nvarchar(128),
@userdata varbinary(8000)
AS
BEGIN
EXEC sp_trace_generateevent
@eventid = @eventid,
@userinfo = @userinfo,
@userdata = @userdata
END
GO

 
このプロセスを完了するために、このストレージ・プロシージャは証明書に署名し、このプロセスにALTER_と有効にします.TRACE_LOGINは同等のすべての権限を登録し、その後、この権限はストレージ・プロシージャを実行する任意のユーザーに付与されます.
 

ADD SIGNATURE TO ThrowEvent
BY CERTIFICATE ALTER_TRACE_CERT
WITH PASSWORD = '-USE_a!sTr0Ng_PWD-or-3~'
GO
 
GRANT EXEC ON ThrowEvent TO [public]
GO

 
ユーザが自分で選択したデータベース上にThrowEventストアド・プロシージャを作成すれば、別のストアド・プロシージャの内部から使用することができます.また、証明書が存在するため、呼び出しプログラムにどのような権限があるかを考慮する必要はありません.間欠障害を見つけようとすると、このツールは非常に重要です.
 
たとえば、テスト中にストレージ中にカラムを更新する必要があることが判明し、カラムが存在するテーブルが更新されない場合があります.この障害は、ストレージ・プロシージャが呼び出されると同時に別のテーブルの状態に起因するように見えますが、ユーザーは以前の配置で再生成できません.デバッグを行い、更新後のストレージに次のコードを挿入できます.
 

IF @@ROWCOUNT = 0
EXEC ThrowEvent 82, N’No data inserted into MyTable’, 0x0000

 
「RPC:Starting」、「SQL:BatchStarting」、および「UserConfigurable:0」イベントをキャプチャするトレースも作成する必要があります.しばらく実行した後、ユーザー定義イベントが励起され、ロー挿入がないことをユーザーに通知された場合、挿入時に別のテーブルの状態を決定するために十分なバックグラウンドデータが収集された可能性があります.
 
この例は、ストレージ・プロシージャ・デバッグ・ツール・ボックスにこのツールを追加するメリットを示します.ストレージ・プロシージャの進行状況に対するユーザーの可視性制御が高いほど、小さな障害が大きくなる前に追跡し、解決しやすくなります.