How to get SQL Profiler working with Azure SQLDb?

Question

We are having a Power BI dataset in the service. The source of this dataset are some Azure SQLdb tables. PaaS (Platform as a Service) setup. The daily refresh of this Power BI dataset takes long. SQL Profiler would be the tool to check the events that are happening. But we can't get it working in this PaaS environment. We used SQLServer, DAX Studio and Azure Data Studio. So how can I trace query execution, capture events in a PaaS environment?

Really hope someone has the answer

regards Ron

Answer 1

SQL Server Profiler and SQL Trace are deprecated. For Azure SQL Database you should use Extended Events to capture the queries.

With Extended Events you can create a session, define what events to be captured in this session, and for each of the events to say which fields to be retrieved. You can define filters on these fields too (e.g. capture the events in one specific database only). The last thing when you create a session is to define where to store the data - in a file, ring buffer and so on. In your case, sql_batch_starting event with sql_text field, captured to a ring buffer should be enough (capturing to a file will require setting up Azure Storage).

You can create the event session with a script or with a wizard in SQL Server Management Studio. The script could be something like this:

CREATE EVENT SESSION [Capture queries] ON DATABASE 
ADD EVENT sqlserver.sql_batch_starting(
    ACTION(sqlserver.sql_text))
ADD TARGET package0.ring_buffer
GO

where [Capture queries] is the name of the session. If you create the session with the wizard, you have the option to start it automatically after it is created, but if you use the script, you must start it manually, like this:

ALTER EVENT SESSION [Capture queries] ON SERVER STATE = START

It is very important to stop the session, when it is not needed anymore, because it has impact on the performance. You can stop a session with the following script:

ALTER EVENT SESSION [Capture queries] ON SERVER STATE = STOP

And eventually drop it when it is no longer needed:

DROP EVENT SESSION [Capture queries] ON SERVER

In SQL Server Management Studio, you can see the result by right-clicking on the ring buffer and select View Target Data...:

which will show you an XML to click on:

Or you can use a query, like this:

select
    se.name as session_name,
    ev.event_name,
    ac.action_name,
    st.target_name,
    se.session_source,
    st.target_data,
    CAST(st.target_data AS XML) as target_data_XML
from sys.dm_xe_database_session_event_actions ac
INNER JOIN sys.dm_xe_database_session_events ev on ev.event_name = ac.event_name and cast(ev.event_session_address AS BINARY(8)) = cast(ac.event_session_address AS BINARY(8))
INNER JOIN sys.dm_xe_database_session_object_columns oc on cast(oc.event_session_address AS BINARY(8)) = cast(ac.event_session_address AS BINARY(8))
INNER JOIN sys.dm_xe_database_session_targets st on cast(st.event_session_address AS BINARY(8)) = cast(ac.event_session_address AS BINARY(8))
INNER JOIN sys.dm_xe_database_sessions se on cast(ac.event_session_address AS BINARY(8)) = cast(se.address AS BINARY(8))

The last column is an XML like the one above, where you can see the captured statements:

Of course, it is possible to use XQuery and transform the returned XML to a tabular result, but in your case it is not needed - just look for the queries in the XML itself.