February 27, 2012 at 2:10 pm
Some background:
We recently upgraded our CRM environment to CRM2011 and part of that was a move to SQL Server 2008 R2. This is from CRM 4.0 and SQL Server 2005.
Noting some performance issues and timeouts occurring in the system, we set the Max Degree of Parallelism to 1 for the SQL server, on the basis that that is what was done on the previous server (apparently on the advice of a consultant way back when that server was setup).
The results were positive; we stopped getting timeout problems and overall performance seemed to have improved.
The other day, on the advice of a new CRM consultant we split tempdb into four files (server has four processors) and set MAXDOP back to 0 for the server.
For the sake of testing we got the SQL script behind one of the reports that was constantly timing out in CRM, and it ran like this:
When tempdb was one file and MAXDOP was 0: 35 seconds; times out in CRM almost every time
When tempdb was one file and MAXDOP was 1: <1 second; never times out in CRM
When tempdb is four files and MAXDOP is 0: <1 second; times out about half the time
When tempdb is four files and MAXDOP is 1: 1 second; never times out in CRM
Now, the strange thing (to me anyway) is that with the four tempdb files, MAXDOP 0 seems to be faster in SQL Management Studio - but the report times out in CRM. Setting it to 1 makes it run slightly slower in Management Studio - but it doesn't time out.
As it stands right now, we're going to leave MAXDOP at 1 and maybe experiment with it later; but for now it seems to be running the best. However, I have never really gotten deep into the whole relationship between the tempdb and MAXDOP so I want to make sure we're not causing any problems...
So anyway, my questions are:
1. If MAXDOP is set to 1 as it is now, is there really any benefit to having four tempdb files? Will the server even use all four of them (consultant seems to think it will not)?
2. Is there any harm to having four tempdb files when the server MAXDOP is 1?
February 27, 2012 at 3:01 pm
cphite (2/27/2012)
1. If MAXDOP is set to 1 as it is now, is there really any benefit to having four tempdb files? Will the server even use all four of them (consultant seems to think it will not)?
Yes and yes. The idea behind files = cores is to 'affinitise' a file to a particular CPU so that you can't get contention on tempDB. MaxDop 1 doesn't mean that only one processor will ever be used. It means that a single query can't use more than one thread, you can still have 4 queries running concurrently.
Even without that, SQL uses a proportional fill algorithm for cases where there are multiple files in a filegroup (and that's with any DB), so any time there are multiple files in a single filegroup, all of the files will be used.
2. Is there any harm to having four tempdb files when the server MAXDOP is 1?
No.
Personally I'd be putting maxdop back to 0 and investigating what's timing out and why, what the queries are waiting for, what's causing them to run slow, not just forcefully limiting the parallel processing.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 29, 2012 at 4:05 pm
cphite (2/27/2012)
As it stands right now, we're going to leave MAXDOP at 1
you've just slapped the hand cuffs on every query executing on the server!
As Gail said, find out why the query causes the issue and resolve it.
You may also consider setting MAXDOP back to 0 and instead tuning the cost thresold for parallelism setting instead. The default is 5 secs, maybe try settings of 15 secs and upwards
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply