Post reply

Need to check which query is taking longer

  • November 19, 2014 at 7:58 am

    #292575

    I have alloy inventory software using SQL 2008 database, I see it is slow,

    Just need to know which query is taking longer,

    Help is appreciated,

    Thanks in Advance,

    Bubby

  • November 19, 2014 at 9:21 am

    #1759563

    You can try this query:

    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

  • November 19, 2014 at 10:57 am

    #1759583

    How long will be the run?

  • November 20, 2014 at 1:25 am

    #1759735

    for me, basically instant

  • November 20, 2014 at 1:35 am

    #1759739

    The DMV above just shows averages of what's in the plan cache, not exact executions of each query. That may be good enough, it may not be.

    Have a read through this. It's a little out of date, but still valid for SQL 2008

    https://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

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

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