"mDOPING" your SCOM Console performance with some simple SQL tips

  • Article

 

As many other monitoring solutions System Center Operations Manager (SCOM) is no different in terms of the workload its console has to handle.

   

However as we seek for proactive answers when using the console is not nice if it tends to be slow, right?

   

This blog is not meant to cover all of things we must look at to avoid constraints in terms of performance. (I'll cover why I blogged about this but if you prefer just jump into the guidelines section further down.)

We pretty much all know that would related to configurations, load balancing and specs of course…

   

But how about if your specs are VERY good? I mean VERY GOOD indeed…?

   

And let's say you think you have done everything you could to make things faster! Well maybe not…

   

Years ago I started to additionally highly recommend the adjustment of two important aspects on the SQL Backend supporting the SCOM environment, being:

  • Max Degree of Parallelism (MDOP)
  • Number and size of TempDB files

   

My best advise here is: give it a go because bottom line the aim is to improve performance of your current scenario.

   

Please keep in mind that there are environments other than SCOM which might suffer if changing the MDOP setting!

Just have a look on this example: https://support.microsoft.com/kb/899000

   

Also, I don't mean to say that it would solve all your performance problems.

However last SCOM scenario I faced on one of my customers made me think: I need to do a blog post on this!

   

So;

   

Scenario:

SCOM 2012 with 3 Physical Management Servers

Physical SQL 2008 R2 Backend with 3 instances - 48 cores, 256 GB RAM and State of the Art storage capable of 120000 IOPS (was told it was measured… yes!)

  • Ops DB (~ 81 GB memory limit)
  • Ops DW (~ 81 GB memory limit)
  • ACS (~ 81 GB memory limit)

   

Console was: SLOW!

   

What was changed:

MDOP was set to 12 (1/4 of total cores)

Temp DB Data files set to 8 files (all with same size)

   

BANG! WoW what a difference!

   

Again: I don't mean to say that it would solve all your performance problems however as long as the systems are capable you should at least notice an improvement.

   

Guidelines and more info:

   

  • Which rules I usually follow when tuning this specific SQL settings on a SCOM environment?

       

# Cores

MDOP Setting

Comments

1

0

  

2

1

Not very useful in this scenario.

4

2

  

8

4

  

> 8

1/4 of total

  

   

# Cores

TempDB DATA Files

TOTAL DATA Files Size

Temp DB LOG Files

TOTAL LOG Files Size

1

1

>= 20% Largest DB on the instance

1

>= 20% TempDB DATA total

2

2

>= 20% Largest DB on the instance

1

>= 20% TempDB DATA total

4

4

>= 20% Largest DB on the instance

1

>= 20% TempDB DATA total

8

8

>= 20% Largest DB on the instance

1

>= 20% TempDB DATA total

> 8

8

>= 20% Largest DB on the instance

1

>= 20% TempDB DATA total

   

Hope this helps!