none
Ouestion on sys.dm_exec_requests system view RRS feed

  • Question

  • Hello!

    This page says: "Returns information abouteach request that is executing within SQL Server." Having read that I expect to see the requests running against all server databases. But when I run select * from sys.dm_exec_requests (or just select session_id, database_id,  open_transaction_count from sys.dm_exec_requests for the sake of bravity) I see only the request against system databases and not against my test databse TEST.

    If I add Use=Test then the Test database gets added to the previous output:

    Why does the  sys.dm_exec_requests system view not display the requests pertaining to the non-system databases by default?

    Thank you in advance,
    Michael?




    • Edited by MF47 Monday, June 3, 2019 2:25 PM
    Monday, June 3, 2019 2:23 PM

Answers

  • but only currently running sessions,"

    Hello Michael,

    that view returns currently running requests, not sessions; for this you can use sys.dm_exec_sessions:

    select SES.session_id, SES.status, REQ.status
    from sys.dm_exec_sessions AS SES
         LEFT JOIN
         sys.dm_exec_requests AS REQ
             ON SES.session_id = REQ.session_id

    You will see, the most sleeping sessions don't have requests


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by MF47 Tuesday, June 4, 2019 2:08 PM
    Tuesday, June 4, 2019 1:19 PM

All replies

  • If I add Use=Test then the Test database gets added to the previous output:

    It do return all session informations for all database, but only currently running sessions, so I guess there are non running on the test database.

    And the reason for the second result is, that this with database id is your own session, which runs a query while connected to the test database.

    You can test it with

    select *
    from sys.dm_exec_requests
    where session_id = @@SPID  -- your own session id


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Monday, June 3, 2019 2:35 PM
  • "And the reason for the second result is, that this with database id is your own session, which runs a query while connected to the test database.

    You can test it with

    select *
    from sys.dm_exec_requests
    where session_id = @@SPID

    "

    - you're right: it is the "use Test" statement that makes the Test database appear in the output and it means only the following statement is true - "It do return all session informations for all database, but only currently running sessions," which contradicts with the MS's definition ""Returns information about each request that is executing within SQL Server." - seems strange to me MS uses such incorrect definition...

    Am I getting it right that there's no any other single command/view to really see the requests for all running sessions and the only way is to create a script for it myself?

    Regards,
    Michael

    Tuesday, June 4, 2019 7:49 AM
  • but only currently running sessions,"

    Hello Michael,

    that view returns currently running requests, not sessions; for this you can use sys.dm_exec_sessions:

    select SES.session_id, SES.status, REQ.status
    from sys.dm_exec_sessions AS SES
         LEFT JOIN
         sys.dm_exec_requests AS REQ
             ON SES.session_id = REQ.session_id

    You will see, the most sleeping sessions don't have requests


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by MF47 Tuesday, June 4, 2019 2:08 PM
    Tuesday, June 4, 2019 1:19 PM
  • Hello Olaf,

    I got it - thank you very much for your help!

    Regards,
    Michael

    Tuesday, June 4, 2019 2:07 PM