'Include Live Query Statistics' using SQL Server Management Studio with Azure SQL Database? Is it supported?

Derek Horrall 196

Should I be able to use 'Include Live Query' statistics to observe query runtime performance as data flows through? I see examples of this using 'on prem' SQL Server, but I am not clear if this is supported in Azure SQL Database. When I try to use it on a long running query, nothing is dynamic. I don't see rows being updated in query plan notes, etc. Is there a setting that needs change for this to work, or is it simply not supported?

2 answers

Alberto Morillo 32,991 Reputation points MVP

2023-08-04T14:44:58.7266667+00:00
Live Query is supported for Azure SQL as you can read in the documentation.

However, Live Query may not work well on the following scenarios:

With short or simple queries

With queries that are blocked by another session

With queries executed on an Azure SQL replica

Queries using features not supported by Live Query like triggers, dynamic SQL or cursors.

With long running queries Live Query usually works well as he is able to generate a live plan unless the query is blocked by another session or is involved on one of the scenarios I mentioned above.
Please sign in to rate this answer.
Oury Ba-MSFT 16,886 Reputation points Microsoft Employee

2023-08-04T18:37:02.61+00:00

Derek Horrall Thank you for reaching out.

If the reply from @Alberto Morillo answered your question, please mark as accept answer. Otherwise comment below and let us know if you have additional questions.

Regards,

Oury

Derek Horrall 196 Reputation points

2023-08-04T18:45:50.5366667+00:00

I have no idea why it is not working then. The 'live plan' just pops up and is static until the query finishes. Are there any settings or permissions that needs to be set in the database?

Oury Ba-MSFT 16,886 Reputation points Microsoft Employee

2023-08-04T19:37:23.4666667+00:00

Derek Horrall Thank you for reaching out.

In addition to @Alberto Morillo 's answer

The permissions require are the below according to the doc.

Requires the database level SHOWPLAN permission to populate the Live Query Statistics results page and requires any permissions necessary to execute the query. On SQL Server, requires the server level VIEW SERVER STATE permission to see the live statistics.
On SQL Database Premium Tiers, requires the VIEW DATABASE STATE permission in the database to see the live statistics. On SQL Database Standard and Basic Tiers, requires the Server admin or an Azure Active Directory admin account to see the live statistics.

Regards,

Oury

Alberto Morillo 32,991 Reputation points MVP

2023-08-04T20:31:28.3466667+00:00

Thank you so much Oury!
Sign in to comment
Erland Sommarskog 102.3K Reputation points

2023-08-04T21:49:15.8433333+00:00

So I tried this, and in my first test I saw the same thing as Derek. That is, I saw "Waiting for Query Plan" and when I got it, all was completed. But then I made a small modification to my query, and this time I saw a few updates. (This was a query that completes in a few seconds.)

One thing to keep in mind is that with Live Query Plan there is a lot going forth and back, and you typically have a lot higher latency with Azure SQL Database than with an on-prem database.
Please sign in to rate this answer.
Derek Horrall 196 Reputation points

2023-08-05T11:48:02.3233333+00:00

In my case. It is a bit different. I have a long running query (1 minute) which I am trying to troubleshoot. When I run it with 'live statistics', it runs, 'Plan' pops up, query still says 'executing', and I see no 'activity' on the screen. No 'nodes' are updating. I only see 'dashed' lines between nodes. I had expected for the 'activity' to show with record counts etc as the query proceeded through the plan steps. The whole point of using this is to try to troubleshoot the bottlenecks, but I can't get it to work.

Erland Sommarskog 102.3K Reputation points

2023-08-05T22:05:43.0466667+00:00

One possibility is that the first operator is doing all work, and once it's does the rest of the operator are done in no time at all. "But wouldn't I see the rowcounts increase for the first operator, then?", you may ask. Probably not, if it is a scan with a filter that is pushed down to the storage engine.

In any case, I tested with a really bad query, and I did see the counts being updated for the operator.

Here is a query, you can try with:

SELECT SUM(convert(bigint, a.object_id) + b.object_id + c.object_id) FROM sys.objects a CROSS JOIN sys.objects b CROSS JOIN sys.objects c

It should run long enough to see counts increasing.

Also, try this query:

SELECT * FROM sys.fn_my_permissions(NULL, 'DATABASE')

As Oury says above, it should list VIEW DATABASE PERFORMANCE STATE. If it does not, that would explain why you don't see the updates.
Sign in to comment

Share via

'Include Live Query Statistics' using SQL Server Management Studio with Azure SQL Database? Is it supported?

2 answers