SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

SQL Database で軽量なクエリプロファイリングを使用し、「実行中のクエリの情報」を取得する

leave a comment

SQL Database の DB エンジンのバージョンですが、本投稿を書いている時点では、SQL Server 2019 CTP 2.0 相当のバージョンとなっています。

SQL Server 2019 では、SQL Server 2016 SP1 で導入が行われた、軽量なクエリプロファイリングがデフォルトで有効になっており、DB レベルの設定として、有効 / 無効の変更ができるようになります。

Lightweight query profiling infrastructure

SQL Database の DB エンジンが 2019 相当になったので、使用できるかどうか試してみました。
結論を書くと、使用できるようになっています。

現状の SQL Database では、軽量なクエリプロファイリングのデフォルト設定は「無効」となっています。

SELECT * FROM sys.database_scoped_configurations

image

「LIGHTWEIGHT_QUERY_PROFILING」が軽量なクエリプロファイリングの設定なのですが「0」となっており、現時点ではこれが既定の値です。

この設定については次のクエリで変更することができます。

ALTER DATABASE SCOPED CONFIGURATION SET LIGHTWEIGHT_QUERY_PROFILING=ON	-- 有効
ALTER DATABASE SCOPED CONFIGURATION SET LIGHTWEIGHT_QUERY_PROFILING=OFF	-- 無効

 

軽量なクエリプロファイリングを有効にすることで、どのようなことが可能になるかというと「実行中のクエリの情報を詳細に取得する」ことが可能となります。

この機能が実装されるまでの SQL Server では、クエリの情報は

  • sys.dm_exec_requests から実行中の情報を取得
  • キャッシュされたクエリの情報から取得

というようなアプローチで取得を行っていました。

「sys.dm_exec_requests」から実行中のクエリの情報を取得することはできたのですが、「実行に使用されている実行プラン」「実行プランのどの箇所が今処理されているか」という情報を判断することができませんでした。

実行プランについては「実行が完了してからキャッシュから取得する」というアプローチをする必要があり、「実行がなかなか完了しないクエリが今、どういう状態になっているのか?」を判断するのが難しいとうのが以前のバージョンの問題点としてありました。

(クエリストアについても、基本は完了したクエリを対象として調べることになるかと)

これをサーバーの CPU 負荷を抑えて解決するのが SQL Server 2016 SP1 で実装された軽量プロファイリングとなります。

(軽量プロファイリングによるオーバーヘッドは 1.5 ~ 2% 程度と言われています)

軽量プロファイリングを使用することで「実行中のクエリ」に対して、次のようなアプローチをすることが可能となります。

1.「実行中のクエリ」の実行プランを取得

今まで、処理時間が短かったものが、急に時間がかかるようになった時、今までは、「実行が完了した後」に、クエリのキャッシュから実行プランを取得し、原因を調査売るというアプローチをしていました。

これが、軽量なクエリプロファイリングを使用すると「実行中のクエリで使用されている実行プラン」を取得することができるようになります。

これには、sys.dm_exec_query_statistics_xml という DMV を使用します。

「LIGHTWEIGHT_QUERY_PROFILING=ON」の状態であれば、次のようなクエリでこの DMV から情報を取得することができます。

SELECT 
	T.session_id,
	er.command,
	er.status,
	er.start_time,
    SUBSTRING(st.text, (qsx.statement_start_offset/2)+1,   
        ((CASE qsx.statement_end_offset  
          WHEN -1 THEN DATALENGTH(st.text)  
         ELSE qsx.statement_end_offset  
         END - qsx.statement_start_offset)/2) + 1) AS statement_text,
	st.text,
	er.last_wait_type,
	er.wait_resource,
	qsx.query_plan AS qsx_query_plan,
	qp.query_plan AS qp_query_plan
FROM
(
	SELECT DISTINCT 
		session_id
	FROM 
		sys.dm_exec_query_profiles
	WHERE 
		session_id <> @@SPID
) AS T
	LEFT JOIN sys.dm_exec_requests AS er ON er.session_id = T.session_id
	OUTER APPLY sys.dm_exec_query_statistics_xml(T.session_id) AS qsx
	OUTER APPLY sys.dm_exec_sql_text(qsx.sql_handle) AS st
	OUTER APPLY sys.dm_exec_query_plan(qsx.plan_handle) AS qp
