Sql server database restore vs index rebuilds

Question

In SQL Server, if Database Restore Time takes less time compare to Index Rebuilds OR Reorganize . Can we Choose Restore as a option for DE-fragmentation. If its not a 24/7 database.

Answer 1

That doesn't make much sense. If you restore a database from a time earlier, you won't be capturing data that has changed since the backup. (but I'm assuming you mean a backup directly followed by a restore)

If you're talking about taking a full backup and then immediately a restore, then you will persist the fragmentation in the restored database as it was when it was backed up.

So either way I will say no that is not advisable. Prudent index rebuilds/reorganization based on the fragmentation is best practice.

See this blog post that on database restoration and index fragmentation that debunks that myth. Restoring a database also restores the fragmentation at the time of the backup.

Answer 2

In general no, the restore, at least in "modern" sql servers, is optimized to read and write data quickly. The indexes are left as they were at the time of backup.

Answer 3

The DB restore may de-fragment (physically) at the disk level, but there will still be the same internal fragmentation at the page level. Hence you will still need to do your index maintenance.

Answer 4

No, it doesn't work that way.

There are two layers at work here that you should be concerned with (there are more if you consider the underlying storage subsystem, but I'll ignore this here):

1. Internal database data file layout: this is how data pages are located within each data file. When SQL Server needs to allocate some space, it does so by grabbing an available space within the data file. This can causes fragmentation within the data file, as these new allocations may be physically out of order to the logical structure of which they're a part. When SQL Server restores a backup, it reconstitutes the exact layout of pages within the data file as when they were backed up: therefore, this does not eliminate index fragmentation.

2. Physical storage layout: this is how parts of the database data files are located on the storage device. SQL Server assumes that database data files themselves are perfectly defragmented on disk (because it would be ridiculous performance-wise to assume otherwise). Windows is free to make allocations wherever there is free space on disk. This is why it's recommended to not rely on file auto-growth: if you have many databases with auto-growth happening, the physical allocations can be all over the disk (i.e., fragmented).

Given these two factors, it's recommended to (a) run index maintenance procedures to eliminate the internal database data file layout fragmentation, and also (b) run Defrag at the Windows level to eliminate physical storage layout fragmentation^*. Doing only one of the two does not eliminate the need for the storage device to do random I/Os.

Doing a restore as you suggest may eliminate the physical storage fragmentation, but it won't eliminate the internal data file fragmentation.

_{^* Physical-level (and lower) defragmentation is not recommended for SSD-based storage.}