I have just come to a new company and we have one database which was created with standard initial size and autogrowth parameters for data and log files (1 MB autogrowth for data / 10% for log).
The database is 70 GB right now and I guess the datafile is spread all over the disk in 1 MB chunks, so sequential reads get penalized. There is just one raid for OS and other RAID 5 for database files (data and logs together, unfortunately).
Will a backup and restore help to store the datafile in a better way, physically speaking? Will there be improvement for long sequential reads? Otherwise, what can I do?
Thanks.
EDIT: Actually, I have just discovered another database in the same server, with the same problem but a size of 500 GB.
Best Answer
A restore will not correct the page fragmentation. The best option would be to take an outage and defrag the entire database using DBCC INDEXDEFRAG or DBCC DBREINDEX. For the OS file fragmentation if you simply detach the database, move the files to a different physical drive and then move it back you should be good.