Performance of joins across filegroups

Question

Performance of joins across filegroups

Jesper-244176

SSCertifiable

Points: 7142
More actions
June 13, 2007 at 8:09 am

#62665

SQL Server 2000. The simplified version of my question goes as follows:
I have a table A in my database and would like to add a new table B (along with data). In my queries, I will often perform an inner join between A and B. We are considering creating a new filegroup and place B in here. How will this affect performance of my joins as compared to the case where B is placed in the same filegroup as A?

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

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

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 1

Your i/o read will be faster moreover placing tables that are joined together in different filegroups will enhance performance of your query.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

Jesper-244176 SSCertifiable Points: 7142 More actions · Answer 2

Sounds reasonable, although I suspected it was the other way round. Thanks.

Ed Hammar Ten Centuries Points: 1084 More actions · Answer 3

"Your i/o read will be faster moreover placing tables that are joined together in different filegroups will enhance performance of your query."

This answer isn't exactly correct and probably based on the assumption that since you are creating additional filegroups, those filegroups will reside on different disk subsystems. If you simply create a new filegroup on the same disk there will be no performance advantage. The reason you can gain some increases in performance by using filegroups is because you can place those filegroups on seperate and independent disk subsystems. One of the primary impediments to speed in any database application is disk seek time so if you have several independent disks and you distribute the tables used in your queries on those disks you can eliminate some of the time spent moving the disk head around the disk. Further if you have independent disks which are connected to independent controllers SQL Server can read from those disks at the same time which results in even greater performance.

If you are like most people and have a server with a single large RAID disk subsystem then creating additional filegroups won't do much for performance until you are able to convince someone to add another array or two to your server.

Antares686 SSC Guru Points: 125444 More actions · Answer 4

I would agree with Ed, unless a seperate array on a seperate controller ( or at least on a different channel on the same) you won't in most cases see is significant improvement. However you would still want to test for your scenario to be sure what happens in your exact case.

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 5

Now another complete different thought ...

It might not make any difference at all depending on the size of your database and the number of rows of data in the tables being joined.

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

Jeff Gray SSChampion Points: 10667 More actions · Answer 6

...And depending on whether the data is already in the buffer cache...

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 7

You've heard some thoughts regarding performance gains.

Did you consider backup performance as well ?

Maybe not that much of a gain with sql2000, but with SQL2005 you'll also be able to get your data back online with filegroup restores....

So for a best practise, it may not be such a bad idea to consider using filegroups.

- If your budget raises, you may be able to add disks and simply move the files. Short downtime na nomore hassle with placing the data.

- If you upgrade to sql2005 and you've placed your data well, you may be able to get things back online sooner than with a full restore at DRP time.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Jesper-244176 SSCertifiable Points: 7142 More actions · Answer 8

Thanks everyone, I learned a lot

The idea was to place the file groups on different disks as the new table B will become quite large over time. My main concern was that performance would get worse when I placed tables in different file groups, but I see that I have nothing to worry about.

Thanks again.