How should I handle very large datafiles?
Q: If i have a db of size 500 GB and have only one data file of 350 GB, then: 1) would splitting the data file into multiple files (3) on same File Group would help performance? 2) do all 3 files have to be on the same disk and /or same file group?
Kevin Kline says: Generally speaking, the main reason you ever segment a database across files and/or filegroups is to put those segments onto separate physical disks. If you don’t place them on separate disk or RAID arrays, then you get no performance improvement because all of the IO still resides in the same place it did before segmentation.
If, on the other hand, you place the filegroups or files onto separate physical disks you will also be moving the IO to separate disks, thus reducing the overall IO load on the disk subsystem(s).
And of course, the first step here is to make sure that the transaction log file is on a separate physical disk or RAID array before doing any of these other steps. If you haven’t done this step first, you’ll still have terrible IO.
Technorati Tags:
SQL Server, datafiles, file groups, segmentation, RAID, I/O
