Poor Performing SQL Server Queries

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Performance Tuning » Poor Performing SQL Server Queries

Poor Performing SQL Server Queries

Rate Topic

Display Mode

Topic Options

Author

Message

Minnu

Posted Tuesday, February 12, 2013 2:44 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 7:30 AM
Points: 124, Visits: 371

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

Post #1418815

GilaMonster

Posted Tuesday, February 12, 2013 2:45 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:30 AM
Points: 37,742, Visits: 30,021

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 2008, MVP
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

Post #1418816

peterdru401

Posted Tuesday, February 12, 2013 4:26 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, April 16, 2013 8:34 AM
Points: 23, Visits: 128

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

Post #1418865

« Prev Topic | Next Topic »

Permissions