1

After Index Optimize the database size increased from 114 to 140Gb, is this normal?

Server RAM usage do not reduced after the process finishing, and stay at 62Gb/98%

6

Yes that is normal.

See: Disk Space Requirements for Index DDL Operations

When a new index structure is created, disk space for both the old (source) and new (target) structures is required in their appropriate files and filegroups. The old structure is not deallocated until the index creation transaction commits.

The following index DDL operations create new index structures and require additional disk space:

  • ALTER INDEX REBUILD

So if one of your indexes was rebuilt by the script, the size needed for that operation was the size of the old index structure + the size of the new index structure for the time the operation was running.

Once the new structure is in place the old structure is removed and free space remains inside the data file, but it is not returned to the operating system so your data file ends up larger but should include some free space that will be reused in the future.

Also take a look at Index Disk Space Example

Total disk space required to support both the source and target structures for the duration of the index operation is 816 MB (363 + 453). The space currently allocated to the source structures will be deallocated after the index operation is committed.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.