none
DBID from sys.dm_exec_sql_text() not showing in sys.databases

    Question

  • I'm getting this weird thing.

    When I select records from sys.databases I get 48 records with max dbid as 48.

    But when I run following query:

     

    select x.*, a.*
    from sys.dm_exec_query_stats a
    cross apply sys.dm_exec_sql_text(sql_handle) x
    

     

    ... I get a strange dbid number i.e. 32767 along with other dbid records.

    Another thing is I've run a Proc and it is runs for about 10-15 mins on dbid=47. When I check with above query, the statements it executes are not showing with dbid 47 but NULL.

    Any ideas about his behavior and from where it gets the 32767 dbid?


    ~manoj | email: http://scr.im/m22g
    http://sqlwithmanoj.wordpress.com
    MCCA 2011
    Friday, June 3, 2011 4:46 AM

Answers

  • Hi,

    Have a look

    http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/7ef42395-0475-4820-969e-b7e7ab297974/


    Thanks and regards, Rishabh , Microsoft Community Contributor
    Friday, June 3, 2011 5:16 AM
  • Hi,

    The answer is explained in the same post which says there is no known database context

    and if you run the query

    select * from sys.databases or something similar to it,you will get the NULL value because it is an adhoc query


    Thanks and regards, Rishabh , Microsoft Community Contributor
    Friday, June 3, 2011 6:25 AM

All replies

  • Hi,

    Have a look

    http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/7ef42395-0475-4820-969e-b7e7ab297974/


    Thanks and regards, Rishabh , Microsoft Community Contributor
    Friday, June 3, 2011 5:16 AM
  • Thanks Rishabh, the link is very useful and informative.

     

    So, here's an excerpt from the above link:

    The dbid column is the actual database id in the server but there are two special cases NULL and 32767 for system usage. 32767 is the 'Resource' database which SQL server internally uses and is not controlled by user and for NULL see the books online http://technet.microsoft.com/en-us/library/ms181929.aspx . The NULL in dbid indicates that those queries are Ad Hoc queries.

     

    A stored procedure belongs to a specific database hence the dbid is known. An ad-hoc query can touch 5 different databases or not a hava a database reference at all (execute in current db context).

    ------------------------------

     

    Still not convinced, why does it shows NULL dbid for queries running in specific Database as queries run for on a specific context even they refer to different DBs.


    ~manoj | email: http://scr.im/m22g
    http://sqlwithmanoj.wordpress.com
    MCCA 2011
    Friday, June 3, 2011 6:19 AM
  • Hi,

    The answer is explained in the same post which says there is no known database context

    and if you run the query

    select * from sys.databases or something similar to it,you will get the NULL value because it is an adhoc query


    Thanks and regards, Rishabh , Microsoft Community Contributor
    Friday, June 3, 2011 6:25 AM
  • Correct Rishabh, Missed checking the other link.

     

    So, this says dbid, objectid & number "Is NULL for ad hoc and prepared SQL statements".

    Convinced that this is by design :)


    ~manoj | email: http://scr.im/m22g
    http://sqlwithmanoj.wordpress.com
    MCCA 2011
    Friday, June 3, 2011 7:01 AM