The importance of filegroups in database performance April 20, 2012Posted by fofo in Sql Server, Sql Server 2005, SQL Server 2008.
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!!!