none
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!
    Monday, February 22, 2010 11:13 PM

Answers


  • Brent,

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

    We apologize of the inconvenience.

    Thank you.


    Tonyp
    • Marked as answer by Yi-Lun Luo Monday, March 01, 2010 9:40 AM
    Wednesday, February 24, 2010 7:47 AM

All replies

  • 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!
    • Edited by Rob FarleyMVP Tuesday, February 23, 2010 12:22 AM I was wrong!
    Monday, February 22, 2010 11:22 PM
  • 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?
    Monday, February 22, 2010 11:25 PM
  • 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!
    Monday, February 22, 2010 11:54 PM
  • 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/
    • Proposed as answer by Rob FarleyMVP Tuesday, February 23, 2010 1:01 AM
    Tuesday, February 23, 2010 12:13 AM
  • 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!
    Tuesday, February 23, 2010 12:20 AM
  • 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.
    Tuesday, February 23, 2010 3:42 AM

  • Glenn,

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

    Thanks

    Tonyp
    Tuesday, February 23, 2010 5:57 AM
  • 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?
    Tuesday, February 23, 2010 5:59 PM
  • 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.
    Tuesday, February 23, 2010 6:10 PM

  • Brent,

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

    We apologize of the inconvenience.

    Thank you.


    Tonyp
    • Marked as answer by Yi-Lun Luo Monday, March 01, 2010 9:40 AM
    Wednesday, February 24, 2010 7:47 AM
  • 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.
    Wednesday, February 24, 2010 7:49 AM