SE の雑記

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

SQL Database で取得していると便利そうな情報を追加した拡張イベントを作成してみる

leave a comment

以前、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 でも取れるといいなと。

Written by masayuki.ozawa

11月 15th, 2015 at 11:21 pm

Posted in SQL Database

Tagged with

Leave a Reply

*