T-SQL の分析

T-SQL の分析

〜安普請なシステムを調査〜

2025-12-25 作成福島

0. 前置き

作成したアプリケーションの動作を、調査や監査をしなければならないことがあります。
特に Microsoft 製品をあてにするプログラマーは運用保守を軽視する傾向が強いため、この作業が必須となります。
日本の通信インフラはかなり高速化されましたが、無駄な処理が多いと処理速度にも影響が現れてしまいます。

本稿では Microsoft SQL Server に対するクエリ発行の調査を 2 通りの方法で実施します。
どちらも一長一短があります。状況に合わせて、好ましいほうを選択してください。

ファイルにログを蓄積する

メモリにログを蓄積する

動作環境: 以前のバージョンでも同様に動作する。

項目内容備考

基本ソフト Windows Server 2025 Standard 本稿記述時の最新版

データベースソフト Microsoft SQL Server 2025

クライアントソフト sqlcmd 17.0 v16 からデフォルトで暗号化接続を要求するようになったが、
localhost や LAN 内の開発では暗号化が意味を持たない
暗号化を抑止するには -No, -C 等のオプションを指定する

1. ファイルにログを蓄積

ログをファイルに蓄積し、採取後にファイルで分析する。
ファイル名は勝手に枝番が付加されるため、作成されたログファイルを削除するのが面倒。

1-1. ファイル作成可能なディレクトリを調査

MS SQL Server は特殊なアカウント (NT Service\MSSQLSERVER) で動作しており、通常ディレクトリに対する書き込み権限がない。
このため、操作ログを保存できる場所を探し当てる必要がある。

sqlcmd はクライアント PC から実行できるが、表示されるディレクトリ名は MS SQL Server 内の場所を指している。
− □ ×

>_ Windows PowerShell × + | ∨
Windows PowerShell
Copyright (C) Microsoft Corporation. All rights reserved.

新機能と改善のために最新の PowerShell をインストールしてください!https://aka.ms/PSWindows  

PS C:\> sqlcmd -? | Select-Object -First 4
Microsoft (R) SQL Server コマンド ライン ツール
バージョン 17.0.1000.7 NT
Copyright (C) 2025 Microsoft Corporation.All rights reserved.

PS C:\> sqlcmd -S localhost,1433 -U sa -P "Password123" -h -1 -W -No -Q "SELECT SERVERPROPERTY('InstanceDefaultDataPath');"  
C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\


PS C:\> sqlcmd -S localhost,1433 -U sa -P "Password123" -h -1 -W -No -Q "SELECT SERVERPROPERTY('InstanceDefaultLogPath');"  
C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\

PS C:\> exit ⏎
暗号化とチェックを無視するためにオプション -No を付加している。

1-2. イベントログの採取条件を指定

SQL の実行が完了したタイミングで以下を記録し、出力ファイルを logsql_xxx…xx.xel にする設定。
(xxx…xx は Windows 基準秒 = 1601-01-01 からの経過時間 ×100ns)

実行した SQL 文

クライアントアプリ名

ログインID

出力ファイルの格納先は上記 1-1 で調査した書き込み可能ディレクトリ。
− □ ×

>_ Windows PowerShell × + | ∨
Windows PowerShell
Copyright (C) Microsoft Corporation. All rights reserved.

新機能と改善のために最新の PowerShell をインストールしてください!https://aka.ms/PSWindows  

PS C:\> @"
CREATE EVENT SESSION LogXEL
ON SERVER
ADD EVENT sqlserver.sql_statement_completed (
  SET collect_statement = 1
  ACTION(sqlserver.sql_text, sqlserver.client_app_name, sqlserver.username)
)
ADD TARGET package0.event_file(SET filename = 'C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\logsql.xel');
"@ | sqlcmd -S localhost,1433 -U sa -P "Password123" -No

PS C:\> exit ⏎
設定の削除はこちら
sqlcmd -S localhost,1433 -U sa -P "Password123" -No -Q "DROP EVENT SESSION LogXEL ON SERVER;"

1-3. SQL の実行とログ採取

				− □ ×
	>_ Windows PowerShell	×	+ \| ∨

Windows PowerShell
Copyright (C) Microsoft Corporation. All rights reserved.

新機能と改善のために最新の PowerShell をインストールしてください!https://aka.ms/PSWindows  

PS C:\> # ログ採取を開始する
PS C:\> sqlcmd -S localhost,1433 -U sa -P "Password123" -No -Q "ALTER EVENT SESSION LogXEL ON SERVER STATE = START;"  


