Simpler ways to add additonal built-in performance counter metrics?
PeterDanielsCRB
Posts: 126 Bronze 3
SQL Monitor 11.2.11.14502
I am familiar with custom metrics and have added a few. However, I often just come back to, "It should be simpler than this". For example, I wanted to look at "SQLServer BufferManager: Lazy Writes/sec" in the Analysis page. It's not part of the stock metrics, so I started to consider a custom metric. The, I got lost in the docs for perf counters:
https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-os-performance-counters-transact-sql?view=sql-server-ver15
Trying to figure out the best way to work with a counter like Lazy Writes/sec as a custom metric was difficult for me. It has a cntr_type of 272696576. Docs say:
"Performance counters where the
What would be awesome is a simple UI in SQL Monitor that allows me to choose additional counters and is smart enough to collect or display the new metric the best way for different cntr_type values. As I type, I'm guessing this might be more of a user voice topic, but I will go ahead and post here in case I'm missing something simple.
Thank you for all you do!
-Peter
I am familiar with custom metrics and have added a few. However, I often just come back to, "It should be simpler than this". For example, I wanted to look at "SQLServer BufferManager: Lazy Writes/sec" in the Analysis page. It's not part of the stock metrics, so I started to consider a custom metric. The, I got lost in the docs for perf counters:
https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-os-performance-counters-transact-sql?view=sql-server-ver15
Trying to figure out the best way to work with a counter like Lazy Writes/sec as a custom metric was difficult for me. It has a cntr_type of 272696576. Docs say:
"Performance counters where the
cntr_type column value is 272696320 or 272696576 display the average number of operations completed during each second of the sample interval. Counters of this type measure time in ticks of the system clock. For example, to get a snapshot-like reading of the last second only for the Buffer Manager:Lazy writes/sec and Buffer Manager:Checkpoint pages/sec counters, you must compare the delta between two collection points that are one second apart."What would be awesome is a simple UI in SQL Monitor that allows me to choose additional counters and is smart enough to collect or display the new metric the best way for different cntr_type values. As I type, I'm guessing this might be more of a user voice topic, but I will go ahead and post here in case I'm missing something simple.
Thank you for all you do!
-Peter
Tagged:
Answers
Hi Peter,
Thanks for reaching out on the Redgate forums and providing your ideas regarding SQL Monitor custom metrics.
You are certainly on the right path that your simple UI concepts are great suggestion for UserVoice
Having a look I can see some existing suggestions which might align with your thoughts, it would be worth adding comments to outline the benefits this type of enhancement would provide to your organisation
UserVoice - SQL Monitor - Add Additional Perfmon Counters
Discussing within some of the technical team it was highlighted that one possible angle with your request could be using the 'Use rate of change' option rather than a returned value however the caveat with that would be the narrowest interval would be a minute.
Outside of this it may be possible to create a table to track things outside of the custom metric and then use the metric to sample that table.
I will take a closer look at the "Use rate of change" option. Was not aware of that...thank you!
Regarding your final sentence, I also have considered some more complex custom efforts to sample these additional metrics in a way that SQL Monitor could more easily consume, but it just seems like that is a bit of a hack.
Thanks for all you do!
-Peter