August 14, 2008 at 4:14 am
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
August 14, 2008 at 4:38 am
Many thanks Ian. That certainly does help!
August 14, 2008 at 6:27 am
Unable to vote for some reason, so I wanted to let you know that I thought this was a great article.
Thanks!
August 14, 2008 at 7:00 am
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.
August 14, 2008 at 8:02 am
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.
[font="Tahoma"]Cheers,
Sean :-D[/font]
August 14, 2008 at 8:08 am
I had to add
END
GO
to the end of the code too. It appears to be missing on the web page.
August 14, 2008 at 8:14 am
END added to the code.
August 17, 2008 at 11:27 am
Hi,
from the feedback, I’m not sure if people have understood this article completely.
The article discusses how to identify queries that are running slower than normal i.e. ones that seem to have degraded in performance. It is not about finding slow queries per se.
If you want to discover which queries are taking the longest to run (in terms of duration or IO), look at the sections entitled “Costly Queries by CPU”, and “Costly Queries by I/O”, in my MSDN article here: http://msdn.microsoft.com/en-us/magazine/cc135978.aspx
Thanks
Ian
August 20, 2008 at 9:28 am
Good article, Ian. Ignore those ignorant enough to criticise without giving a reason. And to those who carp about bugs in the code, I say this: Debug it yourself! You'll learn by doing this. Incorrect syntax? Use Books Online to find out what the correct syntax is. Divide by zero error? Put in your own CASE statement to stop this. Carriage returns lost when copying and pasting? Put them back in. Honestly, whenever I post code I'm half tempted to put deliberate errors in it so that people don't just copy it and use it without thinking about it.
John
September 24, 2008 at 2:31 am
I created the proc and executed it. Got the following:
Msg 8134, Level 16, State 1, Procedure dba_QueryTimeDelta, Line 25
Divide by zero error encountered.
The statement has been terminated.
(0 row(s) affected)
(0 row(s) affected)
Any quick fixes available?
Nick
October 29, 2008 at 9:22 pm
For a SQL 2000 tool google sp_who_3 written by Mike Barzilli.
September 28, 2009 at 12:33 pm
Same here. I've ensured that the code pasted correctly, there are no bad characters and so on. But I'm also getting a divide by zero error - I'm assuming this in the first CASE statement, but I don't have time to debug that right now. Interesting concept, though.
Buck Woody
MCDBA, MCSE, Novell and Sun Certified
September 28, 2009 at 12:38 pm
Hi,
please see a previous discussion entry by jacroberts, he has the relevant code to prevent the divide by zero error.
thanks
Ian
Viewing 13 posts - 46 through 57 (of 57 total)
You must be logged in to reply to this topic. Login to reply