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

Question

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

892717952

SSC Eights!

Points: 843
More actions
December 3, 2021 at 7:08 am

#3959863
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 2 years, 11 months ago by 892717952.
December 3, 2021 at 7:39 pm

This was removed by the editor as SPAM

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

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

Grant Fritchey SSC Guru Points: 398878 More actions · Answer 1

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

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1004302 More actions · Answer 2

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)

892717952 SSC Eights! Points: 843 More actions · Answer 3

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

Jeff Moden SSC Guru Points: 1004302 More actions · Answer 4

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)