Best performance

What is the best thing to do to get the best performance ?
I have a database that contain 600 tables. Those table are reached by 10 di
fferents applications that we developped. I want to know if it's better for
performance to spread those 600 tables in 10 databases or leave those 600 t
ables in one database. Som
e tables are accessed by multiples applications but most of the tables are o
nly for one application.Cris,
This is a nearly impossible to answer correctly question. But...
1. Optimizer seems to be smartest when working within a single database.
Constraints are only within a database.
2. 600 tables is not that many. Some ERP systems have _well_ over 15,000
tables and run just fine.
3. If you have I/O problems, spreading the files (and each db has its own
files) across separate spindles can give you improvement. (But you have to
have a lot of activity before this really becomes worth doing.)
I would break up my tables or keep them together more by how I logically
view the systems and how I administer them, rather that use performance as
the first criteria.
Russell Fields
"Cris" <anonymous@.discussions.microsoft.com> wrote in message
news:3AB917FA-67DC-4C55-BD90-285A412C7F49@.microsoft.com...
> What is the best thing to do to get the best performance ?
> I have a database that contain 600 tables. Those table are reached by 10
differents applications that we developped. I want to know if it's better
for performance to spread those 600 tables in 10 databases or leave those
600 tables in one database. Some tables are accessed by multiples
applications but most of the tables are only for one application.
>|||Hi,
Incase if you have multiple disk controllers then you can create file
groups. SQL Server can improve the performance by controlling
the placement of data and indexes onto specific disk drives. This will
reduce the I/O and will give more performance.
One more option is Create 2 databases or go for 1 more server for reporting
(Incase you have quite a lot of report generation).
1. Make the reporting database sync using Transactional Replication or
Logshipping and keep read only.
This will reduce the query retrieval in the online datatabase and your OLTP
will be much faster.
Thanks
Hari
MCDBA
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:uxNEjYHCEHA.580@.TK2MSFTNGP11.phx.gbl...
> Cris,
> This is a nearly impossible to answer correctly question. But...
> 1. Optimizer seems to be smartest when working within a single database.
> Constraints are only within a database.
> 2. 600 tables is not that many. Some ERP systems have _well_ over 15,000
> tables and run just fine.
> 3. If you have I/O problems, spreading the files (and each db has its own
> files) across separate spindles can give you improvement. (But you have
to
> have a lot of activity before this really becomes worth doing.)
> I would break up my tables or keep them together more by how I logically
> view the systems and how I administer them, rather that use performance as
> the first criteria.
> Russell Fields
> "Cris" <anonymous@.discussions.microsoft.com> wrote in message
> news:3AB917FA-67DC-4C55-BD90-285A412C7F49@.microsoft.com...
10
> differents applications that we developped. I want to know if it's better
> for performance to spread those 600 tables in 10 databases or leave those
> 600 tables in one database. Some tables are accessed by multiples
> applications but most of the tables are only for one application.
>