• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 320
  • Last Modified:

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!
0
sqlagent007
Asked:
sqlagent007
1 Solution
 
RimvisCommented:
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now