August 4, 2021 at 6:07 pm
Yes, extended events is great for this if you're comfortable working with extended events and have the skills needed to programmatically process the extended events results data. A lot of people are not comfortable with doing both of those.
Totally understood. No arguments. However, there are a couple of things. The Live Data window lets you do a ton of data exploration, aggregations, grouping, filtering, no programming needed. I have a video on this and a bunch of posts on my blog. It won't let you do everything you could do if you went at the data programmatically, but it's pretty good. Alternatively, DBA Tools has a bunch of functionality that makes dealing with the XML much, much, easier. But, yeah, you'll have to get somewhat comfy with Powershell to make that work.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 4, 2021 at 7:27 pm
This may give you what you want, or get you close.
SELECT
MAX(QS.last_execution_time) LastExecution,
COUNT(*) ExecutionCount,
DB_NAME(ST.dbid) DatabaseName,
OBJECT_SCHEMA_NAME(ST.objectid, dbid) as [Schema_Name],
OBJECT_NAME(ST.objectid, dbid) as [Object_Name]
FROM sys.dm_exec_query_stats QS
CROSS APPLY sys.dm_exec_sql_text(QS.plan_handle) ST
WHERE ST.objectid > 0
GROUP BY
DB_NAME(ST.dbid),
OBJECT_SCHEMA_NAME(ST.objectid, dbid),
OBJECT_NAME(ST.objectid, dbid)
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
August 4, 2021 at 8:05 pm
This may give you what you want, or get you close.
SELECT
MAX(QS.last_execution_time) LastExecution,
COUNT(*) ExecutionCount,
DB_NAME(ST.dbid) DatabaseName,
OBJECT_SCHEMA_NAME(ST.objectid, dbid) as [Schema_Name],
OBJECT_NAME(ST.objectid, dbid) as [Object_Name]
FROM sys.dm_exec_query_stats QS
CROSS APPLY sys.dm_exec_sql_text(QS.plan_handle) ST
WHERE ST.objectid > 0
GROUP BY
DB_NAME(ST.dbid),
OBJECT_SCHEMA_NAME(ST.objectid, dbid),
OBJECT_NAME(ST.objectid, dbid)
Thanks, that looks useful. I just added an ORDER BY at the end.
ORDER BY DB_NAME(ST.dbid) , OBJECT_NAME(ST.objectid, dbid)
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply