1

TL;DR - Is it possible to capture live procedure calls (with parameter names/values) in SQL Azure, to replicate the output of SQL Profiler illustrated below - or - is there a better way to achieve this?

Historically when my web application has performed badly, I used SQL Server Profiler to capture calls from the web app, and see the stored procedures and their parameters as they were executed. I then copied/pasted them into SSMS and used the execution plan to modify the procedures until performance was enhanced:

Profiler capturing live queries

Now, while testing out SQL Azure, I see a lot of people asking how to use Profiler with SQL Azure, and being told it can't (and probably won't in future). While that might not be true any more (as a lot of questions online are quite old), the question above remains.

I've tested out the Azure Performance Insight, but it gives a good overview, and doesn't capture quite what I'm looking for.

2

You can use SQL Server Management Studio XEvent Profiler and you may also see Azure Data Studio supports seeing XEvents from cloud and on-premises but SSOS uses the ring buffer target.

  • The XEProfiler node only seems to be visible when I use SSMS on local databases. It is not shown for Azure db's. However, the Extended Events node is visible, and I can create Sessions that appear to capture what I need as XML dumps. Does that sound correct please? – EvilDr Mar 15 '18 at 9:19
  • 1
    Yes, it creates XML outputs that you can open with SSMS. Depending on the size you may need to configure SSSMS to handle big XML outputs. – Alberto Morillo Mar 15 '18 at 12:36
2

Microsoft stopped developing functionality for Trace back in 2008. Everything now is focused on Extended Events. The same is true for Azure SQL Database. Extended events capture the rpc_completed event statement text in a very similar manner.

As an example, I'm capturing rpc_completed events from a PowerShell script that is using the SQlClient.SqlCommand object to execute a stored procedure like this:

$Sniffcmd = New-Object System.Data.SqlClient.SqlCommand
$Sniffcmd.CommandType = [System.Data.CommandType]'StoredProcedure'
$Sniffcmd.CommandText = "dbo.ProductTransactionHistoryByReference"
$Sniffcmd.Parameters.Add("@ReferenceOrderID",[System.Data.SqlDbType]"Int")
$Sniffcmd.Connection = $SqlConnection

The resulting output of the rpc_completed event action for the statement is as follows:

statement: exec dbo.ProductTransactionHistoryByReference @ReferenceOrderID=68187

It's exactly what you'd expect if you were running trace, but it's from extended events. Here's documentation to get you started using Extended Events in Azure SQL Database.

  • I managed to capture what I needed using Extended Events in SSMS while connected to Azure. It's a messy XML dump, but the data is in there, so that's progress. Please can you advise how/where you run that PowerShell script? Is that within Extended Events somewhere? Don't need a big tutorial, just a pointer please. – EvilDr Mar 15 '18 at 9:21
  • 1
    That script is just demoing how I did the execution of the procedure. It's not linked directly to ExEvents. To consume the XML data, you have two choices. You can use SSMS to open the output in the data viewer. I like it. It works pretty well for most purposes. Alternatively, you can use the extended query sys.fn_xe_file_target_read_file. Here's some docs on that: docs.microsoft.com/en-us/sql/relational-databases/… – Grant Fritchey Mar 15 '18 at 12:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.