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

Posted on 2012-09-05
Last Modified: 2012-09-06
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!
Question by:sqlagent007
    LVL 19

    Accepted Solution

    Hi sqlagent007,

    Try this one:

    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
    s.max_elapsed_time DESC

    Open in new window

    You try to sort by other columns, for example total_physical_reads
    LVL 1

    Author Closing Comment


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
    In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now