parition of larger tables

Question

I am in the process of starting to partition some of our larger tables. I am doing this by creating the partition function/scheme and then dropping & re-creating the clustered index on the partition scheme.
My main question is does anyone have any hints on how to improve the time it takes to both drop and re-create the clustered indexes? At present it takes about 1hr to drop the clustered index and 2 & 1/2 to re-create it.

I only have a small maintenance window in which to do this work for about 12 tables, so any advice is welcomed.

I have already dropped the non-clustered indexes prior to this operation taking place.

Numbers:
Server - 2 x Intel Quad Core Xeon 2.53GHz
RAM - 48GB
Disk - Dell EqualLogic iSCSI SAN

Table sizes: 500 - 850 million rows

Answer 1

Dropping a clustered index should be quick, so long as you disable (or drop) all non-clustered indexes first.  When you drop a clustered index, the table becomes a heap and the non-clustered indexes have to be rebuilt to point to the rows by their rowid, rather than their clustered index key.

Then rebuild the clustered index, followed by rebuilding the non-clustered indexes.  On the rebuild consider using SORT_IN_TEMPDB, and MAXDOP=8.  Also consider applying ROW COMPRESSION or PAGE COMPRESSION to partitions as appropriate.

David

---

David http://blogs.msdn.com/b/dbrowne/

Answer 2

Take the following steps:
1. Create partition function & scheme
2. Rename data table with sp_rename
3. Create table on partition scheme with desired indexes etc
4. Create staging table with same structure as source/target 
    Apply check constraint that matches the partition I want to populate
5. Load staging table with data
6. Apply indexes that are on partitioned table to the stagin table
7. Switch staging table into desired partition
8. Drop staging table
9. Repeat from stage 4 until new table mirrors the source, only this time with nice chiny new partitions.

---

Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

Answer 3

Don't drop any indexes. Instead say:

CREATE CLUSTERED INDEX .... WITH (DROP_EXISTING = ON) ON PSCHEME(col)

It will still take time of course, but not as long time as if you drop and recreate.

---

Erland Sommarskog, SQL Server MVP, [e-mail]