sys.dm_exec_query_stats DBID column NULL for dynamic SQL - by Theo Ekelmans

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 374600 Comments
Status Closed Workarounds
Type Suggestion Repros 0
Opened 10/10/2008 1:22:46 AM
Access Restriction Public


select * FROM sys.dm_exec_query_stats qs cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt

dbid Is NULL for ad hoc and prepared SQL statements.

WHY?!?!?!? all queries are run against some database

This is really hurting me, because 98% of my QueryPlanCache is dynamic, and i want to be able to analyze which of my 38 CMS databases gets hammered by a badly designed dynamic query, which they do on a regular basis. Finding the database on wich the query is running against will allow me to find the CMS server and the delinquent code.

Having the id and the plan would allso open the option to evict bad or unused queries from the plan cache, but that is a point i will raise @ teched to the SQL program managers.
Sign in to post a comment.
Posted by Jan [MSFT] on 3/24/2011 at 4:58 PM

Thank you for submitting this suggestion, but given its priority relative to the many other items in our queue, it is unlikely that we will actually complete it. As such, we are closing this suggestion as “won’t fix”. If you feel that this is worth reconsidering, feel free to respond to this message and we will take another look.

SQL Server Engine
Posted by Theo Ekelmans on 11/6/2008 at 12:35 AM
TY Harman,

Were running on the IA64 platform, since we have calculation intensive queries, but i will keep an eye out for that error

Posted by Boris [MSFT] on 11/4/2008 at 1:13 PM

I got a chance to get additional info on this. Sql_handle is a hash value which identifies SQL text of the batch being submitted to the server. Since it identifies just the text, it can be submitted against different databases and still be the same. Thus, sql_handle cannot uniquely identify which database this batch/query was submitted against for an ad-hoc statement.

Stored procedure, on other hand, always have the database it resides in and thus we can populate this column.

I suppose for plan_handle you can claim that this information is available (plan_handle uniquely identifies the plan and context) and potentially we can improve this DMV to provide this information.

You can read more about sql_handle and plan_handle in the blog post by Sangeetha:

We do however have the dbid information which you can get using sys.dm_exec_plan_attributes DMV (see for the reference).

Using the following query (similar to what is in the above reference):

SELECT plan_handle, pvt.dbid, pvt.sql_handle
    SELECT plan_handle, epa.attribute, epa.value
    FROM sys.dm_exec_cached_plans
        OUTER APPLY sys.dm_exec_plan_attributes(plan_handle) AS epa
    WHERE cacheobjtype = 'Compiled Plan' AND objtype = 'adhoc') AS ecpa
PIVOT (MAX(ecpa.value) FOR ecpa.attribute IN ("dbid", "sql_handle")) AS pvt
ORDER BY pvt.sql_handle;

you can get the dbid. Here's an example of the output:

Plan_handle, dbid, sql_handle:

0x06000100133A381EB8A1AB05000000000000000000000000,     1,     0x02000000133A381EBDDACEBE0B2417C2CC0228286B6D8AE8
0x06000700133A381EB8610723000000000000000000000000,     7,     0x02000000133A381EBDDACEBE0B2417C2CC0228286B6D8AE8

Note that I have the same sql_handle while plan_handle and dbid are different (the same adhoc query was submitted against 2 different databases).

Hope that helps.

Posted by Harman Sahni on 10/22/2008 at 11:26 AM

while we are on this, I've personally seen "incorrect" DB_ID in sys.dm_exec_requests DMV (and even sysprocesses) for log backup (command like '%backup%') of a user DB sometimes showing 1 (master) or 4(msdb).

We have a case opened with Quest for this as we use Litespeed for SQl Server. I am therefore not reporting this as a SQL bug unless/until we hear from them. FYI: upon further investigation we found out that this occurs on 64 bit SQL 2005 server only. But again will wait for an official verdict from Quest as I suspect this could be VDI related...
Posted by Boris [MSFT] on 10/20/2008 at 12:16 AM

Thank you for the feedback! We will consider it for the next release of SQL Server.