January 7, 2011 at 6:47 am
It seems like I often get performance problems from the server using multiple processors.
Does anyone have a book recommendation for writing T-SQL to be "multiple processor"-safe?
Besides always using MAXDOP 1, I mean. I want to use multiple processors to increase performance, but I want to avoid starvation and deadlocks.
January 7, 2011 at 6:49 am
You can modify the parallelism threshold in the SQL Server properties. Take a look at the data on that in MSDN/BOL.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 7, 2011 at 7:16 am
You don't want to always run MAXDOP 1 or MAXDOP 0. There may be queries that cause issues on your system and need modification, but it probably won't be a global fix. As mentioned above, you can modify the threshhold, and then in places use the query hint to prevent issues, but since this can be workload and query dependent, you want to experiment on your system, working on "problem" queries as they come up.
January 7, 2011 at 7:51 am
I am a huge fan of upping the cost threshold for parallelism. I'd set it higher, a lot higher, than the default for most OLTP systems I manage. 35 is a good starting point, see how things go.
"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 10, 2011 at 11:16 am
As the Grant, Steve and GSquared have mentioned you can enable parallelism at Server Level or at query level.
MAX Degree of Parallelism is not recommended for OLTP Systems until and unless there are some very specific requirements. It cause 100% CPU utilization peaks for very short periods of time and also result in CX_PACKET Waits and leads to blocking.
But if you think, individual queries need parallelism, then use the MAXDOP query hint.
Grant's advice of increasing the threshold is a very good starting point to be on a safe side.
Thank You,
Best Regards,
SQLBuddy
January 11, 2011 at 2:18 am
sqlbuddy123 (1/10/2011)
MAX Degree of Parallelism is not recommended for OLTP Systems until and unless there are some very specific requirements. It cause 100% CPU utilization peaks for very short periods of time and also result in CX_PACKET Waits and leads to blocking.But if you think, individual queries need parallelism, then use the MAXDOP query hint.
Can somebody clarify something for me? (I really shouldn't read the forums at 9:00, it's just too early...:doze:)
I was under the impression that MAXDOP was the limit set for parallelism (without amending the threshold as mentioned above) so that using MAXDOP stops the parallelism and the CXPACKET waits, not causes them. It's the parallelism that causes the CXPACKET waits?
And if individual queries needed to use parallelism, you would avoid the MAXDOP query hint?
It's just that I hit a few issues towards the end of last year with a VERY slow running query and it turned out that the issue was parallelism and CXPACKET waits, problem was resolved by reading this forum and adding MAXDOP (it was a single query at fault) so these comments confused me somewhat.
January 11, 2011 at 5:47 am
SequelSurfer (1/11/2011)
sqlbuddy123 (1/10/2011)
MAX Degree of Parallelism is not recommended for OLTP Systems until and unless there are some very specific requirements. It cause 100% CPU utilization peaks for very short periods of time and also result in CX_PACKET Waits and leads to blocking.But if you think, individual queries need parallelism, then use the MAXDOP query hint.
Can somebody clarify something for me? (I really shouldn't read the forums at 9:00, it's just too early...:doze:)
I was under the impression that MAXDOP was the limit set for parallelism (without amending the threshold as mentioned above) so that using MAXDOP stops the parallelism and the CXPACKET waits, not causes them. It's the parallelism that causes the CXPACKET waits?
And if individual queries needed to use parallelism, you would avoid the MAXDOP query hint?
It's just that I hit a few issues towards the end of last year with a VERY slow running query and it turned out that the issue was parallelism and CXPACKET waits, problem was resolved by reading this forum and adding MAXDOP (it was a single query at fault) so these comments confused me somewhat.
Yes, setting MAXDOP to 1 would eliminate parallelism and CXPACKET waits. MAXDOP can be set to values other than 1, so setting it to 2 on a 2 or more CPU system will enable parallelism.
Also, for most of my OLTP systems, I simply set the threshold for parallelism to something very high, like 30 or 35. Then we only see it in queries that can actually benefit (usually).
"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 11, 2011 at 8:04 am
before you go adjusting ANYTHING, you need to find out EXACTLY what the symptoms are!! Do a file IO stall and wait stats analysis. Then base decisions on the outcome of that.
You mention deadlocks. Usually index (as in missing) related, but bad code and bad design can be culprits too. Review these three blog posts: http://blogs.msdn.com/bartd/archive/2006/09/09/Deadlock-Troubleshooting_2C00_-Part-1.aspx
Consider getting a performance tuning professional on board for a few hours to investigate your system and mentor you. win-win-win
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply