SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Multiple Filegroups vs. Multiple Databases for better Performance


Multiple Filegroups vs. Multiple Databases for better Performance

Author
Message
amns
amns
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2334 Visits: 595
Hi,

I need to add some new tables to an existing database which will have:
1 - much more read/write operations than the current database tables;
2 - some IDs on these new tables will refer to existing IDs on existing database tables with static data;

Which is the best option to have more performance, and also keeping data integrity?
A - Create a new database with these new tables, replicating the tables with static data for PK/FK constraints, keeping the read/write operations separate from the current one (on different mdf and ldf files)
B - Add these new tables to the existing database, but in a different filegroup, keeping the read/write operations separated

Best Regards
ScottPletcher
ScottPletcher
SSC Guru
SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)

Group: General Forum Members
Points: 91371 Visits: 10117
Separate new filegroup(s) are fine, you don't really need a separate db, and you don't want to deal with the three-part-naming issues that a separate db would require.

SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial:
If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.
Steve Jones
Steve Jones
SSC Guru
SSC Guru (650K reputation)SSC Guru (650K reputation)SSC Guru (650K reputation)SSC Guru (650K reputation)SSC Guru (650K reputation)SSC Guru (650K reputation)SSC Guru (650K reputation)SSC Guru (650K reputation)

Group: Administrators
Points: 650089 Visits: 21472
Agree with Scott.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
amns
amns
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2334 Visits: 595
Thanks for your help!
What do you mean by 'three-part-naming issues'?
If I just add the new tables to the existing database in a different filegroup, the read/write operations will not delay operations between filegroups, even with both logging to the same ldf file?

Best Regards
ScottPletcher
ScottPletcher
SSC Guru
SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)

Group: General Forum Members
Points: 91371 Visits: 10117
What do you mean by 'three-part-naming issues'?

If the table resides in a separate db, then you reference the table you will need to include the db name, either in a synonym or view, or in the SELECT itself, like this:

SELECT
FROM dbo.table_in_db1
INNER JOIN db2.dbo.table_in_db2 ...

The log file will not be directly affected. Logging would be less overhead in a single db than if it had to go across 2 dbs, which causes a distributed transaction.

SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial:
If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.
amns
amns
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2334 Visits: 595
I see. Would be a reasonable idea to replicate these tables with static data also in the new DB, so the distributed transaction does not need to exist, and this new database would work almost completely separated?

Thanks, best regards!
Steve Jones
Steve Jones
SSC Guru
SSC Guru (650K reputation)SSC Guru (650K reputation)SSC Guru (650K reputation)SSC Guru (650K reputation)SSC Guru (650K reputation)SSC Guru (650K reputation)SSC Guru (650K reputation)SSC Guru (650K reputation)

Group: Administrators
Points: 650089 Visits: 21472
Why replicate? I'm not sure what you want to do. The distributed transaction is less of an issue on the same server, with SELECT queries, and more with writes.

If you use a new filegroup, you do a few things. One is you keep the data together and consistent with backup/restore, recovery, simple transactions for changes, etc. Second, no code changes for queries. Third, you can get the same performance with a separate filegroup that you would get with a new database on the same server. Separate the IO out to different storage.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
amns
amns
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2334 Visits: 595
Thanks. I ended up opting for creating a new database, since the data on this database would have a different concept, even though they would have some tables and IDs in common.

Best Regards
Jonathan AC Roberts
Jonathan AC Roberts
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13739 Visits: 5762
amns - Thursday, February 14, 2019 10:51 AM
Thanks. I ended up opting for creating a new database, since the data on this database would have a different concept, even though they would have some tables and IDs in common.

Best Regards

If the databases get too much for one server you could always then move one of them onto as seperate server to reduce the load.

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum









































































































































































SQLServerCentral


Search