September 14, 2016 at 6:35 pm
Hi all,
Hoping to find some help in getting this query to work. I'm trying to merge/join two queries to obtain cached sql relating to a specific database in the past N time. The following returns results but with a null Database_Name but hoping for some help to fix the query and help my understanding of the issue.
SELECT plan_handle,last_execution_time, DB_NAME(dbid) Database_Name,total_rows, dest.text
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
WHERE deqs.last_execution_time >= DATEADD(HOUR,(-24),GETDATE())
and plan_handle in (Select plan_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)
--and DB_NAME(dbid)= ('MyDB')
order by 1
To clarify the issue more, here's essentially where i've been led to refer to this "known issue,won't fix" but there might be a workaround.
Thanks in advance
-RR
September 14, 2016 at 11:05 pm
dbid makes sense only for DDL queries - CREATE PROC, ALTER VIEW, etc.
For "adhoc" DML queries you're asking for there is no certain database they can be mapped to.
A single join may have all the tables in different databases, including system ones (most usual case - tempdb).
Therefore dbid for this kind of queries is returned as NULL.
_____________
Code for TallyGenerator
September 14, 2016 at 11:19 pm
Thank you for your reply. Perhaps I'm not being clear, There should be a way to get from the cache a list of queries from a specific database and return stats (executions, elapsed time, etc) from that unique plan_handle. Since the DB_ID returns null, we have to join with another view which can provide this db_id (the sub-select). I'm only looking for DML statements and will filter for long running or high resource utilization queries.
September 14, 2016 at 11:47 pm
Actually in the topic you referenced Boris from MSFT provided a query which returns dbid's for cached plans.
But you have to remember - some dbid's returned by it are odd.
And you may have the same sql_handle mapped to different dbid's.
I modified it a bit in order to see the queries and their stats:
SELECT deqs.last_execution_time, pvt.plan_handle, pvt.dbid, pvt.sql_handle, DB_NAME(CONVERT(INT, pvt.dbid)), dest.text
FROM (
SELECT cp.plan_handle, epa.attribute, epa.value--, epa.is_cache_key
FROM sys.dm_exec_cached_plans cp
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
CROSS APPLY sys.dm_exec_sql_text(CONVERT(varbinary(8000), pvt.sql_handle)) AS dest
LEFT JOIN sys.dm_exec_query_stats AS deqs ON deqs.plan_handle = pvt.plan_handle
ORDER BY deqs.last_execution_time desc, pvt.sql_handle;
_____________
Code for TallyGenerator
September 14, 2016 at 11:59 pm
I can make this work, thank you for your help.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply