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
「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 と組み合わせていますので、ロック競合が発生した場合のオブジェクトの情報なども取得でいます)
「処理されている行数」が「row_count」として取得できますので、複数回実行してみて、値が増加している箇所があればその箇所が「処理中」であるという判断ができるかと。
データベースエンジンのバージョンが新しくなり、DMV 等が増えると、「今までは確認できなかった、新しい観点での情報取得」が可能となることが多々あります。
新しく取得できるようになった情報が「なぜそのような情報を取得することができるように機能向上したのか?」を考えることで、新しい気付きを得ることができることもありますので、取得できる情報が増えた場合には、「どのように使用するか」を考えるのは、色々な気付きがあり、勉強になりますね。