August 3, 2009 at 8:40 am
Hi friends
I wanted to know how MAXDOP works,in detail.
My system contains 32GB RAM, 8CPU & DOP is set to 0(Default)
now,
If i m having select query which runs continuously (from multiple users )
which increases waits on server
now my doubt is ,if i m supplying 8 CPUs for those quries,logically it should execute quickly.
if MAXDOP is set to 4, how will the query performance be better than the prev. settings,
even if it is taking lesser no. of CPUs?
i want detailed ans, if possible with example.
Plzzzzzzzz reply on this
Sanket Ahir
Don't run behind the success, Try to be eligible & success will run behind u......
August 3, 2009 at 1:31 pm
Queries don't automatically use multiple processors to run. MAXDOP defines a limit on how many processors can be used, not on how many will be used. SQL Server also uses the cost threshold for parallelism to determine if a query will be run in a parallel manner. The default value for this 5. That means that a query with a cost estimate of 5 or great can be a candidate for parallel execution. But even then, the query engine itself can decide whether or not it wants to implement a query as a parallel process based on the load that the system is currently under. Usually, parallel processing is pretty expensive. It's only good when you're move large amounts of data.
Are you sure your slow performance is caused by the CPU? Have you monitored the wait states and queues on the system to determine where the slow points are? If not, that's where you should start. If a particular query is running slow, post it here and people can help to tune it.
"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
August 3, 2009 at 10:27 pm
Hi Grant ,
i m having some reports regarding this issue. can i send u ?that will give u the clear idea about this issue.
kindly provide ur email id if possible.
Sanket Ahir
Don't run behind the success, Try to be eligible & success will run behind u......
August 3, 2009 at 10:31 pm
Hi Grant ,
i m having some reportsattached here regarding this issue. that will give u the clear idea about this issue.
reply on that.
Sanket Ahir
Don't run behind the success, Try to be eligible & success will run behind u......
August 3, 2009 at 10:32 pm
If you have specific issues, send them here.
There can be a cost to pulling all that data back together from multiple processors, so the optimizer tries to make an efficient decision. Also, parallelism is for one query. If you have multiple queries or a workload, the optmizer might decide that it wants to use some CPUs to service other queries.
The number of threads that can be spawned out to process things also matters. a SELECT * from MyHugeTable will not run faster with mutliple processors. You are pulling all data from a table, usually with a single I/O thread, so there's no benefit here.
August 4, 2009 at 12:01 am
Hi All,
plz analyse those reports and revert on this.
if anything is required from my side plz tell.
Sanket Ahir
Don't run behind the success, Try to be eligible & success will run behind u......
August 5, 2009 at 5:48 am
I can't support .rar compression files at work (don't ask). Can you zip the files?
"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
August 5, 2009 at 8:33 am
Grant Fritchey (8/5/2009)
I can't support .rar compression files at work (don't ask). Can you zip the files?
Grant, aren't you supposed to be doing WORK at work?? 😀
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 5, 2009 at 8:35 am
Using multiple CPUs will NOT speed up a query, and can actually make it run SLOWER, if your IO subsytem cannot supply data to the CPUs fast enough (which MANY cannot). Do a search on CXPACKET wait sql server 2005. The default settings for maxdop, cost threshhold for parallelism are suboptimal for 98+% of the sql server installations I have ever come across in my dozen years of working with sql server.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 5, 2009 at 9:23 am
TheSQLGuru (8/5/2009)
Grant Fritchey (8/5/2009)
I can't support .rar compression files at work (don't ask). Can you zip the files?Grant, aren't you supposed to be doing WORK at work?? 😀
SSHHHHHH!
"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
August 6, 2009 at 7:23 am
TheSQLGuru (8/5/2009)
Grant Fritchey (8/5/2009)
I can't support .rar compression files at work (don't ask). Can you zip the files?Grant, aren't you supposed to be doing WORK at work?? 😀
I'd call this "training".
Parallelism allows the query optimiser to consider using more than one microprocessor simultaneously to execute a statement. However, many statements, simple ones particularly, don't benefit from having more than one processor involved. For one thing, hard disk may be the bottleneck. In a multi-user environment, you may do better to balance user demand on a one-processor-per-customer basis (although eight users probably don't need eight processors: a user application spends most of its time waiting for the user, not querying). I assume that a parellelised plan will run the parts of the plan on one processor sequentially if there is only one not busy. Some versions of query optimiser get distracted by parallelism and ignore an efficient execution plan that you can find by setting MAXDOP=1 as a session or global property or query hint. And there are many bug fixes in SQL Server service packs relating only to MAXDOP>1, and those are the ones that they found. By excluding parallelism, you probably avoid SQL Server bugs that you don't know about. And since SQL Server is fairly robust, you assume that the bug is in your program, so you spend an awful amount of time trying to find that.
August 6, 2009 at 8:17 am
>> I assume that a parellelised plan will run the parts of the plan on one processor sequentially if there is only one not busy.
I don't think this is true. Investigate CXPACKET waits, which are exactly for threads that are wanting to do parallel work on a CPU but which are waiting for that work (most often for IO to arrive).
>>Some versions of query optimiser get distracted by parallelism and ignore an efficient execution plan that you can find by setting MAXDOP=1 as a session or global property or query hint.
Nor this. There is no 'distraction' in the optimizer. It is just plain multiplication and addition to get a net cost per plan that is appropriate, then lowest cost is chosen. serial (i.e. plans involving just one 'processor') are considered in all cases, whether or not MAXDOP is forced by some mechanism. This is not to say that a suboptimal plan may be considered as the lowest cost and picked because there is something unfortunate for the optimizer, such as skewed data distribution, stale statistics, UDF or table variable usage or other things that cause bad estimates.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 9, 2009 at 11:28 am
hi grant find the attached file
Sanket Ahir
Don't run behind the success, Try to be eligible & success will run behind u......
August 9, 2009 at 11:56 am
I just took a quick glance at your reports. You have a high percentage of parallelism waits - in fact, that is the highest waits you have on this system.
Now, if this system is more of a reporting system (i.e. data warehouse) then that might not be a problem. However, if the system is more of a transactional system (i.e. application OLTP) then this definitely could be a problem.
If you are running an OLTP to support an application, I would recommend changing the max degree of parallelism setting to no more than 2, and possible even 1 depending on your system. Setting to 1 disables parallelism completely - so I would not set that until I was sure that is the fix.
I would also reset the cost threshold for parallelism to at least 50 and possibly even higher.
These are just recommendations and should not be blindly accepted. You need to read up on the parameters and understand what they are doing before just blindly making the changes. If you decide to make the changes, be prepared to monitor the system and to change the settings back if you see a drop in performance.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 9, 2009 at 5:57 pm
Actually if it is an OLTP system the single most important thing to improve performance (part of which will come from reduced CXPACKET waits) is to have optimal indexing. Second is to have efficient queries. Without those two, raising CTFP and reducing MAXDOP can easily result in much longer runtimes.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply