How to search back about 12 hours for issues in SQL 2005

I have a SQL 2005 instance that experienced some performance issues about 12 hours ago. The issues have just been brought to my attention now. Is there a DMV or something that I can use to go back in time and try to figure out the longest running queries during that time period? Or the highest IO, or something that may give me some insight as to what was happening.

Thanks experts!
LVL 1
sqlagent007Asked:
Who is Participating?
 
RimvisConnect With a Mentor Commented:
Hi sqlagent007,

Try this one:

SELECT DISTINCT TOP 10
t.TEXT QueryName,
s.execution_count AS ExecutionCount,
s.max_elapsed_time AS MaxElapsedTime,
ISNULL(s.total_elapsed_time / s.execution_count, 0) AS AvgElapsedTime,
s.creation_time AS LogCreatedOn,
ISNULL(s.execution_count / DATEDIFF(s, s.creation_time, GETDATE()), 0) AS FrequencyPerSec
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t
ORDER BY
s.max_elapsed_time DESC
GO 

Open in new window


You try to sort by other columns, for example total_physical_reads

http://blog.sqlauthority.com/2009/01/02/sql-server-2008-2005-find-longest-running-query-tsql/
0
 
sqlagent007Author Commented:
Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.