Re: Performance issue

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Mike Hodgson (mwh_junk_at_hotmail.com)
Date: 11/23/04 

Date: Tue, 23 Nov 2004 23:08:33 +1100

If I were you I'd tell the consultant he was an idiot. (Ok, maybe that's a
little harsh.)

The only reason that I can think of for splitting the tables across multiple
databases is for more flexible backup purposes but that could be achieved
much more easily with multiple filegroups within a single DB and filegroup
backups.

Putting the tables in multiple DBs will affect performance slightly (but the
impact should be so small you probably wouldn't even notice it) as SQL
Server will have to check the DB & object permissions for every DB involved
in a query for every query unless you have cross-database chaining turned on
(which is not recommended for security reasons that I won't expand on). It
also adds marginally to the administrative overhead in managing the object
permissions.

Inter-database dependencies can be a real pain especially if you need to
move DBs between servers (I encountered big headaches migrating SQL6.5 DBs
to a new SQL2000 cluster a few years ago due mainly to views referencing
objects in other DBs that didn't exist on the new cluster at the time - it
made DB migration order very important, which was a big pain because I'd
never seen any of those DBs before).

Basically, if all the tables are part of the same logical model and can't
really exist without the others (ie. they're not independent of the others)
then they should all be in a single DB. 16GB is not that big for a SQL DB,
it's a reasonable size but not huge. It should backup & restore in a
reasonable amount of time (ie. < 1hr) of half-decent hardware. Unless he
can give you a good reason (I wouldn't have thought the middle- or top-tier
being object-oriented was a very satisfactory reason - that would be true of
most current development), I'd tell him to go jump.

(I hope this all makes sense - it's hard to concentrate with the TV on in
the background.)

That's my 2c worth. 

-- 
Cheers,
Mike
"Henry" <Henry@discussions.microsoft.com> wrote in message 
news:4E2FE682-B5B3-4981-97E0-B571ADA57E32@microsoft.com...
> Dear Sir/Madam,
>
> I would like to know how would be the impacts eg. query performance, if
> tables are relocated to 20 different databases on same server? As a
> consultant is recommending us to relocate the existing tables to different
> databases, in order compile with Object Orientated modle. e.g. Group 
> tables
> and put them to corresponding entity database. Currently, all tables are 
> on
> same database (around 16GB).
>
> My main concern is as query will now be spreading across different
> databases, will performance be downgraded? What issue do I needed to 
> consider?
>
> Many thanks!
>
> From,
> Henry :)

Relevant Pages

  • Re: Performance issue
    ... I concur with Mike and also would like to add a note about dependencies/relations and backup. ... Too often I see applications split over several databases where the developers didn't even consider ... > The only reason that I can think of for splitting the tables across multiple ... > move DBs between servers (I encountered big headaches migrating SQL6.5 DBs ...
    (microsoft.public.sqlserver.server)
  • Re: How should I generate a primary key?
    ... A hash key index has its limitations though. ... performance is not the main reason for my arguments but I add it in as ... the point is that most databases do not ... have a problem with a single incrementing value or GUID as the primary key ...
    (comp.databases)
  • Re: DOS, how long?
    ... There's no reason it should. ... I've always had a way with machines. ... I have done all those things in various small databases, ... Would be a foolish person that did not migrate data to new platforms ...
    (comp.programming)
  • Re: Any Option to Save Changes to Database?
    ... Jonathan Wood ... I played with the data sources window and ended up dragging a table to a new form. ... For whatever reason, I guess Microsoft just assumed people adding the navigation control manually wouldn't want to save their databases. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Error in Index Reorg in Maintenance Task
    ... Somebody disabled page level locking for the index, for some reason. ... You can query sys.indexes to ... I have created a maintenance plan which performs the following (in order, on all databases): ...
    (microsoft.public.sqlserver.server)