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

Hello,

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.

Jan
SQL Server Engine

TY Harman,

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

Hello,

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:

http://blogs.msdn.com/sqlprogrammability/archive/2007/01/09/2-0-sql-handle-and-plan-handle-explained.aspx

We do however have the dbid information which you can get using sys.dm_exec_plan_attributes DMV (see http://msdn.microsoft.com/en-us/library/ms189472.aspx for the reference).

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

SELECT plan_handle, pvt.dbid, pvt.sql_handle
FROM (
    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.

Regards,
Boris.

FYI:

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...

Hello,

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

Regards,
Boris.