First question : what are the necessary step to allow remote access (linked
server) using Windows logging and no Active Directory available
I tried the following without success
EXEC sp_addlinkedsrvlogin 'sql2k5', 'false', 'Soline\myLogin',
'sql2k5\mylogin', 'remotepassword'
but when I map to sa remote server login it works fine, so my questions
A - Does that mean that we can not map windows login as far as delegation
is not possible becaus of non existance of AD?
B - what are the min requirements (privilges) to be given to login rather
then "SA" login on the server leveland on the DB level in order to be able t
o
run distributed query. what I mean by that is there any good step by step
procedure to follow to implment this using min priviliges on both sides as o
n
Lcris blog regarding "Cross database access"Hi,
If you would like to use Windows login without AD to access linked servers,
the Windows Authentication is NTLM then.
It is required to create a same user with same password on each machine.
For your two questions:
> A - Does that mean that we can not map windows login as far as delegation
is not possible becaus of non existance of AD?
If there is no AD, please manually create the Windows user with the same
password on both machine.
> B - what are the min requirements (privilges) to be given to login rather
then "SA" login on the server leveland on the DB level in order to be able
to run distributed query.
> what I mean by that is there any good step by step procedure to follow to
implment this using min priviliges on both sides as on Lcris blog regarding
"Cross database access"
If you just want to query the database, the min requirements is that the
user corresponed to the login needs db_datareader permission.
First create a login on the remote server, double click the login, switch
to the User Mapping, map the login to a user of the database, and assign
db_datareader permission;
Then on your local server, execute the SPs as following:
sp_addlinkedserver 'mySqlServer-03'
sp_addlinkedsrvlogin 'mySqlServer-03',false,NULL,'s1','s1'
Hope this helps. If you have any other questions or concerns, please feel
free to let me know.
Have a good day!
Charles Wang
Microsoft Online Community Support
========================================
=============
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscript...t/default.aspx.
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============sql
Showing posts with label windows. Show all posts
Showing posts with label windows. Show all posts
Thursday, March 29, 2012
Distributed insert on oracle DB does not work from within a transaction.
Dear All,
I have MS SQL 2000 on Windows 2000 box and Oracle 9i on RH Linux 9.
I wanna write a trigger on MS SQL which inserts a record in the Oracle table.
So I have configured a Linked server on MS SQL using Microsoft OLE DB provider MSDAORA and am able to insert into (Single insert st) oracle table successfully from the Query Analyzer.
However the insert does not work when I code it in the trigger. Basically insert on the Oracle DB does not work from a transaction.
I receive the foll error when the trigger executes -
Server: Msg 7391, Level 16, State 1, Procedure AIN_sql2ora, Line 14
The operation could not be performed because the OLE DB provider 'MSDAORA' was unable to begin a distributed transaction.
Can anyone help me on this ?
Regards,
Azhar.set implicit transactions off
I have MS SQL 2000 on Windows 2000 box and Oracle 9i on RH Linux 9.
I wanna write a trigger on MS SQL which inserts a record in the Oracle table.
So I have configured a Linked server on MS SQL using Microsoft OLE DB provider MSDAORA and am able to insert into (Single insert st) oracle table successfully from the Query Analyzer.
However the insert does not work when I code it in the trigger. Basically insert on the Oracle DB does not work from a transaction.
I receive the foll error when the trigger executes -
Server: Msg 7391, Level 16, State 1, Procedure AIN_sql2ora, Line 14
The operation could not be performed because the OLE DB provider 'MSDAORA' was unable to begin a distributed transaction.
Can anyone help me on this ?
Regards,
Azhar.set implicit transactions off
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
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...
>
|||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...
>
|||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...[vbcol=seagreen]
> 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:
|||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...
>
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...
>
|||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...
>
|||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...[vbcol=seagreen]
> 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:
|||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...
>
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
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
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...
>
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...
>
Distribute app/database with SQL or Windows security?
This question is for anybody who has created & sold/distributed an
application that uses MSDE as its backend database.
I am converting my application which currently uses Access 2000 to MSDE
SP3a.
I already have the install process together, but I an in a quandry as to
what security method to use when installing.
If I use SQL security and set the SA password, am I looking for issues in
the future when a client might possibly purchase another program that uses
MSDE and won't install because I have set the SA password?
If I use Windows authentication, then I am leaving my database open to
anybody with Admin privliges.
I use information within a database table to license my program, so I'd like
to keep it secured.
Can anybody tell me what they do for their applications? Install with SQL
or Windows authentication?
TIA
Jim K.
As for other applications using your instance - the MSDE license only allows
your instance to be used by your application. If another application
installs its database on your instance of MSDE, then not only is it
violating the MSDE licensing but it is rather poor behavior, in my opinion.
Users with Administrative rights will always have total access to your MSDE
installation, whether you use integrated (Windows) authentication or not. If
you are concerned about the security of your data then you should implement
some form of encryption for the any data that you consider important
intellectual property or secure data.
With my application we encrypt any important data and implement in the
database a simple way of protecting the licensing the program. I don't go to
great extremes to prevent unlicensed clients from connecting to the
database - just enought to make it a hassle to try to break any system I
might have put in place.
Jim
"Jim K" <krusej@.megsinet.net> wrote in message
news:%235%23vNcuiEHA.3876@.TK2MSFTNGP12.phx.gbl...
> This question is for anybody who has created & sold/distributed an
> application that uses MSDE as its backend database.
> I am converting my application which currently uses Access 2000 to MSDE
> SP3a.
> I already have the install process together, but I an in a quandry as to
> what security method to use when installing.
> If I use SQL security and set the SA password, am I looking for issues in
> the future when a client might possibly purchase another program that uses
> MSDE and won't install because I have set the SA password?
> If I use Windows authentication, then I am leaving my database open to
> anybody with Admin privliges.
> I use information within a database table to license my program, so I'd
like
> to keep it secured.
> Can anybody tell me what they do for their applications? Install with SQL
> or Windows authentication?
> TIA
> Jim K.
>
application that uses MSDE as its backend database.
I am converting my application which currently uses Access 2000 to MSDE
SP3a.
I already have the install process together, but I an in a quandry as to
what security method to use when installing.
If I use SQL security and set the SA password, am I looking for issues in
the future when a client might possibly purchase another program that uses
MSDE and won't install because I have set the SA password?
If I use Windows authentication, then I am leaving my database open to
anybody with Admin privliges.
I use information within a database table to license my program, so I'd like
to keep it secured.
Can anybody tell me what they do for their applications? Install with SQL
or Windows authentication?
TIA
Jim K.
As for other applications using your instance - the MSDE license only allows
your instance to be used by your application. If another application
installs its database on your instance of MSDE, then not only is it
violating the MSDE licensing but it is rather poor behavior, in my opinion.
Users with Administrative rights will always have total access to your MSDE
installation, whether you use integrated (Windows) authentication or not. If
you are concerned about the security of your data then you should implement
some form of encryption for the any data that you consider important
intellectual property or secure data.
With my application we encrypt any important data and implement in the
database a simple way of protecting the licensing the program. I don't go to
great extremes to prevent unlicensed clients from connecting to the
database - just enought to make it a hassle to try to break any system I
might have put in place.
Jim
"Jim K" <krusej@.megsinet.net> wrote in message
news:%235%23vNcuiEHA.3876@.TK2MSFTNGP12.phx.gbl...
> This question is for anybody who has created & sold/distributed an
> application that uses MSDE as its backend database.
> I am converting my application which currently uses Access 2000 to MSDE
> SP3a.
> I already have the install process together, but I an in a quandry as to
> what security method to use when installing.
> If I use SQL security and set the SA password, am I looking for issues in
> the future when a client might possibly purchase another program that uses
> MSDE and won't install because I have set the SA password?
> If I use Windows authentication, then I am leaving my database open to
> anybody with Admin privliges.
> I use information within a database table to license my program, so I'd
like
> to keep it secured.
> Can anybody tell me what they do for their applications? Install with SQL
> or Windows authentication?
> TIA
> Jim K.
>
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
>
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
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
>
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
>
Wednesday, March 21, 2012
Displaying Windows Icons
Sir/Mam
I am creating a task manager functionality in c#.net 2003. I am checking only for open microsoft applications . If any microsoft application is opened ,it will display the application name for ex "Microsoft Word" in the listview control and its status "Running" . Now, I want to display the icon for Microsoft word or any other microsoft application, how should I display it in the ListView control.?
I have tried by taking it in the imagelist , because I have a class which returns the Windows Icons for these applications which are opened. But, I am unable to display it.
Please help me?
Thanks,
Shivanee.
You will want to post this in a .NET forum not a SQL Server forum.
Displaying Windows Icons
Sir/Mam
I am creating a task manager functionality in c#.net 2003. I am checking only for open microsoft applications . If any microsoft application is opened ,it will display the application name for ex "Microsoft Word" in the listview control and its status "Running" . Now, I want to display the icon for Microsoft word or any other microsoft application, how should I display it in the ListView control.?
I have tried by taking it in the imagelist , because I have a class which returns the Windows Icons for these applications which are opened. But, I am unable to display it.
Please help me?
Thanks,
Shivanee.
You will want to post this in a .NET forum not a SQL Server forum.
Monday, March 19, 2012
displaying the list of databases faster in enterprise manager console
Hello,
We have a sql server 2000 sp3 on windows 2000 sp4 svr machine.
On this server we have 59 databases.
Different user's pc connect to it using enterprise manager. Client PC are
mostly windows 2000 sp4 mdac2.8 or Windows XP sp1.
Each time one of these clients connect to the server to display the list of
databases, it takes about 45sec to 1.15 min. I beleive this behaviour comes
from the increasing number of DB (it was much faster when we had about 5 to
10 db). Is there a way to "cache" this catalog (or any other way) in order
to displays this list faster?
thanks
Have you considered using Query Analyzer to look at the list?
http://www.aspfaq.com/2455
Also, make sure you don't have auto-close set for any of the databases.
This forces EM to take a lot longer than it should to show the list...
http://www.aspfaq.com/2469
http://www.aspfaq.com/
(Reverse address to reply.)
<grille11@.yahoo.com> wrote in message
news:cjjpqo$aeh$1@.reader1.imaginet.fr...
> Hello,
> We have a sql server 2000 sp3 on windows 2000 sp4 svr machine.
> On this server we have 59 databases.
> Different user's pc connect to it using enterprise manager. Client PC are
> mostly windows 2000 sp4 mdac2.8 or Windows XP sp1.
> Each time one of these clients connect to the server to display the list
of
> databases, it takes about 45sec to 1.15 min. I beleive this behaviour
comes
> from the increasing number of DB (it was much faster when we had about 5
to
> 10 db). Is there a way to "cache" this catalog (or any other way) in
order
> to displays this list faster?
> thanks
>
We have a sql server 2000 sp3 on windows 2000 sp4 svr machine.
On this server we have 59 databases.
Different user's pc connect to it using enterprise manager. Client PC are
mostly windows 2000 sp4 mdac2.8 or Windows XP sp1.
Each time one of these clients connect to the server to display the list of
databases, it takes about 45sec to 1.15 min. I beleive this behaviour comes
from the increasing number of DB (it was much faster when we had about 5 to
10 db). Is there a way to "cache" this catalog (or any other way) in order
to displays this list faster?
thanks
Have you considered using Query Analyzer to look at the list?
http://www.aspfaq.com/2455
Also, make sure you don't have auto-close set for any of the databases.
This forces EM to take a lot longer than it should to show the list...
http://www.aspfaq.com/2469
http://www.aspfaq.com/
(Reverse address to reply.)
<grille11@.yahoo.com> wrote in message
news:cjjpqo$aeh$1@.reader1.imaginet.fr...
> Hello,
> We have a sql server 2000 sp3 on windows 2000 sp4 svr machine.
> On this server we have 59 databases.
> Different user's pc connect to it using enterprise manager. Client PC are
> mostly windows 2000 sp4 mdac2.8 or Windows XP sp1.
> Each time one of these clients connect to the server to display the list
of
> databases, it takes about 45sec to 1.15 min. I beleive this behaviour
comes
> from the increasing number of DB (it was much faster when we had about 5
to
> 10 db). Is there a way to "cache" this catalog (or any other way) in
order
> to displays this list faster?
> thanks
>
displaying the list of databases faster in enterprise manager console
Hello,
We have a sql server 2000 sp3 on windows 2000 sp4 svr machine.
On this server we have 59 databases.
Different user's pc connect to it using enterprise manager. Client PC are
mostly windows 2000 sp4 mdac2.8 or Windows XP sp1.
Each time one of these clients connect to the server to display the list of
databases, it takes about 45sec to 1.15 min. I beleive this behaviour comes
from the increasing number of DB (it was much faster when we had about 5 to
10 db). Is there a way to "cache" this catalog (or any other way) in order
to displays this list faster?
thanksHave you considered using Query Analyzer to look at the list?
http://www.aspfaq.com/2455
Also, make sure you don't have auto-close set for any of the databases.
This forces EM to take a lot longer than it should to show the list...
http://www.aspfaq.com/2469
--
http://www.aspfaq.com/
(Reverse address to reply.)
<grille11@.yahoo.com> wrote in message
news:cjjpqo$aeh$1@.reader1.imaginet.fr...
> Hello,
> We have a sql server 2000 sp3 on windows 2000 sp4 svr machine.
> On this server we have 59 databases.
> Different user's pc connect to it using enterprise manager. Client PC are
> mostly windows 2000 sp4 mdac2.8 or Windows XP sp1.
> Each time one of these clients connect to the server to display the list
of
> databases, it takes about 45sec to 1.15 min. I beleive this behaviour
comes
> from the increasing number of DB (it was much faster when we had about 5
to
> 10 db). Is there a way to "cache" this catalog (or any other way) in
order
> to displays this list faster?
> thanks
>
We have a sql server 2000 sp3 on windows 2000 sp4 svr machine.
On this server we have 59 databases.
Different user's pc connect to it using enterprise manager. Client PC are
mostly windows 2000 sp4 mdac2.8 or Windows XP sp1.
Each time one of these clients connect to the server to display the list of
databases, it takes about 45sec to 1.15 min. I beleive this behaviour comes
from the increasing number of DB (it was much faster when we had about 5 to
10 db). Is there a way to "cache" this catalog (or any other way) in order
to displays this list faster?
thanksHave you considered using Query Analyzer to look at the list?
http://www.aspfaq.com/2455
Also, make sure you don't have auto-close set for any of the databases.
This forces EM to take a lot longer than it should to show the list...
http://www.aspfaq.com/2469
--
http://www.aspfaq.com/
(Reverse address to reply.)
<grille11@.yahoo.com> wrote in message
news:cjjpqo$aeh$1@.reader1.imaginet.fr...
> Hello,
> We have a sql server 2000 sp3 on windows 2000 sp4 svr machine.
> On this server we have 59 databases.
> Different user's pc connect to it using enterprise manager. Client PC are
> mostly windows 2000 sp4 mdac2.8 or Windows XP sp1.
> Each time one of these clients connect to the server to display the list
of
> databases, it takes about 45sec to 1.15 min. I beleive this behaviour
comes
> from the increasing number of DB (it was much faster when we had about 5
to
> 10 db). Is there a way to "cache" this catalog (or any other way) in
order
> to displays this list faster?
> thanks
>
Subscribe to:
Posts (Atom)