Azure Sql Server Profiling

As you may have already guessed, you cannot use SQL Server Profiler on an Azure database. However, you can use the following code to find out what SQL was executed:

	SELECT TOP 20
	QS.last_execution_time,
	SUBSTRING( ST.text,
	0,
	CASE WHEN LEFT(ST.text, 2) = '(@' THEN
	QS.statement_start_offset / 2
	ELSE
	0
	END
	) AS params,
	SUBSTRING( ST.text,
	(QS.statement_start_offset / 2) + 1,
	((CASE statement_end_offset
	WHEN -1 THEN
	DATALENGTH(ST.text)
	ELSE
	QS.statement_end_offset
	END - QS.statement_start_offset
	) / 2
	) + 1
	) AS statement_text,
	ST.text as 'raw'
	FROM sys.dm_exec_query_stats QS
	CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) ST
	ORDER BY QS.last_execution_time DESC;

view raw AzureSql.sql hosted with ❤ by GitHub

In order to get the real parameter values, you need to enable sensitive data logging by using DbContextOptionsBuilder.EnableSensitiveDataLogging method:

Enables application data to be included in exception messages, logging, etc. This can include the values assigned to properties of your entity instances, parameter values for commands being sent to the database, and other such data. You should only enable this flag if you have the appropriate security measures in place based on the sensitivity of this data.

	protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
	{
	optionsBuilder.EnableSensitiveDataLogging();
	base.OnConfiguring(optionsBuilder);
	}

view raw OnConfiguring.cs hosted with ❤ by GitHub