PS C:\> # SQL を実行する (SQL 文をログに残すのが目的なので結果は気にしない)
PS C:\> sqlcmd -S localhost,1433 -U sa -P "Password123" -No -Q "SELECT @@VERSION"
----------------------------------------------------------------------------------------------------------
Microsoft SQL Server 2025 (RTM) - 17.0.1000.7 (X64)
        Oct 21 2025 12:05:57
        Copyright (C) 2025 Microsoft Corporation
        Developer Edition (64-bit) on Windows Server 2025 Standard Evaluation 10.0 <X64> (Build 26100: )

(1 行処理されました)


PS C:\> # ログ採取を停止する
PS C:\> sqlcmd -S localhost,1433 -U sa -P "Password123" -No -Q "ALTER EVENT SESSION LogXEL ON SERVER STATE = STOP;"

PS C:\> exit ⏎

1-4. ログを表示

				− □ ×
	>_ Windows PowerShell	×	+ \| ∨

Windows PowerShell
Copyright (C) Microsoft Corporation. All rights reserved.

新機能と改善のために最新の PowerShell をインストールしてください!https://aka.ms/PSWindows  

PS C:\> # ログファイル名を調査
PS C:\> @"
SET NOCOUNT ON;
DECLARE @Path NVARCHAR(260);
SET @Path = CAST(SERVERPROPERTY('InstanceDefaultDataPath') AS NVARCHAR(260));

CREATE TABLE #Files (subdirectory NVARCHAR(260), depth INT, isfile BIT);  -- ファイル一覧を取得
INSERT INTO #Files (subdirectory, depth, isfile) EXEC xp_dirtree @Path, 1, 1; -- 1:階層, 1:ファイルのみ
SELECT subdirectory FROM #Files WHERE isfile = 1 AND subdirectory LIKE '%.xel'; -- *.xel が対象

DROP TABLE #Files;
"@ | sqlcmd -S localhost,1433 -U sa -P "Password123" -No -s "," -W
subdirectory
------------
logsql_0_134090512740400000.xel    ← ファイル名はこれにされた (自動採番を拒否できない)


PS C:\> # ログを現像する
PS C:\> @"
SET QUOTED_IDENTIFIER ON;
SET ANSI_NULLS ON;
GO

SELECT
  DATEADD(HOUR,9, CAST(event_data AS XML).value('(/event/@timestamp)[1]','datetime2')),  -- JST に変換
  CAST(event_data AS XML).value('(/event/action[@name="client_app_name"]/value)[1]', 'nvarchar(256)'),
  CAST(event_data AS XML).value('(/event/data[@name="statement"]/value)[1]', 'nvarchar(max)')
FROM sys.fn_xe_file_target_read_file(
  'C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\logsql_0_134090512740400000.xel',
NULL, NULL, NULL)
WHERE CAST(event_data AS XML).exist('/event/data[@name="statement"]/value') = 1;
GO
"@ | sqlcmd -S localhost,1433 -U sa -P "Password123" -No -s "," -W
 , , 
-,-,-
2025-12-01 17:27:59.4310000,SQLCMD,SET TEXTSIZE 4096
2025-12-01 17:27:59.4320000,SQLCMD,SELECT @@VERSION   ← 実行した SQL はこれ (上記 1-3)
2025-12-01 17:28:03.1450000,SQLCMD,SET TEXTSIZE 4096
2025-12-01 17:28:03.1460000,SQLCMD,ALTER EVENT SESSION LogXEL ON SERVER STATE = STOP;

(4 行処理されました)

PS C:\> exit ⏎

2. メモリにログを蓄積

MS SQL Server の内部にリングバッファを作成し、リングバッファにログを蓄積する。

ファイルを残さないので手軽ではあるが、ファイルほど多くのログを蓄積することは出来ない。
また、ログ採取を停止すると蓄積されたデータが消えるため、高負荷の時間帯には実施できない。

2-1. イベントログの採取条件を指定

SQL の実行が完了したタイミングで以下を記録し、リングバッファに出力する設定。

実行した SQL 文

クライアントアプリ名

ログインID

リングバッファを使用するので、バッファが満杯になったときの挙動を指定しなければならない。
ここでは ALLOW_SINGLE_EVENT_LOSS として、古いデータから削除することを指定している。
(古いデータを削除せず、新しいデータを蓄積しない場合は NO_EVENT_LOSS を指定する)
− □ ×

>_ Windows PowerShell × + | ∨
Windows PowerShell
Copyright (C) Microsoft Corporation. All rights reserved.

