SQL Azure returns only one row in sys.dm_exec_sessions

Question

In local Microsoft SQL Server, when I issue this query:

SELECT * FROM sys.dm_exec_sessions

I get one entry per window in SSMS. If I open several query windows in SSMS, I get an entry for each of them, even if they do nothing. In Azure, when I issue the same request, I get only one record. Here's the kicker - I even get only one entry when I have several windows in SSMS that do active work in Azure.

Here is a screenshot:

SQL Azure http://s3temp.brentozar.com/azure1.png

In this screenshot, I have three active tabs in SSMS. Two of them run gigantic insert instructions, which take a few seconds to complete, and the third - the sys.dm_exec_sessions request - but I only get one record. I get a few entries in sys.dm_tran_active_transactions and sys.dm_tran_database_transactions, but this changes how I report active queries if I cannot bind it to individual sessions. Is this expected behavior, or is something MS working on a fix in Azure vNext?

Answer 1

After escalating this inside Microsoft, they agreed that there was an error in sys.dm_exec_sessions. It is not properly filtered, so it always shows only your current session and no other sessions, regardless of your rights. They will update it in a future version of SQL Azure.

Additional information in the MSDN stream if you need confirmation:

http://social.msdn.microsoft.com/Forums/en-US/ssdsgetstarted/thread/db88c830-0b0a-4e38-ad93-062d12d55c31

Answer 2

Azure behavior is the same as SQL:

Permissions: Permission to access the SERVER STATE view on the server is required.

Note. If the user has BROWSE STATE SERVER on the server, the user will see all running sessions using the example of SQL Server; otherwise, the user will only see the current session .