distributed transaction: bit

Showing posts with label bit. Show all posts

Thursday, March 29, 2012

distribute the part of data to different database on Same server

Tuesday, March 27, 2012

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

distribute the part of data to different database on Same server

Distinguishing Processors in Server Properties

I have a SQL 2005 64 bit Standard box running on Windows Server 2003.
It has 4 dual core processors (with hyperthreading) and I would like to
scale it back to 2 dual cores. When I look at the processors in server
properties, it lists:
CPU0
CPU1
etc...
My assumption is that 0-3 are all from the first CPU (1 * dual core *
hyper thread). But I do not want to assume, I want to know which
processors I am picking for use and I can not find any documentation on
how those names map. Does anybody know?
PT
The more I think about it, I am thinking SQL 2005 does not know the
details of the physical processors, and is just told by the OS that
there are X logical processors. Anyhow, I would still like to know.
|||You are correct. SQL sees logical processors only. Plus, there aren't ane
"real" and "virtual" processors. They are all virtual. You have to turn
off hyperthreading at the BIOS level to get "real" processors.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Paul T." <weluvpaul@.hotmail.com> wrote in message
news:1163116200.511102.129730@.k70g2000cwa.googlegr oups.com...
> The more I think about it, I am thinking SQL 2005 does not know the
> details of the physical processors, and is just told by the OS that
> there are X logical processors. Anyhow, I would still like to know.
>
|||Paul
Check out this link - it provides an decent explanation
http://rentacoder.com/CS/blogs/real_life_it/archive/2006/04/28/477.aspx
Cheers
Bil
"Geoff N. Hiten" wrote:

> You are correct. SQL sees logical processors only. Plus, there aren't ane
> "real" and "virtual" processors. They are all virtual. You have to turn
> off hyperthreading at the BIOS level to get "real" processors.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Paul T." <weluvpaul@.hotmail.com> wrote in message
> news:1163116200.511102.129730@.k70g2000cwa.googlegr oups.com...
>
>
|||You shouldn't worry about whch core maps to which socket. Each core is a real
processor, and there is not much you can or should do with that mapping. So
let's just call a core a physical processor. However, there is some
interesting issue with mapping a physical processor to a hyperthreaded (or
logical) one.
Ideally, you should be able to tell because BIOS assigns the first 'logical'
processor for each physical one before looping back to assign the second one
after it has gone through all the physical processors. This is all good.
However, Perfmon lists and numbers the logical processors differently. So you
could end up seeing CPU consumption on each alternate processor in Perfmon.
Microsoft has a little utility called htdump.exe that is distributed as part
of MPS Reports. This utility gives you the physical-to-logical mapping as
reported by Perfmon. Now, this was something we encountered about a year ago.
Things may have changed since then.
Linchi
"Paul T." wrote:

> I have a SQL 2005 64 bit Standard box running on Windows Server 2003.
> It has 4 dual core processors (with hyperthreading) and I would like to
> scale it back to 2 dual cores. When I look at the processors in server
> properties, it lists:
> CPU0
> CPU1
> etc...
> My assumption is that 0-3 are all from the first CPU (1 * dual core *
> hyper thread). But I do not want to assume, I want to know which
> processors I am picking for use and I can not find any documentation on
> how those names map. Does anybody know?
> PT
>

Distinguishing Processors in Server Properties

I have a SQL 2005 64 bit Standard box running on Windows Server 2003.
It has 4 dual core processors (with hyperthreading) and I would like to
scale it back to 2 dual cores. When I look at the processors in server
properties, it lists:
CPU0
CPU1
etc...
My assumption is that 0-3 are all from the first CPU (1 * dual core *
hyper thread). But I do not want to assume, I want to know which
processors I am picking for use and I can not find any documentation on
how those names map. Does anybody know?
PTThe more I think about it, I am thinking SQL 2005 does not know the
details of the physical processors, and is just told by the OS that
there are X logical processors. Anyhow, I would still like to know.|||You are correct. SQL sees logical processors only. Plus, there aren't ane
"real" and "virtual" processors. They are all virtual. You have to turn
off hyperthreading at the BIOS level to get "real" processors.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Paul T." <weluvpaul@.hotmail.com> wrote in message
news:1163116200.511102.129730@.k70g2000cwa.googlegroups.com...
> The more I think about it, I am thinking SQL 2005 does not know the
> details of the physical processors, and is just told by the OS that
> there are X logical processors. Anyhow, I would still like to know.
>|||Paul
Check out this link - it provides an decent explanation
http://rentacoder.com/CS/blogs/real_life_it/archive/2006/04/28/477.aspx
--
Cheers
Bil
"Geoff N. Hiten" wrote:
> You are correct. SQL sees logical processors only. Plus, there aren't ane
> "real" and "virtual" processors. They are all virtual. You have to turn
> off hyperthreading at the BIOS level to get "real" processors.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Paul T." <weluvpaul@.hotmail.com> wrote in message
> news:1163116200.511102.129730@.k70g2000cwa.googlegroups.com...
> > The more I think about it, I am thinking SQL 2005 does not know the
> > details of the physical processors, and is just told by the OS that
> > there are X logical processors. Anyhow, I would still like to know.
> >
>
>|||Inter recommends that HT is implemented such as:
They you have two physical processors. This will surface as 4 processors to the OS. So, the
processors are presented to the OS should be:
CPU0 - Phy CPU0
CPU1 - Phy CPU1
CPU2 - Phy CPU0
CPU3 - Phy CPU1
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Paul T." <weluvpaul@.hotmail.com> wrote in message
news:1163112570.239503.214220@.i42g2000cwa.googlegroups.com...
>I have a SQL 2005 64 bit Standard box running on Windows Server 2003.
> It has 4 dual core processors (with hyperthreading) and I would like to
> scale it back to 2 dual cores. When I look at the processors in server
> properties, it lists:
> CPU0
> CPU1
> etc...
> My assumption is that 0-3 are all from the first CPU (1 * dual core *
> hyper thread). But I do not want to assume, I want to know which
> processors I am picking for use and I can not find any documentation on
> how those names map. Does anybody know?
> PT
>|||You shouldn't worry about whch core maps to which socket. Each core is a real
processor, and there is not much you can or should do with that mapping. So
let's just call a core a physical processor. However, there is some
interesting issue with mapping a physical processor to a hyperthreaded (or
logical) one.
Ideally, you should be able to tell because BIOS assigns the first 'logical'
processor for each physical one before looping back to assign the second one
after it has gone through all the physical processors. This is all good.
However, Perfmon lists and numbers the logical processors differently. So you
could end up seeing CPU consumption on each alternate processor in Perfmon.
Microsoft has a little utility called htdump.exe that is distributed as part
of MPS Reports. This utility gives you the physical-to-logical mapping as
reported by Perfmon. Now, this was something we encountered about a year ago.
Things may have changed since then.
Linchi
"Paul T." wrote:
> I have a SQL 2005 64 bit Standard box running on Windows Server 2003.
> It has 4 dual core processors (with hyperthreading) and I would like to
> scale it back to 2 dual cores. When I look at the processors in server
> properties, it lists:
> CPU0
> CPU1
> etc...
> My assumption is that 0-3 are all from the first CPU (1 * dual core *
> hyper thread). But I do not want to assume, I want to know which
> processors I am picking for use and I can not find any documentation on
> how those names map. Does anybody know?
> PT
>sql

Distinguishing Processors in Server Properties

