【SQL Server】クエリ実行時の待機イベントを確認する

クエリチューニングを実施する際に実行プランやIO、実行時間を確認することが多いかと思いますが、実行時間のうち待機イベントと待機時間を確認する方法の説明です。

以下を使用してCPU時間と実行時間を確認することが多いのではないでしょうか。

SET STATISTICS TIME ON
-- 実行時間を計測するクエリ
SET STATISTICS TIME OFF

お手軽にCPU時間と実行時間を確認することができますが、実行時間のうちCPU以外の待機はどのようであったか把握できません。

拡張イベントwait_comletedを使う

SQLServer2014以降から、待機イベントにかかった時間としてwait_completedが追加されており、待機イベントが終了した時にイベント名と経過時間を記録してくれます。
ただし、各イベントの待機時間の取得している単位以下は丸められてしまっているので、待機事象のミリ秒以下まで正確に把握したい場合があれば、wait_completedではなくwait_infoやwait_info_externalの開始時間と終了時間の差分を算出して合計すべきかもしれません。
しかしクエリチューニングをするうえでは細かい数字の積み上げよりも、どの待機イベントで大きく待ちが発生しているか把握する方が大事であるため、気にする必要はないかと。

事前準備

以降で監視するクエリのセッションIDに対し拡張イベントを作成します。
そのため事前にセッションIDを調べておく必要があります。

SELECT @@SPID

もしくはSSMS上のタブからも確認可能です。

拡張イベントを作成する

事前準備で確認した監視したいセッションIDを指定して拡張イベントを作成します。
以下クエリのxxxは、監視したいセッションIDに置換してください。
また、結果を保存するファイルパスも適宜読み替えてください。

/***************************************/
-- (1)調査対象のセッションIDを確認して
-- 以下xxxをセッションIDに置換してから実行
-- 
-- (2)拡張イベントファイルの保存先も
-- 変更してから実行（36行目）
/***************************************/
CREATE EVENT SESSION [exec_query_wait] ON SERVER 
ADD EVENT sqlos.wait_completed(SET collect_wait_resource=(1)
    ACTION(sqlserver.sql_text)
    WHERE ([package0].[equal_uint64]([sqlserver].[session_id],(xxx)) AND [duration]>(0))),
ADD EVENT sqlserver.query_post_compilation_showplan(
    ACTION(sqlserver.sql_text)
    WHERE ([sqlserver].[session_id]=(xxx))),
ADD EVENT sqlserver.query_post_execution_showplan(
    ACTION(sqlserver.sql_text)
    WHERE ([sqlserver].[session_id]=(xxx))),
ADD EVENT sqlserver.sp_statement_completed(
    ACTION(sqlserver.sql_text)
    WHERE ([sqlserver].[session_id]=(xxx))),
ADD EVENT sqlserver.sp_statement_starting(
    ACTION(sqlserver.sql_text)
    WHERE ([sqlserver].[session_id]=(xxx))),
ADD EVENT sqlserver.sql_batch_completed(
    ACTION(sqlserver.sql_text)
    WHERE ([sqlserver].[session_id]=(xxx))),
ADD EVENT sqlserver.sql_batch_starting(
    ACTION(sqlserver.sql_text)
    WHERE ([sqlserver].[session_id]=(xxx))),
ADD EVENT sqlserver.sql_statement_completed(
    ACTION(sqlserver.sql_text)
    WHERE ([sqlserver].[session_id]=(xxx))),
ADD EVENT sqlserver.sql_statement_starting(SET collect_statement=(1)
    ACTION(sqlserver.sql_text)
    WHERE ([sqlserver].[session_id]=(xxx))) 
ADD TARGET package0.event_file(SET filename=N'C:\Program Files\Microsoft SQL Server\MSSQL13.INS_NISHI2016\MSSQL\Log\wait_event.xel')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO

ALTER EVENT SESSION [exec_query_wait] ON SERVER STATE = START
GO

-- ===============================
-- 別セッション(session_ID = xxx)でクエリ実行
-- ===============================

上記で指定したセッションIDにて確認したいクエリの実行が完了し、拡張イベントによるトレースが完了したら拡張イベントによる監視終了と、拡張イベントを削除します。

ALTER EVENT SESSION [exec_query_wait] ON SERVER STATE = STOP
GO
DROP EVENT SESSION [exec_query_wait] ON SERVER
GO	

結果をSQLで確認する場合は、例えば次のようなSQLで集計するとよいでしょう。
以下SQLでは待機イベントの待機時間の合計とクエリの構文解析・コンパイル時間、クエリ実行時のCPU時間と実行時間を出力しています。
sys.fn_xe_file_target_read_fileのファイルパスは、確認したい結果を保存したパスに適宜読み替えてください。

-- SQL Server2016なら
DROP TABLE IF EXISTS tempdb..#wait_analyze 
GO

-- SQL Server2014まで
/*
IF OBJECT_ID(N'tempdb..#wait_analyze', N'U') IS NOT NULL
	DROP TABLE #wait_analyze
GO
*/

CREATE TABLE #wait_analyze (event_data XML)
GO

INSERT INTO #wait_analyze (event_data)
SELECT CAST(event_data AS XML) AS wait_events
FROM sys.fn_xe_file_target_read_file(	  N'C:\Program Files\Microsoft SQL Server\MSSQL13.INS_NISHI2016\MSSQL\Log\wait_event*.xel'
														, NULL
														, NULL
														, NULL
														)
GO