GO

 

この情報から「実行中のクエリがどのような実行プランで実行されているか」の調査をすることができるようになりますので、中々終わらないクエリが存在している場合に、クエリの完了を待つことなく、調査に取り掛かることができます。

 

2.「実行中のクエリ」の現在の処理状況を取得

実行中のクエリの調査で確認したいのが「今、実行プランのどの部分を処理しているのか?」の情報となります。

これを取得するためのアプローチとして「ライブクエリ統計」がありますが、軽量なクエリプロファイリングでは、この情報に近いものが取得できるようになります。

これには、sys.dm_exec_query_profiles という DMV を使用します。

この DMV についても、「LIGHTWEIGHT_QUERY_PROFILING=ON」の状態であれば、次のようなクエリで情報を取得することができます。

ELECT 
	qp.session_id,
	qp.request_id,
	er.command,
	er.status,
	er.start_time,
	qp.physical_operator_name,
	DB_NAME(qp.database_id) AS database_name,
	OBJECT_NAME(qp.object_id) AS object_name,
	ix.name AS index_name,
	OBJECT_NAME(page_info.object_id) AS object_name,
	ix.name,
	page_info.partition_id,
	page_info.alloc_unit_id,
	page_info.slot_count,
	page_info.free_bytes,
	qp.node_id,
	qp.thread_id,
	qp.estimate_row_count,
	qp.row_count,
	qp.rewind_count,
	qp.rebind_count,
	qp.end_of_scan_count,
	qp.first_active_time,
	qp.last_active_time,
	qp.open_time,
	qp.first_row_time,
	qp.last_row_time,
	qp.close_time,
	qp.elapsed_time_ms,
	qp.cpu_time_ms,
	qp.scan_count,
	qp.logical_read_count,
	qp.physical_read_count,
	qp.read_ahead_count,
	qp.actual_read_row_count,
	qp.estimated_read_row_count,
	qp.write_page_count,
	qp.lob_logical_read_count,
	qp.lob_physical_read_count,
	qp.lob_read_ahead_count,
	qp.segment_read_count,
	qp.segment_skip_count,
	page_info.database_id,
	page_info.page_id,
	page_info.page_type,
	page_info.page_type_desc,
	page_info.page_level
FROM 
	sys.dm_exec_query_profiles AS qp
	LEFT JOIN sys.indexes AS ix ON ix.object_id = qp.object_id AND ix.index_id = qp.index_id
	LEFT JOIN sys.dm_exec_requests AS er ON er.session_id = qp.session_id
	OUTER APPLY sys.fn_PageResCracker (er.page_resource) AS r  
	OUTER APPLY sys.dm_db_page_info(COALESCE(r.db_id, 0), r.file_id, r.page_id, DEFAULT) AS page_info
WHERE 
	qp.session_id <> @@SPID
ORDER BY
	qp.session_id ASC,
	qp.node_id ASC
GO

SQL Server 2019 で使用可能になった、「sys.dm_db_page_info」「sys.fn_PageResCracker」と組み合わせて使用していますが、実行プランの各処理で、何行処理されているかを確認することができます。

(SQL Server 2019 で使用可能になった DMV と組み合わせていますので、ロック競合が発生した場合のオブジェクトの情報なども取得でいます)

image

「処理されている行数」が「row_count」として取得できますので、複数回実行してみて、値が増加している箇所があればその箇所が「処理中」であるという判断ができるかと。

データベースエンジンのバージョンが新しくなり、DMV 等が増えると、「今までは確認できなかった、新しい観点での情報取得」が可能となることが多々あります。

新しく取得できるようになった情報が「なぜそのような情報を取得することができるように機能向上したのか?」を考えることで、新しい気付きを得ることができることもありますので、取得できる情報が増えた場合には、「どのように使用するか」を考えるのは、色々な気付きがあり、勉強になりますね。

Written by masayuki.ozawa

11月 14th, 2018 at 10:14 pm

Posted in SQL Database

Tagged with

Leave a Reply

*