Parallel CREATE INDEX

Question

Parallel CREATE INDEX

GriffithsJ

SSC Veteran

Points: 266
More actions
August 9, 2004 at 11:42 am

#85145

This is apparently supported by Enterprise edition, but not by Standard edition.

I've had a look at BOL, but am none the wiser. What does this mean exactly?

Does it mean that I can't get the standard edition to run two or more create index commands at the same time? If so, what would happen if I attempted to do that? Would all but the first get ignored or would SQLServer simply queue them to run them sequentially?

Thanks

Griff

Viewing 6 posts - 1 through 5 (of 5 total)

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

DCPeterson SSCoach Points: 15266 More actions · Answer 1

This feature refers to the ability of SQL Server to employ more than one processor in the creation of an index, rather than referring to the number of indexes that can be created. It is similar to query parallelism and happens automatically without any intervention, unless you turn it off or set the max degree of parallelism threshold lower.

/*****************

If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek

*****************/

sa24 SSCrazy Eights Points: 8027 More actions · Answer 2

I looked at BOL and i did not find anywhere where it says that Standard edition will not create index in parallel. Can you please provide the link where it says so.

DCPeterson SSCoach Points: 15266 More actions · Answer 3

From BOL "CREATE INDEX"

"On multiprocessor computers on SQL Server Enterprise and Developer Editions, CREATE INDEX automatically uses more processors to perform the scan and sort, in the same way as other queries do. The number of processors employed to execute a single CREATE INDEX statement is determined by the configuration option max degree of parallelism as well as the current workload. If SQL Server detects that the system is busy, the degree of parallelism of the CREATE INDEX operation is automatically reduced before statement execution begins."

/*****************

If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek

*****************/

sa24 SSCrazy Eights Points: 8027 More actions · Answer 4

I think below is the author's concern.

This is apparently supported by Enterprise edition, but not by Standard edition.

Does it mean that I can't get the standard edition to run two or more create index commands at the same time?

DCPeterson SSCoach Points: 15266 More actions · Answer 5

Yes you can run multiple CREATE INDEX statements "at the same time" on Standard Edition, however the mention of parallel index creation refers to a single CREATE statement being able to use multiple processors. It has nothing to do with how many indexes can be created concurrently except that each CREATE statemtent is a single thread and can only run on a single processor. If you have a single CPU the whole point is moot.

/*****************

If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek

*****************/