SELECT
	 [waits].[sql_text]
	,[waits].[wait_type]
	,COUNT([waits].[wait_type]) AS wait_count
	,SUM([waits].[duration_ms]) AS total_wait_time_ms
	,SUM([waits].[signal_duration_ms]) AS total_signal_wait_time_ms
	,SUM([waits].[duration_ms]) - SUM([waits].[signal_duration_ms]) AS total_resource_wait_time_ms
FROM (
	SELECT 
		 event_data.value ('(/event/@timestamp)[1]', 'DATETIME') AS [date]
		,event_data.value ('(/event/@name)[1]' , 'VARCHAR(255)') AS [name]
		,event_data.value ('(/event/data[@name = ''wait_type'']/text)[1]', 'VARCHAR(255)') AS [wait_type]
		,event_data.value ('(/event/data[@name = ''duration'']/value)[1]', 'BIGINT') AS [duration_ms]
		,event_data.value ('(/event/data[@name = ''signal_duration'']/value)[1]', 'BIGINT') AS [signal_duration_ms]
		,event_data.value ('(/event/action[@name = ''sql_text'']/value)[1]', 'VARCHAR(MAX)') AS [sql_text]  
	FROM #wait_analyze
	WHERE event_data.value ('(/event/@name)[1]' , 'VARCHAR(255)') = 'wait_completed'
	) AS waits
GROUP BY [waits].[sql_text],[waits].[wait_type]
ORDER BY total_wait_time_ms DESC
GO

SELECT 
	 [waits].[sql_text] AS [sql_text]
	,[waits].[name] AS [event_name]
	,SUM([waits].[cpu_time_μs])/1000 AS [total_cpu_time_ms]
	,SUM([waits].[duration_μs])/1000 AS [total_elapsed_time_ms]
FROM (
	SELECT 
		 event_data.value ('(/event/@timestamp)[1]', 'DATETIME') AS [date]
		,event_data.value ('(/event/@name)[1]' , 'VARCHAR(255)') AS [name]
		,event_data.value ('(/event/data[@name = ''cpu_time'']/value)[1]', 'BIGINT') AS [cpu_time_μs]
		,event_data.value ('(/event/data[@name = ''duration'']/value)[1]', 'BIGINT') AS [duration_μs]
		,event_data.value ('(/event/action[@name = ''sql_text'']/value)[1]', 'VARCHAR(MAX)') AS [sql_text]  
		,event_data.value ('(/event/data[@name = ''statement'']/value)[1]', 'VARCHAR(MAX)') AS [statement]
	FROM #wait_analyze
	WHERE 
		event_data.value ('(/event/@name)[1]' , 'VARCHAR(255)') IN (
									'query_post_compilation_showplan'
									,'sql_statement_completed'
									,'sp_statement_completed'
									,'sql_batch_completed'
									,'query_post_execution_showplan'
									)
	) AS waits
GROUP BY [waits].[sql_text], [waits].[name]
GO


SELECT 
	 event_data.value ('(/event/@timestamp)[1]', 'DATETIME') AS [date]
	,event_data.value ('(/event/@name)[1]' , 'VARCHAR(255)') AS [name]
	,event_data.value ('(/event/data[@name = ''cpu_time'']/value)[1]', 'BIGINT') AS [cpu_time_μs]
	,event_data.value ('(/event/data[@name = ''duration'']/value)[1]', 'BIGINT') AS [duration_μs]
	,event_data.value ('(/event/action[@name = ''sql_text'']/value)[1]', 'VARCHAR(MAX)') AS [sql_text]  
	,[temp_xml].[xml_plan].query('.') AS [query_plan]
FROM #wait_analyze AS [wait_analyze]
OUTER APPLY [wait_analyze].event_data.nodes(N'/event/data[@name="showplan_xml"]/value/*') AS  temp_xml(xml_plan)
WHERE 
	event_data.value ('(/event/@name)[1]' , 'VARCHAR(255)') IN (
									 'query_post_compilation_showplan'
									,'query_post_execution_showplan'
								    )
GO

GUIにて確認する際には、出力したファイルをダブルクリックすればSSMSで開くことができます。
拡張イベントは表示列を追加することで確認したい列で集計やソートが可能であるため、待機イベントの待機時間の合計や、待機イベントのうち上位のイベントを確認することもできます。

ただし、待機時間の単位については以下の通り異なりますので要注意です。

単位

イベント	単位	説明
sql_statement_completedイベントのduration列	ステートメントの実行時間	マイクロ秒
query_post_compilation_showplanイベントのduration列	構文解析とコンパイルの実行時間	マイクロ秒
wait_info,wait_completedイベントのduration列	待機事象の経過時間	ミリ秒

拡張イベントwait_infoを使う

SQLServer2012で同じことをする場合は、wait_completedではなくwait_infoを使うことになるかと思います。
基本的な流れは上記同様です。

/***************************************/
-- wait_completed用の拡張イベント
-- (1)調査対象のセッションIDを確認して
-- 以下xxxをセッションIDに置換してから実行
-- 
-- (2)拡張イベントファイルの保存先も
-- 変更してから実行（37行目）
/***************************************/
CREATE EVENT SESSION [exec_query_wait] ON SERVER 
ADD EVENT sqlos.wait_info(
    ACTION(sqlserver.sql_text)
    WHERE ([package0].[equal_uint64]([sqlserver].[session_id],(xxx)) AND [duration]>(0))),
ADD EVENT sqlserver.query_post_compilation_showplan(
    ACTION(sqlserver.sql_text)
    WHERE ([sqlserver].[session_id]=(xxx))),
ADD EVENT sqlserver.query_post_execution_showplan(
    ACTION(sqlserver.sql_text)
    WHERE ([sqlserver].[session_id]=(xxx))),
