3

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?

  • Man 200MBs for tempdb. How many processors you have and how many memory on this box ? – Kuba Miazek Apr 17 '14 at 7:45
2

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.

  • Thank you so much for the help. Yes, the space allotted to tempdb is small for the server but I'm having trouble getting our server admin to give me more room. Maybe this will get him to change his mind. – Aaron Hurst Apr 16 '14 at 17:46
-3

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).

  • 1
    Yes it has to sort somewhere, but for SORT_IN_TEMPDB = OFF as stated in the question, neither the sort runs nor the mapping index use tempdb. The point about uniquifiers for non-unique indexes is valid, but doesn't really answer the question either. – Paul White Apr 17 '14 at 4:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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