design issue: better speed performance by separating into different databases?

Hi,
From my limited knowledge, I think we can gain some speed performance by
breaking up a database out into multiple groups of tables and create one
database for each group - given that I don't care about the relationship as
much. The reason is, each database's dasta is saved down to a physical data
file (not quite, I realize that we can create filegroups) so sometimes
reading and writing operations must get into each other ways and then end up
get serialized by sqlserver automatically. I'm hoping to get some advice
from the experienced people in this area. Would using multiple filegroups
for a single db is better in speed performance than using multiple
databases?
I'm using sqlserver 2005 Enterprise edition.
Thank you for your comment or advice.Zen,
There are a host of approaches for increasing performance of a database
system. Which one/ones to use are dependent on the circumstances and the
performance issues. Many issues can be resolved my careful analysis and
modification of the query and/or indexing. What performance problem are you
experiencing? Can you narrow it down to make the resolution easier to
identify?
HTH
Jerry
"Zen" <zen@.nononospam.com> wrote in message
news:%23S0j7ZCgGHA.4004@.TK2MSFTNGP04.phx.gbl...
> Hi,
> From my limited knowledge, I think we can gain some speed performance by
> breaking up a database out into multiple groups of tables and create one
> database for each group - given that I don't care about the relationship
> as much. The reason is, each database's dasta is saved down to a physical
> data file (not quite, I realize that we can create filegroups) so
> sometimes reading and writing operations must get into each other ways and
> then end up get serialized by sqlserver automatically. I'm hoping to get
> some advice from the experienced people in this area. Would using multiple
> filegroups for a single db is better in speed performance than using
> multiple databases?
> I'm using sqlserver 2005 Enterprise edition.
> Thank you for your comment or advice.
>|||I don't have performance issue yet, but I'm just designing a solution that
maximize the read operations - so I'm concerned about the write operations
on the shared resource (physical file etc...). It's a small part of the
bigger design. Modification of the query and indexing will be done on top
of what we can do to avoid resource sharing (if it makes perf difference)
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:eQTxueCgGHA.3860@.TK2MSFTNGP02.phx.gbl...
> Zen,
> There are a host of approaches for increasing performance of a database
> system. Which one/ones to use are dependent on the circumstances and the
> performance issues. Many issues can be resolved my careful analysis and
> modification of the query and/or indexing. What performance problem are
> you experiencing? Can you narrow it down to make the resolution easier to
> identify?
> HTH
> Jerry
> "Zen" <zen@.nononospam.com> wrote in message
> news:%23S0j7ZCgGHA.4004@.TK2MSFTNGP04.phx.gbl...
>|||Zen wrote:
> I don't have performance issue yet, but I'm just designing a solution that
> maximize the read operations - so I'm concerned about the write operations
> on the shared resource (physical file etc...). It's a small part of the
> bigger design. Modification of the query and indexing will be done on top
> of what we can do to avoid resource sharing (if it makes perf difference)
>
That is absolutely no reason to consider using separate databases. As
Jerry says, there is a whole range of ways to optimize database
performance and all of them are available to you in a single database.
The valid reasons you might need separate databases are if the data has
different requirements for availability, security, backup or for other
administrative issues, not for performance. I'd also add that you are
going about your design the wrong way if you are considering
performance issues before you've planned and tested a logical database
design.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||"Zen" <zen@.nononospam.com> wrote in message
news:%23S0j7ZCgGHA.4004@.TK2MSFTNGP04.phx.gbl...
> Hi,
> From my limited knowledge, I think we can gain some speed performance by
> breaking up a database out into multiple groups of tables and create one
> database for each group - given that I don't care about the relationship
> as much. The reason is, each database's dasta is saved down to a physical
> data file (not quite, I realize that we can create filegroups) so
> sometimes reading and writing operations must get into each other ways and
> then end up get serialized by sqlserver automatically. I'm hoping to get
> some advice from the experienced people in this area. Would using multiple
> filegroups for a single db is better in speed performance than using
> multiple databases?
>
Yes. There is absolutely, positively no reason to use multiple databases
for performance reasons.
Filegroups will allow you to slice up the storage any way you want.
But, no offense meant here, your comments reveal a nearly complete lack of
knoledge of SQL Server internals.
Unless you understand the relationship between the Data Files, the Buffer
Cache and the Transaction Log, and how a query is processed, you shouldn't
make design decisions based on performance. Just create a design that is
simple, models your business domain reasonably well and is reasonably
relationally correct, and performance will usually take care of itself. In
fact, even if you do know all the performance details, you still shoudn't
make design decisions based on performance.
To learn more about how SQL Server stores data and processes queries, there
are some OK topic in Books Online.
Write-Ahead Transaction Log
http://msdn2.microsoft.com/en-us/library/ms186259.aspx
I/O Architecture
http://msdn.microsoft.com/library/d...br />
3339.asp
And Inside Microsoft SQL Server 2000 is the standard reference. Inside
Microsoft SQL Server 2005 is in the process of beging released in multiple
volumes.
David|||I guess the key part of of my question is: assuming that logical database
design correction is the same, would it make perf difference if they are
separate in different databases? thanks!
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1148585796.728951.103090@.38g2000cwa.googlegroups.com...
> Zen wrote:
> That is absolutely no reason to consider using separate databases. As
> Jerry says, there is a whole range of ways to optimize database
> performance and all of them are available to you in a single database.
> The valid reasons you might need separate databases are if the data has
> different requirements for availability, security, backup or for other
> administrative issues, not for performance. I'd also add that you are
> going about your design the wrong way if you are considering
> performance issues before you've planned and tested a logical database
> design.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Zen wrote:
> I guess the key part of of my question is: assuming that logical database
> design correction is the same, would it make perf difference if they are
> separate in different databases? thanks!
>
Not really the right question because performance depends on other
factors. Put it this way: using separate databases doesn't give you any
extra options for enhancing performance - for example data can be
partitioned across multiple files even within the same database. So it
would be more accurate to say that you can achieve the same performance
with one database as you can with multiple databases - assuming that
all the other database administration factors are uniform across your
data.
You haven't given us any idea what kind of data volumes you are talking
about. Multiple-terabyte databases are common currency today. If you
are at or approaching terabyte scale however, you should hire some
proper expertise now because mistakes are easily made and can be very
expensive to fix.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Zen,
It "could" in certain situations i.e., distributed partitioned views in a
federation of servers however this is a high-availability solution for
specific demonstrated performance issues and not generally a first pass
optimization technique. I would recommend you stick with one database in
this case.
HTH
Jerry
"Zen" <zen@.nononospam.com> wrote in message
news:O3G$64DgGHA.2456@.TK2MSFTNGP04.phx.gbl...
>I guess the key part of of my question is: assuming that logical database
>design correction is the same, would it make perf difference if they are
>separate in different databases? thanks!
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1148585796.728951.103090@.38g2000cwa.googlegroups.com...
>|||Jerry Spivey wrote:
> Zen,
> It "could" in certain situations i.e., distributed partitioned views in a
> federation of servers however this is a high-availability solution for
> specific demonstrated performance issues and not generally a first pass
> optimization technique. I would recommend you stick with one database in
> this case.
> HTH
>
Ah, that's a good point. If "separate databases" also means "separate
servers" then there obviously might be a reason for doing it.
The point I was trying to make was this: If you take one database and
naively make an identical copy of that database on the same server then
you won't achieve anything useful. True, you will have created at least
two new files (one data, one log) and depending on the placement of
those files you may perhaps see some performance impact. But then you
might have achieved the same thing by creating those extra files in one
database instead of two. So performance always depends on things other
than the number of databases. As you say, it depends on the number of
servers for instance :-)
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--