You may got your answer already and here are some pointers.Ĭheck what increased in the database. (allocation-BULK_LOGGED-mode-locking).aspx You can also visit the site where Paul Randal has explained the Myth about the index rebuilt : This following link describes the Data and Log File Management very clearly : So, if you want to improve the performance you need to turn on the Instant File Initialization (ofcourse if your windows admin agrees to, i dont see much reasons not to agree upon though.) MB increase in size. Also, zeroing of the data file has to occur. This is because the engine has to work on increasing the size of the database file for every 1 Second, you mentioned that you have set the autogrowth by 1MB, which means that you will always have bad performance when your database size starts increasing.

So, if you have to have index rebuild in you maintanance task then you should allow you database to have The size of the database then you would unnecessarily waste the effort of shrinking it as the size will increase again when you rebuild the index again. While index is rebuilt, parallel indexing structure is created and after it is created it switches as new cluster index. The increase in database size due to the index rebuilt is expected.

Rebuild master database in sql server