ADD EVENT sqlserver.sp_statement_completed(
    ACTION(sqlserver.sql_text)
    WHERE ([sqlserver].[session_id]=(xxx))),
ADD EVENT sqlserver.sp_statement_starting(
    ACTION(sqlserver.sql_text)
    WHERE ([sqlserver].[session_id]=(xxx))),
ADD EVENT sqlserver.sql_batch_completed(
    ACTION(sqlserver.sql_text)
    WHERE ([sqlserver].[session_id]=(xxx))),
ADD EVENT sqlserver.sql_batch_starting(
    ACTION(sqlserver.sql_text)
    WHERE ([sqlserver].[session_id]=(xxx))),
ADD EVENT sqlserver.sql_statement_completed(
    ACTION(sqlserver.sql_text)
    WHERE ([sqlserver].[session_id]=(xxx))),
ADD EVENT sqlserver.sql_statement_starting(SET collect_statement=(1)
    ACTION(sqlserver.sql_text)
    WHERE ([sqlserver].[session_id]=(xxx))) 
ADD TARGET package0.event_file(SET filename=N'C:\Program Files\Microsoft SQL Server\MSSQL13.INS_NISHI2016\MSSQL\Log\wait_event.xel')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO

ALTER EVENT SESSION [exec_query_wait] ON SERVER STATE = START
GO

-- ===============================
-- 別セッション(session_ID = xxx)でクエリ実行
-- ===============================

別セッションで監視するクエリが完了したら拡張イベントのセッションを停止します。

ALTER EVENT SESSION [exec_query_wait] ON SERVER STATE = STOP
GO
DROP EVENT SESSION [exec_query_wait] ON SERVER
GO	

集計は以下クエリ。

/*******************************************************/
-- 拡張イベントwait_infoの集計用
/*******************************************************/
-- SQL Server2016なら
/*
DROP TABLE IF EXISTS tempdb..#wait_analyze 
GO
*/

-- SQL Server2014まで
IF OBJECT_ID(N'tempdb..#wait_analyze', N'U') IS NOT NULL
	DROP TABLE #wait_analyze
GO


CREATE TABLE #wait_analyze (event_data XML)
GO

INSERT INTO #wait_analyze (event_data)
SELECT CAST(event_data AS XML) AS wait_events
FROM sys.fn_xe_file_target_read_file(	  N'C:\Program Files\Microsoft SQL Server\MSSQL13.INS_NISHI2016\MSSQL\Log\wait_event*.xel'
														, NULL
														, NULL
														, NULL
														)
GO

SELECT 
	 [waits].[sql_text]
	,[waits].[wait_type]
	,COUNT([waits].[wait_type]) AS wait_count
	,SUM([waits].[duration_ms]) AS total_wait_time_ms
	,SUM([waits].[signal_duration_ms]) AS total_signal_wait_time_ms
	,SUM([waits].[duration_ms]) - SUM([waits].[signal_duration_ms]) AS total_resource_wait_time_ms
FROM (
	SELECT 
		 event_data.value ('(/event/@timestamp)[1]', 'DATETIME') AS [date]
		,event_data.value ('(/event/data[@name = ''wait_type'']/text)[1]', 'VARCHAR(255)') AS [wait_type]
		,event_data.value ('(/event/data[@name = ''duration'']/value)[1]', 'BIGINT') AS [duration_ms]
		,event_data.value ('(/event/data[@name = ''signal_duration'']/value)[1]', 'BIGINT') AS [signal_duration_ms]
		,event_data.value ('(/event/action[@name = ''sql_text'']/value)[1]', 'VARCHAR(MAX)') AS [sql_text]  
	FROM #wait_analyze
	WHERE event_data.value ('(/event/@name)[1]' , 'VARCHAR(255)') = 'wait_info'
	) AS waits
GROUP BY [waits].[sql_text],[waits].[wait_type]
ORDER BY total_wait_time_ms DESC
GO

SELECT 
	 [waits].[sql_text] AS [sql_text]
	,[waits].[name] AS [event_name]
	,SUM([waits].[cpu_time_μs])/1000 AS [total_cpu_time_ms]
	,SUM([waits].[duration_μs])/1000 AS [total_elapsed_time_ms]
FROM (
	SELECT 
		 event_data.value ('(/event/@timestamp)[1]', 'DATETIME') AS [date]
		,event_data.value ('(/event/@name)[1]' , 'VARCHAR(255)') AS [name]
		,event_data.value ('(/event/data[@name = ''cpu_time'']/value)[1]', 'BIGINT') AS [cpu_time_μs]
		,event_data.value ('(/event/data[@name = ''duration'']/value)[1]', 'BIGINT') AS [duration_μs]
		,event_data.value ('(/event/action[@name = ''sql_text'']/value)[1]', 'VARCHAR(MAX)') AS [sql_text]  
		,event_data.value ('(/event/data[@name = ''statement'']/value)[1]', 'VARCHAR(MAX)') AS [statement]
	FROM #wait_analyze
	WHERE 
		event_data.value ('(/event/@name)[1]' , 'VARCHAR(255)') IN (
									'query_post_compilation_showplan'
									,'sql_statement_completed'
									,'sp_statement_completed'
									,'sql_batch_completed'
									,'query_post_execution_showplan'
									)
	) AS waits
GROUP BY [waits].[sql_text], [waits].[name]
GO


SELECT 
	 event_data.value ('(/event/@timestamp)[1]', 'DATETIME') AS [date]
	,event_data.value ('(/event/@name)[1]' , 'VARCHAR(255)') AS [name]
	,event_data.value ('(/event/data[@name = ''cpu_time'']/value)[1]', 'BIGINT') AS [cpu_time_μs]
	,event_data.value ('(/event/data[@name = ''duration'']/value)[1]', 'BIGINT') AS [duration_μs]
	,event_data.value ('(/event/action[@name = ''sql_text'']/value)[1]', 'VARCHAR(MAX)') AS [sql_text]  
	,[temp_xml].[xml_plan].query('.') AS [query_plan]
FROM #wait_analyze AS [wait_analyze]
OUTER APPLY [wait_analyze].event_data.nodes(N'/event/data[@name="showplan_xml"]/value/*') AS  temp_xml(xml_plan)
WHERE 
	event_data.value ('(/event/@name)[1]' , 'VARCHAR(255)') IN (
									 'query_post_compilation_showplan'
									,'query_post_execution_showplan'
								    )
GO

以上、クエリの待機イベントの確認方法でした。
拡張イベントをSSMS上で確認するときはGUI操作による列追加やフィルタ等が毎回必要となりめんどくさいので、一度上記のようなSQLを作っておくと便利でしょう。

【2017/06/18追記】
拡張イベントで取得した実行プランをSQLで参照できるよう各クエリに追加しました。
待機イベントやクエリの実行時間を確認したら、チューニングするために実行プランは確認する必要があるので。

【SQL Server】処理の進捗がわかるライブクエリ統計

SQLServer2014以降で使用することができるライブクエリ統計の機能がクエリのパフォーマンスチューニングに役立ちそうだったため使い方を記します。
ライブクエリ統計を使うことで実行に長時間かかるクエリが、どの処理で時間を要しているのかわかるため、改善すべき処理が把握でき便利かと思います。
（多少なりとも負荷がかかる設定のため、商用環境以外での使用となるかと思いますが）

事前準備

使用するためには事前に以下の設定が必要です。

セッションのみの監視をする場合

これから監視するクエリのセッションで事前にSET STATISTICS XML ON;もしくはSET STATISTICS PROFILE ON;を実行

インスタンス全体のクエリを監視する場合

拡張イベントでquery_post_execution_showplanを有効にする

SQLServer 2016 SP1以降の場合、軽量版統計インフラストラクチャを有効にすることでも確認可能となります。

トレースフラグ7412を使用

DBCC TRACEON(7412, -1)

インスタンス全体のクエリを監視する場合

拡張イベントでquery_thread_profileを有効にする

実際に試してみる

ためしに時間のかかる適当なクエリを実行してライブクエリ統計を確認してみます。

WITH CTE AS (
SELECT 
		EMP.EMPLOYEE_ID,
		EMP.FIRST_NAME,
		EMP.LAST_NAME,
		EMP.SALARY,
		JOBS.MIN_SALARY,
		JOBS.MAX_SALARY,
		HIST.EMPLOYEE_ID AS [HIST_EMP_ID]
	FROM	dbo.EMPLOYEES AS [EMP]
	INNER JOIN dbo.JOBS AS [JOBS]
	ON EMP.JOB_ID = JOBS.JOB_ID
	LEFT OUTER JOIN  JOB_HISTORY AS [HIST]
	ON EMP.EMPLOYEE_ID = HIST.EMPLOYEE_ID
)
SELECT 
	*
FROM	 CTE AS [CTE_A]
	,CTE AS [CTE_B]
;

利用状況モニターを使用してインスタンス全体のクエリから対象のクエリを確認する場合

利用状況モニターを開いて、「アクティブなコストの高いクエリ」を押下して、確認するクエリを選択し右クリック、「ライブ実行プランの表示」を選択します。

セッションから対象のクエリのみを確認する場合

SSMSの上部メニューから「ライブクエリ統計を含む」アイコンをクリックします。

確認結果は以下のようになります。

処理中である箇所や、各処理に要した時間や処理行数がリアルタイムで確認することが可能になりますので、処理の完了を待たずにボトルネックが把握できるため長時間が予想されるクエリのチューニング時に活用してみては。

参考情報:
ライブ クエリ統計

【SQL Server】OPTIMIZE FOR UNKNOWNを使用した場合の統計について

ストアドプロシージャのパラメータスニッフィングによるパフォーマンス低下対策としてRECOPILEやOPTIMIZE FOR UNKNOWNを使うことが多いかと思いますが、OPTIMIZE FOR UNKNOWNで使われる統計がどのように算出されているのか検証してみました。

パラメータスニッフィングについては以下をご参照ください。
http://blog.hatena.ne.jp/utiowa/memorandom-nishi.hatenablog.jp/entry/2016/06/15/013111

MSDNにはOPTIMIZE FOR UNKNOWNについて次のような説明がされています。

OPTIMIZE FOR UNKNOWN
クエリ オプティマイザーでクエリをコンパイルおよび最適化するときに、強制パラメーター化によって作成されたパラメーターも含め、すべてのローカル変数に対して初期値の代わりに統計データを使用することを指定します。
場合 OPTIMIZE FOR @variable_name = literal_constant OPTIMIZE FOR UNKNOWN、同じクエリ ヒントで使用されると、クエリ オプティマイザーを使用して、 literal_constant特定の値および"不明"の残りの変数の値に指定されています。 これらの値はクエリを最適化する過程でのみ使用され、クエリの実行時には使用されません。

クエリ ヒント (Transact-SQL)

まずは検証用にテーブルとデータ、インデックスを用意します。

USE Param_testDB
GO

/***********************************/
-- テストテーブル作成
/***********************************/

DROP TABLE IF EXISTS [dbo].[STATS_ROWTEST]
GO

