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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy