distribute the part of data to different database on Same server

Hello
I have a very general question on SQL memory management. We have a SQL 2000
(64 bit) on Windows 2003 server. Our production database is growing in no
time. And we do see lot of contention on the Temp db on load. We are plannin
g
to distribute the part of data to different database on the same server. By
doing this will there be any improvement in contention & performance? Is it
advisable.
By doing this we will be adding the overhead on different procedure writing
cross database queries.
Your help will be appreciated
Thanks
NaveenMoving data into another database on the same server will not help tempdb at
all, unless the 'other database' is in a separate instance of SQL Server -
and then you'll still get IO contention unless you ensure that tempdb for
the new instance is on a separate set of disks.
My first approach would have to be identifying what is causing the tempdb
contention - it might be one or two major queries, or widespread throughout
the DB. If the former, well, optimise - if the latter, either rearchitect
or split the load between different instances, or even better, different
boxes.
Does tempdb comprise multiple files? You should have one file per CPU, if I
remember correctly - at least, for MSSQL 2005 this is recommended, and it
can't hurt 2000...
"Naveen" <Naveen@.discussions.microsoft.com> wrote in message
news:DB650185-CA27-4AFA-8513-B7E91529D4C9@.microsoft.com...
> Hello
> I have a very general question on SQL memory management. We have a SQL
> 2000
> (64 bit) on Windows 2003 server. Our production database is growing in no
> time. And we do see lot of contention on the Temp db on load. We are
> planning
> to distribute the part of data to different database on the same server.
> By
> doing this will there be any improvement in contention & performance? Is
> it
> advisable.
> By doing this we will be adding the overhead on different procedure
> writing
> cross database queries.
> Your help will be appreciated
> Thanks
> Naveen|||The one file per CPU is recommended in 2000, and there is also a trace flag
that -might- help (if the situation is correct). See:
http://support.microsoft.com/defaul...kb;en-us;328551
Agreed, though, this is probably a situation where pretty straightforward
optimization will do the trick.
The OP mentioned that it was during a data load, so I'm curious as to how
the data is being loaded? Are bulk loads being done, and if so, what is the
batch size being used? Larger batch sizes will buffer to tempdb. The
general recommendation I've seen is to shoot for batch sizes of around 10000
rows to avoid problems.
Adam Machanic
SQL Server MVP
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"Will Alber" <junk@.crazy-pug.co.uk> wrote in message
news:%23BSljDBuHHA.3480@.TK2MSFTNGP04.phx.gbl...
> Moving data into another database on the same server will not help tempdb
> at all, unless the 'other database' is in a separate instance of SQL
> Server - and then you'll still get IO contention unless you ensure that
> tempdb for the new instance is on a separate set of disks.
> My first approach would have to be identifying what is causing the tempdb
> contention - it might be one or two major queries, or widespread
> throughout the DB. If the former, well, optimise - if the latter, either
> rearchitect or split the load between different instances, or even better,
> different boxes.
> Does tempdb comprise multiple files? You should have one file per CPU, if
> I remember correctly - at least, for MSSQL 2005 this is recommended, and
> it can't hurt 2000...
> "Naveen" <Naveen@.discussions.microsoft.com> wrote in message
> news:DB650185-CA27-4AFA-8513-B7E91529D4C9@.microsoft.com...
>