• WhiteLotus (8/24/2014)


    How do I know which query or StoreProc that has crap code because there are hundreds of StoreProc?

    You don't.

    This is one of the flaws at looking at just perfmon counters. Sure, work tables are 'high', but you haven't said if they're higher than normal or if they're causing problems (don't use the tools blindly).

    Performance analysis is never about one counter. There's no one counter or value that tells you everything. It's about looking at the whole system, perfmon counters, wait stats, query execution statistics and drawing conclusions from all of them together.

    The perfmon counters, with very few exceptions don't have good or bad thresholds. They have 'normal' and 'not normal' for your server. The wait stats tell you what, in general, SQL is spending time waiting for during query executions and the query stats tell you which are your most resource-intensive queries overall.

    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