Creating Clustered Index uses all my tempdb space. Why?

Question

Table size = 44GB (no clustered index)

Row Count = 122016576

Disk space allocated to tempdb = 200MB

I wanted to create a clustered index for the table due to slow query times so I ran the following query:

USE [myDatabase]
GO

CREATE CLUSTERED INDEX [IX_Name] ON [dbo].[myTable]
(
    [myColumn] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

After 50 minutes of executing, my server's tempdb files (4 total) ran out of space and the database was sent into "In Recovery" status for about 10 minutes. It's up and running now but I'm confused as to why tempdb was utilized so much when I specifically said SORT_IN_TEMPDB = OFF.

Does anyone know why this operation utilized so much tempdb space?

Answer 1

Although you had SORT_IN_TEMPDB = OFF, that is not the only thing that uses tempdb. You also had set ONLINE = ON.

Since the job ran 50 minutes before you had trouble, it may be that you had enough activity in that single transaction to fill tempdb with row versioning data. This is described here:

http://technet.microsoft.com/en-us/library/ms179542.aspx

In part it says: "Online index operations use row versioning to isolate the index operation from the effects of modifications made by other transactions. ... Concurrent user update and delete operations during online index operations require space for version records in tempdb."

EDIT: Actually a 200 MB tempdb seems quite small for a database with a 44 GB table.

Answer 2

It's got to sort the data for the clustered index some where and then rewrite your rows in that order.

Don't forget that SQL Server will add an extra integer, the "uniquifier", so that it can disambiguate between the non-unique values of "myColumn". This will cause all your non-clustered index to include "myColumn" + "uniquifier" (4-byte).