SQL Server Performance

External Fragmentation

Discussion in 'Performance Tuning for DBAs' started by seasider, Feb 21, 2003.

  1. seasider New Member

    I recently reviewed a non-manged service customer site's database set-up. It was poor at best. I am now recommending a full server re-build with a better RAID & software set-up. I will need to restore the existing database's onto the server after the re-build. However the database's were very poorly maintained and the growth options were set to between 1-16MB on 2 database's of 15GB in size.

    The server was also used as a file store so I imagine the database extents are spread far and wide across the disk. When I restore these 2 database's onto my new server what is the best way to ensure that I get continguous file space. Ideally I want 1 very large file allocated that will cope with the database growth for some time, e.g. 20GB.

    Is the best way to restore the database, create the new data files, shrink the old files with the empty option so that the data moves into the new files, then delete the old files once they are empty?

    Or would the process of restoring the database's onto the newly built server ensure that I am allocated a contiguous pages on disk? Then all I would need to do is a DB-Reindex?

    Also is there any disadvantage of creating 1 large 20GB file rather than several smaller files if all files are on the same array?

    Any advice would be appreciated.


  2. bradmcgehee New Member

    Great question. I hope I have a great response.

    Here is what I would do. First, I would shrink the current databases on this server as small as possible, then perform the backups you make before the rebuild. (Also don't forget to test your backups with a test restore just in case, before you rebuild the server.)

    Rebuild the server, then restore the databases. When you restore the databases, they will restore as one contigious file, assuming there is enough room to do so. Then I would rebuild the indexes. This will cause the databases to expand as room is needed for the temporary space needed when indexed are rebuilt.

    Whether 1 large 20GB file, or several smaller files offers the best performance somewhat depends on disk configuration. But geneally speaking, if you have a RAID 5 or 10, then one large file will work fine, as the array will spread out the reads and writes equally among all the disks of the array.

    Brad M. McGehee

Share This Page