is performance can increase if existing database table moves into different file group

Question

is performance can increase if existing database table moves into different file group

pankaj.kuchaliya

SSC Eights!

Points: 944
More actions
August 26, 2010 at 12:24 am

#226221

Hi,
Suppose I have database "test". And i have tables A,B,C,D
A:- master table
B:- transaction table
C:- transaction table.
D:- transaction table.
problem is :
each transaction table have the 30000 transaction in a single day.
if i create the different file group for each transaction then performance will increase or not.
if i place the heavy load tables in the different filegroup and at the time of fetching all these table are join .will the performance increase of slowdown.
Please Help,
Thanks.

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

It depends. If your current bottleneck is IO and the new filegroup is on a separate physical disk (with separate spindles), you may see a performance improvement.

If your current bottleneck is not IO or the files are on the same drive as the other files, you'll likely see little to no gain at all.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

pankaj.kuchaliya SSC Eights! Points: 944 More actions · Answer 2

hi,

thanks for reply,

Is it possible to create the new file group on separate physical disk.how can we do that.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

pankaj.kuchaliya (8/26/2010)
Is it possible to create the new file group on separate physical disk.

Of course it is. Assuming that you have such disk space available.

how can we do that.

Ur... You do know what I mean by 'separate physical disk'?

What's the storage for this database? SAN, RAID array or normal disks?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

John Mitchell-245523

SSC Guru

Points: 148809

August 26, 2010 at 3:31 am

#1212160

http://www.lmgtfy.com/?q=CREATE+FILEGROUP

pankaj.kuchaliya SSC Eights! Points: 944 More actions · Answer 5

hi,

Thanks for the reply.

how can i create the file group on normal disks? i m using the normal disk.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 6

Did you follow the link I posted? If so, what don't you understand?

pankaj.kuchaliya SSC Eights! Points: 944 More actions · Answer 7

John Mitchell-245523 (9/9/2010)
Did you follow the link I posted? If so, what don't you understand?

Yes , i have create the file group. but it is on the same location in the disk.how can i create on different location, but i have one disk .

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

If there's only one disk, then you have nowhere else to create the files and there's little point in doing so. There's little performance benefit from multiple files on the same disk.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

pankaj.kuchaliya SSC Eights! Points: 944 More actions · Answer 9

GilaMonster (9/9/2010)
If there's only one disk, then you have nowhere else to create the files and there's little point in doing so. There's little performance benefit from multiple files on the same disk.

Thanks for reply,

I have create the file group on different locations.

for good performance i have to create on different disk .i have one question "same process will be for different disk"

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

Not sure I understand you.

Splitting the database into multiple files is not going to help performance if all the files are on the same physical drive. Doesn't matter where on the drive they are. If you are planning to split the database into multiple files for performance reasons, you need multiple drives (physical drives, not logical partitions) and the files should be placed on different drives. If you only have one drive, then this is not possible and there's little point in splitting the DB purely for performance reasons.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

pankaj.kuchaliya SSC Eights! Points: 944 More actions · Answer 11

GilaMonster (9/9/2010)
Not sure I understand you.
Splitting the database into multiple files is not going to help performance if all the files are on the same physical drive. Doesn't matter where on the drive they are. If you are planning to split the database into multiple files for performance reasons, you need multiple drives (physical drives, not logical partitions) and the files should be placed on different drives. If you only have one drive, then this is not possible and there's little point in splitting the DB purely for performance reasons.

thanks,I m beginner to this .

Now I understand if i have to increase speed then i will have to create file group on different physical drive.

Means different hard disk i have to use.

How can i create file group on different physical drive.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

pankaj.kuchaliya (9/9/2010)
How can i create file group on different physical drive.

The same way you create a file any other time. Just as a location put the other drive's drive letter and folder.

It still may not improve performance. Only if the bottleneck is IO and you've done the design for the filegroups (which tables on which ones) based on usage patterns.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

steveb. SSC-Forever Points: 46733 More actions · Answer 13

I would only consider spliiting up to different filegroups if you have exhausted other performance tuning options and if there is a noticable I/O bottleneck. Filegroups are not a quick fix.

pankaj.kuchaliya SSC Eights! Points: 944 More actions · Answer 14

GilaMonster (9/9/2010)
pankaj.kuchaliya (9/9/2010)
How can i create file group on different physical drive.
The same way you create a file any other time. Just as a location put the other drive's drive letter and folder.

I have one more query.

Suppose i have

Harddisk1 & harddisk2

harddisk1 have C:,D:

and

harddisk2 have E:,F:

Now i database on D: of harddisk1

and i have to create the filegroup on D: and E:. then it will increase the performance.

Thank you for the help