Post reply

Logging or monitoring slow query

  • March 2, 2012 at 5:55 am

    #391707

    Hello!

    How can i logging or monitoring the slow queries on the SQL Server 2005 and SQL Server 2008?

    Thanks

  • March 2, 2012 at 6:07 am

    #1454111

    you would want to build a query up which looks at sys.dm_exec_requests and cross applies it to sys.dm_exec_sql_text where the sys.dm_exec_requests.total_elapsed_time is greater than the number of seconds what you qualify as a slow query and also doesnt show any system spids

  • March 5, 2012 at 9:10 am

    #1454860

    Thanks! Now I know monitoring the queries.

    But how to know I logging the slow queries (Examlpe the time of query bigger then 5 seconds)?

  • March 5, 2012 at 9:28 am

    #1454871

    WHERE sys.dm_exec_requests.total_elapsed_time > 5

    Jared
    CE - Microsoft

  • March 5, 2012 at 9:51 am

    #1454888

    you would also want to exclude any spid below 50 so that no system processes are picked up as well which could take longer than X seconds

  • March 5, 2012 at 1:27 pm

    #1454987

    Here's what I use (includes everything that has already been mentioned), I adapted it from an article I read here on SSCCREATE PROC [dbo].[utl_SeeWhatsExecuting]

    @Duration int = NULL

    AS

    /*

    exec utl_SeeWhatsExecuting 5000

    */

    BEGIN

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

    IF @Duration IS NULL

    SET @Duration = 0

    SELECT

    session_id [Spid],

    total_elapsed_time [Duration],

    DB_NAME(sp.dbid) [DB],

    nt_username [User],

    er.status [Status],

    wait_type [Wait],

    SUBSTRING (qt.text,

    er.statement_start_offset/2,

    (CASE WHEN er.statement_end_offset = -1

    THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2

    ELSE er.statement_end_offset END -

    er.statement_start_offset)/2) [Currently Executing],

    qt.text [Calling Code],

    program_name [Program],

    Hostname [Host],

    nt_domain [Domain],

    start_time [Started]

    FROM sys.dm_exec_requests er

    INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid

    CROSS APPLY sys.dm_exec_sql_text(er.sql_handle)as qt

    WHERE session_Id > 50

    AND session_Id NOT IN (@@SPID)

    AND total_elapsed_time > @Duration

    ORDER BY 1, 2

    END

    ______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply