Issue: Index creation is not running in parallel

  • okbangas

    SSChampion

    Points: 11773

    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]

  • ChrisM@Work

    SSC Guru

    Points: 186078

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

    [font="Arial"]“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


    For fast, accurate and documented assistance in answering your questions, please read this article[/url].
    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]
    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
    [url

  • This was removed by the editor as SPAM

  • anthony.green

    SSC Guru

    Points: 112364

    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: 186078

    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?

    [font="Arial"]“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


    For fast, accurate and documented assistance in answering your questions, please read this article[/url].
    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]
    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
    [url

  • anthony.green

    SSC Guru

    Points: 112364

    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: 186078

    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.

    [font="Arial"]“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


    For fast, accurate and documented assistance in answering your questions, please read this article[/url].
    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]
    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
    [url

  • SQLKnowItAll

    SSC Guru

    Points: 61781

    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?

    Thanks,
    Jared
    PFE - Microsoft
    SQL Know-It-All
    How to post data/code on a forum to get the best help - Jeff Moden[/url]

  • okbangas

    SSChampion

    Points: 11773

    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]

  • anthony.green

    SSC Guru

    Points: 112364

    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: 186078

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

    [font="Arial"]“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


    For fast, accurate and documented assistance in answering your questions, please read this article[/url].
    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]
    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
    [url

  • This was removed by the editor as SPAM

  • okbangas

    SSChampion

    Points: 11773

    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: 186078

    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.

    [font="Arial"]“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


    For fast, accurate and documented assistance in answering your questions, please read this article[/url].
    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]
    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
    [url

  • Daxesh Patel

    Hall of Fame

    Points: 3208

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

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