Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

So we have a customer site that is complaining about some seriously slow performance. I took one look and it's obvious that the problem is because Somebody Else (grrrr) designed a table holding some 20 million-plus records without a clustered index.

Now I want to create a clustered index on that table - but in my test environment my create index command has been running for an hour and it's still not done. The customer site is a shop floor that works 24/7, and cannot afford an hour of down time while I create an index.

Is there some less brute-force method of creating the index that will either finish the job quickly, or do it in some smart way that will not totally kill the server's performance while it's busy?

We are using SQL Server Enterprise Edition.

share|improve this question

migrated from stackoverflow.com Jan 31 '12 at 13:51

This question came from our site for professional and enthusiast programmers.

    
You need to explain to your customer that they are already experiencing outages, every time their queries run slowly. Every time a person or process has to wait for their highly fragmented table to return results, they are in fact losing money. They need downtime to address the situation. Period. –  datagod Apr 25 '12 at 3:28

3 Answers 3

up vote 18 down vote accepted
  • If your sql server is Enterprise+ edition and table has no any BLOB fields - CREATE CLUSTERED INDEX ... WITH(ONLINE=ON)

  • If not - there is no any way except to create the table with the same schema on side and wisely transfer data to it, including all INSERT/UPDATE/DELETE operations (using trigger, for example), and then accurately drop old table and rename new to the same name as old - cons are: slow, adds extra load to server and storage

share|improve this answer
    
Sometimes, even with ONLINE = ON, the table might be too large and have too many changes such that tempdb cannot handle it (i.e. my environment). So, for the "if not or won't work" case, migration of existing data should be done in batches via a SQL Agent job and UPDATEs and DELETEs should be synced via a Trigger (or Change Data Capture). I explain this in detail here Restructure 100 Million Row (or more) Tables in Seconds. SRSLY! and have used this method many times with tables exceeding 100 million rows that were heavily hit. –  srutzky Dec 14 at 16:30

Not sure what version of SQL server is your customer using. In enterprise you can build the index with (ONLINE = ON), so the table is available till the index is created.

share|improve this answer
  • Create new table identical to original (obviously will need a different name)
  • Create clustered index on new table
  • Load data into new table
  • Drop original table
  • Rename new table using the original name

Make sure you apply any permissions that were required from the original.

share|improve this answer
2  
This is quite interesting but the insert/update/delete rate on the original table must be not too high or else you won't be able to keep up with this. –  Andrei Rinea Feb 28 '12 at 16:20
    
@AndreiRinea That is not true if the migration of existing data is done in batches via a SQL Agent job and the UPDATEs and DELETEs are synced via a Trigger (or Change Data Capture). I explain this in detail here Restructure 100 Million Row (or more) Tables in Seconds. SRSLY! and have used this method many times with tables exceeding 100 million rows that were heavily hit. –  srutzky Dec 14 at 16:24

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.