May 28, 2014 at 9:03 am
Dear Experts,
Need your help to estimate the disk space requirements for rebuilding index on a table that is 400GB in size. This tables is in a DB with simple recovery mode. The table has 1 clustered index and 7 non clustered index.
Size of table,
# Records = 600 Million
Reserved (KB) = 400 GB
Data (KB) = 250 GB
Indexes (KB) = 140 GB
Unused (KB) = 10 GB
Indexes on this table:
there are 8 indexes on this tables, i got this size by using the query,
SELECT
i.name AS IndexName,
SUM(page_count * 8) AS IndexSizeKB
FROM sys.dm_db_index_physical_stats(
db_id(), object_id('dbo.TableName'), NULL, NULL, 'DETAILED') AS s
JOIN sys.indexes AS i
ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
GROUP BY i.name
ORDER BY i.name
clustered index 1: 240 GB
non clustered index 1: 13 GB
non clustered index 2: 16 GB
non clustered index 3: 16 GB
non clustered index 4: 22 GB
non clustered index 5: 15 GB
non clustered index 6: 22 GB
non clustered index 7: 21 GB
I would need your help in,
1. determining how much disk space is required to run the rebuild job? at the moment fragmentation is > 30 for all indexes on this table.
2. Any good approach for rebuilding index?
This sql server 2008 standard and there are NO partitions on this table. I will be using tempdb to sort so would be helpful if you can recommend space for tempdb as well?
May 28, 2014 at 9:19 am
For a rebuild, you generally want to have enough free space to cover your largest index and then add some for breathing room.
If you decide to do a reorg instead - the space requirement is less.
In this case for a rebuild we would be looking at needing at least 240GB free to store a copy of the index as it is rebuilt. Then you also need log space to support the actual operation.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply