Crazy DB Performance Collection rules in the SQL MPs

One of my customers was experiencing lots of growth in their OperationsManager DB.

They monitor hundreds of SQL servers. I had a look into their Top Tables using Kevin Holman’s Large table query. http://bit.ly/1REx9Os

Things looked pretty normal where Performance tables are the top tables

Table2

 

I drilled down a little further in the performance data and see this.

Tables

 

Focusing on the two top counters that are 4x larger then the next few.

I took a look at the counters for one of my SQL servers and realize that we are collecting the same counter over and over for each database instance on a SQL server.

Perfmon

 

This is crazy.  It might make some sense if a customer ran every database on a individual drive, but that is not the norm.  Most SQL servers have one or a few drives for their DB files and Logs.

To double check I looked at the OperationsManager database performance tables.

dbData

Sure enough we are collecting the same data 13 times in my case.

So how do we fix this?  Disable the rule that collects this data using an override for SQL 2005, 2008, 2012, and 2014.

Rule for SQL 2012 is called

  • MSSQL 2012: Collect DB Disk Write Latency (ms)
  • MSSQL 2012: Collect DB Disk Read Latency (ms)

Latench

 

 

 

disable

 

I then recommend collecting this data if you need it once per disk.

To enable it, create an override for Windows Server 2008 and 2012 Logical Disk

  • Collection Rule for Average Disk Seconds Per Write Windows Server 2012
  • Collection Rule for Average Disk Seconds Per Read Windows Server 2012

LogicalDisk

 

LogicalDiskTrue

Override True

 

 

One Response to Crazy DB Performance Collection rules in the SQL MPs

  1. Brett February 16, 2016 at 4:49 pm #

    Awesome article!
    I have servers with multiple instances with 50 plus databases per instance and all on the same drive.

Leave a Reply