Update December 9th 2014: Based on some good and valid feedback from PFE Microsoft Germany I’ve decided to update this posting with their advice and experiences from the field:
The bigger your SCOM environment gets, the better it is to test different MDoP (MAXDOP) settings. Simply because the MDOP Calculator mentioned in this posting, might not always give you the best MDoP values for SCOM. Also in virtualized environments many other factors play an important role as well. For instance, PFE Germany bumped into an environment with 5000+ SCOM Agents. The recommended MDoP setting was 8 but as it turned out, a reduction to 4 for MDoP worked better.
So: TEST YOURSELF BEFORE YOU WRECK YOURSELF.
A BIG word of thanks to Microsoft Germany PFE for their valid feedback and their willingness to share their own experiences from the field.
Issue
A dead slow SCOM 2012x Console. No matter what we did, there was only some minor improvement. And yes I know. The SCOM 2012x Console isn’t speedy at all. It’s by design apparently 
But in this particular case it was dead slow. Even for SCOM 2012x it was totally unacceptable. So even when tuning SQL and the related SCOM Operations database, temp databases and disks to the max as the environment allowed for, the SCOM 2012x Console became a bit faster. But still no good and still frustrating.
So it was time for some deeper investigation and searching on the internet. And suddenly I bumped into this blog posting written by S.Carrilho, all about "mDOPING" your SCOM Console performance with some simple SQL tips.
And as it turned out, the change of speed is AWESOME! Let me first try to explain what MDoP is and does.
Time to meet Max Degree of Parallelism (MDoP)
MDoP = When SQL Server runs on a server with more than one processor or CPU, it will try to find the best level of parallelism by itself. Parallelism is the number of processors used to run a single statement, for each query which has a parallel execution plan.
So far so good.
Out of the box will SQL Server detect by itself what level of MDoP to use. HOWEVER, on servers using hyper threading this won't work out too well. Simply because SQL Server thinks there are more processors/CPUs present than there actually are. This happens on physical and virtual servers alike as long as hyper threading is enabled.
In cases like these it's better to set that value by yourself, for instance by using this MAXDOP Calculator. Otherwise SQL Server will use all the available processors it thinks it has resulting in serious performance issues…
Example
In this example I’ll use the MAXDOP Calculator on my test environment.
- Out of the box MDoP is set to zero so SQL Server can decide for itself what the best setting is. Right click on your SQL Server instance in SQL Server Management Studio > Advanced: > Max Degree of Parallelism:
- However, the VM running SQL Server uses hyper threading, so the MDoP setting SQL Server chooses for itself isn’t that good at all:
- Time to use the MAXDOP Calculator:
First I run this SQL query: SELECT COUNT(DISTINCT memory_node_id) AS NUMA_Nodes FROM sys.dm_os_memory_clerks WHERE memory_node_id!=64. And this is what the query returned:
Then I run this PS cmdlet: Get-WmiObject -namespace "root\CIMV2" -class Win32_Processor -Property NumberOfCores | select NumberOfCores. And this is the output it returns:
Let’s copy & paste that information into the MAXDOP Calculator and see what it returns: - So in this particular case MDoP needs to be set to three.
This can be done by using the query as described in KB899000 OR you can use the interface itself as shown in Step 1. Simply change the value for Max Degree of Parallelism and click on OK.
NO REBOOT REQUIRED NOR A RESTART OF SQL SERVER SERVICES!!!
It’s applied right away after you clicked OK: - In the case of this customer the speed difference is HUGE! (Mind you, their MDoP setting is different than the one in this posting but I don’t share customer information) No, the SCOM Console hasn’t become a speed monster, but right after this setting was applied, people came to us asking what we had done since ALL OF THE SUDDEN THE SCOM CONSOLE BECAME WORKABLE!!!
- So we went ahead and changed MDoP on all SQL Servers used by SCOM. And still the differences are HUGE!
Recap
Never I heard of MDoP before not how hyper threading can throw SQL Server of it’s track when setting MDoP for itself. When using dedicated SQL Server instances for SCOM it certainly pays of to check the MDoP setting which is currently used and compare it by what the MAXDOP Calculator tells you is best.
When working with dedicated SQL admins, keep them in the loop and – even better – in the lead. And for this customers I’ve noticed some awesome improvements. From dead slow to a workable situation. Still no Ferrari but the current SCOM Console will never get to that level.
Perhaps this setting can be applied for other System Center components as well. Until now I haven’t seen that modifying the MDoP setting had a bad influence. But always check it with the SQL experts 
.
Resources
These are the resources I’ve found all about MDoP, in general and related to SCOM and other SC components:
1 comment:
Thanks for this post, it makes the console much faster
Post a Comment