I have a SQL 2005 64 bit Standard box running on Windows Server 2003.
It has 4 dual core processors (with hyperthreading) and I would like to
scale it back to 2 dual cores. When I look at the processors in server
properties, it lists:
CPU0
CPU1
etc...
My assumption is that 0-3 are all from the first CPU (1 * dual core *
hyper thread). But I do not want to assume, I want to know which
processors I am picking for use and I can not find any documentation on
how those names map. Does anybody know?
PTThe more I think about it, I am thinking SQL 2005 does not know the
details of the physical processors, and is just told by the OS that
there are X logical processors. Anyhow, I would still like to know.|||You are correct. SQL sees logical processors only. Plus, there aren't ane
"real" and "virtual" processors. They are all virtual. You have to turn
off hyperthreading at the BIOS level to get "real" processors.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Paul T." <weluvpaul@.hotmail.com> wrote in message
news:1163116200.511102.129730@.k70g2000cwa.googlegroups.com...
> The more I think about it, I am thinking SQL 2005 does not know the
> details of the physical processors, and is just told by the OS that
> there are X logical processors. Anyhow, I would still like to know.
>|||Paul
Check out this link - it provides an decent explanation
http://rentacoder.com/CS/blogs/real.../04/28/477.aspx
Cheers
Bil
"Geoff N. Hiten" wrote:

> You are correct. SQL sees logical processors only. Plus, there aren't an
e
> "real" and "virtual" processors. They are all virtual. You have to turn
> off hyperthreading at the BIOS level to get "real" processors.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Paul T." <weluvpaul@.hotmail.com> wrote in message
> news:1163116200.511102.129730@.k70g2000cwa.googlegroups.com...
>
>|||Inter recommends that HT is implemented such as:
They you have two physical processors. This will surface as 4 processors to
the OS. So, the
processors are presented to the OS should be:
CPU0 - Phy CPU0
CPU1 - Phy CPU1
CPU2 - Phy CPU0
CPU3 - Phy CPU1
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Paul T." <weluvpaul@.hotmail.com> wrote in message
news:1163112570.239503.214220@.i42g2000cwa.googlegroups.com...
>I have a SQL 2005 64 bit Standard box running on Windows Server 2003.
> It has 4 dual core processors (with hyperthreading) and I would like to
> scale it back to 2 dual cores. When I look at the processors in server
> properties, it lists:
> CPU0
> CPU1
> etc...
> My assumption is that 0-3 are all from the first CPU (1 * dual core *
> hyper thread). But I do not want to assume, I want to know which
> processors I am picking for use and I can not find any documentation on
> how those names map. Does anybody know?
> PT
>|||You shouldn't worry about whch core maps to which socket. Each core is a rea
l
processor, and there is not much you can or should do with that mapping. So
let's just call a core a physical processor. However, there is some
interesting issue with mapping a physical processor to a hyperthreaded (or
logical) one.
Ideally, you should be able to tell because BIOS assigns the first 'logical'
processor for each physical one before looping back to assign the second one
after it has gone through all the physical processors. This is all good.
However, Perfmon lists and numbers the logical processors differently. So yo
u
could end up seeing CPU consumption on each alternate processor in Perfmon.
Microsoft has a little utility called htdump.exe that is distributed as part
of MPS Reports. This utility gives you the physical-to-logical mapping as
reported by Perfmon. Now, this was something we encountered about a year ago
.
Things may have changed since then.
Linchi
"Paul T." wrote:

> I have a SQL 2005 64 bit Standard box running on Windows Server 2003.
> It has 4 dual core processors (with hyperthreading) and I would like to
> scale it back to 2 dual cores. When I look at the processors in server
> properties, it lists:
> CPU0
> CPU1
> etc...
> My assumption is that 0-3 are all from the first CPU (1 * dual core *
> hyper thread). But I do not want to assume, I want to know which
> processors I am picking for use and I can not find any documentation on
> how those names map. Does anybody know?
> PT
>

Thursday, March 29, 2012

distribute the part of data to different database on Same server

Tuesday, March 27, 2012

distribute the part of data to different database on Same server

distribute the part of data to different database on Same server

Distinguishing Processors in Server Properties

Distinguishing Processors in Server Properties

Distinguishing Processors in Server Properties

distributed transaction

Blog Archive

About Me