SQL Database の拡張イベントをもう少し触ってみようかと。
SQL Database で使用できるイベント/アクション/ターゲットですが、Extended events in SQL Database で公開されている、以下のクエリで確認することができます。
SELECT
o.object_type,
p.name AS [package_name],
o.name AS [db_object_name],
o.description AS [db_obj_description]
FROM
sys.dm_xe_objects AS o
INNER JOIN sys.dm_xe_packages AS p ON p.guid = o.package_guid
WHERE
o.object_type in
(
'action', 'event', 'target'
)
ORDER BY
o.object_type,
p.name,
o.name;
SQL Server 2016 CTP 2.4 で上記のクエリを実行した場合は、「1,212 行」のデータを取得することができますが、SQL Database では、「65 行」となっており、現状はかなり限定されたイベントが取得できる状態となっているようです。
ターゲットとして、「event_file」「ring_buffer」が使えるので、ファイルへの出力と一時的な確認の両方に対応しています。
# 「event_counter」によるイベント発生回数も使えるのですが、こちらは私があまり使ったことがなく。。。
「ring_buffer」(Ring Buffer target code for extended events in SQL Database) については前回の投稿で触れましたので、今回は「event_file」(Event File target code for extended events in SQL Database) を見てみたいと思います。
Contents
ログの保存場所の設定
SQL Database の event_file は Azure ストレージに対して拡張イベントのログを取得することができます。
Azure ストレージに対しては SAS トークンを使用してアクセスを行いますので、SAS トークンの取得を行います。
Add-AzureAccount $Subscription = Get-AzureSubscription | SELECT SubscriptionId, SubscriptionName | Out-GridView -OutputMode Single $Subscription |? Select-AzureSubscription -Current $StorageAccount = Get-AzureStorageAccount | select StorageAccountName | Out-GridView -OutputMode Single $Context = New-AzureStorageContext -StorageAccountName $StorageAccount.StorageAccountName -StorageAccountKey (Get-AzureStorageKey -StorageAccountName $StorageAccount.StorageAccountName).Primary $Container = Get-AzureStorageContainer -Context $context | Out-GridView -OutputMode Single $SASToken = New-AzureStorageContainerSASToken -Name $Container.Name -Permission rwdl -FullUri -Context $context -ExpiryTime (Get-Date).AddYears(50) ($SASToken.ToString() -split "\?")
SAS トークンが取得できたら、取得した情報をもとに SQL Database に資格情報を作成します。
IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
BEGIN
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'M@sterEr0s'
END
GO
DECLARE @StorageAccount nvarchar(255) = 'https://<ストレージアカウント名>.blob.core.windows.net/<コンテナー名>'
DECLARE @SASToken nvarchar(255) = '<sv=で始まる SAS トークン>'
DECLARE @sql nvarchar(max)
IF EXISTS (SELECT * FROM sys.database_scoped_credentials WHERE name = @StorageAccount)
BEGIN
SET @sql =
N'DROP DATABASE SCOPED CREDENTIAL [' + @StorageAccount + ']'
EXEC (@sql)
END
SET @sql =
N'CREATE DATABASE SCOPED CREDENTIAL [' + @StorageAccount + ']
WITH
IDENTITY = ''SHARED ACCESS SIGNATURE'',
SECRET = ''' + @SASToken + '''
';
EXEC (@sql)
拡張イベントの作成
作成した、資格情報を使用して、拡張イベントを設定します。
IF EXISTS(select 1 from sys.database_event_sessions where name = 'SlowQuery')
BEGIN
DROP EVENT SESSION [SlowQuery] ON DATABASE
END
DECLARE @LogName nvarchar(255) = N'https://<ストレージアカウント名>.blob.core.windows.net/<コンテナー名>/slowquery.xel'
DECLARE @sql nvarchar(max) = '
CREATE EVENT SESSION [SlowQuery] ON DATABASE
ADD EVENT sqlserver.sql_batch_completed(
ACTION(sqlserver.sql_text)
WHERE ([duration]>=(3000000)))
ADD TARGET package0.event_file(SET filename=''' + @LogName + ''',max_file_size=(100))
WITH (STARTUP_STATE=OFF);
'
EXEC (@sql)
今回は 3 秒以上時間がかかったクエリをログに出力する設定にしています。
作成した拡張イベントは、自動起動しない設定としていましたので、手動で拡張イベントを起動します。
ALTER EVENT SESSION [SlowQuery] ON DATABASE STATE=START
その後、正常に出力されているかを確認するため、以下のクエリを実行してみます。
SELECT @@SERVERNAME WAITFOR DELAY '00:00:01' GO SELECT @@SERVERNAME WAITFOR DELAY '00:00:02' GO SELECT @@SERVERNAME WAITFOR DELAY '00:00:03' GO SELECT @@SERVERNAME WAITFOR DELAY '00:00:04' GO
3 秒と 4 秒の待ちを入れたクエリが拡張イベントのログに出力されていれば、想定した設定となっていることになります。
それでは、以下のクエリを実行してデータを確認してみます。
SELECT
*,
CAST(event_data AS XML) AS [event_data_XML]
FROM
sys.fn_xe_file_target_read_file('https://<ストレージアカウント名>.blob.core.windows.net/<コンテナー名>/slowquery', null, null, null)
2 件のデータがログに出力されていることが確認できますね。
ログを停止する場合は、以下のクエリで拡張イベントを停止します。
ALTER EVENT SESSION [SlowQuery] ON DATABASE STATE=STOP
ストレージに保存させているファイルは、拡張イベントの xel ファイルですので、ダウンロードすれば、SSMS から開くこともできます。
SQL Server 2016 CTP 2.4 の SSMS であれば、「新規セッション ウィザード」を使用しての拡張イベントの作成にも対応していたので、設定のベースについてはこちらを使用して作るとよいかもしれないですね。
# 「新規セッション」や「プロパティの表示」はまだ実施できないようですので、SQL Database の拡張イベントのすべてに対応しているわけではないようです。
SQL で XML の操作ができる えろす師匠 (a.k.a. えろす財閥総帥快楽亭えろす師匠ひさし胡麻油ビスコのりたま)? が 毎日更新している 日々のメモ でクエリでログ内の SQL 文も出力できる情報を発信してくれるはずなので、あとは任せたいと思います。