Home Dashboard Directory Help
Search

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


Status: 

Closed
 as Won't Fix Help for as Won't Fix


0
0
Sign in
to vote
Type: Suggestion
ID: 374600
Opened: 10/10/2008 1:22:46 AM
Access Restriction: Public
1
Workaround(s)
view

Description

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.
Details
Sign in to post a comment.
Posted by Microsoft on 3/24/2011 at 4:58 PM
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
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 Microsoft on 11/4/2008 at 1:13 PM
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.
Posted by Harman Sahni on 10/22/2008 at 11:26 AM
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...
Posted by Microsoft on 10/20/2008 at 12:16 AM
Hello,

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

Regards,
Boris.
Sign in to post a workaround.
Posted by EuropaIce on 2/13/2014 at 10:20 PM
I too think it should appear. The workaround is to use various sources and coalesce. We can use the dbid from sys.exec_query_plan, and the dbid from sys.dm_exec_plan_attributes where the attribute = 'dbid', and lastly my failsafe is to pull in the @database attribute out of the plan itself. Indeed I have seen it all, the plan attribute is NULL and the sys.dm_exec_query_plan dbid is null, but it is not likely that the query plan XML does not have the database attribute somewhere in it. If multiple databases are in a plan, this gets the first one found. Here's some SQL to get the top 10 high CPU queries and their database (of course you could also group by query_hash or query_plan_hash for better aggregations):

select top 10 total_elapsed_time,
    coalesce(db_name(cast(pa.value as int)), replace(replace(qp.query_plan.value('(//@Database)[1]', 'nvarchar(255)'), '[', ''), ']', ''), db_name(qp.dbid)),
    qt.text
from sys.dm_exec_query_stats qs with(nolock)
cross apply sys.dm_exec_plan_attributes(qs.plan_handle) pa
cross apply sys.dm_exec_query_plan(qs.plan_handle) qp
cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt
where pa.attribute = 'dbid'
order by total_elapsed_time desc

The beauty of query_hash is that it is DB agnostic. Once you've found a rogue query, get its query_hash and find out the DBs that issue that query type and have high CPU:

select top 10 db, sum(total_elapsed_time)*1./sum(execution_count) avg_cpu
from (
    select total_elapsed_time, execution_count, coalesce(db_name(qp.dbid), qp.query_plan.value('(//@Database)[1]', 'nvarchar(255)')) db
    from sys.dm_exec_query_stats qs with(nolock)
    cross apply sys.dm_exec_query_plan(qs.plan_handle) qp
    where qs.query_hash = 0x812A24FD274A5AB8
) x
group by db
order by 2 desc