May 16, 2012 at 3:45 pm
ChrisM@Work (5/16/2012)
Stewart "Arturius" Campbell (5/16/2012)
ChrisM@Work (5/16/2012)
The number specified in the max degree of parallelism server configuration option.This is overridden using the MAXDOP as part of the index creation command and should, therefore have been qualified by MS accordingly (e.g. where no MAXDOP hint exists in the relevant command, in which case this will be taken into consideration)
Not really. The BOL section I quoted above states in a roundabout kind of way that if the instance is set to MAXDOP = 1, then any maxdop setting, for any query or index rebuild, will be ignored.
Interesting. From this Books Online article:
MAXDOP number
Overrides the max degree of parallelism configuration option of sp_configure and Resource Governor for the query specifying this option. The MAXDOP query hint can exceed the value configured with sp_configure. If MAXDOP exceeds the value configured with Resource Governor, the Database Engine uses the Resource Governor MAXDOP value, described in ALTER WORKLOAD GROUP (Transact-SQL). All semantic rules used with the max degree of parallelism configuration option are applicable when you use the MAXDOP query hint. For more information, see max degree of parallelism Option.
I thought one of our options when combating poor performance related to over-parallelization on an instance used primarily for OLTP workloads that also handles some reporting duties is to change the MAXDOP server option to 1 and use Query Hints to set higher MAXDOP values for specific queries that benefit from parallelism.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 17, 2012 at 1:39 am
opc.three (5/16/2012)
ChrisM@Work (5/16/2012)
Stewart "Arturius" Campbell (5/16/2012)
ChrisM@Work (5/16/2012)
The number specified in the max degree of parallelism server configuration option.This is overridden using the MAXDOP as part of the index creation command and should, therefore have been qualified by MS accordingly (e.g. where no MAXDOP hint exists in the relevant command, in which case this will be taken into consideration)
Not really. The BOL section I quoted above states in a roundabout kind of way that if the instance is set to MAXDOP = 1, then any maxdop setting, for any query or index rebuild, will be ignored.
Interesting. From this Books Online article:
MAXDOP number
Overrides the max degree of parallelism configuration option of sp_configure and Resource Governor for the query specifying this option. The MAXDOP query hint can exceed the value configured with sp_configure. If MAXDOP exceeds the value configured with Resource Governor, the Database Engine uses the Resource Governor MAXDOP value, described in ALTER WORKLOAD GROUP (Transact-SQL). All semantic rules used with the max degree of parallelism configuration option are applicable when you use the MAXDOP query hint. For more information, see max degree of parallelism Option.
I thought one of our options when combating poor performance related to over-parallelization on an instance used primarily for OLTP workloads that also handles some reporting duties is to change the MAXDOP server option to 1 and use Query Hints to set higher MAXDOP values for specific queries that benefit from parallelism.
Interesting. Here's the section I quoted above. Note that it's specific to indexing operations.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 17, 2012 at 5:57 am
ChrisM@Work (5/17/2012)
Interesting. Here's the section I quoted above. Note that it's specific to indexing operations.
I can see where you would go there for indexes though you swept queries into your earlier assessment as well.
That same article says:
You can override the max degree of parallelism value for individual index operations by setting the MAXDOP index option in the CREATE INDEX, ALTER INDEX, DROP INDEX, and ALTER TABLE statements.
I think the article focuses too heavily on the scenario where one might want to restrict the number of CPUs used in an index operation. I would have liked it better if they specified more clearly how the query hint played into things but I think Kristian has confirmed he could get a parallel plan using a query hint when the instance setting is 1. SQL Server apparently picked a single CPU plan initially presumedly based on current load on the server, but a later attempt when load was lower yielded a parallel plan.
I think MAXDOP is in play when the instance setting is 1 for queries and index operations.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 17, 2012 at 6:14 am
opc.three (5/17/2012)
ChrisM@Work (5/17/2012)
Interesting. Here's the section I quoted above. Note that it's specific to indexing operations.I can see where you would go there for indexes though you swept queries into your earlier assessment as well.
That same article says:
You can override the max degree of parallelism value for individual index operations by setting the MAXDOP index option in the CREATE INDEX, ALTER INDEX, DROP INDEX, and ALTER TABLE statements.
I think the article focuses too heavily on the scenario where one might want to restrict the number of CPUs used in an index operation. I would have liked it better if they specified more clearly how the query hint played into things but I think Kristian has confirmed he could get a parallel plan using a query hint when the instance setting is 1. SQL Server apparently picked a single CPU plan initially presumedly based on current load on the server, but a later attempt when load was lower yielded a parallel plan.
I think MAXDOP is in play when the instance setting is 1 for queries
and index operations.
I'm still unconvinced about index operations - but I take your point about queries.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 17, 2012 at 6:16 am
The MAXDOP is to control parllelism degree but you can not have this value more than configured value.If you will have MAXDOP value more than configured value then it will use upto configured value. In You case server's maxdop is 1 .Thus you can not have any of your opertaion in parallel.
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
May 17, 2012 at 6:19 am
I did not have time to test it, i may later today, but I am referring to Kristian's post as to why I am thinking it pertains to index operations as well:
okbangas (5/16/2012)
A small update: The first index just finished, the second started off in parallel as it should just now. So, what I've learned from this situation: If your server happens to have (quite) a bit of load when the create index statement is started, it may choose to run in a single thread. In that case, it may be beneficial to cancel the index creation, wait a few seconds, and try again.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 17, 2012 at 6:23 am
okbangas (5/16/2012)
A small update: The first index just finished, the second started off in parallel as it should just now. So, what I've learned from this situation: If your server happens to have (quite) a bit of load when the create index statement is started, it may choose to run in a single thread. In that case, it may be beneficial to cancel the index creation, wait a few seconds, and try again.
Kristian, what is the MAXDOP configuration of the server? Is this the customer one from your first post?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 17, 2012 at 2:54 pm
Gullimeel (5/17/2012)
The MAXDOP is to control parllelism degree but you can not have this value more than configured value.If you will have MAXDOP value more than configured value then it will use upto configured value. In You case server's maxdop is 1 .Thus you can not have any of your opertaion in parallel.
This is wrong. The setting for the individual query overrides the instance setting, but it cannot override a resource governor setting.
May 17, 2012 at 2:57 pm
Kristian, what is the MAXDOP configuration of the server? Is this the customer one from your first post?
As stated in the first post, it is 1. However, this is overridden in the create index statement. And, as I said in a later post, this worked fine for the second index created. The issue was (most likely) that the server considered the load too high when the first index creation was started, and thus reduced the DOP down to 1 again.
May 18, 2012 at 6:48 am
okbangas (5/16/2012)
A small update: The first index just finished, the second started off in parallel as it should just now. So, what I've learned from this situation: If your server happens to have (quite) a bit of load when the create index statement is started, it may choose to run in a single thread. In that case, it may be beneficial to cancel the index creation, wait a few seconds, and try again.
It is my understanding that at EXECUTION time the engine can ALWAYS reduce parallelism for a given query/query plan due to at-run-time-server-load. And I agree with your "look at the index build query plan and cancel if not parallel" idea on a billion row table index build!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 18, 2012 at 3:36 pm
okbangas (5/17/2012)
The setting for the individual query overrides the instance setting, but it cannot override a resource governor setting.
That's exactly right. Interestingly (though not usefully!) you can tell the difference between an index build start starts off serial (because the load was high when the statement was compiled) versus the degree of parallelism being dynamically reduced to 1 (because the server is short on threads or memory) by running the statement with Actual Execution Plan on. In the first case, the plan will be serial (no Parallelism operators); in the second case, the Parallelism operators will be there, but the actual row counts will be zero, and only one thread processes rows between the operators. None of that really helps you, but I think it's interesting. We need a MINDOP hint 🙂
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 19, 2012 at 3:09 am
MINDOP : If i get you right you would like MINDOP to be used so that query will be execute only if it could use a parallel degree equal to or more than MINDOP.
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
May 19, 2012 at 4:20 am
Gullimeel (5/19/2012)
MINDOP : If i get you right you would like MINDOP to be used so that query will be execute only if it could use a parallel degree equal to or more than MINDOP.
Something along those lines, yes (though that comment was intended to be read somewhat light-heartedly).
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 3, 2013 at 10:04 pm
Not sure if this thread is still going but I find myself in a similar situation.
Server maxdop = 0, It's a small 8 core box with 16gb ram
I've got a 30gb table, 100columns wide, contains random indexes about 29, a couple of FK's , clustered index , 0 fragmentation as I just rebuilt all the indexes, the table has a trigger and a schema bound view referencing it and a computed column.
If I create or rebuild an index specifying maxdop 2,6,8 or 0. It doesn't use a parallel plan.
If I create an exact copy of the table, just a different name. Same FK, Views, Triggers,Computed column,Filegroup etc.
I run the same code with the maxdop 0,2,4,6 etc. I get the corresponding parallel threads every time.
SQL 2012.
So basically for two identical tables aside from their name and the fact that I created one new as opposed to a legacy old table with FK's turned on and off etc. It's been around the block.
I get different parallel behavior using the same code, not this is all within the same database. Behavior persist after reboots.
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply