November 25, 2008 at 1:40 am
HI, I want to write a script which can list down the top 10 poor queries on a SQL server.
I got many on Inet but they all are for currently running queries or the current state of the server.
I want it the way that once I executed it, it returned me top 10 from across the server (overall in I/O, elapsed time, memory etc...)
Hope, I convey my requirement. Can somebody help me Plz?
November 25, 2008 at 2:28 am
The easiest way to get this kind of info is to install the SQL Server performance dashboard. You can get it here:
As an alternative here are two queries from MS:
--- top 50 statements by IO
--- Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm
SELECT TOP 50
(qs.total_logical_reads + qs.total_logical_writes) /qs.execution_count as [Avg IO],
SUBSTRING(qt.text,qs.statement_start_offset/2,
(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,
qt.dbid, dbname=db_name(qt.dbid),
qt.objectid,
qs.sql_handle,
qs.plan_handle
FROM sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY
[Avg IO] DESC
--- top 50 statements by Avg CPU Time
SELECT TOP 50
qs.total_worker_time/qs.execution_count as [Avg CPU Time],
SUBSTRING(qt.text,qs.statement_start_offset/2,
(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,
qt.dbid, dbname=db_name(qt.dbid),
qt.objectid
FROM sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY
[Avg CPU Time] DESC
[font="Verdana"]Markus Bohse[/font]
November 25, 2008 at 2:48 am
HI Thanx for the reply..I still have one doubt..
when I execute this query how it will populate the data. will it consider all the existing queries on the server or just those queries running at the time when I executes this script...
November 25, 2008 at 2:53 am
Vishal Singh (11/25/2008)
HI Thanx for the reply..I still have one doubt..when I execute this query how it will populate the data. will it consider all the existing queries on the server or just those queries running at the time when I executes this script...
It will consider all queries which qan since the last time SQL Server started.
[font="Verdana"]Markus Bohse[/font]
November 25, 2008 at 2:57 am
November 26, 2008 at 7:51 am
Check out the SQL Server 2005 Performance Dashboard Reports
Those should easily answer most of your questions. After that it's time to really start digging, but these reports are my first stop when I'm looking for problem causers.
November 26, 2008 at 7:59 am
MarkusB (11/25/2008)
It will consider all queries which qan since the last time SQL Server started.
Not quite. It will consider all queries whose execution plans are still in cache. As soon as a query's plan is dropped from cache (due to a recompile, change in underlying schema, change in statistics or memory pressure) the query's stats are dropped as well.
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
November 27, 2008 at 9:20 pm
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply