Each table in Owner Filegroup

Question

Each table in Owner Filegroup

ulisseslourenco

SSC-Addicted

Points: 417
More actions
April 30, 2011 at 4:18 am

#250134

Hi guys
I have a question. If I put each table in Owner Filegroup in same disk, it can have some performance gain? In my job my Boss give me this suggestion but a coleague of mine is against due to i/o performance can decrease.
Actually some database custumers are bad performance due to their size, we have one almost 1 terabyte of data.
My manager suggested:
Table1 => table1.ndf
Table2 => table2.ndf
Table3 => tabel3.ndf
etc,
In fact if I move a big table to other filegroup I will gain some performance howerver separating each table I will gain some performance? and same disk?
Regards
Ulisses

Viewing 10 posts - 1 through 9 (of 9 total)

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

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 1

If you separate your tables onto different File Groups but on the same disk, you will not get any performance improvement unless you place them on separate physical disks.

Thank You,

Best Regards,

SQLBuddy

ulisseslourenco SSC-Addicted Points: 417 More actions · Answer 2

sqlbuddy123 (4/30/2011)
If you separate your tables onto different File Groups but on the same disk, you will not get any performance improvement unless you place them on separate physical disks.
Thank You,
Best Regards,
SQLBuddy

Thanks dude,

It isn't best practice keep several filegroups in same disk, mainly a big database.

Syed Jahanzaib Bin hassan SSChampion Points: 11410 More actions · Answer 3

if you split tables on the same disk but different file group and data on the tables are huge then you should increase the performance and you can increase more performance if you have multi core processor

If data is not huge in the tables then you dont have any performance gain

Regards,

Syed Jahanzaib Bin Hassan

MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA

My Blog

http://www.aureus-salah.com

My Blog
www.aureus-salah.com

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

Syed Jahanzaib Bin hassan (5/1/2011)
if you split tables on the same disk but different file group and data on the tables are huge then you should increase the performance and you can increase more performance if you have multi core processor

Errr.... Why would multiple files on the same disk improve performance?

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

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

ulisseslourenco (4/30/2011)
In fact if I move a big table to other filegroup I will gain some performance howerver separating each table I will gain some performance? and same disk?

Unless the root cause of the performance problems was IO and those files are on separate disks you're unlikely to see any gain. In fact, too many filegroups may degrade performance.

Before you try something so extreme, maybe some query optimisation?

http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/

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

ulisseslourenco SSC-Addicted Points: 417 More actions · Answer 6

GilaMonster (5/1/2011)
ulisseslourenco (4/30/2011)
In fact if I move a big table to other filegroup I will gain some performance howerver separating each table I will gain some performance? and same disk?
Unless the root cause of the performance problems was IO and those files are on separate disks you're unlikely to see any gain. In fact, too many filegroups may degrade performance.
Before you try something so extreme, maybe some query optimisation?
http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/
http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/

Thanks dude,

I will see those links.

In fact, My manager based in split multiple text files in same disk is faster to seek some information than a big file so he thought if you would divide tables in multiple filegroups the performance should be increase due to SQLserver would find some row faster and faster. However I afraid performance problems therefore the datadase is accessed a lot, importing informations into database, users loading reports in application and other things.

Best Regards

Ulisses

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

ulisseslourenco (5/1/2011)
In fact, My manager based in split multiple text files in same disk is faster to seek some information than a big file so he thought if you would divide tables in multiple filegroups the performance should be increase due to SQLserver would find some row faster and faster.

That may be true for text files, but it's not something that can be generalised to all files. SQL does not read files beginning to end (like text files are read), when it fetches pages off disk, it knows what offset those pages are within the file and can just ask for the portion of the file it needs. Multiple files on the same disk are not going to help you.

However I afraid performance problems therefore the datadase is accessed a lot, importing informations into database, users loading reports in application and other things.

If this is critical and you have no idea where to start (and I don't mean to be insulting, but it sounds like that is the case), it may be of benefit to bring in a performance tuning specialist for a couple of days. Someone that can get to the root of the problem fast and give you some training for the future.

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

ulisseslourenco SSC-Addicted Points: 417 More actions · Answer 8

GilaMonster (5/1/2011)
ulisseslourenco (5/1/2011)
In fact, My manager based in split multiple text files in same disk is faster to seek some information than a big file so he thought if you would divide tables in multiple filegroups the performance should be increase due to SQLserver would find some row faster and faster.
That may be true for text files, but it's not something that can be generalised to all files. SQL does not read files beginning to end (like text files are read), when it fetches pages off disk, it knows what offset those pages are within the file and can just ask for the portion of the file it needs. Multiple files on the same disk are not going to help you.
However I afraid performance problems therefore the datadase is accessed a lot, importing informations into database, users loading reports in application and other things.
If this is critical and you have no idea where to start (and I don't mean to be insulting, but it sounds like that is the case), it may be of benefit to bring in a performance tuning specialist for a couple of days. Someone that can get to the root of the problem fast and give you some training for the future.

Thanks for explanation. In relation Performance Turning Specialist isn´t insult for me. A coleague of mine was against manager split filegroups in same disk either. He thought to separate a big database table in other disk and partition it is better option.

Best regards

Ulisses

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

ulisseslourenco (5/1/2011)
A coleague of mine was against manager split filegroups in same disk either. He thought to separate a big database table in other disk and partition it is better option.

He's right, but how much improvement that gets you is another question. If the problem is poor queries rather than IO bottleneck, that may not help that much.

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