SQL MP bloat


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.

 

 

Check out the Gallery post for download

Gallery Download https://gallery.technet.microsoft.com/SCOM-SQL-Performance-Rule-0354ea38

 

Zip file contains

  1.  OFF MP's for 2008,2012,2014,2016
  2. XLS sheets to allow you to go to the SQL team and ask them what performance counters they use

 

 

Comments (0)

Skip to main content