May 16, 2012 at 4:16 am
I have a customer who has a SQL Server 2008 R2 Enterprise Edition clustered instance. The instance is configured with max degree of parallelism=1 and cost threshold for parallelism=5. An index has to be recreated due to include two more columns, and I issue a statement like:
CREATE NONCLUSTERED INDEX [index] ON
(
[column] ASC
)
INCLUDE
(
[included_column_1],
[included_column_2]
)
WHERE
(
[included_column_1] IS NULL
WITH
(
PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF,
DROP_EXISTING = ON,
ONLINE = ON,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON,
MAXDOP=4
) ON [PRIMARY]
The table has a few billion records. For some reason, even though I specify maxdop, the index creation is not running in parallel. Does anyone have an idea why?
May 16, 2012 at 6:45 am
Because "The instance is configured with max degree of parallelism=1", perhaps?
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 16, 2012 at 6:47 am
This was removed by the editor as SPAM
May 16, 2012 at 7:04 am
Stewart "Arturius" Campbell (5/16/2012)
The MAXDOP only overrides the configuration option for index operations once the index has been created. The actual Index operation (plan execution) will use more processors as and when required.The index creation will use the current instance configuration.
Stewart, I also looked at this link when I first saw this, but from what I read it should run in parallel within the CREATE INDEX statement as per what that article says
Parallel index execution and the MAXDOP index option apply to the following Transact-SQL statements:
CREATE INDEX
ALTER INDEX REBUILD
DROP INDEX (This applies to clustered indexes only.)
I dont see any mention of it saying that the index has to exist before you can override the MAXDOP settings.
What I did see is this...
If the SQL Server Database Engine detects that the system is busy, the degree of parallelism of the index operation is automatically reduced before statement execution starts.
...so might be that SQL is overriding, the override from 1 to 4 then back to 1 again.
I may have missed something on that article so please correct me if I'm wrong.
May 16, 2012 at 7:28 am
BOL also states "To suppress parallel plan generation, set max degree of parallelism to 1". My italics. If parallel plan generation is suppressed, should it matter what MAXDOP setting you've specified with your index create statement?
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 16, 2012 at 7:31 am
I thought the whole point of allowing the MAXDOP setting in statements is to override what is set at the server level as the options are evaluated at run time of the query which override the servers MAXDOP setting.
May 16, 2012 at 7:36 am
anthony.green (5/16/2012)
I thought the whole point of allowing the MAXDOP setting in statements is to override what is set at the server level as the options are evaluated at run time of the query which override the servers MAXDOP setting.
Of course it is.
Using it here is very revealing. The instance I'm using is configured as MAXDOP = 8, and we run REBUILDS WITH MAXDOP = 4. If we run them WITH MAXDOP = 1 they take forever, if we omit WITH MAXDOP and default to 8, they suck the server dry. Many of the rebuilds are on quite large tables, 500m rows to nearly 3000m rows - maybe this is significant.
I'd still expect WITH MAXDOP to be ignored in a rebuild statement if the instance is configured parallel plan suppression.
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 16, 2012 at 7:37 am
If the SQL Server Database Engine detects that the system is busy, the degree of parallelism of the index operation is automatically reduced before statement execution starts.
...so might be that SQL is overriding, the override from 1 to 4 then back to 1 again.
+1 Kristian (sorry if that is not how I should refer to your name), have you been able to replicate this on a server that does not have any traffic?
Jared
CE - Microsoft
May 16, 2012 at 7:40 am
Thanks for the information. I don't know why I have not already found this page. For the record:
* Resource Governor is not running
* Server CPU load is less than 1 of 8 cores
* Statistics of the data are extremely similar in the test environment and the production environment (it is a copy of the production database).
In the test environment is maxdop configured to 4 on the instance level however, and here the index was created in parallel. This is still a mystery to me, but it appears that SQL Server somehow has found a reason to decrease degree of parallelism for the index creation to 1.
May 16, 2012 at 7:45 am
ChrisM@Work (5/16/2012)
anthony.green (5/16/2012)
I thought the whole point of allowing the MAXDOP setting in statements is to override what is set at the server level as the options are evaluated at run time of the query which override the servers MAXDOP setting.Of course it is.
Using it here is very revealing. The instance I'm using is configured as MAXDOP = 8, and we run REBUILDS WITH MAXDOP = 4. If we run them WITH MAXDOP = 1 they take forever, if we omit WITH MAXDOP and default to 8, they suck the server dry. Many of the rebuilds are on quite large tables, 500m rows to nearly 3000m rows - maybe this is significant.
I'd still expect WITH MAXDOP to be ignored in a rebuild statement if the instance is configured parallel plan suppression.
Sorry I now get your point.
So I guess the question boils down to, if the instance is set to MAXDOP 1, if you specify an query which has MAXDOP > 1 does it produce a parallel plan or not.
Just so happens that I have an instance which is MAXDOP 1, but no where near the amount of rows in a table which you guys have, but would be interesting to know.
May 16, 2012 at 7:46 am
okbangas (5/16/2012)
Thanks for the information. I don't know why I have not already found this page. For the record:* Resource Governor is not running
* Server CPU load is less than 1 of 8 cores
* Statistics of the data are extremely similar in the test environment and the production environment (it is a copy of the production database).
In the test environment is maxdop configured to 4 on the instance level however, and here the index was created in parallel. This is still a mystery to me, but it appears that SQL Server somehow has found a reason to decrease degree of parallelism for the index creation to 1.
Also from BOL:
"When the Database Engine builds an index execution plan, the number of parallel operations is set to the lowest value from among the following:
The number of microprocessors, or CPUs in the computer.
The number specified in the max degree of parallelism server configuration option.
The number of CPUs not already over a threshold of work performed for SQL Server threads."
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 16, 2012 at 7:51 am
This was removed by the editor as SPAM
May 16, 2012 at 7:54 am
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.
May 16, 2012 at 8:06 am
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.
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 16, 2012 at 8:23 am
Data (or statistics) can be the issue some time
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply