Identifying Queries Running Slower Than Normal

  • ianstirk

    Ten Centuries

    Points: 1310

    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

    SSC Enthusiast

    Points: 174

    Many thanks Ian. That certainly does help!

  • sql60190

    SSC Veteran

    Points: 266

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

    Thanks!

  • Question Guy

    SSCommitted

    Points: 1930

    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

    Mr or Mrs. 500

    Points: 539

    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]

  • Andy Holliday

    SSC Enthusiast

    Points: 174

    I had to add

    END

    GO

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

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715809

  • ianstirk

    Ten Centuries

    Points: 1310

    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

  • John Mitchell-245523

    SSC Guru

    Points: 148306

    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

  • nick-889921

    Grasshopper

    Points: 13

    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

  • Troy Gatchell-386101

    SSC Rookie

    Points: 45

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

  • BuckWoody

    Ten Centuries

    Points: 1077

    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

  • ianstirk

    Ten Centuries

    Points: 1310

    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