Friday, 8 August 2008

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'


No comments:

There was an error in this gadget