SQL Server 2008 IO Tuning & FileGroups

A separate thread is created for each file in a database. So avoid the practice of lumping every single object in your database into one database file. As you create more files, you are able to utilize more threads which are of particular use when you are running SQL Server on a Multi-Core Processor and even more so when running Multiple Multi-Core Processors.

Further more, you are able to do better performance tuning if you separate the System Objects into their own filegroup. Since the System Objects will be changing at a slower rate than the insertion of data into your tables, you can get great performance gains by creating all your Application Objects in their own file group(s).

To achieve this, after creating your database, add a new filegroup and set it as the default.
ALTER DATABASE dbname MODIFY FILEGROUP filegroupname DEFAULT;

In addition to better IO performance, using multiple files makes backup and recovery operations easier, especially when transferring files between servers. Depending on your resources, you may not have the same amount of Hard Disk space on a single drive in a testing machine when moving data for a multi-terabyte database.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s