Azure SQL – Where the heck is SQL Profiling?

Cheat Code (this is combo cheat, so pay attention!)
> Azure SQL Audit + Blog Storage + XEL File + SSMS!

I’m puzzled, cursing, banging my head, almost throw my laptop (lucky I didn’t do that, so bloody expensive if I did that – My company charge full laptop replacement to our paycheck if we broke it – DARN!)

It started with a crazy developer came to me and said “Bro, I have no idea why my transaction looks weird, the API shall put 26 items, but only 11 item inserted”. (Step 1: fill body with anger) – I said “You DEVELOPER, You WRITE CODE, YOU… YOU… YOU!!!! fine… i’ll check)“

Sometime I have no idea what are they get paid for… Anyway, new things to do on lazy afternoon – not bad.

So I fire up my beloved SSMS, logon to Azure SQL using my super secret account (sysadmin man!) -> Tools -> SQL Server Profiler

Then tried to connect to the Azure SQL and I meet my nemesis…
“In order to run a trace against SQL server you must be a member of sysadmin fixed server role or have the ALTER TRACE permission”
Bloody hell! I am SYSADMIN, I am GOD on this SQL Azure, I can kill it if I want.. Fine!
(Step 2: cursing… done)

Googled around and basically in Azure SQL we are not GOD, the Azure team are more GOD-er than us… we are just merely LESSER-GOD than them. Although we pay them… sigh.

So to solve this madness, we need to enable Azure SQL Auditing, pretty simple step. Head over to Azure portal, -> Azure SQL -> Auditing -> ON!
I choose Storage, cause it’s easy and accessible (feel free to select other poison)

Once the auditing enabled, I asked the SMART-ASS developer to do whatever he want to do with the DB again.

Then head over to Azure Storage Explorer and check out the baby…

Download the file and be puzzled… My over-engineering part of my head thought this guy need super damn special tool to open.. so I google and found StackOverflow (here) that provide magical query:

select
    n.value('(@name)[1]', 'varchar(50)') as event_name,
    n.value('(@package)[1]', 'varchar(50)') AS package_name,
    n.value('(@timestamp)[1]', 'datetime2') AS [utc_timestamp],
    n.value('(data[@name="duration"]/value)[1]', 'int') as duration,
    n.value('(data[@name="cpu_time"]/value)[1]', 'int') as cpu,
    n.value('(data[@name="physical_reads"]/value)[1]', 'int') as physical_reads,
    n.value('(data[@name="logical_reads"]/value)[1]', 'int') as logical_reads,
    n.value('(data[@name="writes"]/value)[1]', 'int') as writes,
    n.value('(data[@name="row_count"]/value)[1]', 'int') as row_count,
    n.value('(data[@name="last_row_count"]/value)[1]', 'int') as last_row_count,
    n.value('(data[@name="line_number"]/value)[1]', 'int') as line_number,
    n.value('(data[@name="offset"]/value)[1]', 'int') as offset,
    n.value('(data[@name="offset_end"]/value)[1]', 'int') as offset_end,
    n.value('(data[@name="statement"]/value)[1]', 'nvarchar(max)') as statement,
    n.value('(action[@name="database_name"]/value)[1]', 'nvarchar(128)') as database_name
from (select cast(event_data as XML) as event_data
from sys.fn_xe_file_target_read_file('D:\Folder\MySession*.xel', null, null, null)) ed
cross apply ed.event_data.nodes('event') as q(n)

Voila!
Job done. We found the STUPID mistake the Smart-Developer made.

Then I close all the windows and sipping my coffee until…
Accidentally Double clicking the XEL file…
And my screen showing MAGICAL GATEWAY to read XEL file…
My beloved SSMS just opened and shown the XEL file opened with all the bloody events listed nicely with FILTER, Columns selector…

Darn!
There you go… shorter cheat combo actually exists in this world.
Have Fun!