SQL Server Space Required Rebuild Index Operation

Hi Friends,

Today, I will show you that impact of SQL Server Space Required Rebuild Index operation on data file. Actually when we perform clustered index rebuild operation than it will required almost same amount of space as of your clustered index. The reason behind this thing is very simple when we perform any index rebuild operation than SQL Server first create new index structure without deleting the old structure but after the creation of new index structure it will delete the old structure from data file. That means for a time your data file will contain two index structures. So that it will require double space. In the end of index rebuild operation it will leave some free space in the data file this space will be almost equivalent to your index size. Now let me show you this thing practically:

Case: I am having a database with a single table having clustered index on it. Here if i will perform index rebuild operation then it will make the database size double (because index rebuild require almost same amount of space as of your index size) where almost half of space will be free after the index rebuild operation.

Create database SizeTest
go
use SizeTest
go
create table xttest1
(
id int identity,
fname char(500),
lname char(500)
)
go
alter table xttest1
add constraint IX_CLUS_xttest1
primary key clustered(id)
go
insert into xttest1 values('prince1','rastogi1')
go 80000

Now you can run the below command to get the size of data file as well as what is your auto growth:

use master
go
sp_helpdb sizetest
go

1_SQL_Server_Space_Required_by_Index_Rebuild_operation

Here you can see the size of data file is 94272 kb/1024 kb = 92.07 almost equivalent to your database size which is 93.88 MB. Auto growth setting for data file is 1 MB here. Now perform the index rebuild operation and also find out the details about database

   
USE [SizeTest]
GO
ALTER INDEX [IX_CLUS_xttest1] ON [dbo].[xttest1] REBUILD
GO
use master
go
sp_helpdb sizetest
go

2_SQL_Server_Space_Required_by_Index_Rebuild_operation

Here you can see that now the size of your database data file is 187456 KB / 1024 KB = 183.06 MB which is almost double of your data file size (92.07) before Rebuild operation. Now you can also check that how much amount of space is free in data file (by using management studio go for shrink file operation for data file which will show you how much amount space is free inside the data file):

3_SQL_Server_Space_Required_by_Index_Rebuild_operation

Here 91.25 MB Space is free in data file which is almost equal to 50% of your data file.

Conclusion: If you are running alter index.. Rebuild operation then maximum space required by this operation inside the data file will be almost equal to the size of your biggest clustered index.

HAPPY LEARNING!

 

Regards

Prince Rastogi

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on TwitterFollow me on FaceBook

   

About Prince Rastogi

Prince Rastogi is working as Database Administrator at Elephant Insurance, Richmond. He is having more than 8 years of experience and worked in ERP Domain, Wealth Management Domain. Currently he is working in Insurance domain. In the starting of his career he was working on SQL Server, Internet Information Server and Visual Source Safe. He is post graduate in Computer Science. Prince is ITIL certified professional. Prince likes to explore technical things for Database World and Writing Blogs. He is Technical Editor and Blogger at SQLServerGeeks.com. He is a regular speaker at DataPlatformDay events in Delhi NCR. He has also presented some in depth sessions about SQL Server in SQL Server Conferences in Bangalore.

View all posts by Prince Rastogi →

6 Comments on “SQL Server Space Required Rebuild Index Operation”

  1. Thank you , It is very useful and I have one doubt, which one is good practice rebuilding index or drop and create index.

    Please clarify.

  2. hi Prince ,

    Nice article, i am having the following case could you please share your thoughts.

    What is the best approach Handle the table having around 3 TB size and the clustered index with 1 TB in size fragmented 41% .

    How much free disk space required where this data file resides.

    Sai G

  3. Hi Sai,

    In Your case, Table Size is 3 TB and Index Size is 3 TB. Is that means, you have one clustered index with 3 TB Size and Rest 3 TB space consumed by Non Clustered indexes? I hope your database is in Simple recovery model or Full recovery model with frequent transaction log backups.

    In Short, I can say, you can go with Alter Index .. Reorganize. This will take long time but very less resources.You can stop the reorganize in between when your maintenance window ends. Later on next maintenance window you can start the reorganize again then it will start from the same place it left in previous maintenance window.

    If you have extensive amount of resources on the Server (RAM, Transaction log file Space, CPU cycles), Enterprise edition and very long maintenance window time then you can go with Index Rebuild after Index Reorganize.

    Thank you!

    ~Prince Rastogi

  4. Thank you Prince ,

    I am also planning for the same REORGANIZE,

    Yes the db in FULL recovery configured for AG

    Thanks ,
    Sai

Leave a Reply

Your email address will not be published.