Optimize me!

Finally, yesterday I decided to actually read one of the newsletters I get flooded with by SqlServerCentral and then I discovered a really good tsql script to help with identifying slow queries.
The original post is here but as with everything I like I have made a few small changes and would like to post it so I can find it quickly when I need it. Oh, and I might read these newsletters a bit more often from now on ;)

Please note: The code below will only work on Sql Server 2005 or above .

USE master

GO



IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[dba_QueryTimeDelta]') AND type in (N'P', N'PC'))

EXEC sys.sp_executesql N'CREATE PROCEDURE [dbo].[dba_QueryTimeDelta] AS SELECT ''this is just temp. please replace with actual code.'''

GO





ALTER PROC [dbo].[dba_QueryTimeDelta]

@DatabaseName nvarchar(255) = ''



AS

/*---------------------------------------------------------------------- 

Purpose: Identify queries that are running slower than normal , when taking into account IO volumes.

------------------------------------------------------------------------ 

Parameters: @DatabaseName, optional



Revision History:

13/01/2008 Ian_Stirk@yahoo.com Initial version

08/08/2008 plamen.balkanski.net Added filtering by Database name



Example Usage:

1. exec YourServerName.master.dbo.dba_QueryTimeDelta

2. exec master.dbo.dba_QueryTimeDelta 'AllItems20080808'



----------------------------------------------------------------------*/



BEGIN



-- Do not lock anything, and do not get held up by any locks. 

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

-- Identify queries running slower than normal. 

SELECT TOP 100 [Runs] = qs.execution_count

            , [Total time] = qs.total_worker_time - qs.last_worker_time

            , [Avg time] = (qs.total_worker_time - qs.last_worker_time) /(qs.execution_count - 1)

            , [Last time] = qs.last_worker_time

            , [Time Deviation] = (qs.last_worker_time - ((qs.total_worker_time - qs.last_worker_time) /(qs.execution_count - 1)))

            , [% Time Deviation] = CASE WHEN qs.last_worker_time = 0 THEN 100 ELSE (qs.last_worker_time - ((qs.total_worker_time - qs.last_worker_time) /(qs.execution_count - 1)))* 100 END /(((qs.total_worker_time - qs.last_worker_time) /(qs.execution_count - 1)))

            , [Last IO] = last_logical_reads + last_logical_writes + last_physical_reads , [Avg IO] = ((total_logical_reads + total_logical_writes + total_physical_reads) - (last_logical_reads + last_logical_writes + last_physical_reads)) / (qs.execution_count - 1)

            , [Individual Query] = 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)

            , [Parent Query] = qt.text

            , [DatabaseName] = DB_NAME(qt.dbid)

INTO #SlowQueries

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) qt

WHERE qs.execution_count > 1

    AND DB_NAME(qt.dbid) = CASE WHEN @DatabaseName='' THEN DB_NAME(qt.dbid) ELSE @DatabaseName END

ORDER BY [% Time Deviation] DESC



            -- Calculate the [IO Deviation] and [% IO Deviation]. 

            -- Negative values means we did less I/O than average. 

SELECT TOP 100 [Runs] ,

                [Avg time] ,

                [Last time] ,

                [Time Deviation] ,

                [% Time Deviation] ,

                [Last IO] ,

                [Avg IO] ,

                [IO Deviation] = [Last IO] - [Avg IO] ,

                [% IO Deviation] = CASE WHEN [Avg IO] = 0 THEN 0 ELSE ([Last IO]- [Avg IO]) * 100 / [Avg IO] END ,

                [Individual Query] ,

                [Parent Query] ,

                [DatabaseName]

INTO #SlowQueriesByIO

FROM #SlowQueries

ORDER BY [% Time Deviation] DESC

-- Extract items where [% Time deviation] less [% IO deviation] is 'large' 

-- These queries are slow running, even when we take into account IO deviation. 



SELECT TOP 100 [Runs] ,

        [Avg time] ,

        [Last time] ,

        [Time Deviation] ,

        [% Time Deviation] ,

        [Last IO] ,

        [Avg IO] ,

        [IO Deviation] ,

        [% IO Deviation] ,

        [Impedance] = [% Time Deviation] - [% IO Deviation] ,

        [Individual Query] ,

        [Parent Query] ,

        [DatabaseName]

FROM #SlowQueriesByIO

WHERE [% Time Deviation] - [% IO Deviation] > 20

ORDER BY [Impedance] DESC



-- Tidy up. 

DROP TABLE #SlowQueries

DROP TABLE #SlowQueriesByIO



END



-- Test script

-- EXEC master.dbo.dba_QueryTimeDelta 'AllItems20080808'