以前、SQL Database の拡張イベントをもう少し触ってみる で、SQL Database の拡張イベントについて書いてみましたが、取得しておくと便利そうな情報を追加した拡張イベントを作成してみたいと思います。
Azure ストレージへの接続情報については、以前の投稿と同じとなります。
拡張イベントを作成しているクエリを以下で実行してもらえれば、情報を追加した拡張イベントが作成できます。
IF EXISTS(select 1 from sys.database_event_sessions where name = 'Query_Trace')
BEGIN
DROP EVENT SESSION [Query_Trace] ON DATABASE
END
DECLARE @LogName nvarchar(255) = N'https://<ストレージアカウント名>.blob.core.windows.net/<コンテナー名>/queryinfo.xel'
DECLARE @sql nvarchar(max) = '
CREATE EVENT SESSION [Query_Trace] ON DATABASE
ADD EVENT sqlserver.blocked_process_report,
ADD EVENT sqlserver.execution_warning(SET collect_server_memory_grants=(1)
ACTION(sqlserver.sql_text)),
ADD EVENT sqlserver.hash_warning(
ACTION(sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.sql_text)),
ADD EVENT sqlserver.lock_deadlock_chain(SET collect_database_name=(1),collect_resource_description=(1)
ACTION(sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.sql_text)),
ADD EVENT sqlserver.missing_column_statistics(SET collect_column_list=(1)
ACTION(sqlserver.sql_text)),
ADD EVENT sqlserver.sort_warning(
ACTION(sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.sql_text)),
ADD EVENT sqlserver.sql_batch_completed(SET collect_batch_text=(1)
WHERE ([duration]>=(10000000))),
ADD EVENT sqlserver.xml_deadlock_report(
ACTION(sqlserver.sql_text))
ADD TARGET package0.event_file(SET filename=''' + @LogName + ''',max_file_size=(100))
WITH (STARTUP_STATE=OFF);
'
EXEC (@sql)
ALTER EVENT SESSION [Query_Trace] ON DATABASE STATE=START
以下のような情報を取得しています。
- ロック競合が発生したクエリ (blocked prosess threshold の設定が必要)
- メモリ許可の待機が 1 秒以上発生したクエリ
- ハッシュ結合時のメモリ不足が発生したクエリ
- 統計が設定されていない列に対して実行されたクエリ
- ソート時にメモリ不足が発生したクエリ
- 実行に 10 秒以上かかったクエリ
- デッドロックレポート/デッドロックチェーン
取得するように設定しているのですが、確認できるコンフィグ上は、「blocked prosess threshold 」は 20 秒となっていそうなのですが、情報の取得がうまくいかなかったので、設定をしても効果はないかもしれません…
また、デッドロックについても SQL Database では、うまく拾えないのですよね…。
ちなみに、以前は「sys.event_log」で取得できたのですが、v12 になって master を選択した状態で、「sys.fn_xe_telemetry_blob_target_read_file」に変更になったらしいです。
Checking deadlocks in Azure SQL V12
Deadlocks in SQL Azure V12
オンプレミスの SQL Server だと各情報が取れているので、SQL Database でも取れるといいなと。