SQL Server 2017＋Windows Server 2019 で、大規模なストアドプロシージャ（SP）の不具合原因を行単位で突き止めたい──しかし SSMS 18/19 には T‑SQL デバッガがない。この記事は、そのギャップを埋める実践ガイドです。Visual Studio（SSDT）を使う方法、SSMS 17.9.1 併用、サードパーティ製 IDE、さらに「デバッガなし」で攻めるトラブルシュート術まで、現場で役立つ手順と運用の勘所を網羅します。
目次
前提とゴール
- 対象環境：SQL Server 2017（Standard Edition）、Windows Server 2019、クライアントに Visual Studio 2019 Community（または 2022）。
- 課題：SSMS 18/19 に T‑SQL デバッガがないため、
PRINTベースでは大規模 SP の解析が煩雑。 - ゴール：行単位のステップ実行・ブレークポイント・ウォッチを実現する方法を確立し、加えて代替デバッグ手法（Extended Events 等）を手元に持つ。
結論（最短ルート）
- 最も手軽：既に VS を使っているなら Visual Studio＋SSDT。SSMS に戻す必要なく、行単位でデバッグできます。
- SSMS 派：どうしても SSMS で完結したいなら SSMS 17.9.1 を併用（18/19 と共存可）。EoL 版のため隔離運用が前提。
- 高機能統合：dbForge Studio for SQL Server や ApexSQL Debug 等のサードパーティ IDE。データ比較・プロファイラ等と一体で使いたい場合に有効。
方法の比較
| 方法 | 手順・ポイント | メリット | デメリット / 注意点 |
|---|---|---|---|
| A. Visual Studio 2019/2022 ＋ SSDT | 1) VS インストーラで SQL Server Data Tools を追加 2) SQL Server Object Explorer（SSOE）で DB 接続 3) Programmability → Stored Procedures から SP を右クリック→ Debug Procedure 4) パラメータ値を入力して開始。F10/F11 でステップ、F9 でブレークポイント、Locals/Watch/Call Stack を活用 | 行単位デバッグ、ウォッチ、条件付きブレークポイント、ソース管理統合が可能。SSMS に依存せず完結。 | VS が重い／SSDT のセットアップが必要。サーバー側でデバッグ権限やネットワーク要件に配慮。 |
| B. SSMS 17.9.1 を併用 | 1) 17.9.1 を別途導入（18/19 と共存可） 2) SP を開き デバッグの開始 または Alt+F5 3) ブレークポイント／ウォッチ／ステップ実行は 17 系で利用 | SSMS だけで完結。学習コストが低い。 | EoL 版。隔離された検証用 VM での使用やネットワーク最小化などセキュリティ対策が前提。 |
| C. サードパーティ IDE | 例：dbForge Studio for SQL Server、ApexSQL Debug など。GUI で行単位デバッグに加えスキーマ比較やプロファイリングも提供。 | 多機能・わかりやすい UI。保守業務の一括効率化が期待できる。 | 有償ライセンス。ネットワークや権限設定次第で「Debugger が読み込めない」等の初期エラーが出やすい。 |
方法 A：Visual Studio（SSDT）で SP を行単位デバッグする
セットアップ
- Visual Studio インストーラを起動し、SQL Server Data Tools を追加します（既存の VS 2019/2022 に後から追加可能）。
- VS を起動し、表示 → SQL Server Object Explorer を開き、対象インスタンスに接続します。
- 接続ノードのコンテキストメニューで Allow SQL/CLR Debugging（Transact‑SQL デバッグを許可） を有効化します。
※接続し直すと反映されます。デバッグは一般に sysadmin 権限が必要です（最小権限で運用したい場合は検証用環境で）。
デバッグの開始
- 対象 DB → Programmability → Stored Procedures で SP を右クリックし、Debug Procedure を選択。
- パラメータ入力ダイアログが表示されるので値をセットし、OK でブレークポイント可能な T‑SQL デバッガが立ち上がります。
- 以下の操作が使えます：
- F11：ステップイン（子プロシージャ／関数に入る）
- F10：ステップオーバー
- Shift+F11：ステップアウト
- F9：ブレークポイントの設定／解除（右クリックで条件・ヒットカウント・フィルタを設定可能）
- Locals/Watch ウィンドウ：パラメータやローカル変数の値を確認
- Immediate ウィンドウ：簡易式の評価や
SELECT実行（デバッグセッション内）
よくあるつまずきと対処
| 症状 | 考えられる原因 | 対処 |
|---|---|---|
| 「Unable to start T‑SQL Debugging」 | 権限不足／接続で Allow SQL/CLR Debugging が無効／RPC（DCOM）通信が遮断 | 接続を作り直して許可を有効化、sysadmin で試行、可能ならサーバー上で VS を起動してローカル接続でデバッグ |
| リモートだと開始できないがローカルだと成功 | ファイアウォールで TCP 135（RPC）と動的 RPC ポートが閉じている | 検証用に RDP でサーバーへ入りローカルデバッグに切り替え（本番は不可）。ネットワーク開放は最小範囲で。 |
| 変数の値が期待と違う | SET NOCOUNT ON や副作用のあるトリガーで制御フローが読みにくい | 一時的に NOCOUNT を外す／ヒット回数条件付きブレークポイントで分岐直前を捕捉 |
デバッグに強い T‑SQL の書き方（VS でも SSMS でも有効）
- 大規模 SP は論理ブロックごとに 小さな内部プロシージャ に分解し、個別に実行可能にする。
- パラメータ検証 を入口で行い、
THROWで即座に失敗させる（早期リターン）。 - 副作用の位置を固定（更新系は専用のブロックに隔離）。
- WITH RECOMPILE をデバッグ中だけ付与してキャッシュプランの影響を避ける。
CREATE OR ALTER PROCEDURE dbo.usp_OrderClose
@OrderId int,
@DryRun bit = 1
WITH RECOMPILE
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
IF @OrderId IS NULL OR @OrderId <= 0
THROW 50001, 'OrderId is invalid.', 1;
DECLARE @st nvarchar(20) = N'start';
RAISERROR(N'[debug] step=%s, order=%d', 0, 1, @st, @OrderId) WITH NOWAIT;
-- 論理1: 入力検証
EXEC dbo.usp_ValidateOrder @OrderId;
-- 論理2: 集計
EXEC dbo.usp_AggregateForClose @OrderId;
-- 論理3: 更新（DryRun のときはロールバック）
IF @DryRun = 1
BEGIN TRAN;
EXEC dbo.usp_ApplyClose @OrderId;
IF @DryRun = 1 ROLLBACK TRAN;
END TRY
BEGIN CATCH
DECLARE @msg nvarchar(4000) = ERROR_MESSAGE();
RAISERROR(N'[error] %s (line %d)', 16, 1, @msg, ERROR_LINE());
RETURN;
END CATCH
END
上のように RAISERROR ... WITH NOWAIT を挿入すると、バッファリングされず即時に出力され、処理の進み具合が追いやすくなります（PRINT よりもリアルタイム）。
方法 B：SSMS 17.9.1 を併用する（EoL 版の安全運用）
SSMS 17.9.1 は SSMS 18/19 と共存できます。17 系に内蔵された T‑SQL デバッガを使えば、普段の SSMS の操作感でブレークポイント・ステップ実行・ウォッチが可能です。ただし更新終了版のため、以下の運用を強く推奨します。
- 隔離：インターネット非接続の検証用 VM に導入し、用途をデバッグのみに限定。
- 権限：検証用のテスト DB／アカウントを用意し、最小範囲で sysadmin を付与。
- 手順：SP を開き デバッグの開始（Alt+F5）。必要に応じてパラメータを入力し、F10/F11、ウォッチで追跡。
方法 C：サードパーティ IDE（dbForge / ApexSQL など）
dbForge Studio for SQL Server や ApexSQL Debug は、行単位デバッガを備えつつ、スキーマ・データ比較、依存関係解析、実行計画の可視化など保守運用で欲しい機能をワンストップで提供します。選定の際は以下をチェックします。
- 基本操作：SP を開く → ブレークポイント設定 → デバッグ開始 → 変数ウォッチ／条件付き停止。
- インストール要件：クライアントにデバッガ補助 DLL が配置され、サーバーにロード可能か（権限・ネットワーク・ビット数の整合）。
- 典型的な初期エラー：「Failed to load debugger」「Permission denied」など。多くは権限・通信・バージョン不整合が原因。
- コスト：ユーザー数／年額・買い切り・保守料、チーム規模に応じた TCO を試用で確認。
デバッガなしでも深追いできる：代替デバッグ手法
本番相当のデータ量や複雑な副作用が絡むと、行単位デバッグだけで原因に迫れない場合があります。以下は どの環境でも使える 再現性の高い手法です。
TRY…CATCH ＋ THROW／RAISERROR（NOWAIT）
BEGIN TRY
-- 問題の箇所
EXEC dbo.usp_Something @id=@OrderId;
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS error_number,
ERROR_SEVERITY() AS severity,
ERROR_STATE() AS state,
ERROR_PROCEDURE() AS proc,
ERROR_LINE() AS line_no,
ERROR_MESSAGE() AS message;
-- ログに残す
RAISERROR(N'[catch] %s (num=%d, line=%d)',
16, 1, ERROR_MESSAGE(), ERROR_NUMBER(), ERROR_LINE());
THROW; -- 再スローで上位に伝播
END CATCH;
PRINT はバッファリングされますが、RAISERROR(...,0,1) WITH NOWAIT は即時出力されるため、流れを追いやすくなります。
Extended Events（XEvent）でステートメント完了を取得
SP 内の各ステートメント完了を時系列で拾い、重い箇所を特定します。簡易リングバッファ版の例：
-- セッション作成（対象 SP の object_id でフィルタ）
IF EXISTS (SELECT 1 FROM sys.server_event_sessions WHERE name = 'debug_sp')
DROP EVENT SESSION debug_sp ON SERVER;
GO
DECLARE @obj int = OBJECT_ID(N'dbo.usp_OrderClose');
CREATE EVENT SESSION debug_sp ON SERVER
ADD EVENT sqlserver.sp_statement_completed(
ACTION(sqlserver.sql_text, sqlserver.database_name, sqlserver.session_id)
WHERE (object_id = @obj))
ADD TARGET package0.ring_buffer(SET max_memory = (4096))
WITH (TRACK_CAUSALITY = ON);
GO
ALTER EVENT SESSION debug_sp ON SERVER STATE = START;
GO
-- SP 実行後、結果を読む
WITH xe AS (
SELECT CAST(t.target_data AS xml) AS x
FROM sys.dm_xe_sessions s
JOIN sys.dm_xe_session_targets t
ON t.event_session_address = s.address
WHERE s.name = 'debug_sp' AND t.target_name = 'ring_buffer'
)
SELECT
n.value('(event/@timestamp)[1]', 'datetime2') AS [utc_time],
n.value('(event/action[@name="session_id"]/value)[1]', 'int') AS spid,
n.value('(event/data[@name="statement"]/value)[1]', 'nvarchar(max)') AS stmt,
n.value('(event/data[@name="duration"]/value)[1]', 'bigint') AS duration_microsec
FROM xe
CROSS APPLY x.nodes('/RingBufferTarget/event') AS tab(n)
ORDER BY [utc_time];
GO
ALTER EVENT SESSION debug_sp ON SERVER STATE = STOP;
負荷の高いセグメントや、想定外に繰り返し実行されるクエリを素早く特定できます。
SET STATISTICS IO, TIME
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
EXEC dbo.usp_OrderClose @OrderId = 1001, @DryRun = 1;
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
読み取りページ数や CPU/経過時間を把握し、テーブルスキャンや過剰なルックアップを検知します。
ログテーブル方式（軽量トレース）
CREATE TABLE dbo.DebugLog(
ts datetime2(3) NOT NULL DEFAULT sysdatetime(),
step nvarchar(64) NOT NULL,
msg nvarchar(4000) NULL
);
-- 使い方（SP 内）
INSERT dbo.DebugLog(step, msg)
VALUES (N'aggregate-start', CONCAT(N'order=', @OrderId));
本番ではトランザクション境界や PII の扱いに注意し、検証環境・限定期間で運用します。
大規模 SP を壊さずに直すための実践パターン
- フェーズ分離：検証／集計／更新を明確に分割し、
@DryRunスイッチで 更新だけロールバック できるようにする。 - 入力の正規化：NULL や空文字、タイムゾーン／
DATETIMEの境界値を入口で潰す。 - 副問い合わせの可視化：複雑な式は CTE に分解、段階ごとに件数・チェックサムをログ。
- 非決定的関数の排除：
GETDATE()等は変数化して同一値を使い回し、再現性を確保。 - トランザクション境界の最小化：ロックを短く保ち、デバッグ時のブロッキングを減らす。
チェックリスト（導入前に確認）
| 項目 | チェック内容 |
|---|---|
| 環境 | 検証用インスタンス／サンプルデータは本番と同一スキーマ・近似データ量か |
| 権限 | デバッグ用ログインに必要最小の権限を付与（検証環境のみ sysadmin 可） |
| ネットワーク | リモートデバッグが必要な場合の RPC 通信（TCP 135 等）ポリシーの確認。推奨はローカルデバッグ。 |
| 変更管理 | SSDT または Git でスクリプトをバージョン管理。デバッグ用変更（WITH RECOMPILE、RAISERROR 等）はブランチ分離。 |
| 監査 | ログ出力に PII を含めない。デバッグログは定期削除。 |
FAQ（現場質問ベース）
Q. VS のデバッグが開始できません。
A. 接続で Allow SQL/CLR Debugging を有効化し、権限は一時的に sysadmin で確認。可能ならサーバー上からローカル接続で開始し、通信要件の影響を切り分けます。
Q. SSMS 17.9.1 は危なくないですか？
A. 本番ネットワークに常駐させず、検証用の隔離 VM に導入。用途をデバッグに限定し、不要時はスナップショットで巻き戻すのが安全です。
Q. サードパーティはどれを選べば？
A. 「行単位デバッグ＋比較・デプロイ自動化」まで求めるなら dbForge 系、SSMS 中心の作業導線を崩したくないなら ApexSQL 系が候補。評価版で自社 SP（長大・多分岐）を実際に動かし、安定性とトータルの時短効果を見極めてください。
Q. 行単位デバッグが禁止の環境です。
A. 上記の XEvent／RAISERROR ... WITH NOWAIT／統計出力／ログテーブルを組み合わせ、「いつ・どこで・どれだけ時間がかかったか」を時系列で可視化すると、多くの不具合は再現・切り分けできます。
安全運用の要点（必読）
- 本番でのデバッグ禁止：ブロッキングや意図せぬ副作用のリスクが高く、規定違反になり得ます。常に検証環境で。
- 権限は最小に：やむなく sysadmin を付与するのは検証環境のみ。作業後は必ず剥奪。
- 変更の見える化：SSDT で DB スキーマをプロジェクト化し、Pull Request レビューを必須に。
- デバッグ痕の除去：
WITH RECOMPILEやRAISERROR、ログテーブルの呼び出しは本番投入前に削除。
まとめ：最短で再現、最小で修正
SSMS 18/19 でデバッガがなくても、Visual Studio（SSDT）を使えば SP を行単位で追跡できます。SSMS 17.9.1 併用やサードパーティ IDE は選択肢を広げますが、いずれも 検証環境・最小権限・記録の徹底 が前提。さらに XEvent、RAISERROR ... WITH NOWAIT、統計出力といった「デバッガレス」技法を武器にすれば、複雑な SP の原因究明は一段と速く、確実になります。自社のポリシー・予算・チーム構成に合わせて、最も現実的なルートから導入してください。
付録：現場でそのまま使えるスニペット集
条件付きブレークポイントの代替（ヒット回数で STOP）
-- 例：100件ごとに進捗を表示
IF (@i % 100 = 0)
RAISERROR(N'[debug] i=%d, time=%s', 0, 1, @i, CONVERT(varchar(23), SYSDATETIME(), 121)) WITH NOWAIT;
分岐のトレース（タグ付き）
IF @mode = 'A'
BEGIN
RAISERROR(N'[branch=A] id=%d', 0, 1, @id) WITH NOWAIT;
-- 処理...
END
ELSE
BEGIN
RAISERROR(N'[branch=B] id=%d', 0, 1, @id) WITH NOWAIT;
-- 処理...
END
副作用ブロックの一極集中
BEGIN TRY
IF @DryRun = 1 BEGIN TRAN;
-- ここから更新系を集約
UPDATE ...;
INSERT ...;
DELETE ...;
-- ここまで更新系
IF @DryRun = 1 ROLLBACK TRAN;
END TRY
BEGIN CATCH
IF XACT_STATE() < 0 ROLLBACK TRAN;
THROW;
END CATCH;
重いクエリの局所化（検証用に RECOMPILE）
SELECT /* debug */ ...
FROM ...
WHERE ...
OPTION (RECOMPILE); -- デバッグ中のみ
Query Store で直近の実行を追跡（検証環境）
-- 例：対象 SP の強調表示
SELECT TOP (20)
qsrs.last_execution_time,
qsq.query_sql_text
FROM sys.query_store_query_text AS qsq
JOIN sys.query_store_query AS qsqry ON qsqry.query_text_id = qsq.query_text_id
JOIN sys.query_store_plan AS qsp ON qsp.query_id = qsqry.query_id
JOIN sys.query_store_runtime_stats AS qsrs ON qsrs.plan_id = qsp.plan_id
WHERE qsq.query_sql_text LIKE N'%EXEC dbo.usp_OrderClose%'
ORDER BY qsrs.last_execution_time DESC;
これらのスニペットは、Visual Studio の行単位デバッグと併用すると、再現・観察・修正のサイクルを劇的に短縮します。
コメント