新機能と改善のために最新の PowerShell をインストールしてください!https://aka.ms/PSWindows  

PS C:\> @"
CREATE EVENT SESSION LogBuf
ON SERVER
ADD EVENT sqlserver.sql_statement_completed (
  SET collect_statement = 1
  ACTION(sqlserver.sql_text, sqlserver.client_app_name, sqlserver.username)
)
ADD TARGET package0.ring_buffer WITH (MAX_MEMORY = 8192KB, EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS);
"@ | sqlcmd -S localhost,1433 -U sa -P "Password123" -No

PS C:\> exit ⏎
設定の削除はこちら
sqlcmd -S localhost,1433 -U sa -P "Password123" -No -Q "DROP EVENT SESSION LogBuf ON SERVER;"

2-2. SQL の実行とログの表示

				− □ ×
	>_ Windows PowerShell	×	+ \| ∨

Windows PowerShell
Copyright (C) Microsoft Corporation. All rights reserved.

新機能と改善のために最新の PowerShell をインストールしてください!https://aka.ms/PSWindows  

PS C:\> # ログ採取を開始する
PS C:\> sqlcmd -S localhost,1433 -U sa -P "Password123" -No -Q "ALTER EVENT SESSION LogBuf ON SERVER STATE = START;"  


PS C:\> # SQL を実行する (SQL 文をログに残すのが目的なので結果は気にしない)
PS C:\> sqlcmd -S localhost,1433 -U sa -P "Password123" -No -Q "SELECT @@VERSION"
----------------------------------------------------------------------------------------------------------
Microsoft SQL Server 2025 (RTM) - 17.0.1000.7 (X64)
        Oct 21 2025 12:05:57
        Copyright (C) 2025 Microsoft Corporation
        Developer Edition (64-bit) on Windows Server 2025 Standard Evaluation 10.0 <X64> (Build 26100: )

(1 行処理されました)


PS C:\> # ログを表示する
PS C:\> @"
SET QUOTED_IDENTIFIER ON;
SET ANSI_NULLS ON;

SELECT
  DATEADD(HOUR,9, evt.value('(@timestamp)[1]', 'datetime2')) AS datetime,  --> JST
  evt.value('(action[@name="client_app_name"]/value)[1]', 'nvarchar(256)'),
  evt.value('(action[@name="sql_text"]/value)[1]', 'nvarchar(max)')
FROM (
    SELECT CAST(targ.target_data AS XML) AS xmldata FROM sys.dm_xe_sessions AS sess
      JOIN sys.dm_xe_session_targets AS targ ON sess.address = targ.event_session_address
    WHERE sess.name = 'LogBuf' AND targ.target_name = 'ring_buffer'
    ) AS XML
  CROSS APPLY XML.xmldata.nodes('RingBufferTarget/event') AS T(evt)
  ORDER BY [datetime];
"@ | sqlcmd -S localhost,1433 -U sa -P "Password123" -No -W -s "," -h -1
2025-12-02 14:24:50.3050000,SQLCMD,SET TEXTSIZE 4096
2025-12-02 14:24:50.3060000,SQLCMD,SELECT @@VERSION   ← 実行した SQL はこれ
2025-12-02 14:25:15.2700000,SQLCMD,SET TEXTSIZE 4096         ↓ ヒアドキュメントを使っているため、
2025-12-02 14:25:15.2870000,SQLCMD,SET QUOTED_IDENTIFIER ON;    自分の SQL の一部も表示されてしまう
SET ANSI_NULLS ON;

SELECT
  DATEADD(HOUR,9, evt.value('(@timestamp)[1]', 'datetime2')) AS datetime,  --> JST
  evt.value('(action[@name="client_app_name"]/value)[1]', 'nvarchar(256)'),
  evt.value('(action[@name="sql_text"]/value

(4 行処理されました)


PS C:\> # ログ採取を停止する (リングバッファが停止するため、ログ表示もできなくなる)
PS C:\> sqlcmd -S localhost,1433 -U sa -P "Password123" -No -Q "ALTER EVENT SESSION LogBuf ON SERVER STATE = STOP;"

PS C:\> exit ⏎

項目	内容	備考
基本ソフト	Windows Server 2025 Standard	本稿記述時の最新版
データベースソフト	Microsoft SQL Server 2025	本稿記述時の最新版
クライアントソフト	sqlcmd 17.0	v16 からデフォルトで暗号化接続を要求するようになったが、 localhost や LAN 内の開発では暗号化が意味を持たない暗号化を抑止するには -No, -C 等のオプションを指定する