how to identify BottleNeck by using the data of sys.dm_os_performance_counters

  • There are many many data to show the SQL Server current performance, May I know how to use these counters to observe the SQL Server's performance?

    that is to say, which parameters show the IO bottleNeck and Disk bottleNeck?RAM bottleNeck etc ? it means the cntr_value exceed which value there will be a bottleneck, thanks!

    • This topic was modified 9 months, 4 weeks ago by  892717952.
  • These counters, by themselves, won't necessarily show a bottleneck. You need to capture the metrics over time so you have comparison points (x% yesterday, y% today, look, it's getting worse). Then, you need to combine them with other metrics like wait times. These metrics just give you counts. Here's how much RAM you're using. It doesn't say whether or not anything is waiting on getting RAM. How many IOPS are you performing? Is that good or bad? No way to know unless we know how many the disk(s) supports and whether or not there are waits on disks. It's not that these are not useful, but no one counter tells you the story you need. Instead, you have to capture a bunch of metrics, observe them over time, compare them to other metrics.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore Roosevelt
    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • This was removed by the editor as SPAM

  • If you do a search for "sys.dm_os_performance_counters", you'll find a ton of some pretty good articles on what to look for.  The trouble is, the counters are just reporting symptoms.  Not a one of them will lead you to the code that is causing you problems.  They're just labels on the neck of the bottle and no list of ingredients.  IMHO, looking at "wait stats" doesn't do much more than report symptoms, either.

    You can also setup alerts to email you and you can configure those emails to pop up on your screen.  They'll definitely let you know when you have excessive CPU usage, compiles, excessive file usage, reads/sec, writes/sec and a whole bunch of other things... and all of them are just symptoms.  They don't tell you anything about the cause.

    What you need to do is visit Brent Ozar's site and look at all the different flavors of sp_Blitz* code he has or Adam Mechanic's sp_WhoIsActive and have your alerts call those to save stuff you can go and look at in a table, which can normally include the code that may be causing the problem and, sometimes, even the estimated execution plan that goes with it.

    You can also use one of Grant's favorite methods of setting up some similar Extended Events to record the "high spots" in a table.

    You could also buy some monitoring software.

    But I wouldn't waste my time digging into "sys.dm_os_performance_counters".  To me, it's not even as useful as having a PerfMon chart running with a 2 second sample rate (total of a half hour of history).   It's like hearing an animal on your roof... you know there's an animal there but you don't know what kind it is.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Grant Fritchey and Jeff Moden, Thank you for your kind help ! much appreciated!

  • You're welcome and thank you for the feedback.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 6 posts - 1 through 6 (of 6 total)

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