September 18, 2014 at 10:14 am
Hello,
I just recently started at a new company as an application administrator with a specialization in SQL.
We are just finishing our migration to SQL 2012. In our old environment, the instance which held our SharePoint databases also served other applications. We did not experience any performance related issues in the past due to this.
SharePoint basically requires MAXDOP to be 1, which is correct on the old server. Since this configuration may not be ideal for other applications that may be put within our environment, we our entertaining the idea of isolating SharePoint into its own instance, probably on the same box.
My manager wants me to come up with performance trace data to better prove that we need to go this route since we apparently have had issues in the past by blindly following Microsoft's best practices.
1.MAXDOP configuration - I understand this may be a 2 pronged approach that would require looking at various execution plans and CPU related counters in Perfmon. SharePoint likely requires a maxdop of 1 due to the nature of the application (lots of concurrent processes). What is the best way to show this need graphically?
2. Memory configuration for multiple instances - Does the Total Server Memory reveal all the memory that a given SQL instance is utilizing? Should I use this counter to identify appropriate min/max memory configurations for multiple instances on a single cluster?
The problem with the perfmon approach is that it's scope is limited to just the server. Since our SharePoint environment is currently being shared with other applications, I understand that I may have to utilize DMV statistics to narrow down my analysis.
Thanks!
September 18, 2014 at 12:14 pm
As far as MAXDOP and SharePoint go, SharePoint 2013 resets MAXDOP to 1 if you change it, according to an MVP I know, so if you have applications that are used more for reporting than OLTP operations you should definitely isolate SharePoint to it's own instance.
For other instances I always start with the advice given in this KB Article. I've seen major performance problems on a server with 128 logical processors and MAXDOP set to 0 because the cost to partition and regather streams was more resource intensive thatn what was saved by parallelizing. Setting MAXDOP according the KB article and raising the Cost Threshold for Parallelism solved these performance issues.
For Max Memory does appear to be for an instance. Just be sure to make sure you leave enough memory for the OS, other application, and areas of SQL Server that are outside the buffer pool, like CLR.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 18, 2014 at 2:13 pm
Thanks for the advice. I think given the circumstances I will push towards an isolated SharePoint instance considering the amount of other applications that we support.
Regarding the optimization of MAXDOP and memory configuration, what perfmon counters would you use for analysis?
For memory, I'm sure setting the min and max memory around the average of Total Server Memory when tracing for a week, assuming that there is still plenty of overhead for the OS in the event that both instances reach max memory.
MAXDOP seems a bit more complicated, though. I would imagine I would have to baseline various performance counters on a per core basis. Do you have any advice as far as this goes?
We want to be as precise and thought out as possible with our configurations and would prefer to base it around empirical evidence. If the analysis is too much, we would then refer to Microsoft's general guidelines.
Nice bass btw..is that an American P-bass?
September 18, 2014 at 3:00 pm
Well, I'd start from the guidelines provided by MS and then tune from there instead of going the other way. For the most part I've found most of MS's recommendations to be a good starting point for performance. These recommendations are much better than the defaults in Setup (MAXDOP 0, Cost Threshold for Parallelism 5, Max Memory - all of it).
There are 2 blog posts that I refer people to for dealing with parallelism:
http://www.brentozar.com/archive/2013/09/five-sql-server-settings-to-change/
http://www.sqlskills.com/blogs/jonathan/tuning-cost-threshold-for-parallelism-from-the-plan-cache/
You'll want to look at wait statistics for parallelism especially CXPACKET waits.
I base Max Memory on a formula provided by Jonathan Kehayias in this blog post:
http://www.sqlskills.com/blogs/jonathan/how-much-memory-does-my-sql-server-actually-need/
He also mentions the counters he uses to determine if he can reduce or increase the max memory setting. You'll need to adjust to look at the counters for both instances if you have the SharePoint instance and the "application" instance on one physical(virtual) server.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply