SQL Azure only returning one row in sys.dm_exec_sessions

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:

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

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.

Answer 2

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