Following the article on choosing the right disk subsystem comes this one on optimal file placement. Even with a very fast disk subsystem, the right layout of the data and log files is essential to bright performance. This article deals with correct and optimal file layout.
To optimize your file layout, follow these four rules:
1. Use RAID
2. The more disks, the better
3. Separate sequential and random I/Os
4. Separate sequentially accessed files on different disks
You have seen in a previous article that RAID has tremendous advantages in terms of speed and security. Always consider RAID as not being an option! You must use RAID disk subsystem.
The more disks, the better
Again, spreading data on multiple disks increases the number of simultaneous parallel requests. It is better to use 9 x 1 Gb disks, than 1 x 9 Gb disk! Nevertheless, this last sentence is a tricky one, because it depends on the disk throughput. But with recent disks, the rule the more, the better is always true.
Separate sequential and random I/Os
Essentially, log files are sequential and data files are random. The first thing is then to separate log files from data files. As a rule of thumb, log files are on a RAID 1 subsystem and data files on RAID 5 or 10.
Let's go a step further. If you don't want your data be mixed with system data, you can create two datafiles and two different filegroups: the Primary filegroup will contain the system objects, and the other one (e.g. named MyFileGroup) will contain your objects (pay attention when creating a table or an index to specify MyFileGroup as the filegroup for the object).
Though, the previous split is not a real performance enhancer, it permits to avoid mixing different type of data.
Now if you have big fat sequentially accessed tables (like history ones, by instance), it's better to put them on their own filegroup, separated from the small OLTP tables, essentially random-accessed.
Of course, separating tables on different filegroups need data-access knowledge and need extra work to identify the real type of access. But it worth it!
Separate sequentially accessed files on different disks
In some queries, the system has to scan different tables and/or different indexes at the same time. Separating these tables and/or indexes on different disk subsystems can enhance the performance of your system.
Let's assume you have two disk arrays. Two possibilities are offered:
1. You create two files (one on each disk array) and two filegroups. You need then to distribute tables and indexes manually on these two filegroups. It's then possible to have an unbalanced system, even if you feel you have more control on your database
2. You create two files (one on each disk array) belonging to the same filegroup. SQL Server uses a proportionate fill strategy to store data in the files. That means that both files will fill at the same speed, data being balanced amongst both files. You my feel having less control on the system, but it will perform better, spreading evenly data access on both disk subsystems.
SQL Server 7 takes advantage of RAID subsystem, increasing significantly query performance if you build an optimize file layout. Remember the four rules I gave you at the beginning, you will ever thank them.