Index build and parallelism

Question

Post reply

Index build and parallelism

george sibbald

SSC Guru

Points: 104210
More actions
November 24, 2010 at 4:28 pm

#142642

To put the question in a nutshell - can Sql server 2005 STANDARD edition run a create index as a parallel command or is it limited to one CPU?
I was under the impression that the create index would use the maxdop setting of the server, current load on the server allowing. Running a create clustered index on a dual quad core server, server level maxdop set to 4 and with no other activity on the server I noticed that just one core was being maxed out. The core in use would change from time to time but only ever one core was maxed out at a time.
This leads me to think I have misinterpreted BOL and other info I have found, and with standard edition you are restricted to serial index creates, can anyone confirm this (and provide a definitive link if possible)?
the create index was on a 460 million row table and took 24 hours!
---------------------------------------------------------------------

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

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

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 1

Hi George,

Could you check that with the MAXDOP hint ?

Check the following "Parallel index operations are available only in SQL Server 2005 Enterprise Edition." from the below link

http://www.sqlservercentral.com/scripts/31857/

Thank You,

Best Regards,

SQLBuddy.

george sibbald SSC Guru Points: 104210 More actions · Answer 2

thanks, but I am after confirmation that standard edition index rebuilds will always be serial, rather than you are just not able to explicitly specify a maxdop value.

---------------------------------------------------------------------

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 3

Hi George,

Please check the following Links

http://technet.microsoft.com/en-us/library/ms189329%28SQL.90%29.aspx

http://www.microsoft.com/sqlserver/2005/en/us/enterprise-compare-editions.aspx

http://www.sqlservercentral.com/articles/News/comparisonofsqlserver2005editions/1768/

Thank You,

Best Regards,

SQLBuddy

george sibbald SSC Guru Points: 104210 More actions · Answer 4

My testing shows an index build to be serial even with maxdop specified (it allows me to specify a maxdop value) so I will have to accept it is.

the links would support that but I have not found somewhere that explicitly states they are serial in standard edition.

cheers

george

---------------------------------------------------------------------

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 5

though i am not sure but i dont think standard edition supports MAXDOP

http://www.microsoft.com/sqlserver/2005/en/us/enterprise-compare-editions.aspx#bene

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 6

All that being said, couldn't you "partition" the table using a partitioned view? That would allow you to build/reorg/rebuild indexes on the underlying tables in parallel.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

george sibbald SSC Guru Points: 104210 More actions · Answer 7

Hmmm, interesting Jeff, a poor mans partitioned table. It looks as if it would take a fair amount of effort to get to this set up though if it was not designed in at the beginning.

Perhaps I should explain why I posted this question in the first place. There is a monthly ETL process which creates a new database each time, the data is related to Policy movements. Once the database is created it is read only, so index rebuilds are not required. Part of the process is a clustered index create on a 480 million row, 50GB table. Last month this took 1 hour, this month 24 hours (and on my subsequent investigation runs), with no material change in data quantity. Others are checking for changes in the make up of the data supplied.

Investigating why this might be I noticed the index build was serial, I wanted to be sure this was not an indication of some type of server\resource issue.

Our driver here is to make sure this runs through in time for the all important End of Year run, so not much time for a redesign, and it won't happen if I cannot give an answer for the increase in index build run time.

I will be pursuing methods to speed up this index create, but I think all I have got is sort in tempdb.

---------------------------------------------------------------------

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 8

george sibbald (11/27/2010)
It looks as if it would take a fair amount of effort to get to this set up though if it was not designed in at the beginning.

It may not take as much as you think. IIRC, SPLIT works on a single partition/single table even in Standard Edition. It can be used to very easily split off pieces of the table.

Provided the required constraint is built properly, a simple Indexed View of the same name as the table was previously called is all that is needed to reassemble the tables. No code changes should be needed in procs or GUI's.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

george sibbald SSC Guru Points: 104210 More actions · Answer 9

Thanks Jeff.

I am not familiar with the SPLIT command. do you mean alter table..SWITCH, which would not be available in standard edition.

---------------------------------------------------------------------

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 10

Apparently, I've got splitters on the mind, George. Yeah... I meant SWITCH and although I've not tried it myself, I saw Paul White demo it in a post on standard edition just to carve pieces of a large table. It won't do the whole gambit for a partitioned table, but he did make it split (see what I mean?) off a single separate table. He was actually using it for large deletes and it was very effective.

I believe you can use the same technique more than once to "split" your larger table into smaller pieces.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

george sibbald SSC Guru Points: 104210 More actions · Answer 11

OK Jeff, thanks.

---------------------------------------------------------------------

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 12

george sibbald (11/27/2010)
Hmmm, interesting Jeff, a poor mans partitioned table. It looks as if it would take a fair amount of effort to get to this set up though if it was not designed in at the beginning.
Perhaps I should explain why I posted this question in the first place. There is a monthly ETL process which creates a new database each time, the data is related to Policy movements. Once the database is created it is read only, so index rebuilds are not required. Part of the process is a clustered index create on a 480 million row, 50GB table. Last month this took 1 hour, this month 24 hours (and on my subsequent investigation runs), with no material change in data quantity. Others are checking for changes in the make up of the data supplied.
Investigating why this might be I noticed the index build was serial, I wanted to be sure this was not an indication of some type of server\resource issue.
Our driver here is to make sure this runs through in time for the all important End of Year run, so not much time for a redesign, and it won't happen if I cannot give an answer for the increase in index build run time.
I will be pursuing methods to speed up this index create, but I think all I have got is sort in tempdb.

1) I would look at more than changes in the data. I would look for server setting changes, database create changes (especially initial file and tlog sizes and growth factors), check to make sure someone didn't disable instant file initialization, review IO subsystem for changes (HUGE potential for someone to mess things up with that, especially if a SAN is involved), etc.

2) Obviously make sure you create database with sufficient size for everything to be put in it with no growth (tlog and data both), with instant file initialization enabled if security needs allow it.

3) Since it is ETL, can you check to see if someone messed with things such that you no longer get a minimally logged insert into this big table? That is another order-of-magnitude plus perf killer. Review BOL and look for everything you can to get minimally-logged inserts directly into the table with the clustered index already in place.

4) If nothing else works, consider dropping in a fusionIO card and put tempdb there and use sort_in_tempdb option. :w00t:

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

george sibbald SSC Guru Points: 104210 More actions · Answer 13

cheers kevin

---------------------------------------------------------------------

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 14

TheSQLGuru (11/29/2010)
4) If nothing else works, consider dropping in a fusionIO card and put tempdb there and use sort_in_tempdb option. :w00t:

Even though you can select the option in the Standard Edition, the sort_in_tempdb option does nothing in the Standard Edition.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)