January 28, 2009 at 11:32 am
I'm sure I just don't know the proper terminology to search for some usage history... at least when they were last run, but more information would be helpful such as how often.
Thanks very much in advance 🙂
January 31, 2009 at 7:21 am
You can find some information based on Query Plans in sys.dm_exec_query_stats. Check out this link: http://msdn.microsoft.com/en-us/library/ms189741.aspx.
You can also use a server-side trace to track usage.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 1, 2009 at 7:30 pm
As Jack said you can query sys_dm_exec_query_stats which will give you last execution time.
If would like to get the SQL text run and the table used use the dmv sys.dm_exec_sql_text.
According to books online:
Provides information about CPU time, IO reads and writes, and number of executions for the top five queries by average CPU time
SELECT TOP 5
total_worker_time/execution_count AS [Avg CPU Time],
(SELECT SUBSTRING(text,statement_start_offset/2,(CASE WHEN statement_end_offset = -1 then LEN(CONVERT(nvarchar(max), text)) * 2 ELSE statement_end_offset end -statement_start_offset)/2) FROM sys.dm_exec_sql_text(sql_handle)) AS query_text
FROM sys.dm_exec_query_stats
ORDER BY [Avg CPU Time] DESC
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply