Issue: Index creation is not running in parallel

  • Orlando Colamatteo

    SSC Guru

    Points: 182269

    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

  • ChrisM@Work

    SSC Guru

    Points: 186078

    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.

    [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

  • Orlando Colamatteo

    SSC Guru

    Points: 182269

    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

  • ChrisM@Work

    SSC Guru

    Points: 186078

    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.

    [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

  • Gullimeel

    Hall of Fame

    Points: 3761

    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]

  • Orlando Colamatteo

    SSC Guru

    Points: 182269

    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

  • ChrisM@Work

    SSC Guru

    Points: 186078

    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?

    [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

  • okbangas

    SSChampion

    Points: 11773

    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.



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

    Concatenating Row Values in Transact-SQL[/url]

  • okbangas

    SSChampion

    Points: 11773

    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.



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

    Concatenating Row Values in Transact-SQL[/url]

  • TheSQLGuru

    SSC Guru

    Points: 134017

    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

  • Paul White

    SSC Guru

    Points: 150442

    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 🙂

  • Gullimeel

    Hall of Fame

    Points: 3761

    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]

  • Paul White

    SSC Guru

    Points: 150442

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

  • Jannie-186227

    SSCrazy

    Points: 2289

    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 29 (of 29 total)

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