sys.dm_exec_sessions only returning one record despite additional sessions

Question

In on-premises 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:
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.  Is that expected behavior?

Thanks!

Answer 1

Yeah, that's normal. You have to remember that in SQL Azure, you might not be connecting to the same server. One session isn't aware of other sessions.

Edited: Ah - I'm wrong about the problem you're facing. But still be careful about what is reported about sessions in an environment that could switch servers at any time.

---

Blog: http://sqlblog.com/blogs/rob_farley
Company: http://www.lobsterpot.com.au
Community: Adelaide SQL UG President

Did this help? Mark it as an answer!

Answer 2

So, dumb question - what good are the DMVs then?  If the DMVs that show sessions don't actually show sessions, why would you use 'em?

Answer 3

I think the SQL Azure Team just wanted to get some DMVs working in the SQL Azure Service Update 1, so they could show some progress. I can think of a lot of database specific DMVs that would be much more useful.

---

Glenn Berry
http://glennberrysqlperformance.spaces.live.com/
http://www.twitter.com/GlennAlanBerry
Please click the Mark as Answer button if a post solves your problem!

Answer 4

Wow - as it turns out, you're both wrong.  I got the right answer on StackOverflow within 6 minutes of posting the question.  I'm kinda surprised by this one myself:

http://stackoverflow.com/questions/2315060/sql-azure-only-returning-one-row-in-sys-dm-exec-sessions/

Answer 5

Ok. So you were querying it without VIEW SERVER STATE permissions. Do watch out for DMVs in SQL Azure though, because there are always a good chance that you're looking at a different server to a few moments earlier.

---

Blog: http://sqlblog.com/blogs/rob_farley
Company: http://www.lobsterpot.com.au
Community: Adelaide SQL UG President

Did this help? Mark it as an answer!

Answer 6

Hello, actually in SQL Azure, you only need to have the "VIEW DATABASE STATE" permissions. You do not have access to the physical server, and the logical server you see on the portal is just a logical container of your databases, which is different from a physical SQL Server, and thus does not have any server states. This is documented in http://msdn.microsoft.com/en-us/library/ff394114.aspx and http://msdn.microsoft.com/en-us/library/ff394107.aspx:


If the user has VIEW DATABASE STATE permission on the server, the user will see all executing sessions on the database; otherwise, the user will see only the current session.

---

Lante, shanaolanxing This posting is provided "AS IS" with no warranties, and confers no rights.

Answer 7

Glenn,

Please post your list of useful DMVs -- we can use the feedback.

Thanks

---

Tonyp

Answer 8

Hello, actually in SQL Azure, you only need to have the "VIEW DATABASE STATE" permissions. You do not have access to the physical server, and the logical server you see on the portal is just a logical container of your databases, which is different from a physical SQL Server, and thus does not have any server states. This is documented in http://msdn.microsoft.com/en-us/library/ff394114.aspx  and http://msdn.microsoft.com/en-us/library/ff394107.aspx :


If the user has VIEW DATABASE STATE permission on the server, the user will see all executing sessions on the database; otherwise, the user will see only the current session.

---

Lante, shanaolanxing This posting is provided "AS IS" with no warranties, and confers no rights.

That doesn't appear to be the case.  I've granted users VIEW DATABASE STATE for their database, but they still only see one record in sys.dm_exec_sessions.  Here's a screenshot of two windows in SSMS, each with only one record:

http://s3temp.brentozar.com/azure2.png

Can anybody show a similar screenshot with multiple records in sys.dm_exec_sessions?

Answer 9

To further illustrate the problem, here's another screenshot:

http://s3temp.brentozar.com/azure3.png

Note that I *do* see records for other sessions in sys.dm_exec_connections, which leads me to believe that sys.dm_exec_sessions is being filtered improperly.

Answer 10

Brent,

You are correct about the improper flitering -- we now have a bug open on this.

We apologize of the inconvenience.

Thank you.

---

Tonyp

Answer 11

Oh, sorry, I thought you were querying dm_exec_connections, which works fine for me... Yes, I've verified dm_exec_sessions always returns a single session, even if you have the "VIEW DATABASE STATE" permission. We have recognized it as a bug.

---

Lante, shanaolanxing This posting is provided "AS IS" with no warranties, and confers no rights.