Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Identifying Queries Running Slower Than Normal


Identifying Queries Running Slower Than Normal

Author
Message
Akash Agarwal
Akash Agarwal
SSC-Enthusiastic
SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)

Group: General Forum Members
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
dan.holmes
dan.holmes
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 137
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
Jonathan AC Roberts
Jonathan AC Roberts
SSC-Addicted
SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)

Group: General Forum Members
Points: 430 Visits: 1836
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.
adicks
adicks
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 41
Thanks for the code, Ian. Really useful.
Andy Holliday
Andy Holliday
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
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.
ianstirk
ianstirk
SSC Journeyman
SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)

Group: General Forum Members
Points: 98 Visits: 1037
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
Andy Holliday
Andy Holliday
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 37
Many thanks Ian. That certainly does help!
sql60190
sql60190
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
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!
Question Guy
Question Guy
SSC Journeyman
SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)

Group: General Forum Members
Points: 90 Visits: 763
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.
Sean Woehrle
Sean Woehrle
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 155
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 :-D

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search