Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Identifying Queries Running Slower Than Normal Expand / Collapse
Author
Message
Posted Thursday, August 7, 2008 3:25 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 12, 2008 8:25 AM
Points: 8, Visits: 43
I get [NULL] in 'DataBaseName' column
SQL:[2005]

Otherwise v.useful.tool
Post #548104
Posted Thursday, August 7, 2008 3:33 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 2:21 AM
Points: 271, Visits: 722
Hi Ian

Can we have similar kinda query/SP for SQL 2000.

Also can you brief us about DMV.

Thanks

Post #548111
Posted Thursday, August 7, 2008 3:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 22, 2013 1:55 AM
Points: 4, Visits: 78
Copying the SQL directly from the Word document worked for me and it's very useful. Thanks!
Post #548127
Posted Thursday, August 7, 2008 5:55 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, August 23, 2010 2:33 AM
Points: 96, Visits: 62
Is there an equivalent for SQL Server 2000?
Post #548199
Posted Thursday, August 7, 2008 6:14 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, August 7, 2008 7:06 AM
Points: 27, Visits: 5
Somebody wake me up when the proc is useable.
Post #548213
Posted Thursday, August 7, 2008 6:26 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, August 1, 2014 9:56 AM
Points: 338, Visits: 1,422
Here's one with divide by zero error removed and indentation:

------------------------------------------------------------------------
-- Purpose: Identify queries that are running slower than normal,
-- when taking into account IO volumes.
--------------------------------------------------------------------------
ALTER PROC [dbo].[dba_QueryTimeDelta]
AS
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 / NullIf((((qs.total_worker_time - qs.last_worker_time) / (qs.execution_count - 1))), 0),
[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
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
GO
Post #548230
Posted Thursday, August 7, 2008 6:50 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, August 21, 2014 3:12 AM
Points: 48, Visits: 839
Hi,

Nice to hear some of you are enjoying the tool.

If the database column value is NULL, it means someone is running ad hoc or prepared SQL statements (i.e. it is not a stored proc).

You can discover all about the wonderful world of DMVs here:
http://msdn.microsoft.com/en-gb/magazine/cc135978.aspx

Unfortunately there is no SQL Server 2000 equivalent of DMVs.

Ian
Post #548243
Posted Thursday, August 7, 2008 6:55 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 2:21 AM
Points: 271, Visits: 722
Thanks for the response.

Is there something that can work in SQL 2000.

I just need to find sp/query running slow in 2k
Post #548247
Posted Thursday, August 7, 2008 7:01 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, August 21, 2014 3:12 AM
Points: 48, Visits: 839
Hi,

For SQL Server 2000, you can use SQL Server Trace (that contains a Duration column) to determine what SQL is running slowly (this is different from this article, which is about what is running unusually slowly).

Lots of info on the Internet about this...

Ian
Post #548254
Posted Thursday, August 7, 2008 7:05 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, August 14, 2014 4:26 PM
Points: 462, Visits: 368
The Code posted is tough but the attached Word document is very good. I think they just cut&pasted the Word text into the post and that just does not work :)
The real value is the discussion in the Word document which would make a much better article than the posted code.
Ian has done a nice job providing insight into a very useful DMV that I have not looked at before. I do have one caution on the subject. The Average and delta that Ian calculates are a fairly simple view of performance change. The technique will not give an accurate indication of changes for queries that tend to have a highly variable runtime. For example assume a stored procedure that checks a queue for work. 3 or 4 times in 5 it finds nothing to do and runs in less than a tick. Occassionally it finds work and takes 10 to 15 ms. In a case like this the Average is misleading.
Ian's queries provide a nice starting point for monitoring queries, but you really need to add some historical and trend data for each query to get the whole picture.
Thanks Ian for highlighting the DMV for me.
Post #548257
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse