Issue: Index creation is not running in parallel

Question

Issue: Index creation is not running in parallel

okbangas

SSChampion

Points: 11773
More actions
May 16, 2012 at 4:16 am

#392269

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?

Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter
Concatenating Row Values in Transact-SQL[/url]
May 16, 2012 at 6:47 am

This was removed by the editor as SPAM
May 16, 2012 at 7:51 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 28 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Because "The instance is configured with max degree of parallelism=1", perhaps?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

Ant-Green SSC Guru Points: 113445 More actions · Answer 2

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.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

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?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

Ant-Green SSC Guru Points: 113445 More actions · Answer 4

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.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

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.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

Jared SSC Guru Points: 61793 More actions · Answer 6

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

okbangas SSChampion Points: 11773 More actions · Answer 7

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.

Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

Concatenating Row Values in Transact-SQL[/url]

Ant-Green SSC Guru Points: 113445 More actions · Answer 8

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.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

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."

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

okbangas SSChampion Points: 11773 More actions · Answer 10

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.

Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

Concatenating Row Values in Transact-SQL[/url]

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 11

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.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

Daxesh Patel Hall of Fame Points: 3208 More actions · Answer 12

Data (or statistics) can be the issue some time

http://blogs.msdn.com/b/psssql/archive/2009/03/18/be-aware-of-parallel-index-creation-performance-issues.aspx