Microsoft SQL Server 2012 Performance Tuning Cookbook

Using files and filegroups

By placing certain database objects on different physical disks, you can improve the performance of your databases. But, how do we control the placement of certain database objects on particular physical disks?

Well, files and filegroups are used in SQL Server to physically organize your database files and database objects. By organizing data files with the help of filegroups, you can place specific database objects, such as tables and indexes, on particular physical disks.

Let's say that you are responsible for creating and designing a new production database, which will be accessed by many applications. You expect one particular table to grow very large with time. You are lucky enough to have enough hardware resources ...