SQL Azure only returning one row in sys.dm_exec_se

Question

In on-premises Microsoft SQL Server, when I issue this query:

SELECT * FROM sys.dm_exec_sessions

I get one record per window in SSMS. If I open several query windows in SSMS, I get a record for each of those, even if they're not doing anything. In Azure, when I issue that same query, I only get one record back. Here's the kicker - I even only get one record back when I've got multiple windows in SSMS doing active work in Azure.

Here's a screenshot:

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

In that screenshot, I've got three active tabs in SSMS. Two of them are running giant insert statements that take several seconds to execute, and the third one is querying sys.dm_exec_sessions - but I'm only getting one record back. I get multiple records in sys.dm_tran_active_transactions and sys.dm_tran_database_transactions, but this changes how I report on active queries if I can't tie it back to individual sessions. Is that expected behavior, or something MS is working to fix in Azure vNext?

Answer 1

After escalating this inside Microsoft, they've agreed that there's a bug in sys.dm_exec_sessions. It's improperly filtered, so it always shows just your current session and no other sessions regardless of your permissions. They'll update it in a future release of SQL Azure.

More info in the MSDN thread if you need proof:

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

Answer 2

The behavior of Azure is the same as SQL:

Permissions: Requires VIEW SERVER STATE permission on server.

Note: If the user has VIEW SERVER STATE permission on the server, the user will see all executing sessions on the instance of SQL Server; otherwise, the user will see only the current session.