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
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
View of SCOM report from console reporting tab
Select the timeframe (from, to)
Data Volume MP selected
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
Data volume of SQL after
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.
Check out the Gallery post for 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