December 20, 2013 at 2:16 pm
hello i have a cluster sql 2008 with two active nodes. I have a sql 2008 r2 sp2 instance installed in each node. I have total of 8 processors. a couple of questions, when i right click instance ,i see number of processors 8, does that mean is 8 for both nodes?, meaning the 8 processors are shared in the cluster? At this time i have the max degree of parellism set to two for each of the instances, should i leave as is or should i change to 1? we are having performance issues with these instances, just slowness overall and timeouts. This is for a SCOM instance, does anyone have experience with scom servers? i was doing my research and for biztalk the recommendation is to have the max level of parellism to 1.
December 20, 2013 at 4:19 pm
Based on what you're saying, I'm pretty sure it's 8 for each machine, not shared. Unless you're running active/active and the instances are both on one server. Then, it's shared between them.
While SCOM might suggest setting MAXDOP to 1, instead, I'd set it to either 0 or... 7 (reserve one for the OS), but, change the cost threshold for parallelism from the default value of 5. Set it to something like 40 or 50 and see how your parallelism issues are then.
As to slowness, this may or may not be a factor. You'll need to monitor and gather metrics to understand what's going on with your system. The most important, to start with, are wait statistics. What resources are most in contention is easily identify by looking at sys.dm_os_wait_stats and ordering by wait time there.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 17, 2014 at 8:05 am
Thank you!
January 17, 2014 at 8:26 am
SCOM and biztalk are not the same thing. I have never seen a recommendation to to set maxdop to 1 for SCOM, so I would not do that.
SCOM is an extremely heavy data cruncher, tends to be a memory and I/O hog . Best thing to check is you are only gathering the stats you need to and at sensible intervals, else you can flood the database.
---------------------------------------------------------------------
January 27, 2014 at 8:40 pm
thank you George, would you happen to know any good best practices documentation for SCOM sql instances?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply