June 12, 2009 at 6:50 am
Guys,
I had a situation where the queries were taking a while to run in Production.
Upon further investigation I found that the data file drive was being pounded - the disk queue length was hovering around 123 for a single user.
At the same time in the active monitor I could see lot of waits of type PAGEIOLATCH_SH. The MAX Degree of Parallelism was set to 0, I had to change it to 1 and the performance improved dramatically, the PAGEIOLATCH_SH waits also decreased. the CPU of the production box dual quadcore with 16GB and SQL using 2GB RAM, both are 32bit. However I am little concerned now that MAXDOP has changed to 1 which means it is using one CPU to query the DB for an OLTP envrionment, I am not sure if this is enough but it is performing as supposed to default MAXDOP = 0.
Any suggestions and inputs will help.
Thanks
June 12, 2009 at 7:20 am
Interesting - I just went through this same process myself last week, but I did it a little differently becuase I was concerned about the same thing. Instead of setting MaxDop to 1, I set it to 2, and then I raised the Cost Threshold of Parallelism to 80. Its a little high, but as I tested, I noticed queries still hitting the MaxDop level that I didn't think should. The other step I took was to include OPTION (MAXDOP 1) for certain queries, which sets the limit to 1.
To me, it seems that MaxDop of 1 might be the way to go, but I am taking it slowly and watching the performance. But performance has gotten WAY better since I changed it to 2 and cranked up the Cost Threshold, so I'm already happy. If you check out the waitstats and cleared them before you started on MaxDop 1, you will definitely see the difference.
Randy
June 12, 2009 at 7:31 am
doesn't maxdop=0 means use all available processors? or am i missing something here?
June 12, 2009 at 7:38 am
Yes it does. It turns out that that's not necessarily the best thing to do. In fact, even on a machine with 16 processors, the MaxDop would be better set to 8.
The way I think about it is like traffic. If you have four lanes and everyone is allowed to take up all four lanes, then nobody else can go anywhere. They get all backed up and have to wait for the one that's there to finish (CXPACKET wait). But if you force everyone to stay in their own lane, 4 people can drive at the same time. If the driver in lane 1 takes a while, then there's still lanes 2 - 4. People can zip by in lane 4 so those little queries can get by fast. The report might be lumbering along on lane 1. If you let that report take all the lanes (MAXDOP 0) then everybody's gotta wait.
Randy
June 12, 2009 at 7:41 am
Thanks for the explanation 🙂
June 12, 2009 at 7:41 am
A maxdop of 0 will indeed use all the CPU's, there can be issues where it takes more resources to manage the CPU's than you'll actually get from using them (yes, a little strange). This is usually related to CX_Packet waits however.
If you are going to adjust your maxdop then you should certainly try different values and check the performance on each.
Give that you are running an OLTP environment and not doing a great deal of reporting a lower maxdop might be just what the doctor ordered
Have you cleared out the stats and run your processes followed by checking up on the sys.dm_os_wait_Stats dynamic management view, and seen what your primary wait is?
June 12, 2009 at 8:08 am
simsr (6/12/2009)
Interesting - I just went through this same process myself last week, but I did it a little differently becuase I was concerned about the same thing. Instead of setting MaxDop to 1, I set it to 2, and then I raised the Cost Threshold of Parallelism to 80. Its a little high, but as I tested, I noticed queries still hitting the MaxDop level that I didn't think should. The other step I took was to include OPTION (MAXDOP 1) for certain queries, which sets the limit to 1.To me, it seems that MaxDop of 1 might be the way to go, but I am taking it slowly and watching the performance. But performance has gotten WAY better since I changed it to 2 and cranked up the Cost Threshold, so I'm already happy. If you check out the waitstats and cleared them before you started on MaxDop 1, you will definitely see the difference.
What you're seeing is an artifact of the estimated cost values. SQL Server can't know how long a query is going to take to run, so the estimated cost is used to determine parallel execution (along with other factors). If you're estimates are way out of line from the actuals, you'll see more queries qualifying for parallel execution than should. It might be an indication of out of date statistics, or less than optimal queries...
"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
June 12, 2009 at 8:11 am
What you're seeing is an artifact of the estimated cost values. SQL Server can't know how long a query is going to take to run, so the estimated cost is used to determine parallel execution (along with other factors). If you're estimates are way out of line from the actuals, you'll see more queries qualifying for parallel execution than should. It might be an indication of out of date statistics, or less than optimal queries...
My bet is on the less than optimal queries. The database I work on isn't even close to normalized.
But that is an interesting point. If you have your Cost Threshold set to 0 and your MaxDop set to 0, isn't it extremely likely that many queries will take up the parallel execution? (Not sure if those are defaults, but thats how I found the databases I work on.)
Randy
June 12, 2009 at 8:20 am
Actually, MAXDOP = 1 is the Microsoft recommended setting for OLTP environments, especially heavily loaded ones.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 12, 2009 at 8:26 am
Just to clarify: MAXDOP (maximum degree of parallelism) does NOT restrict how many CPU cores a SQL Server instance can use at one time. Rather it only restricts how many CPU cores a single Request (a batch) can use at the same time.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 12, 2009 at 8:26 am
simsr (6/12/2009)
What you're seeing is an artifact of the estimated cost values. SQL Server can't know how long a query is going to take to run, so the estimated cost is used to determine parallel execution (along with other factors). If you're estimates are way out of line from the actuals, you'll see more queries qualifying for parallel execution than should. It might be an indication of out of date statistics, or less than optimal queries...
My bet is on the less than optimal queries. The database I work on isn't even close to normalized.
But that is an interesting point. If you have your Cost Threshold set to 0 and your MaxDop set to 0, isn't it extremely likely that many queries will take up the parallel execution? (Not sure if those are defaults, but thats how I found the databases I work on.)
The default cost threshold is 5, which is really, really WAY too low. I usually reset it to 25 on OLTP systems, just to start.
"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
June 12, 2009 at 9:41 am
The default cost threshold is 5, which is really, really WAY too low. I usually reset it to 25 on OLTP systems, just to start.
Oh, that's right - that's how mine was set too, now that you have me remembering. The cost threshold refers to an amount of time the query is expected to run, and 5 paired very nicely with the MaxDop of 0, i.e. "Help yourself, folks! Grab all you want!"
Randy
June 12, 2009 at 10:35 am
RBarryYoung (6/12/2009)
Actually, MAXDOP = 1 is the Microsoft recommended setting for OLTP environments, especially heavily loaded ones.
Hey Barry, If you start working with spatial data in 2008, you may find that having maxdop set to 1 is detrimental. I'm seeing real benefits from multiple cpu's in the spatial queries.
"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
June 12, 2009 at 10:44 am
Grant Fritchey (6/12/2009)
RBarryYoung (6/12/2009)
Actually, MAXDOP = 1 is the Microsoft recommended setting for OLTP environments, especially heavily loaded ones.Hey Barry, If you start working with spatial data in 2008, you may find that having maxdop set to 1 is detrimental. I'm seeing real benefits from multiple cpu's in the spatial queries.
Oh I use MAXDOP = 0 on all of MY stuff. 🙂 But it's different when your trying to get SQL Server to time-slice with a thousand other users.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 12, 2009 at 12:00 pm
We have a few of our queries that we had the option MAXDOP set to 8 in one of our servers (16 CPUs). These queries used to throw out scheduler blocking errors on its original setting default maxdop which is 0. A single process for the query generates 100+ threads, multiplied by a number of processes potentially running in parallel. All the work needed to run the query in parallel including merging them again together overshadowed the benefit of parallelism.
I found this article http://support.microsoft.com/kb/329204 though this is just a general guideline, it swayed me even more to use the setting. Thorough tests proved that my queries performed better with maxdop 8, or at least at par when it used to be 0, and we eliminated scheduler blocking errors.
_____________
Donn Policarpio
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply