Here’s another feature that I would like to see in SQL Server. When it comes to managing any database, IO management becomes a critical task. It’s something I feel most DBMS have not addressed. Data placement is most of the time a manual and very time consuming thing to do. For the rare occasions that we are forced to manage it because of a performance issue, the resulting work might only be a temporary solution as the data access paterns will change over time. Most storage units can’t mix fibre channel and SATA drives in the same RAID volume with the exception of Compellent and a few other storage array vendor.
It would be nice to see in a DBMS or the OS itself, the ability to spread the same data file over a mix of solid state, fibre channel and SATA drives. The software will then take care of migrating the pages which are used frequently to the faster drives and the ones used unfrequently to the slower drives. This way the most expensive drives would be used to their fullest while the slower and cheaper drives would contain most of the data. Pages could be moved in one direction or another, during idle time or scheduled maintenance, depending on the data access patern detected by the software. As a rule of thumb, data residing in the buffer pool would most likely be placed on the faster drives.