June 15, 2015 at 9:40 am
How can we get most frequent queries that are running against to a table in our database?
June 15, 2015 at 11:39 am
You have a few options.
1. Setup a trace using profiler
2. Extended Events (I'm assuming your using 2008)
3. You can get some limited stats from DMV's i.e. sys.dm_exec_query_stats
Cheers,
June 15, 2015 at 12:14 pm
yb751 (6/15/2015)
You have a few options.1. Setup a trace using profiler
2. Extended Events (I'm assuming your using 2008)
3. You can get some limited stats from DMV's i.e. sys.dm_exec_query_stats
Cheers,
These are all valid options. I use #3. You can order by execution_count to get the most frequently used ones.
June 15, 2015 at 12:34 pm
Does this work?
SELECT TOP (100)
COALESCE(DB_NAME(ST.dbid),
DB_NAME(CAST(PA.value AS INT)) + '*', 'Resource') as [Database_Name]
,OBJECT_SCHEMA_NAME(ST.objectid,dbid) as [Schema_Name]
,OBJECT_NAME(ST.objectid,dbid) as [Object_Name]
,SUM(QS.execution_count) as [Execution_Count]
FROM sys.dm_exec_query_stats QS
JOIN sys.dm_exec_cached_plans CP ON QS.plan_handle = CP.plan_handle
CROSS APPLY sys.dm_exec_sql_text(QS.plan_handle) ST
OUTER APPLY sys.dm_exec_plan_attributes(QS.plan_handle) PA
WHERE --OBJECT_NAME(ST.objectid,dbid) LIKE 'asp_ciptr%'
text LIKE '%YOUR TABLE NAME%'
GROUP BY COALESCE(DB_NAME(ST.dbid),
DB_NAME(CAST(PA.value AS INT)) + '*', 'Resource')
,OBJECT_SCHEMA_NAME(ST.objectid,dbid)
,OBJECT_NAME(ST.objectid,dbid)
ORDER BY execution_count DESC
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/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply