November 6, 2013 at 3:49 pm
I have sql queries which return around 40-50 millions records for loading information into Reporting database. I was trying to look at the server settings and noticed a MAXDOP of 0 (which means we are allowing sql server to decide how many CPU's are needed to run the query). In what cases do we generally set a particular value for MAXDOP?? Does setting a value of MAXDOP create sql query blocks as we are removing parallel processing feature??
P.S: sql queries are being executed from DTS package's and there is lot of parallel processing involved in ETL operation
November 6, 2013 at 11:51 pm
Set MAXDOP to the total number of cores you have (don't count hyperthreading). If you have 24 cores (4 CPU/Sockets times 6 cores), then set MAXDOP to 24.
/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!
November 7, 2013 at 2:15 am
The current recommendation is to set it to the number of cores per NUMA node (if equipped), 8 for servers with 8 or more processors, 0-N for servers with less than 8.
http://support.microsoft.com/kb/2806535
As always though, this is just a recommendation and I would take the recommended MAXDOP as a maximum.
This could work to see how many NUMA nodes you have to divide your cores by.
SELECT MAX(c.memory_node_id) + 1
FROM sys.dm_os_memory_clerks c
WHERE memory_node_id < 64
November 7, 2013 at 4:07 am
The other recommendations for MAXDOP are good. You can also leave it at zero if you're not experiencing any particular CPU pressure.
But, the one thing I would suggest changing on any system is to modify the Cost Threshold for Parallelism value. It's 5 by default and that is radically too low. I'd suggest setting it up to 35 and seeing how it affects performance from there. Friends of mine suggest 50 and then monitor from there. Pick a number, but 5 is way too low.
"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
November 8, 2013 at 2:55 pm
Grant Fritchey (11/7/2013)
The other recommendations for MAXDOP are good. You can also leave it at zero if you're not experiencing any particular CPU pressure.But, the one thing I would suggest changing on any system is to modify the Cost Threshold for Parallelism value. It's 5 by default and that is radically too low. I'd suggest setting it up to 35 and seeing how it affects performance from there. Friends of mine suggest 50 and then monitor from there. Pick a number, but 5 is way too low.
+1 on Cost Threshold for Parallelism.
As far as selecting a MAXDOP value, you can either follow rule of thumb advice like that above (which makes sense), or if it's important enough, pull up Profiler, and watch SQL:Batchcompleted event CPU, Read, Write, and Duration columns, and test a set of MAXDOP values on whatever system will actually run the query. I usually try a sequence of values of 0, 1, 2, 4, and if 4 was better* than 2 I keep going to 8, 16, etc. until I get an average worse* result. If it's critical, then repeat the test within the better* range until you get the best* result.
Bear in mind that precise selections can only be made on the hardware you're actually using, and you do need to collect multiple samples of each one (all normal benchmarking advice applies, including don't worry about it if it's not important enough to you).
*better - Whatever tradeoff you find desirable between CPU, reads, writes, and duration.
*worse - not better*
*best - the better* value you find most desirable among those you have.
November 8, 2013 at 3:49 pm
Grant Fritchey (11/7/2013)
The other recommendations for MAXDOP are good. You can also leave it at zero if you're not experiencing any particular CPU pressure.But, the one thing I would suggest changing on any system is to modify the Cost Threshold for Parallelism value. It's 5 by default and that is radically too low. I'd suggest setting it up to 35 and seeing how it affects performance from there. Friends of mine suggest 50 and then monitor from there. Pick a number, but 5 is way too low.
+1
I usually adjust that setting to 35 and have toyed with the idea of starting even higher and then adjusting from there.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply