InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 20:23:54 GMT20http://www.sqlservercentral.com/Forums/Topic1418815-360-1.aspxCheck out the below threads to find out the poor performing query [url]http://stackoverflow.com/questions/2499910/how-to-find-the-worst-performing-queries-in-sql-server-2008[/url][url]http://msdn.microsoft.com/en-us/magazine/cc135978.aspx[/url]Tue, 12 Feb 2013 04:26:15 GMTpeterdru401http://www.sqlservercentral.com/Forums/Topic1418815-360-1.aspx[url]http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/[/url][url]http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/[/url]Tue, 12 Feb 2013 02:45:04 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1418815-360-1.aspxHi 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 qpWHERE 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 > 1000ORDER 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 DESCTue, 12 Feb 2013 02:44:04 GMTMinnu