Updated 25 Feb 2023
Ever wish alerts were like a wad of cash?
The more you solve, the more you make!
How about performance counter data?
The SQL management packs are awesome for visualizations, and provide a bunch of data.
Tim McFadden pointed out SQL Performance counters https://www.scom2k7.com/crazy-db-performance-collection-rules-in-the-sql-mps/
His blog brings up SQL MP Disk Latency performance counters.
His blog got me thinking about SQL DB and DB file design, where multiple DB files are on the same Drive, causes duplicate performance counters (SCOM workflows) on the agent, and will typically be one of the culprits for HealthService restarts.
SQL MP creates performance counters (per DB file, group, instance, engine)
Let’s start with how I figured out why all my money goes into storage.
Start in the SCOM console
Click on the Reporting Tab
Click on the ‘System Center Core Monitoring Reports’ folder
Double click on the Data Volume by Management Pack
Select the timeframe (from, to)
Click Run
Reporting Data
I have 2 2016 DB’s and 1 2014 (SCVMM) database server monitored, and it’s 50% of my data volume!
Another example – had the DW shutdown for days
Did you know there are 60+ perf counter rules in 2012 alone, and nearly 200 in 2016?
How about an OFF pack, a management pack that turns off all the performance counter rules?
The monitors still exist for health, just no pretty performance graph, should you look.
Github repo link
Check out the Gallery post for download
TechNet gallery download
Zip file contains
- OFF MP’s for 2008,2012,2014,2016
- XLS sheets to allow you to go to the SQL team and ask them what performance counters they use