Implementing Physical Database Structure - Microsoft SQL Server 2012 Performance Tuning - page 336

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 in the form of
multiple physical disks that you can use to distribute your database physically. You decide
to place the table that you estimate will become very large and expect will be accessed
heavily by many requests, so that you can achieve the maximum disk I/O performance
on the requests made on this table. How would you configure this implementation? Well,
to find out, follow this recipe!

Getting ready

In this recipe, you will learn how to configure databases for large objects. To do this, we
will create a sample database that we will configure with multiple data files and filegroups.
This recipe requires that you have at least three physical drives available, as this example
references following physical disk volumes:

F E drive—for primary data file (primary filegroup)
F G drive—for secondary data file ( fg_LargeData filegroup)
F L drive—for log file

The following are the prerequisites to completing this recipe:

F An instance of SQL Server 2012 Developer or Enterprise Evaluation edition
F E —drive should be available on your machine
F G —drive should be available on your machine
F L —drive should be available on your machine

How to do it...

The following steps will describe how to work with files and filegroups, to distribute your
database across multiple physical disk drives:

1. Start SQL Server Management Studio and connect to SQL Server.