SE の雑記

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

SQL Database の拡張イベントをもう少し触ってみる

leave a comment

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) を見てみたいと思います。

ログの保存場所の設定

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 件のデータがログに出力されていることが確認できますね。

image

ログを停止する場合は、以下のクエリで拡張イベントを停止します。

ALTER EVENT SESSION [SlowQuery] ON DATABASE STATE=STOP

 

ストレージに保存させているファイルは、拡張イベントの xel ファイルですので、ダウンロードすれば、SSMS から開くこともできます。

image

SQL Server 2016 CTP 2.4 の SSMS であれば、「新規セッション ウィザード」を使用しての拡張イベントの作成にも対応していたので、設定のベースについてはこちらを使用して作るとよいかもしれないですね。

# 「新規セッション」や「プロパティの表示」はまだ実施できないようですので、SQL Database の拡張イベントのすべてに対応しているわけではないようです。

image

 

SQL で XML の操作ができる えろす師匠 (a.k.a. えろす財閥総帥快楽亭えろす師匠ひさし胡麻油ビスコのりたま)  が 毎日更新している 日々のメモ でクエリでログ内の SQL 文も出力できる情報を発信してくれるはずなので、あとは任せたいと思います。

Written by masayuki.ozawa

10月 18th, 2015 at 3:39 pm

Posted in SQL Database

Tagged with

Leave a Reply

*