Over the past 6 months I have been trying to push myself to use extended events (XEvents) over SQL trace, once you get past the learning curve it’s probably the way to go. If you are operating in the Azure space then you have no choice. Extended events are what you will need to use if you want to collect information against SQL database. There are some subtle differences on how you write the T-SQL between SQL database (Azure) and your locally installed SQL Servers.

Here I wanted something simple to find deadlocks and general T-SQL activity.

So, I have connected to SQL database via SSMS (SQL Server Management Studio).

In the context of your database run the following: (NOT master database – it will not work)

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'
)
ORDER BY
o.object_type,
p.name,
o.name;

This will return a list of available actions and events. For example:

There are 22 actions and 261 events. Naturally less than your local based SQL Servers, for example on my local 2014 machine running the above query returned 50 actions and 284 events.

The targets available in Azure are:

If you decide to use the event_file this will write it to Azure storage, to set this up you will need to have a SAS token with the relevant permissions (read, write and list). This is quite a lengthy setup because you then need to create a DATABASE SCOPED CREDENTIAL whose identity (secret) uses the SAS token.

To make things easier I am going to work with the ring buffer.

I wrote a basic session (shown below), nothing complex here.

CREATE EVENT SESSION azure_monitor
ON DATABASE

ADD EVENT sqlserver.sql_statement_completed
( ACTION (sqlserver.sql_text,sqlserver.database_name)),
ADD EVENT sqlserver.database_xml_deadlock_report

ADD TARGET package0.ring_buffer
(SET max_memory = 500, max_events_limit = 10 )
;

ALTER EVENT SESSION azure_monitor
ON DATABASE
STATE = START;

See the subtle difference? I am creating the session ON DATABASE not ON SERVER. Also I would suggest using a max_memory setting (ring buffer) because you may receive an error message that says a memory maximum was enforced when operating in Azure.

When started run the following within your database to see a successful start-up.

 select * from sys.dm_xe_database_sessions

By the way, when you have created the session you will not see it in SSMS (SQL Server Management Studio) – maybe this is a bug?

I kept the information that I wanted to a minimum, so lets’ query it now.

DECLARE @Shredit XML;
SELECT @Shredit = CAST(target_data AS XML)
FROM sys.dm_xe_database_sessions AS s
JOIN sys.dm_xe_database_session_targets AS t
ON t.event_session_address = s.address
WHERE s.name = N'azure_monitor';

SELECT
qp.query('.') AS event_data
FROM @Shredit.nodes('RingBufferTarget/event') AS q(qp);

So the output is what I expect really.

Here is a snippet from the deadlock graph.

Maybe you want to track the T-SQL executed against the SQL database? XQuery is your friend.

DECLARE @ShredMe XML;
SELECT @ShredMe = CAST(target_data AS XML)
FROM sys.dm_xe_database_sessions  AS s
JOIN sys.dm_xe_database_session_targets AS t
ON t.event_session_address  = s.address
WHERE s.name = N'azure_monitor';

SELECT
QP.value('(data[@name="statement"]/value)[1]', 'varchar(max)') as [SQL CODE],
QP.value('(action[@name="database_name"]/value)[1]', 'varchar(max)') as [Database],
QP.value('(@timestamp)[1]', 'datetime2') AS [timestamp]

FROM @ShredMe.nodes('RingBufferTarget/event[@name=''sql_statement_completed'']') AS q(QP);
GO

Clean-up code:

ALTER EVENT SESSION azure_monitor
ON DATABASE
STATE = stop;

ALTER EVENT SESSION  azure_monitor
ON DATABASE
DROP TARGET package0.ring_buffer;

DROP EVENT SESSION azure_monitor
ON DATABASE;
GO

For more differences between Azure and your local based extended event sessions go read https://docs.microsoft.com/en-gb/azure/sql-database/sql-database-xevent-db-diff-from-svr. Hopefully you have enjoyed this post.