Extended Events (XEvents) feature for Azure SQL Database (SQL DB) has just been released in Public Preview. In this blog post, I’d like to show how you can create an XEvents session with Azure Blob Container target file and consume the output in Power BI.

If you’re familiar with XEvents on SQL Server, I’d like to recommend reading the differences between the two as detailed here.

Example of Consuming XEvents Output

In this section, I will outline a simple example on how to consume and visualise wait_info event session.

Prerequisites:

1. On the database to be monitored, create an XEvent session with wait_info that writes to an Azure Blob container. In this example, I’m creating the event session (and the necessary credential) in AdventureWorksSQLDBV12 using this script. More in-depth explanation around Azure storage based target for XEvents is available here.
2. Have a separate database (Azure SQL DB) that can read the data from the same container. In this example, I’m using my OpenXMLTesting SQLDB.

Note: The instruction to create Credential with Storage Access Signature (SAS Token) on a SQL DB is similar to creating a credential in SQL Server 2014. Please remember to create a Master Key Encryption on the SQL DB first.

Steps:

1. Create a table valued function that reads XEL output from a Blob file URL.
2. Consume the data in Power BI desktop or equivalent visualization tool.

The following image illustrates where I have put my XEvents session and where I have the function that reads the XEvents target file.

Below are the steps in details.

Step 1: Table Valued Function To Read XEL File

The [sys].[fn_xe_file_target_read_file] function is available in Azure SQL DB now. So, we can create a table valued function that uses [sys].[fn_xe_file_target_read_file] to directly read the Azure Blob storage based XEL output file from a wait_info XEvent session.

Please note that every event in XEvents has its own XML output schema. So, please use and adapt this function to capture the events you are interested in. The [dbo].[fn_XEWaitInfoReader] function below only works for wait_info for the version at the time of writing. (Yes, unfortunately the schema may change from one version to another). It also assumes that the file being read contains the additional XEvent actions (fields) of sql_text, client_app and user_name.

/**********************************************************************************
 *
 *	Purpose:	To read XEL file directly from a Blob url
 *	Input:		@file the URL path to the blob storage
 *	Author:		Julie Koesmarno (@MsSQLGirl | https://www.mssqlgirl.com)
 *	Version:
 *			20151011 Julie Koesmarno	Initial Draft
 *	Disclaimer:	Please use the following script at your discretion. Using this 
 *				script/function in Production environment without thorough 
 *				testing is not recommended. Please adapt and deploy as necessary.
 *
 *********************************************************************************/
CREATE FUNCTION dbo.fn_XEWaitInfoReader (@file NVARCHAR(255))
RETURNS TABLE
	RETURN WITH CTE_WaitInfoXE (WaitInfo) AS
	(
		SELECT CAST(C.query('.') AS XML) AS WaitInfo
		FROM (SELECT
				CAST(event_data AS XML) AS XMLDATA 
			FROM
				sys.fn_xe_file_target_read_file(    
				@file, null, null, null)) a
		CROSS APPLY a.XMLDATA.nodes('/event') as T(C)
		WHERE C.query('.').value('(/event/@name)[1]', 'varchar(255)') = 'wait_info'	
	)
	 SELECT 	 
		WaitInfo.value('(//event/@name)[1]','varchar(50)') as Name 
		,WaitInfo.value('(//event/@timestamp)[1]','datetime') as [TimeStamp]
		,WaitInfo.value('(//event/@package)[1]','varchar(128)') as Package
		,WaitInfo.value('(//event/data[@name="wait_type"]/text)[1]', 'varchar(50)') as WaitType
		,WaitInfo.value('(//event/data[@name="opcode"]/text)[1]', 'varchar(50)') as OpCode
		,WaitInfo.value('(//event/data[@name="duration"]/value)[1]', 'int') as Duration
		,WaitInfo.value('(//event/data[@name="signal_duration"]/value)[1]', 'int') as SignalDuration
		,WaitInfo.value('(//event/data[@name="wait_resource"]/value)[1]', 'varchar(50)') as WaitResource
		,WaitInfo.value('(//event/action[@name="sql_text"]/value)[1]', 'varchar(MAX)') as SQLText
		,WaitInfo.value('(//event/action[@name="username"]/value)[1]', 'varchar(50)') as UserName
		,WaitInfo.value('(//event/action[@name="client_app_name"]/value)[1]', 'varchar(100)') as Client
	FROM CTE_WaitInfoXE;

To use this function on SSMS, simply run a query similar to the following

SELECT * 
FROM dbo.fn_XEWaitInfoReader ('https://mssqlgirldbstorage01.blob.core.windows.net/sqldbxedemo/WaitStats_0_130890773808640000.xel');

Using Power BI For Visualization

With Power BI Desktop, you can connect to Azure SQL DB where the [dbo].[fn_XEWaitInfoReader] function lives.

1. Create a new report in Power BI Desktop. Then connect to Azure SQL Database as illustrated below. In this example, I’m only analyzing one file at a time.
   
2. Once the information is completed, you can design your visualization. Mine looks something similar to the following:
   

Your Turn!

The best way of learning about XEvents is by doing. You can follow the example above and capture the events that you are interested in. Use the following query to check what events are available on SQLDB:

SELECT [name], [description]
FROM [sys].[dm_xe_objects]  
WHERE [object_type] = 'event'

SQL DB does not come with a default event session unlike SQL Server’s system_health event session. However, you can create your own event sessions (with an automatic restart) based on database-scoped events available on SQL DB.

Wrap Up

Extended Events is a feature that you can add to your monitoring and diagnostic tool for Azure SQL Database in addition to Query Store, Index Advisor and DMVs. You can use the sample in this blog post from just a simple report to an alerting system with Power BI. If you’re familiar with Power BI the online version, you can also create an alert in your iPhone based on the dashboard. In fact, you can summarise all the important metrics from DMVs and XEvents in Power BI for alerting.

In the next blog post I’ll show you how to create a real time monitoring tool based on XEvents Ring Buffer target.

Further Reading

Extended events in SQL Database

Ring Buffer target code for extended events in SQL Database

Event File target code for extended events in SQL Database

Public preview: Extended Events for Azure SQL Database