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.

Improve this question

2 Answers 2

Reset to default
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.

Improve this answer
2
  • 1
    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
    Commented Mar 15, 2018 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
    Commented Mar 15, 2018 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.

Improve this answer
2
  • 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
    Commented Mar 15, 2018 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: learn.microsoft.com/en-us/sql/relational-databases/…
    – Grant Fritchey
    Commented Mar 15, 2018 at 12:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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