Poor Performing SQL Server Queries

• Minnu

  SSCertifiable

  Points: 6451

  More actions

  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

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  Follow

  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

• peterdru401

  Mr or Mrs. 500

  Points: 535

  More actions

  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