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 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
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/default.aspx?scid=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...
>> 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
>|||Thanks for your input.
We did all the optimization on the queries. We donâ't load huge data on the
application but number of transaction are hugeâ?¦.(close to 200 call per sec )
Now ..reason for moving the data to different database on the same server is
because of the load we expecting on the system in upcoming releases. The
data we are moving to different database is the area where we are expecting
the load and we are bringing in couple of thousand more users. The CPU is
around 65 % and we do see the spike to 80 % during the month end and temp DB
contention goes up as well.
If new database on to different disks will that any way to reduce the Server
loadâ?¦We donâ't want to move to different server because the application is so
tightly integrated we defiantly need to have cross queries.
"Adam Machanic" wrote:
> 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/default.aspx?scid=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...
> >> 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
> >
> >
>|||No matter where you move the database to, if you're within the same instance
of SQL Server you will still be hitting the same tempdb, so your contention
will not decrease.
Are you planning on having a separate instance on the box?
"Naveen" <Naveen@.discussions.microsoft.com> wrote in message
news:BFF130E0-463C-4F3F-9F21-63581066FB12@.microsoft.com...
> Thanks for your input.
> We did all the optimization on the queries. We don't load huge data on
> the
> application but number of transaction are huge..(close to 200 call per
> sec )
> Now ..reason for moving the data to different database on the same server
> is
> because of the load we expecting on the system in upcoming releases. The
> data we are moving to different database is the area where we are
> expecting
> the load and we are bringing in couple of thousand more users. The CPU is
> around 65 % and we do see the spike to 80 % during the month end and temp
> DB
> contention goes up as well.
> If new database on to different disks will that any way to reduce the
> Server
> load.We don't want to move to different server because the application is
> so
> tightly integrated we defiantly need to have cross queries.
>
> "Adam Machanic" wrote:
>> 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/default.aspx?scid=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...
>> >> 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
>> >
>> >|||What kind of device do you have tempdb on? How big is it? There are lots
of options for optimizing it... More spindles, a RamSan, or even a RAM disk
(if you can afford losing the memory to tempdb)...
Adam Machanic
SQL Server MVP
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"Naveen" <Naveen@.discussions.microsoft.com> wrote in message
news:BFF130E0-463C-4F3F-9F21-63581066FB12@.microsoft.com...
> Thanks for your input.
> We did all the optimization on the queries. We donâ't load huge data on
> the
> application but number of transaction are hugeâ?¦.(close to 200 call per
> sec )
> Now ..reason for moving the data to different database on the same server
> is
> because of the load we expecting on the system in upcoming releases. The
> data we are moving to different database is the area where we are
> expecting
> the load and we are bringing in couple of thousand more users. The CPU is
> around 65 % and we do see the spike to 80 % during the month end and temp
> DB
> contention goes up as well.
> If new database on to different disks will that any way to reduce the
> Server
> loadâ?¦We donâ't want to move to different server because the application is
> so
> tightly integrated we defiantly need to have cross queries.
>
> "Adam Machanic" wrote:
>> 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/default.aspx?scid=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...
>> >> 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
>> >
>> >|||Huu...I need to verify this information with the Server Administration
team.Will get back to you as Soon as I have the ifnormation
"Adam Machanic" wrote:
> What kind of device do you have tempdb on? How big is it? There are lots
> of options for optimizing it... More spindles, a RamSan, or even a RAM disk
> (if you can afford losing the memory to tempdb)...
>
> --
> Adam Machanic
> SQL Server MVP
> Author, "Expert SQL Server 2005 Development"
> http://www.apress.com/book/bookDisplay.html?bID=10220
>
> "Naveen" <Naveen@.discussions.microsoft.com> wrote in message
> news:BFF130E0-463C-4F3F-9F21-63581066FB12@.microsoft.com...
> > Thanks for your input.
> >
> > We did all the optimization on the queries. We donâ't load huge data on
> > the
> > application but number of transaction are hugeâ?¦.(close to 200 call per
> > sec )
> > Now ..reason for moving the data to different database on the same server
> > is
> > because of the load we expecting on the system in upcoming releases. The
> > data we are moving to different database is the area where we are
> > expecting
> > the load and we are bringing in couple of thousand more users. The CPU is
> > around 65 % and we do see the spike to 80 % during the month end and temp
> > DB
> > contention goes up as well.
> >
> > If new database on to different disks will that any way to reduce the
> > Server
> > loadâ?¦We donâ't want to move to different server because the application is
> > so
> > tightly integrated we defiantly need to have cross queries.
> >
> >
> >
> > "Adam Machanic" wrote:
> >
> >> 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/default.aspx?scid=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...
> >> >> 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
> >> >
> >> >
> >>
>sql