Sql-server – Clustered Index Creation

indexsql server

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.

Best Answer

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.

Related Question