Clustered Index Creation

Question

I have been trying to create a clustered index on a table which is 1.4 TB in size.

This is my query:

CREATE CLUSTERED INDEX [C_IDX_Impressions] 
ON [dbo].[Impressions] ( [User_ID] ASC )
WITH (PAD_INDEX = OFF, 
      STATISTICS_NORECOMPUTE = OFF, 
      SORT_IN_TEMPDB = OFF, 
      DROP_EXISTING = OFF, 
      ONLINE = OFF, 
      ALLOW_ROW_LOCKS = ON, 
      ALLOW_PAGE_LOCKS = ON) 
ON [Impressions]
GO

The [Impressions] filegroup has 1TB of free space. But the query is failing with the following message:

Could not allocate space for object 'dbo.SORT temporary run storage: 140740546723840' in database 'HULU' because the 'Impressions' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

Am I missing something here?

The Tempdb should not come in the picture. Is it correct?

Any input will be appreciated.

Answer 1

When you add a clustered index to an existing table, SQL Server needs space to build the new index structure, in essence the entire table.

If space is an issue, you could create a new table with the clustered index, then migrate the data in chunks, drop the original table and rename the new one. It goes without saying that moving 1.4 TB if data like this is really only realistic if you're in SIMPLE recovery model, since you'd fill the log with that data otherwise.

You could probably use partition switching to avoid renaming, as renames carry some risks. That would involve truncating the original table, adding the clustered index to it, and finally using ALTER TABLE ... SWITCH.

Note that this operation will interrupt users, and you'll also have to be mindful of foreign key constraints and other schemabound objects. However, creating a clustered index on a TB+ table will also block the table for a long time, so it's your call.

I would probably just ask my SAN admin for more disk space if that's an option.

Answer 2

Right now table [dbo].[Impressions] is stored as a HEAP with no special order (last insert goes to the end of the last page) on the [Impressions] file group. RID (8 bytes) are used to uniquely identify each record.

When a CLUSTERED INDEX is created, it changes the way the table is recorded on disk. Rows will be recorded in consecutive pages and ordered by User_Id. During this operation the whole table has to be copied (under the hood) to its new destination and into its new format: the CLUSTERED INDEX. Once the index is created, the heap is removed and the space it uses is released.

The RID won't be needed anymore and User_id will be used instead by the CLUSTERED INDEX. If the size is similar (8 bytes), the new Clustered Index probably requires about the same size as the original table.

This suppose and I guess that User_id is unique. If this is not the case, row with non unique User_id will also use a 4 bytes value (internally generated) called a uniqueifier. This could lead to some extra space being used. If the current heap table contains NONCLUSTERED INDEX on the same file group ([Impressions]), their updates may also require some extra space.

The script contains SORT_IN_TEMPDB = OFF and it mentions because the 'Impressions' filegroup is full. Therefore tempdb seems fine. File group Impressions is the real problem. It has only 1TB free when the new table structure will require at least roughly 1.4TB.

The best solution here is to add enough space to file group [Impressions] (i.e. ~1.5 TB total) and enough space to the disk if the disk itself is full. This is explained under Disk Space Requirements in SORT_IN_TEMPDB Option For Indexes:

When you create a clustered index on a table that does not have nonclustered indexes, you must have available as free space:

If SORT_IN_TEMPDB is set to ON, there must be sufficient free space in tempdb to store the sort runs.These include the data rows of the table.There must be sufficient free space in the destination filegroup to store the final index structure.This includes the data rows of the table and the index B-tree.You may have to adjust the estimate for factors such as having a large key size or a fill factor with a low value.

If SORT_IN_TEMPDB is set to OFF, the free space in the destination filegroup must be large enough to store the final table.This includes the index structure.The continuity of the table and index extents may be improved if more free space is available.

When you create a clustered index on a table that has nonclustered indexes, you must have available as free space:
... ...

Space is also needed to store intermediate sort result in either the DB (SORT_IN_TEMPDB = OFF) or in tempdb (SORT_IN_TEMPDB = ON) as mentioned in CREATE INDEX (Transact-SQL):

Clustered Indexes

Creating a clustered index on a table (heap) or dropping and re-creating an existing clustered index requires additional workspace to be available in the database to accommodate data sorting and a temporary copy of the original table or existing clustered index data.
...

Aside from this error, setting SORT_IN_TEMPDB to ON could be a good choice on such a big table. This will store index creation transaction log in the tempdb logs and won't mix it with transactions recorded by user actions. For more information, read Transaction Log Disk Space for Index Operations.

Data copy to a new table on another file group could be an option. However this option may require more IO (i.e. more than the CI creation) on both file groups (Read from Impression and Write to NewFG) and a huge amount of transaction on the HULU log file. This could lead to a new space and file group is full issue on another file.

Partition switch can't be used because the table structure are not compatible. Row in HEAP are not ordered and are not recorded in the same fashion. It also must be performed on the same file group. For more information, read Transferring Data Efficiently by Using Partition Switching.