Identifying Queries Running Slower Than Normal

  • 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

  • Many thanks Ian. That certainly does help!

  • Unable to vote for some reason, so I wanted to let you know that I thought this was a great article.

    Thanks!

  • 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.

  • 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]

  • I had to add

    END

    GO

    to the end of the code too. It appears to be missing on the web page.

  • 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

  • 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

  • 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

  • For a SQL 2000 tool google sp_who_3 written by Mike Barzilli.

  • 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

  • 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 58 (of 58 total)

You must be logged in to reply to this topic. Login to reply