Multiple Filegroups vs. Multiple Databases for better Performance

Question

Multiple Filegroups vs. Multiple Databases for better Performance

amns

SSCrazy

Points: 2628
More actions
February 11, 2019 at 3:46 am

#370540

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

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

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

ScottPletcher SSC Guru Points: 100946 More actions · Answer 1

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) A socialist is someone who will give you the shirt off *someone else's* back.

Steve Jones - SSC Editor SSC Guru Points: 734434 More actions · Answer 2

Steve Jones - SSC Editor

SSC Guru

Points: 734434

February 11, 2019 at 2:55 pm

#2021729

Agree with Scott.

amns SSCrazy Points: 2628 More actions · Answer 3

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 SSC Guru Points: 100946 More actions · Answer 4

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) A socialist is someone who will give you the shirt off *someone else's* back.

amns SSCrazy Points: 2628 More actions · Answer 5

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 - SSC Editor SSC Guru Points: 734434 More actions · Answer 6

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.

amns SSCrazy Points: 2628 More actions · Answer 7

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 SSCoach Points: 18257 More actions · Answer 8

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.