Sql-server – Recreate Indexes on 1 billion record table

indexpartitioningsql-server-2008-r2

I have a table with over 1 billion records and it has 6 indexes (including Clustered index (ID)). I need to partition this table on a new Clustered index with date column. I have just enough space (150gb) in the ldf file to grow in case of index rebuild (with tempdb OFF).

Please help me with an efficient way to rebuild the indexes on new Partition function/scheme. Which of the two approaches would be an efficient and less resource consuming way (OR recommend any other approach)?

Drop existing CIX and drop all N-CIX's
Create CIX on PScheme
Create aligned/non-aligned indexes on PScheme

Drop only CIX
Create CIX on PScheme
Create aligned/non-aligned indexes (with DROP_EXISTING) ON

Thanks

Best Answer

Now, the best way to answer this is to set up a smaller test database of a few gigabytes. It will accurately predict the best technique.

Your technique (2) is horrible because step 1 leads to all NCI's being rebuilt to work together with the resulting heap table. Don't do that.

The good news is that DROP_EXISTING works with changing the index definition including columns and partition scheme:

The existing index is dropped and rebuilt. The index name specified must be the same as a currently existing index; however, the index definition can be modified. For example, you can specify different columns, sort order, partition scheme, or index options.

And this is the reason why this feature exists. It is not equivalent to first dropping, then creating.

Here are the basic techniques (all of yours are horrible due to the intermediate heap table):

Drop all NCIs, rebuild the CI WITH (DROP_EXISTING = ON), create missing NCIs
Drop all non-aligned NCIs, rebuild the CI WITH (DROP_EXISTING = ON), create missing NCIs
Rebuild the CI WITH (DROP_EXISTING = ON)

(1) is very light on the transaction log. It is not maximally efficient. (3) is the simplest - it will cause all indexes to be rebuild as aligned indexes. This is not so good because you will have to rebuild those that you don't want to align.

(2) is probably best in all cases. I can't think of any case where it might be worse-off. It has the advantage of reusing existing indexes for a more efficient rebuild (this is possible because you didn't just drop all NCIs). NCIs which you want to be non-aligned don't benefit from this method and would be rebuild needlessly. So get rid of them before starting the procedure.

All of this actually works online although you said you don't need that.

Again, just try it out on a sample database. Certainly you shouldn't entrust the availability of your database to my advice. I suggest you take the ideas from this post and try them out.

Related Solutions

Sql-server – Large Fact table and partitioning key dilemma

I hate to state the obvious but I'd say test both scenarios and run your production queries against all 3 scenarios (scenario 1 being the current non partitioned one). The reason I state this is because I don't know what your code is querying. Do they actually have a benefit of being sorted in the base table by the identity column as opposed to the other column? For example, are your queries actually looking for the row ID often? If you're not sure, you might be surprised by getting some performance benefits. The general idea of using ID as the clustered key was for range scans, but in our case, we scan by customerID and Date, so it worked out perfectly for us, and perhaps you. Check out this article by Kim Tripp: http://www.sqlskills.com/blogs/kimberly/post/The-Clustered-Index-Debate-Continues.aspx

"What is often cited as the “reason” for IDENTITY PRIMARY KEY clustered index definitions is its monotonic nature, thus minimizing page splits. However, I argue that this is the only “reason” for defining the clustered index as such, and is the poorest reason in the list. Page Splits are managed by proper FILLFACTOR not increasing INSERTS. Range Scans are the most important “reason” when evaluating clustered index key definitions and IDENTITies do not solve this problem.Moreover, although clustering the IDENTITY surrogate key will minimize page splits and logical fragmentation due to its monotonic nature, it will not reduce EXTENT FRAGMENTATION, which can cause just as problematic query performance as page splitting. In short, the argument runs shallow "

Typically you want your clustered index to be as narrow as possible, unique, and non nullable as it get's carried into all other indexes. I have a table with roughly 10 billion rows and we partition off the datetime column, which has worked out great.

Sql-server – Move Primary Key to Filegroup (SQL Server 2012)

CREATE UNIQUE CLUSTERED INDEX Your_PK_Name
    ON YourTable(YourColumnList)
WITH (DROP_EXISTING =  ON )
ON [YourOtherFileGroup]

This preserves the logical PK property despite it not being mentioned in the syntax.

Best Answer

Related Solutions

Sql-server – Large Fact table and partitioning key dilemma

Sql-server – Move Primary Key to Filegroup (SQL Server 2012)

Related Question