Post reply

Finding Which query is the CPU hog

  • March 20, 2009 at 9:53 am

    #384174

    Sometimes we find that sqlserver is using 95% of the CPU for an extended period of time. Assuming that a t-sql query is the source of the issue (it always has been in our case, usually indexes) what approach would you use to determine the offending query? (I don't know the query or the database it is running in. All I know is that the CPU is 95% pergged.)

    TIA,

    barkingdog

  • March 20, 2009 at 10:10 am

    #963642

    it's in BOL under one of the DMV's. there should be one about CPU

    i have it in my email, but can't find it in my code archive. PSS sent it to me a few years back

  • March 20, 2009 at 6:13 pm

    #963874

    Thanks, I will look into it.

    Barkingdog

  • March 21, 2009 at 2:59 am

    #963943

    It's not necessarily one query. It may be that there are a number of queries all using excessive CPU.

    Check sys.dm_exec_requests to see what's currently running and what cpu they've useds. Note, that's cumulative since the connection logged in, so a high CPU count may not be a problem.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 21, 2009 at 9:43 am

    #963985

    Sounds like a good one to trace using sql trace procs or profiler. Put results into a table, or better yet, use RML to view what's happening.

  • March 21, 2009 at 10:39 am

    #963994

    Lee,

    What is RML?

    Barkingdog

  • March 21, 2009 at 8:23 pm

    #964058

    Oh sorry. It's the Replay Markup Utilities for SQL Server. It's the PSS diag written by Ken Henderson but most up to date version, more or less. It can look at and summarize trace replay files and render the results in handy reports. Here's a couple of links.

    http://support.microsoft.com/kb/944837

    http://blogs.msdn.com/psssql/archive/2007/12/18/rml-utilities-for-microsoft-sql-server-released.aspx

    To the original question - remember that when analyzing queries you need to look at the hogs and the queries that are hogs due to the cumulative CPU, disk, and memory that they consume. For instance, a borderline acceptable query or sp running once an hour isn't an issue. If it runs 200x/minute it could become a hog simply by the velocity or number of times that it runs.

  • March 22, 2009 at 10:46 am

    #964140

    Thank you, Lee.

    Barkingdog

Viewing 8 posts - 1 through 7 (of 7 total)

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