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 «««23456»»

Identifying Queries Running Slower Than Normal Expand / Collapse
Author
Message
Posted Wednesday, August 13, 2008 12:09 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, May 9, 2011 2:56 AM
Points: 131, Visits: 22
I m getting ther error on the line
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) qt

while creating the Proc
The error is

Msg 102, Level 15, State 1, Procedure dba_QueryTimeDelta1, Line 49
Incorrect syntax near '.'.

Please Guide

Thanks

Akash

Post #551615
Posted Wednesday, August 13, 2008 10:50 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 8:12 AM
Points: 10, Visits: 101
This being a proc makes it less usable than it could be. Here is a version that is a view. As a view it can be sorted as necessary and the deviation can be change at invocation as well. I think i moved it around and it is still accurate.

CREATE VIEW LastExecutionQueryStatsComparedToAvg
AS
SELECT [Runs]
, [Avg time]
, [Last time]
, [Time Deviation]
, [% Time Deviation]
, [Last IO]
, [Avg IO]
, [IO Deviation] = [Last IO] - [Avg IO]
, [Individual Query]
, [Parent Query]
, [DatabaseName]
, [Last Execution Time]
, [% IO Deviation] =
CASE WHEN ([Total IO] - [Last IO]) / (Runs - 1) = 0 THEN 0
ELSE ([Last IO]- (([Total IO] - [Last IO])
/ (Runs - 1))) * 100 / ([Total IO] - [Last IO])
/ (Runs - 1)
END
, [Impedance] = [% Time Deviation] -
CASE WHEN ([Total IO] - [Last IO]) / (Runs - 1) = 0 THEN 0
ELSE ([Last IO]- (([Total IO] - [Last IO])
/ (Runs - 1))) * 100 / ([Total IO] - [Last IO])
/ (Runs - 1)
END
FROM (
SELECT
qs.execution_count Runs
, [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)))
, [Total IO] = (total_logical_reads + total_logical_writes + total_physical_reads)
, [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)
, [Last Execution Time] = qs.last_execution_time
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) qt
WHERE qs.execution_count > 1
) SlowQueries


Post #552103
Posted Wednesday, August 13, 2008 1:16 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, September 22, 2014 8:23 AM
Points: 338, Visits: 1,429
Akash Agarwal (8/13/2008)
I m getting ther error on the line
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) qt

while creating the Proc
The error is

Msg 102, Level 15, State 1, Procedure dba_QueryTimeDelta1, Line 49
Incorrect syntax near '.'.

Please Guide

Thanks

Akash



Are you using SQL server 2005?
It won't work on lower versions.
Post #552220
Posted Thursday, August 14, 2008 2:32 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 28, 2011 1:33 AM
Points: 21, Visits: 41
Thanks for the code, Ian. Really useful.
Post #552485
Posted Thursday, August 14, 2008 3:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 5, 2013 3:23 AM
Points: 4, Visits: 37
Hi, Very useful script thanks. We're just looking at the results table. Any chance of an explanation of what all the values mean?

I know it's sorted by impedance by default, but I'm not really sure what this means! It would be useful to know what is considered a high/bad value so we can investigate further.
Post #552513
Posted Thursday, August 14, 2008 4:14 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 12, 2014 7:11 AM
Points: 48, Visits: 843
Hi Andy,

Impedance is a measure of how slow the query is performing in terms of CPU usage, taking into account the amount of IO used. Sorting the results on Impedance will show you which queries are running relatively most slowly.

The results show which queries are relatively worse, i.e. a query that averages 4ms and the last run was 20ms will be ranked higher than a query that takes 5000ms on average but the last run was 6000ms (the former query is relatively slower but not absolutely).

Maybe you’re only interested in queries that take longer than an absolute amount of CPU usage. In this case you would need to change the first query to sort by [Last Time].

Hope this helps
Ian

Post #552534
Posted Thursday, August 14, 2008 4:38 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 5, 2013 3:23 AM
Points: 4, Visits: 37
Many thanks Ian. That certainly does help!
Post #552550
Posted Thursday, August 14, 2008 6:27 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, November 9, 2012 1:39 PM
Points: 22, Visits: 166
Unable to vote for some reason, so I wanted to let you know that I thought this was a great article.

Thanks!
Post #552610
Posted Thursday, August 14, 2008 7:00 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, August 18, 2014 10:09 PM
Points: 72, Visits: 686
I'm using SQL 2005. This script does not work for me. I get:
Msg 102, Level 15, State 1, Procedure dba_QueryTimeDelta, Line 49
Incorrect syntax near '.'.
That occurs at CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) qt

I've never used cross apply before.
Post #552634
Posted Thursday, August 14, 2008 8:02 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 7:05 AM
Points: 16, Visits: 126
All, I had to add an End to the code. For some reason it didn't appear in the code window. The compiles fine for me after this change.

Cheers,

Sean
Post #552708
« Prev Topic | Next Topic »

Add to briefcase «««23456»»

Permissions Expand / Collapse