SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Create Clustered Index on a Very Large Table (500 GB)


Create Clustered Index on a Very Large Table (500 GB)

Author
Message
-A
-A
SSC Journeyman
SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)

Group: General Forum Members
Points: 78 Visits: 125
Hello,

I have a scenario where I need to create a Clustered Index (CI) on a very large SQL Server 2012 database table. This table has about approximately 10 billion rows, 500 GB in size. The job ran for about 20 hours into it and then fails with error: "Out of disk space in tempdb". My tempDB size is 1.8TB, but yet it's still not enough.

I have exhausted Google search and decided to post it here to get some extra help.

Here is my script:

CREATE CLUSTERED INDEX CI_IndexName
ON TableName(Column1,Column2)
WITH (MAXDOP= 4, ONLINE=ON, SORT_IN_TEMPDB = ON, DATA_COMPRESSION=PAGE)
ON sh_WeekDT(Day_DT)
GO

Thank you in advance for all your help.


Abdel Ougnou
cunningham
cunningham
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1735 Visits: 856
Do you have any downtime windows to create the clustered index offline? Could be worth a go, will use less resource.
SQL Guy 1
SQL Guy  1
SSCertifiable
SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)

Group: General Forum Members
Points: 5864 Visits: 2637
1. Change SORT_IN_TEMPDB = OFF
2. Remove MAXDOP or set it to high and run at at weekend.
3. Consider partitioning such a huge table by date.
TheSQLGuru
TheSQLGuru
SSC Guru
SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)

Group: General Forum Members
Points: 51950 Visits: 8793
SQL Guy 1 (5/7/2014)
1. Change SORT_IN_TEMPDB = OFF
2. Remove MAXDOP or set it to high and run at at weekend.
3. Consider partitioning such a huge table by date.


1) I believe the table is partitioned (...ON sh_WeekDT(Day_DT))

2) It could be that there are other massive tempdb consuming queries running at the same time. Were you monitoring tempdb usage during these 20 hours?? Big necessity when you know you are going to be crushing it with something like you are trying to do.

3) I also wonder if it isn't the ONLINE part that is getting you, or at least having a play in the issue. Keeping track of stuff that is currently getting modified can add up depending on what is happening.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
-A
-A
SSC Journeyman
SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)

Group: General Forum Members
Points: 78 Visits: 125
Thanks for the replays.

--Here is the answers to the follow up questions:
1) I have to create this Index online since it is huge and I cannot afford such a long down time.

2) MAXDOP was set to 10 at first and showed no significant improvement in speed. I believe at the end the create index still happens on 1 processor anyways.

3) Also SORT_IN_TEMPDB = OFF or ON still didn't matter as SQL always seemed to use up all of the tempdb.

4) This table is already partitioned by date and currently has 570 partitions.


Abdel Ougnou
-A
-A
SSC Journeyman
SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)

Group: General Forum Members
Points: 78 Visits: 125
TheSQLGuru (5/7/2014)

2) It could be that there are other massive tempdb consuming queries running at the same time. Were you monitoring tempdb usage during these 20 hours?? Big necessity when you know you are going to be crushing it with something like you are trying to do.

3) I also wonder if it isn't the ONLINE part that is getting you, or at least having a play in the issue. Keeping track of stuff that is currently getting modified can add up depending on what is happening.


Yes I was monitoring and very little to nothing was happening as I did this on the weekend.

Perhaps worth trying to do the index offline to see if it finishes faster. However this was done on another DB that was not being accessed by anyone.


Abdel Ougnou
-A
-A
SSC Journeyman
SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)

Group: General Forum Members
Points: 78 Visits: 125
TheSQLGuru (5/7/2014)

3) I also wonder if it isn't the ONLINE part that is getting you, or at least having a play in the issue. Keeping track of stuff that is currently getting modified can add up depending on what is happening.


Just wanted to loop back and close this issues as successfully resolved.

Thanks to all for the help. TheSQLGuru was right, the online index creation was trying to keep track of too many changes as it tried to create and keep the index online at the same time. It could not complete this task within the given space on tempDB and withing a reasonable time. So changing this index creation setting to offline worked. The Clustered Index creation completed successfully and under 7:45 Hours. This was done over night during the time when users were offline.

The other great thing that is worth mentioning in case someone else has this similar scenario is by setting the index COMPRESSION=PAGE I was able to compress this 500 GB table down to 400 GB and the index size is only 1 GB. This is a savings of a 100 GB.

Thank you again to all.


Abdel Ougnou
cunningham
cunningham
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1735 Visits: 856
cunningham (5/7/2014)
Do you have any downtime windows to create the clustered index offline? Could be worth a go, will use less resource.


Where's the love!
-A
-A
SSC Journeyman
SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)

Group: General Forum Members
Points: 78 Visits: 125
cunningham (5/12/2014)
cunningham (5/7/2014)
Do you have any downtime windows to create the clustered index offline? Could be worth a go, will use less resource.


Where's the love!
hhh, sorry, I actually didn't see that. Thank you very much.


Abdel Ougnou
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (339K reputation)SSC Guru (339K reputation)SSC Guru (339K reputation)SSC Guru (339K reputation)SSC Guru (339K reputation)SSC Guru (339K reputation)SSC Guru (339K reputation)SSC Guru (339K reputation)

Group: General Forum Members
Points: 339542 Visits: 42623
-A (5/7/2014)
Thanks for the replays.

--Here is the answers to the follow up questions:
1) I have to create this Index online since it is huge and I cannot afford such a long down time.

2) MAXDOP was set to 10 at first and showed no significant improvement in speed. I believe at the end the create index still happens on 1 processor anyways.

3) Also SORT_IN_TEMPDB = OFF or ON still didn't matter as SQL always seemed to use up all of the tempdb.

4) This table is already partitioned by date and currently has 570 partitions.


Wait justa minute... you have a partitioned table that has no Clustered Index??? Blink

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search