Tracing a session SQL in "Azure SQL"

With our 'cloud first' strategy we are trying to make use of Azure SQL for all of our databases as much as possible. This (as we have discovered) does come with a few things you need to be aware of as this is not 'normal' SQL Server - there are various things that are not there and quirks about the way some of the things work.

In a case this week we were having trouble getting a certain process working and we wanted to trace what the application code was running inside the database. Now the PaaS GUI via portal.azure.com does offer some stuff - it basically picks out some of the top statements and helps you tune them but it doesn't allow you to just pick a session and show everything its doing.

So how to do this - my initial thought (and bearing in mind I was an Oracle DBA and not really a SQL Server expert) was to use SQL Profiler - I've used this in the past and it allows you to see everything that's going on - it's actually quite a nice tool and better than Oracle here in a lot of respects.

Problem is though there is no SQL Profiler.... Even if you take the SQL 2017 client and try and point this at PaaS it just doesn't work and is not an option - so what do we do?

Well after a bit of digging I found this blog note from Arun - which did exactly what I wanted to do (and actually the blog seems generally useful - one to remember):

https://blobeater.blog/2017/02/06/using-extended-events-in-azure/

In my case I just pulled out these small bits of SQL to achieve what I wanted - I don't know all the in's and out's and what extra options etc there are for this - it just does what I want and I didn't need to look any further than that yet......

First thing we do is create the following and enable it

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_bufferSET max_memory = 500, max_events_limit = 10 )
;

ALTER EVENT SESSION azure_monitor ON DATABASE
STATE = START;

In my application I then run the function I'm having trouble with - then after that run the query below to show the sql that has been running - you seem to have to be quite quick to get this info - perhaps the setting I the code above can be made larger to keep more in memory so there is more to query.

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



This shows me output from the code running something like this:

Then I can see the code of interest (and everything else the session ran) - very useful

Then I just need to tidy up so we don't trace for ever

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



Now we're back to normal - think this code will be very useful - thanks again Arun




;