Poorly executing Queries.

  • 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?

    -Forum Etiquette: How to post Performance Problems[/url]

    -Forum Etiquette: How to post data/code to get the best help [/url]

  • The easiest way to get this kind of info is to install the SQL Server performance dashboard. You can get it here:

    http://www.microsoft.com/downloads/details.aspx?FamilyId=1d3a4a0d-7e0c-4730-8204-e419218c1efc&displaylang=en

    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]

  • 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...

    -Forum Etiquette: How to post Performance Problems[/url]

    -Forum Etiquette: How to post data/code to get the best help [/url]

  • 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]

  • Thanks a lot !! I will just try and let you know..

    -Forum Etiquette: How to post Performance Problems[/url]

    -Forum Etiquette: How to post data/code to get the best help [/url]

  • Check out the SQL Server 2005 Performance Dashboard Reports

    http://www.microsoft.com/downloads/details.aspx?FamilyID=1d3a4a0d-7e0c-4730-8204-e419218c1efc&displaylang=en

    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.

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Gail seems to be right..I executed this query and it seems to involving only those whos plan is in the cache.

    -Forum Etiquette: How to post Performance Problems[/url]

    -Forum Etiquette: How to post data/code to get the best help [/url]

Viewing 8 posts - 1 through 7 (of 7 total)

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