jump to navigation

The importance of filegroups in database performance April 20, 2012

Posted by fofo in Sql Server, Sql Server 2005, SQL Server 2008.
Tags:
trackback

In this post I would like to explain you with a hands-on example the importance of filegroups in Sql Server databases.

This is not a post that experienced/advnanced DBAS/developers should read. The target audience is beginners (Level 100).

Most beginners in SQL Server  do not understand the concept of filegroups and why we need them.

Ι am going to start with a short introduction on data files,log files.

We all know that our data is stored eventually in operating system files, the same way the data we write in our Word documents are stored in .docx files in the physical disk.Every SQL Server database has two operating system files types:

  • a data file
  • a log file

A data file contains data and objects such as tables,indexes,stored procedures and views. Every database has one primary data file.The recommended file name extension for primary data file is .mdf.We can have secondary data files.The recommended file name extension for secondary data files is .ndf

The transaction log files hold the log information that is needed to recover the database. There must be at least one log file for each database. The recommended file name extension for transaction logs is .ldf.

Filegroups are named collections of files and are used to increase database performance,simplify data placement and administrative tasks such as backup and restore operations.

Filegroups enable data placement, because we can create a table in a specific filegroup. This improves performance, because all I/O for a specific table can be directed at a specific disk.

If you have tables that are frequently used together, you should put them on separate filegroups and physical drives.A good example could be  to put tables in the same join queries in different filegroups.

If you have a heavily used table, it can be put on one file in one filegroup, located on one disk, and the other less heavily accessed tables in the database can be put on the other files in another filegroup, located on a second disk.You can also put  the non-clustered indexes of a table in one filegroup located on one disk and the table in another filegroup on another disk.

Files cannot be moved to different filegroups and a file cannot be a member of more than one filegroup.We cannot have log files in filegroups.Filegroups can contain only data files.

We will need an instance of SQL Server running in our machine.You can download and install the free SQL Server Express edition,  here.

Launch SSMS and connect to an instance of SQL Server. In a new query window type the following T-SQL statements and execute them


USE master
GO
CREATE DATABASE TEST
GO

ALTER DATABASE TEST ADD FILEGROUP TEST_DATA_1
GO
ALTER DATABASE TEST ADD FILEGROUP TEST_DATA_2
GO

ALTER DATABASE TEST
ADD FILE
( NAME = TEST1,
FILENAME = 'C:\DATA\TEST_1.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 1MB)
TO FILEGROUP TEST_DATA_1
GO

ALTER DATABASE TEST
ADD FILE
( NAME = TEST2,
FILENAME = 'd:\data\TEST_2.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 1MB)
TO FILEGROUP Test_DATA_2
GO

USE TEST
GO

CREATE TABLE TABLE1
(
ID INT IDENTITY(1,1),
NAME VARCHAR(100),
CONSTRAINT PK_TAB1 PRIMARY KEY(ID)
) ON TEST_DATA_1

GO

CREATE TABLE TABLE2
(
ID INT IDENTITY(1,1),
NAME VARCHAR(100),
CONSTRAINT PK_TAB2 PRIMARY KEY(ID)
) ON TEST_DATA_2

GO



First we create a database that is called TEST.Then we create two filegroups (Test_DATA_1,Test_DATA_2).Then we add two secondary data files to each of the filegroups. Then we have this scenario. We want to create two tables that we know that will be heavily accessed by our application.We want to place them in different filegroups.The last two SQL batches show how to accomplish that.

When I select the TEST database, right-click on it,select Properties and the Files and then Filegroups I have a visual representation of the operations I just issued.

Have a look below to see what I mean

 

Hope it helps!!!

Comments»

1. Dot Net Rules : The importance of filegroups in database performance - April 20, 2012

[…] and why we need them.Ι am going to start with a short introduction on data files,log files. (read more) Share Posted: Παρασκευή, 20 Απριλίου 2012 9:52 μμ από το […]

2. sfilip - April 20, 2012

is there a specific guideline to using filegroups when SQL Server runs on a VM? (I know it’s not recommended but it happens some times :))

fofo - April 30, 2012

I do not much on the subject….We must find a whitepaper or a very targeted blog post….

fofo - April 30, 2012

one nice article I found regarding ” Best Practices for Running SQL Server on Hyper-V ” is this one
http://www.sqlmag.com/projectplans/migratingtosqlserver2008r2/detail/tabid/4568/catpath/sql-server/topic/7-practices-running-sql-server-hyperv-135931

3. sfilip - April 30, 2012

Cool! Just as I expected. Different fixed VHDs on different disks, separate VHD for OS and if possible separate VHDs for log files and databases. It seems that considerations for filegroups are identical to those for physical disks, as long as each VHD is on a different physical disk.


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

%d bloggers like this: