Post reply

Poor Performing SQL Server Queries

  • February 12, 2013 at 2:44 am

    #289094

    Hi Team,

    Am using below query to get top 20 poor performing queries, finally i got the result set of 20 records,

    how to find that particular query is performing slow, what are the major things to be considered, can u please explan.

    SELECT TOP 20

    GETDATE() AS "Collection Date",

    qs.execution_count AS "Execution Count",

    SUBSTRING(qt.text,qs.statement_start_offset/2 +1,

    (CASE WHEN qs.statement_end_offset = -1

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

    ELSE qs.statement_end_offset END -

    qs.statement_start_offset)/2) AS "Query Text",

    DB_NAME(qt.dbid) AS "DB Name",

    qs.total_worker_time AS "Total CPU Time",

    qs.total_worker_time/qs.execution_count AS "Avg CPU Time (ms)",

    qs.total_physical_reads AS "Total Physical Reads",

    qs.total_physical_reads/qs.execution_count AS "Avg Physical Reads",

    qs.total_logical_reads AS "Total Logical Reads",

    qs.total_logical_reads/qs.execution_count AS "Avg Logical Reads",

    qs.total_logical_writes AS "Total Logical Writes",

    qs.total_logical_writes/qs.execution_count AS "Avg Logical Writes",

    qs.total_elapsed_time AS "Total Duration",

    qs.total_elapsed_time/qs.execution_count AS "Avg Duration (ms)",

    qp.query_plan AS "Plan"

    FROM sys.dm_exec_query_stats AS qs

    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt

    CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp

    WHERE

    qs.execution_count > 50 OR

    qs.total_worker_time/qs.execution_count > 100 OR

    qs.total_physical_reads/qs.execution_count > 1000 OR

    qs.total_logical_reads/qs.execution_count > 1000 OR

    qs.total_logical_writes/qs.execution_count > 1000 OR

    qs.total_elapsed_time/qs.execution_count > 1000

    ORDER BY

    qs.execution_count DESC,

    qs.total_elapsed_time/qs.execution_count DESC,

    qs.total_worker_time/qs.execution_count DESC,

    qs.total_physical_reads/qs.execution_count DESC,

    qs.total_logical_reads/qs.execution_count DESC,

    qs.total_logical_writes/qs.execution_count DESC

  • February 12, 2013 at 2:45 am

    #1586888

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

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

    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

  • February 12, 2013 at 4:26 am

    #1586927

    Check out the below threads to find out the poor performing query

    http://stackoverflow.com/questions/2499910/how-to-find-the-worst-performing-queries-in-sql-server-2008

    http://msdn.microsoft.com/en-us/magazine/cc135978.aspx

Viewing 3 posts - 1 through 2 (of 2 total)

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