I'm working on indexing and partitioning a single data warehouse table that weighs in around 500 GB. The table is a heap, has over a hundred
TEXT columns, and the
TEXT_IN_ROW option is enabled. I didn't design this table, and I don't have any capacity to change it in the immediate future.
I've been tasked with partitioning it. We're tackling this using a copy of the database on a test server. It can push about 2 GB per second to the SSD RAID arrays, so I/O isn't a significant bottleneck, and it's got 16 cores (2 NUMA nodes), and 64 GB of RAM.
My approach is to disable all the nonclustered indexes, create a partition function and partition scheme (about 12 partitions, all on the
PRIMARY filegroup - they're using this to enable rolling maintenance and provide more localized inserts for nightly ETL, and not to distribute I/O), then build a clustered index for the table using this partition scheme.
I'm creating the clustered index and partitioning the table as follows:
CREATE CLUSTERED INDEX CX_DailyTable ON DailyTable (LoadDate, SeqNumber) WITH (SORT_IN_TEMPDB = ON) ON monthly_on_primary (LoadDate)
Obviously, it's taking a long time (3 hours so far as of this post), and I certainly don't expect it to be quick. What worries me slightly is that tempdb is now pushing nearly 1 TB and steadily climbing, despite the current table being around half that size. The MS docs I've read suggest the tempdb space usage should be about the size of the final table/clustered index.
If SORT_IN_TEMPDB is set to ON, there must be sufficient free space in tempdb to store the sort runs, and sufficient free space in the destination filegroup to store the final index structure. The sort runs contain the leaf rows of the index.
Are their estimates incorrect? Is tempdb being used for substantially more than just the sort runs? Or is creating this clustered index somehow doubling the size of the table? (Seems pretty unlikely; it's a rather wide table, and I estimate we're getting an extra 4-8 bytes per row, plus non-leaf pages by adding a clustered index.)