cost/benefit of consolidating databases

The database group in my company charges back to our department
according to the number of database schemas we use. Since we have many
apps, each with its own database (on the same server), our manager
wants us to consolidate them in order to save money.
My question is how much does that hurt performance ?
Whereas the applications used to query its respective databases, they
will now all hit the same database, albeit different tables.
Does sql server automatically place all objects in the same database
close to each other (same area or platter of the harddisk for example),
hence we will lose this advantage and slow down all our applications
when we condolidate ?
Basically, everything being equal, with no tablename/permissioning
conflict, if we have a hundred giant queries hitting 2 databases (50
each), will the queries slow down, speed up, or stay the same if we
consolidate them and have all 100 queries hit the same database.
On a side note, how much does it hurt to cross databases when joining
tables ? My guess is that for large queries it can be quite a bit
since now you have to open up more tables to check for locks,
permissioing, etc."Buma" <devtai@.gmail.com> wrote in message
news:1133895996.342197.189820@.g14g2000cwa.googlegroups.com...
> The database group in my company charges back to our department
> according to the number of database schemas we use. Since we have many
> apps, each with its own database (on the same server), our manager
> wants us to consolidate them in order to save money.
> My question is how much does that hurt performance ?
Probably not -- this is not really a performance question since you're
on a single server, but more of a maintainability question. Splitting
things up makes them easier to maintain, which may be cheaper in the long
run than consolidation. Which is more expensive? Your time, or the charges
to the database group?
What is the rationale behind charging based on number of schemas? That
really doesn't make much sense to me...
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--|||Buma wrote:

> The database group in my company charges back to our department
> according to the number of database schemas we use. Since we have many
> apps, each with its own database (on the same server), our manager
> wants us to consolidate them in order to save money.
>
The database group is part of your company? So what is the business
case for you spending time on this when the net effect is just to move
money around from one department to another? Seems like your manager
wants to *spend* money in order to disguise a failure of budget
management - nothing will be saved.
In performance terms the consolidation may well be negligible. You can
place each application's data on separate file groups on separate
physical drives if necessary to eliminate any contention.
I would have thought the most important implications were around
configuration and management. Do all your schemas have the same
requirements of maintenance plans, backup and availability? If not,
then you are probably better off with separate databases.
What about release management? It will be harder to guarantee that
changes in one part of a single database don't affect another unrelated
application. Do you intend to regression test all your applications
each time just one of them changes? I think that would be a big issue
for me if I was an application owner or responsible for testing and
support.
David Portas
SQL Server MVP
--|||You are correct in that the net effect of db consolidation to the
company is nil (if not negative because of time necessary to
consolidate db and more work needed to manage a larger db). But since
this is a large company, and considerable investment has been to made
to their charge-back methodology, there is virtually no chance that
they will change this anytime soon.
I was sorta hoping that sql server is optimized in such a way that
database separation allows for greater concurrency and increase
performance, however minor, but gets magnified as resources are
strained. It would be so much easier to justify the status quo if I
can show for example, that during heavy usage when all the apps (some
applications are webbased, some are heavy middleware such as building
olap cubes) are running, that the websites can slow down considerably,
or even for a few seconds.
Maintenance, management, backups, etc., are all pretty standard across
databases. They are in fact handle by the dba, so in some way it might
be easier for them to do one database whether than separate them.
As for complicating releases/debugging because of added database
complexities. That is difficult to quantify and I doubt would be
enough to convince management.|||"Buma" <devtai@.gmail.com> wrote in message
news:1133907692.066720.59050@.z14g2000cwz.googlegroups.com...
> I was sorta hoping that sql server is optimized in such a way that
> database separation allows for greater concurrency and increase
> performance, however minor, but gets magnified as resources are
> strained. It would be so much easier to justify the status quo if I
Well, you may be able to make that argument in some cases for multiple
instances, each with processor affinities and maximum memory utilization...
but that might make certain situations even worse.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--