Index Creation for large table failed.

Question

Index Creation for large table failed.

JasonO

SSC-Addicted

Points: 482
More actions
May 5, 2023 at 1:22 am

#4184469
I'm trying to create a clustered index on a very large heap table. Below is the script i'm using to perform the creation job.
```
CREATE CLUSTERED INDEX [IX_Arc_tblFST_GSNID] ON [dbo].[tblFST]
(
[GSN_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = ON, DATA_COMPRESSION=PAGE) ON [ps_tbl_arctbltnr]([TNR_Date])
```
However the creation job keep failing with TempDB space error.
The statement has been terminated.
Msg 1105, Level 17, State 2, Line 1
Could not allocate space for object '<temporary system object: 423661373489152>' in database 'tempdb' because the 'PRIMARY' filegroup is full.
Shouldn't with SORT_IN_TEMPDB=OFF parameter the index creation shouldn't use TempDB to perform the sorting? Is this some sort of a bug with SQL 2016? Currently I'm running on SQL 2016 SP2

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 1

JasonO wrote:

I'm trying to create a clustered index on a very large heap table. Below is the script i'm using to perform the creation job.
CREATE CLUSTERED INDEX [IX_Arc_tblFST_GSNID] ON [dbo].[tblFST]
(
[GSN_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = ON, DATA_COMPRESSION=PAGE) ON [ps_tbl_arctbltnr]([TNR_Date])
However the creation job keep failing with TempDB space error.
The statement has been terminated.
Msg 1105, Level 17, State 2, Line 1
Could not allocate space for object '<temporary system object: 423661373489152>' in database 'tempdb' because the 'PRIMARY' filegroup is full.
Shouldn't with SORT_IN_TEMPDB=OFF parameter the index creation shouldn't use TempDB to perform the sorting? Is this some sort of a bug with SQL 2016? Currently I'm running on SQL 2016 SP2

What is this??? ON [ps_tbl_arctbltnr]([TNR_Date]) It's tagged onto the end of your Create Index.
You say "very large heap" . How big is it in GB and what is the size of TempDB?
Is the [GSN_ID] and IDENTITY column?
How many rows does the heap contain?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

JasonO SSC-Addicted Points: 482 More actions · Answer 2

Jeff Moden wrote:

JasonO wrote:
I'm trying to create a clustered index on a very large heap table. Below is the script i'm using to perform the creation job.
CREATE CLUSTERED INDEX [IX_Arc_tblFST_GSNID] ON [dbo].[tblFST]
(
[GSN_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = ON, DATA_COMPRESSION=PAGE) ON [ps_tbl_arctbltnr]([TNR_Date])
However the creation job keep failing with TempDB space error.
The statement has been terminated.
Msg 1105, Level 17, State 2, Line 1
Could not allocate space for object '<temporary system object: 423661373489152>' in database 'tempdb' because the 'PRIMARY' filegroup is full.
Shouldn't with SORT_IN_TEMPDB=OFF parameter the index creation shouldn't use TempDB to perform the sorting? Is this some sort of a bug with SQL 2016? Currently I'm running on SQL 2016 SP2
What is this??? ON [ps_tbl_arctbltnr]([TNR_Date]) It's tagged onto the end of your Create Index.
You say "very large heap" . How big is it in GB and what is the size of TempDB?
Is the [GSN_ID] and IDENTITY column?
How many rows does the heap contain?

Hi Jeff,

The heap table itself is partitioned based on date (TNR_Date), so when creating the index, I'm creating based on the same partition schema & column as the table. Is this incorrect?
Table is about 330GB & Temp is about 400GB
GSN_ID isn't an identity column.
Estimated is about 4bil rows

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 3

I forgot about table partitioning. That answers my question there.
If the database is in the FULL Recovery Model, I recommend temporarily shifting to the BULK LOGGED Recovery Model just before the index build and back to FULL after the index build. With ONLINE = ON is probably the cause of the TempDB explosion but not 100% sure. The BULK LOGGED trick is to make the index rebuild substantially faster so you might be able to turn ONLINE = OFF. Having that off and being in the BULK LOGGED model won't only make things quite a bit faster but it'll also keep your log file from exploding.
As a bit of a sidebar, if GSN_ID is like an SSN in the U.S.A., I strongly recommend you encrypt it so you don't read about yourself in the news some morning.
4BN rows with ONLINE = ON is just going to crush your log file.

Shifting gears to the partioning thing...

Unless you have reasonable size partitions (like monthly, for example) AND you have 1 filegroup per month with 1 file per month, I see no advantage to partitioning here. Partitioning will actually make your good code run slower. If you do the 1 filegroup per month thing, you could probably make the older months Read Only, do a final backup of each of those filegroups, and never have to back them up again. Just a thought.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

JasonO SSC-Addicted Points: 482 More actions · Answer 4

Jeff Moden wrote:

I forgot about table partitioning. That answers my question there.
If the database is in the FULL Recovery Model, I recommend temporarily shifting to the BULK LOGGED Recovery Model just before the index build and back to FULL after the index build. With ONLINE = ON is probably the cause of the TempDB explosion but not 100% sure. The BULK LOGGED trick is to make the index rebuild substantially faster so you might be able to turn ONLINE = OFF. Having that off and being in the BULK LOGGED model won't only make things quite a bit faster but it'll also keep your log file from exploding.
As a bit of a sidebar, if GSN_ID is like an SSN in the U.S.A., I strongly recommend you encrypt it so you don't read about yourself in the news some morning.
4BN rows with ONLINE = ON is just going to crush your log file.
Shifting gears to the partioning thing...
Unless you have reasonable size partitions (like monthly, for example) AND you have 1 filegroup per month with 1 file per month, I see no advantage to partitioning here. Partitioning will actually make your good code run slower. If you do the 1 filegroup per month thing, you could probably make the older months Read Only, do a final backup of each of those filegroups, and never have to back them up again. Just a thought.

Hi Jeff,

Yea the database is on FULL RECOVERY model. Will changing the recovery model to BULK LOGGED cause any backup failures since all the while backup is taken with FULL Recovery model.

I didn't receive any log files spike alert though, it's only the tempdb failures. I'll check with the app owners if that particular table is being updated frequently or not, if I can perhaps get them to stop any jobs updating that table then i could try to run the create index offline.

I couldn't say much on the reason on why partitioning is being used on the table since this DB has been running before I join the organization. There's basically not much detail information on it except a basic understanding that the DB is an archiving DB. Partitioning is used to split the datas according to it's date to allow easier data maintenance e.g. housekeeping.