CREATE TABLE [dbo].[STATS_ROWTEST](
	[col1] [int] IDENTITY(1,1) NOT NULL,
	[col2] [int] NULL,
	[col3] [int] NULL
PRIMARY KEY CLUSTERED 
(
	[col1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

/***********************************/
-- テストデータ作成
/***********************************/

SET NOCOUNT ON
DECLARE @i int = 1
WHILE @i <= 10000000
	BEGIN
		INSERT INTO [dbo].[STATS_ROWTEST]
		VALUES ((CONVERT(INT, RAND()*10)),(CONVERT(INT, RAND()*100)))
			SET @i += 1
	END
SET NOCOUNT OFF
GO

-- =========================
-- col3を偏りを発生させるため、一部の値で更新
-- =========================
UPDATE [dbo].[STATS_ROWTEST]
SET col3 = 50
WHERE col3 
	BETWEEN	0
	　　AND 20


/***********************************/
-- index作成
/***********************************/

CREATE NONCLUSTERED INDEX [NonClusteredIndex_col3] ON [dbo].[STATS_ROWTEST]
(
	[col3] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

続いてインデックスの統計情報を更新し、統計情報を確認します。

/***********************************/
-- 統計情報を更新
/***********************************/

UPDATE STATISTICS [dbo].[STATS_ROWTEST] [NonClusteredIndex_col3]
GO

/***********************************/
-- 統計情報が更新されていることを確認
/***********************************/

DBCC SHOW_STATISTICS ([STATS_ROWTEST], [NonClusteredIndex_col3]) WITH NO_INFOMSGS
GO

通常のストアドプロシージャとOPTIMIZE FOR UNKNOWNを使用したストアドプロシージャを作成します。

/***********************************/
-- UNKNOWNなし
/***********************************/
CREATE PROCEDURE usp_select_test
	@Param1 int
AS
BEGIN
	SET NOCOUNT ON;
	SELECT col3 FROM [dbo].[STATS_ROWTEST]
	WHERE col3 = @Param1
END
GO


/***********************************/
-- UNKNOWNあり
/***********************************/

CREATE PROCEDURE usp_select_test_unknown
	@Param1 int
AS
BEGIN
	SET NOCOUNT ON;
	SELECT col3 FROM [dbo].[STATS_ROWTEST]
	WHERE col3 = @Param1
	OPTION (OPTIMIZE FOR UNKNOWN)
END
GO

OPTIMIZE FOR UNKNOWNを使用していない場合

まずはUNKNOWNが無い場合のクエリを実行した場合の予測行数は次のとおりとなります。

DBCC FREEPROCCACHE
GO

DECLARE	@Param1 int
SET	@Param1 = 50
EXECUTE [dbo].[usp_select_test] 
   @Param1
GO

DECLARE	@Param1 int
SET	@Param1 = 60
EXECUTE [dbo].[usp_select_test] 
   @Param1
GO

この場合、パラメータスニッフィングが有効となっており実行プラン作成時のパラメータで最適化されているため、初回実行時のcol3=50の予測行数でプランが作成されていることが分かります。

実行プランを削除してからパラメータを変更して実行プランを確認するとプラン作成時のパラメータに応じて予測行数が変わっていることがわかります。

DBCC FREEPROCCACHE
GO

DECLARE	@Param1 int
SET	@Param1 = 60
EXECUTE [dbo].[usp_select_test] 
   @Param1
GO

DECLARE	@Param1 int
SET	@Param1 = 50
EXECUTE [dbo].[usp_select_test] 
   @Param1
GO

OPTIMIZE FOR UNKNOWNを使用した場合

UNKNOWNが有る場合のクエリを実行した場合の予測行数は次のとおりとなります。

DBCC FREEPROCCACHE
GO

DECLARE	@Param1 int
SET	@Param1 = 50
EXECUTE [dbo].[usp_select_test_unknown] 
   @Param1
GO

DECLARE	@Param1 int
SET	@Param1 = 60
EXECUTE [dbo].[usp_select_test_unknown] 
   @Param1
GO

先ほどのパラメータを使用しているにも関わらず、予測行数が異なっています。先ほど同様に実行プランを削除してパラメータを変更して実行してみます。

DBCC FREEPROCCACHE
GO

DECLARE	@Param1 int
SET	@Param1 = 60
EXECUTE [dbo].[usp_select_test_unknown] 
   @Param1
GO

DECLARE	@Param1 int
SET	@Param1 = 50
EXECUTE [dbo].[usp_select_test_unknown] 
   @Param1
GO

UNKNOWNが無い場合とは異なり、初回実行時のパラメータに左右されることなく一定の予測行数となっていることがわかるかと思います。

ヒストグラムを確認

通常時の予測行数の算出は以前の記事に記載がありますので、詳しくはそちら(【SQL Server】統計情報のヒストグラムと実行プランの予測行数 - 小物SEのメモ帳)を見ていただければわかるかと思いますが、UNKNOWN時の場合は以下のように全てのユニークな値の分布（All density）から全体行（Rows）に対し何％含まれているか計算して予測行数としているようです。

予測行数 = All density(1 / distinct values) * Rows

/***********************************/
-- 統計情報を確認
/***********************************/

DBCC SHOW_STATISTICS ([STATS_ROWTEST], [NonClusteredIndex_col3]) WITH NO_INFOMSGS
GO

先ほど確認した実行プランの予測行数と一致することがわかります。

よってOPTIMIZE FOR UNKNOWNを使用する場合、統計情報から各データのユニークな値の分布と全体行数を考慮して平均的な行数を予測した実行プランを作るといえるかと思います。
ただし、値毎の出現頻度までは考慮していないように見えます。今回でいうとcol3=50は件数が多いはずですが、col3のユニークな値と全体行数からしか予測行数を算出していないため。念のためヒストグラムを可視化しておくと以下の通りとなります。

特定のパラメータの実行頻度が高い場合は、そのパラメータで最適になるようヒント句でパラメータを指定することもありかと思いますが、平均的な実行プランで処理させたい場合はOPTIMIZE FOR UNKNOWNを使う検討をするのもありかと思います。
#その分パラメータによっては最速なプランは作成されないためトレードオフではありますが。

【SQL Server】テーブルのデータをINSERT文付きでエクスポート

テーブル内のデータをINSERT文付きでエクスポートする手順です。
データ移行用のデータ抽出などで利用できるかと。

SSMSの左ペインのオブジェクトエクスプローラのデータベース名で右クリック、「タスク」を選択し、「スクリプト生成」を選びます。

今回は特定のテーブルのデータのみ抽出したいため、「特定のデータベース オブジェクトの選択」を選び、エクスポートしたいテーブル名を選択。

「詳細設定」を押下します。

オプションの「全般」にある「スクリプトを作成するデータの種類」で「データのみ」を選択。

「次へ」を選択し、次画面で「完了」を押下します。

指定したフォルダにINSERT文付きのテーブルデータがエクスポートされます。

「スクリプトを作成するデータの種類」の際に「スキーマとデータ」を選択すれば、テーブル作成や制約付きでエクスポートすることができますし、「統計のスクリプトを作成」で「統計とヒストグラムのスクリプトを作成します」を選択すれば現時点の統計情報も出力できるので別環境での検証の際にも使えそうですね。
（商用環境から抽出する場合などのデータの取り扱いには十分注意が必要ですが。）

【SQL Server】よく忘れる構文備忘録

たまに使用する際に毎回調べて書いていたので、備忘録＆サンプルとして。

ストアドの実行結果をINSERT

ストアドの結果セットが一つであれば、INSERT INTO Table_name SELECT…と同じ要領で記述できます。

INSERT INTO Table_name 
	EXEC sp_lock

他のテーブルのデータでテーブルを更新する

UPDATE FROMを使用して他のテーブルの値で更新する構文です。

-- 事前準備
CREATE TABLE Table_x (
	 Column1 VARCHAR(50)
	,Column2 VARCHAR(50)
	,Column3 VARCHAR(50)
	)

CREATE TABLE Table_y (
	 Column1 VARCHAR(50)
	,Column2 VARCHAR(50)
	,Column3 VARCHAR(50)
	)

INSERT INTO [dbo].[Table_x]
VALUES 
	 ('001'	,'a001'	,'x000001')
	,('002'	,'a002'	,'x000002')
	,('003'	,'a003'	,'x000003');

INSERT INTO [dbo].[Table_y]
VALUES 
	 ('001'	,'a001'	,'y000001')
	,('002'	,'a002'	,'y000002')
	,('003'	,'a003'	,'y000003');

準備したデータは以下のとおり。

[Table_x]

Column1	Column2	Column3
001	a001	x000001
002	a002	x000002
003	a003	x000003

[Table_y]

Column1	Column2	Column3
001	a001	y000001
002	a002	y000002
003	a003	y000003

Table_xのColumn1が001であるものについて、Table_xのColumn3をTable_yのColumn3の値で更新してみます。

UPDATE Table_x
SET [Table_x].[Column3] = [Table_y].[Column3]
FROM  [Table_y]
WHERE [Table_x].[Column2] = [Table_x].[Column2]
  AND [Table_x].[Column1] = '001'

INNER JOINを組み合わせることもできます。

UPDATE Table_x
SET [Table_x].[Column3] = [Table_y].[Column3]
FROM       [Table_x] 
INNER JOIN [Table_y]
	ON [Table_x].[Column2] = [Table_x].[Column2]
WHERE [Table_x].[Column1] = '001'

UPDATE対象のデータの処理前、処理後を出力する

SET句の後ろにOUTPUT句で記述します。

UPDATE [dbo].[Table_x]
SET Column2 = 'b001'
OUTPUT   deleted.*
	,inserted.*
WHERE Column3 IN ('x000001','x000003');

一時テーブルが存在するならば、削除する

一時テーブルにデータを作成⇒データ格納をしながら繰り返し試行錯誤する際に便利です。

IF OBJECT_ID(N'tempdb..#temp_TBL', N'U') IS NOT NULL
    DROP TABLE #temp_TBL;

一時テーブルが存在するならば、削除する【SQL Server2016】

簡潔に書くことができます。

DROP TABLE IF EXISTS tempdb..#temp_TBL;

テーブルが存在するならば、削除する

IF EXISTS (
	SELECT *
	FROM sys.objects
	WHERE object_id = OBJECT_ID(N'Table_name')
	)
　　DROP TABLE Table_name;

テーブルが存在するならば、削除する【SQL Server2016】

とてもシンプル。見やすく書きやすい。

DROP TABLE IF EXISTS Table_name;

カラムが存在するならば、削除する

IF EXISTS (
	SELECT *
	FROM sys.columns
	WHERE NAME = 'Column_name'
	　AND object_id = OBJECT_ID('[dbo].[Table_name]')
	)
	ALTER TABLE [dbo].[Table_name] DROP COLUMN [Column_name] 
GO

カラムが存在するならば、削除する【SQL Server2016】

こちらもシンプルに記述できて便利です。

ALTER TABLE [dbo].[Table_name] DROP COLUMN IF EXISTS [Column_name] 
GO

DROP xxx IF EXISTSは、PROCEDUREやFUNCTION等にも使えるため、様々なDDL文に使用できます。

カラムが存在しないならば、追加する

単に削除の反対ですが。

IF NOT EXISTS (
	SELECT *
	FROM sys.columns
	WHERE NAME = 'Column_name'
	　　　AND object_id = OBJECT_ID('[dbo].[Table_name]')
	)
　　ALTER TABLE [dbo].[Table_name] ADD [Column_name] VARCHAR(50)
GO

新規ならINSERT、存在するならUPDATE処理をする

データ更新時に行が存在すればUPDATE、新規であればINSERTをする場合はMRGER構文を使います。（ただし、SQL Server2008以降）
さらに処理した行の結果をOUTPUT句にて出力します。

-- 事前準備
DROP TABLE IF EXISTS Table_x;
DROP TABLE IF EXISTS Table_y;

CREATE TABLE Table_x (
	 Column1 VARCHAR(50)
	,Column2 VARCHAR(50)
	,Column3 VARCHAR(50)
	)

CREATE TABLE Table_y (
	 Column1 VARCHAR(50)
	,Column2 VARCHAR(50)
	,Column3 VARCHAR(50)
	)

INSERT INTO [dbo].[Table_x]
VALUES 
	 ('001'	,'a001'	,'x000001')
	,('002'	,'a002'	,'x000002')
	,('003'	,'a003'	,'x000003');

INSERT INTO [dbo].[Table_y]
VALUES 
	 ('001'	,'a001'	,'y000001')
	,('003'	,'a003'	,'y000003')
	,('004'	,'a001'	,'y000004')
	,('005'	,'a002'	,'y000002')
	,('006'	,'a003'	,'y000006');

Table_xのColumn1とTable_yのColumn1が同じものについて、Table_xに存在すればColumn3のみTable_yの値で更新、存在しなければ行を追加してみます。

MERGE Table_x AS [Target]
USING (SELECT Column1,Column2,Column3 FROM Table_y) AS [Source]
  ON [Target].[Column1] = [Source].[Column1]
  WHEN MATCHED THEN 
      UPDATE SET [Target].[Column3] = [Source].[Column3]
  WHEN NOT MATCHED BY Target THEN
      INSERT (
         [Column1]
        ,[Column2]
        ,[Column3]
        ) 
      VALUES (
           [Source].[Column1]
          ,[Source].[Column2]
          ,[Source].[Column3]
          )
  OUTPUT deleted.*, inserted.*, $action;
  

Table_xのColumn1とTable_yのColumn1が同じものについて、Table_xに存在すればColumn3のみTable_yの値で更新、存在しなければ行を追加し、条件にあてはまらないTable_yの行を削除する場合。

MERGE Table_x AS [Target]
USING (SELECT Column1,Column2,Column3 FROM Table_y) AS [Source]
  ON [Target].[Column1] = [Source].[Column1]
  WHEN MATCHED THEN 
      UPDATE SET [Target].[Column3] = [Source].[Column3]
  WHEN NOT MATCHED BY Target THEN
      INSERT (
         [Column1]
        ,[Column2]
        ,[Column3]
        ) 
      VALUES (
           [Source].[Column1]
          ,[Source].[Column2]
          ,[Source].[Column3]
          )
  WHEN NOT MATCHED BY Source THEN
      DELETE 
  OUTPUT deleted.*, inserted.*, $action;
  

[参考情報]
MERGE (Transact-SQL)
OUTPUT 句 (Transact-SQL)

【SQL Server】並列処理時のパフォーマンスについて

前回は、並列処理の設定確認や変更方法を記しましたが、今回は並列実行されていることの確認や、その効果検証についてです。
memorandom-nishi.hatenablog.jp

並列処理時の確認

動的管理ビューのdm_exec_query_statsにSQL Server2016以降から並列度の次数(dop)やスレッド数が追加されています。

total_dop	並列処理の次数の合計
last_dop	最後に実行したときの並列処理の次数
min_dop	累積のうち最小の並列処理の次数
max_dop	累積のうち最大の並列処理の次数
total_used_threads	並列処理時のスレッド数の合計
last_used_threads	最後に実行したときの並列処理時のスレッド数
min_used_threads	累積のうち並列処理時の最小のスレッド数
max_used_threads	累積のうち並列処理時の最大のスレッド数

#dopとthreadの違いはMSのblogに記載があります。
https://blogs.msdn.microsoft.com/jpsql/2013/10/15/dop/

直近の実行での並列度や平均並列度は上記動的管理ビューを見ることでわかります。
例えば以下のようなSQLで確認することができます。

#以前作ったCPU、IO負荷の高いクエリの使いまわしですが。

SELECT TOP 100
        rank() over(order by total_dop/execution_count desc,sql_handle,statement_start_offset) AS [row_no]
,       db_name(st.dbid) AS [database_name]
,       creation_time
,       last_execution_time
,       (total_worker_time+0.0)/1000 AS [total_worker_time(ms)]
,       (total_worker_time+0.0)/(execution_count*1000) AS [AvgCPUTime(ms)]
,       (total_elapsed_time+0.0)/1000 AS [total_elapsed_time(ms)]
,       (total_elapsed_time+0.0)/(execution_count*1000) AS [AvgElapsedTime(ms)]
,       total_logical_reads AS [LogicalReads(page)]
,       total_logical_writes AS [logicalWrites(page)]
,       total_logical_reads+total_logical_writes AS [AggIO(page)]
,       (total_logical_reads+total_logical_writes)/(execution_count + 0.0) AS [AvgIO(page)]
,       execution_count
,       total_rows
,       max_dop
,       total_dop
,       total_dop/execution_count AS [AvgDOP]
,       st.text AS [batch_query_text]
,   CASE 
        WHEN sql_handle IS NULL
        THEN ' '
        ELSE ( SUBSTRING(st.text,(qs.statement_start_offset+2)/2,(CASE 
                                                                      WHEN qs.statement_end_offset = -1        
                                                                      THEN LEN(CONVERT(NVARCHAR(MAX),st.text))*2      
                                                                      ELSE qs.statement_end_offset    
                                                                  END 
        - qs.statement_start_offset) /2  ) )
    END AS [statement_query_text] 
,       plan_generation_num
,       qp.query_plan
FROM sys.dm_exec_query_stats   AS [qs]
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS [st]
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS [qp]
WHERE total_worker_time > 0 
ORDER BY total_dop/execution_count  DESC,sql_handle,statement_start_offset
OPTION (RECOMPILE)

グラフィカル表示の実行プランを確認する場合、次のアイコンがあれば並列実行される可能性があります。

アイコン	並列処理の物理操作	説明
	Distribute Streams	1つのインプットに対しパラレル処理
	Repartition Streams	複数のパラレル処理結果に対しパラレル処理
	Gather Streams	複数パラレル処理を1つに統合

実際の実行プランを取得するとアイコンにカーソルを合わせると実行回数として並列度が確認できます。

それぞれのスレッドでの処理行数やCPU時間や実行時間も見ることができます。

今回は並列処理のGather Streamsなので、イメージ的には次のような動きかと。

ただし、「実際の実行プラン」でなければこの情報は見ることができないため、拡張イベント等の仕込みが無い場合は後から見ることができません。その場合は上記で説明した動的管理ビューからの平均・最大並列数で確認するのが現実的でしょうか。

並列処理した場合のパフォーマンスコスト

並列処理した場合のクエリを比較して負荷の違いを見てみます。
試しにMaxdopを1～4にした場合の各5回の実行からの平均統計で比較すると次のような結果となります。

お試しクエリ

-- キャッシュをクリア
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE


SET STATISTICS IO ON
SET STATISTICS TIME ON


SELECT TOP 10000
	A.[col1]
    ,	A.[col2]
    ,	A.[col3]
    ,	B.*
  FROM			[Param_testDB].[dbo].[Parallel_test] AS A
  INNER JOIN		[Param_testDB].[dbo].[Parallel_test] AS B
  ON A.col2 = B.col2
  ORDER BY NEWID(),4
  OPTION (MAXDOP 1)
--  OPTION (MAXDOP 2)
--  OPTION (MAXDOP 3)
--  OPTION (MAXDOP 4)

GO

SET STATISTICS TIME OFF
SET STATISTICS IO OFF

非並列時の読み取りページと実行プラン

並列時の読み取りページ実行プラン

上記結果から、並列度が高いとCPU負荷が高くなるかわりに実行時間が少し早くなることがわかるかと思います。
特にCPUTimeが実行時間（ElapsedTime）を超えている場合は、並列で処理している結果であると言えそうです。

また、わずかですが並列無しに比べ、読み取りページもわずかに増えておりメモリにも多少なりとも影響を与えていると言えるでしょう。

【SQL Server】並列処理MaxDOPの設定確認と変更

SQL Serverでの並列クエリ処理

複数のCPUでクエリを処理して並列処理される場合、実行時間を短縮することができます。
並列クエリの実行では、取得するデータセットを小さく分割して、分割されたデータセットをそれぞれのワーカー・スレッドが並列処理することでクエリ実行の実行時間を短縮することができます。

並列クエリとなる基準は以下の通りです。
1.－数のCPUが搭載されている環境か
2.十分な数のスレッドが使用できるか
3.並列プランを使用するに適しているクエリ、もしくはインデックスか
4.処理行が十分に多いか
5.分布統計が使用できるか

MSに詳しい説明があります。
並列処理の次数

設定値の確認と変更

インスタンス全体にかかる設定としてsp_configure max degree of parallelismオプションを使用して設定するか、特定のクエリの並列を最大並列度（MAXDOP）で指定することができます。
尚、デフォルトは0で、この場合論理プロセッサに対し最大限に使用することがある状態となります。

インスタンス全体にかかる設定の確認をクエリにて行う場合は次のように確認できます。

-- 拡張オプションを表示できるよう設定を変更
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO

-- 拡張オプションを表示
EXEC sp_configure 
GO

または、プロパティからも確認することができます。
左ペインのオブジェクトエクスプローラからサーバを右クリックし、「プロパティ」を選択。

「ページの選択」で「詳細設定」を選択して、「並列処理」項目の「並列処理の最大限度」を確認。

設定を変更する場合、次のように変更できます。

-- 並列度を1にする場合
EXEC sp_configure 'max degree of parallelism', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO

特定のクエリに対するMAXDOPを変える場合は、オプション句でMAXDOPを以下のように指定して実行します。
この場合、上記のsp_configureの設定値を超えた並列度を設定することができます。

SELECT TOP 50000 
　　　[col1]
    , [col2]
    , [col3]
  FROM [Param_testDB].[dbo].[Parallel_test]
  ORDER BY NEWID()
  OPTION (MAXDOP 2)

並列実行時は実行時間が早くなる分、CPUやメモリへの影響もありますので注意が必要ですが、MSとしてのMaxDOPは推奨値が8以下となっているようです。以下は参考になるかと思いますので、目を通しておくとよいでしょう。
[SQL Server2005以降の推奨値]
https://support.microsoft.com/ja-jp/help/2806535/recommendations-and-guidelines-for-the-max-degree-of-parallelism-configuration-option-in-sql-server

[その他参考]
64 個を超える CPU を搭載したコンピューター上で SQL Server を実行する場合のベスト プラクティス
最適なパフォーマンスを実現するための max degree of parallelism オプションの設定