About Me

My photo
Mumbai, Maharastra, India
He has more than 7.6 years of experience in the software development. He has spent most of the times in web/desktop application development. He has sound knowledge in various database concepts. You can reach him at viki.keshari@gmail.com https://www.linkedin.com/in/vikrammahapatra/ https://twitter.com/VikramMahapatra http://www.facebook.com/viki.keshari

Search This Blog

Thursday, April 25, 2013

Spread storage of data and indexes to different disks to improve I/O performance


When we are creating any database, the default FILEGROUP which gets created is PRIMARY file group, so by default Primary filegroup is the default filegroup.

Any database objects user create will stores in PRIMARY file group by default, but one can change the default filegroup. Even at the time of object creation user can specify where the object will get stored.

For our example let’s create a database “DemoFileGroup”

use master
go
create database DemoFileGroup
go
Command(s) completed successfully.

Now we are creating two filegroup and attaching it to the newly created database “DemoFileGroup” as fg1 and fg2.

alter database DemoFileGroup add FILEGROUP fg1
alter database DemoFileGroup add FILEGROUP fg2
go
Command(s) completed successfully.

Now let’s query sysfilegroups to find out, various filesgroups gets created in the database.

use DemoFileGroup
go

select groupName as FileGroupName from sysfilegroups
FileGroupName
-----------------------------
PRIMARY
fg1
fg2

(3 row(s) affected)

At this point we have three file groups primaryFG1 and FG2 in our new database. Any new table or index will be created in the primary filegroup which is the default unless we specify the filegroup to use.

If we want to create a data object in fg1 or fg2 then the only way is to specify the filegroup when creating the storage object as we are doing in the below example.

Here we are creating table table1 but we are not specifying on which filegroup it should get created, so by default it will get created in the PRIMARY file group.

create table table1
(id int identity(1,1))
Go

Lets query sp_help with table1 to find out the where the table object “table1” gets its storage.
sp_help table1
go
Data_located_on_filegroup
---------------------------
PRIMARY

So by output we can see the table gets created in the default file group ie, PRIMARY filegroup.

However at the time of table creation we can specify the filegroup name as we are doing in the below example.

create table table2
(id int identity(1,1),
fname varchar(20))
on fg1
go
Command(s) completed successfully.

Now let’s query sp_help with table1 to find out the where the table object “table2” gets its storage.

sp_help table2
go
Data_located_on_filegroup
---------------------------
fg1

Here we can see the table gets created in the file group fg1 which we specified at the time of table creation.
Now that we created the table on the new filegroup we can try to insert data.

insert into table2(fname) values('Prachi')
Msg 622, Level 16, State 3, Line 1
The filegroup "fg1" has no files assigned to it. Tables, indexes,
text columns, ntext columns, and image columns cannot be populated
on this filegroup until a file is added.

Now since we have no file associated with File Group fg1 we got the above error. Even if we try change the default filegroup, we will be getting the same error.

alter database DemoFileGroup modify FILEGROUP fg1 default
go
Msg 5050, Level 16, State 1, Line 1
Cannot change the properties of empty filegroup 'fg1'.
The filegroup must contain at least one file.

At this moment, we are done with FileGroup creation, its now time to create file and attached it to the newly created filegroup. Lets see the below script to create and attach file to the newly created filegroup “fg1”.
  
-- Add data file to FG1
alter database DemoFileGroup
add file
(NAME = DemoFileGroup_fg1,FILENAME = 'D:\Sqldata\DemoFileGroup_fg1.ndf')
to FILEGROUP fg1
go
Command(s) completed successfully.

Now we can set the default FILEGROUP to fg1, since now it has file associated with it.

alter database DemoFileGroup modify FILEGROUP fg1 default
go
The filegroup property 'DEFAULT' has been set.

We can check this by going to the database properties




















Now if you try to insert data to table 2 which is associated by FILEGROUP fg2, it will not throw error.

insert into table2(fname) values('Prachi')
(1 row(s) affected)

Here we can can conclude, we are now known with the filegroup and file creation. In coming post, I will explain various benefits and uses of filegroup with Horizontal Partition.

Dare to Challenge code, challenge youself 

Post Reference: Vikram Aristocratic Elfin Share