July 3, 2011 at 8:51 am
Hi All,
I have a table with below specification
tbl_Huge:
Size : 514 GB
RowCount :225,436,212
No of Partition:4
Type of Partition: Monthly (150GB per partition)
I am trying to create an index on recordid(bigint identity) with the below script.
CREATE CLUSTERED INDEX [IX_Recordid] ON [dbo].[tbl_Huge]
(
[RecordID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PS_Monthly]([ORIGINATE_OFFER_DATE])
GO
Problem: The script ran for 1 day,consumed 400 GB of storage and failed with storage shortage error.
Can you please let me know why the index is taking this huge amount when the table size is 514 GB and I have set the FillFactor=90.
July 3, 2011 at 9:28 am
To create an index you will need easily 120% of the size of the index. Since your table is 514GB and you asked for 90% fill factor that means you'll need at least 575 GB of space, easily far more. This is because it needs somewhere to put the new index (a clustered index is your entire table) and it needs somewhere to sort it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 3, 2011 at 1:34 pm
I think you are right.. I never realized this because this is the first time I am facing the issue. So is there any workaround.
July 3, 2011 at 1:39 pm
Make sure there's enough space available. There's no way to build an index when you don't have enough space to put it.
You can use Sort in TempDB to move the sorting to TempDB (make sure that is big enough) but you still need enough space for the new index.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 3, 2011 at 2:10 pm
Thanks Gail. I think I need to add some space.
July 4, 2011 at 4:20 am
Just out of curiosity, does this need to be a clustered index? A non-clustered index would not require such huge amounts of storage to create...does depend what queries you're running against the table, of course!
July 4, 2011 at 8:30 am
Yes,I can create a non clustered index. But this is the surrogate key in my Fact table. So I needed a clustered index. I think I dont have any options left than increasing the storage. However I think I can copy all the data to a new table which already has an index but that will be time consuming.
July 20, 2011 at 10:46 pm
Hi
One thing you need to check here,
Make sure your tempdb is on the default 8mb growth.
Then add the sort in tempdb and maxdop to your index build.
If you have no alternativies in terms of storage you should build the table with the index enabled.
I.e. create index before the table is populated.
THe insert you can do in smaller batches.
Once you have this, or in not particular order you could also apply partitioning.
This will give you the ability to only rebuild sections of your index if you align it.
Once you have it setup you can easily maintain it as you can perfom index rebuilds on specific partitions which will be a subset of your fact and hence a subset of your size problem.
However I reckon it's a good practise to have some free space hanging around, for large sorts or temporary things going on.
Rule of thumb I try and have twice that of my biggest table or biggest DB, depending on your environment.
Cheers
Jannie
July 21, 2011 at 1:07 am
Thank you Jannie for your valuable inputs.
July 21, 2011 at 1:22 am
Jannie-186227 (7/20/2011)
Make sure your tempdb is on the default 8mb growth.Then add the sort in tempdb and maxdop to your index build.
Is or isn't? The default tempDB size and growth is stilly and any reasonable production server should NOT be using the defaults.
Sort in tempDB, yes providing tempDB is big enough to handle it. Maxdop on the index build maybe as long as it's not doing something like maxdop 1. There's no point in crippling the index build by forcing it onto one CPU.
Even with sort in tempDB you'll need the size of the index free in the user database, that just removes the extra 20% for sorts.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply