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

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

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