Performace penality when selecting on a numeric column in SQL 2005

In SQL 2000 SP4 there was introduced a performance penalty when doing select
on numeric columns (article: http://support.microsoft.com/kb/899976/en-us).
It was possible to revert to the behaviour before SP4 by running the server
with a trace flag (-T9059).
Now I'm trying to find out the behaviour in SQL 2005. Is the behaviour same
as in SQL 2000 SP4 where you get an index scan instead of index seek. Or have
they fixed this in some other way? I know that the trace flag does no longer
exist in 2005.
> Is the behaviour same as in SQL 2000 SP4 where you get an index
> scan instead of index seek.
Sorry, I mean vice versa ofcourse:
Is the behaviour same as in SQL 2000 SP4 where you get an index _seek_
instead of index _scan_?
|||I ran the "scenario 2" query from the article under SQL 2005 SP2 and got an
index seek instead of scan. It looks like the issue has been addressed.
Hope this helps.
Dan Guzman
SQL Server MVP
"Stefan Solender" <StefanSolender@.discussions.microsoft.com> wrote in
message news:5D27738A-4DAC-4EBF-9F2E-B4DAC489C65F@.microsoft.com...
> Sorry, I mean vice versa ofcourse:
> Is the behaviour same as in SQL 2000 SP4 where you get an index _seek_
> instead of index _scan_?
>
|||Yes I did the same test too and got index scan. But that does not have to
mean the there is no other performance penalty. I just want some final
statement that I can give our customers.

Performace penality when selecting on a numeric column in SQL 2005

In SQL 2000 SP4 there was introduced a performance penalty when doing select
on numeric columns (article: http://support.microsoft.com/kb/899976/en-us).
It was possible to revert to the behaviour before SP4 by running the server
with a trace flag (-T9059).
Now I'm trying to find out the behaviour in SQL 2005. Is the behaviour same
as in SQL 2000 SP4 where you get an index scan instead of index seek. Or have
they fixed this in some other way? I know that the trace flag does no longer
exist in 2005.> Is the behaviour same as in SQL 2000 SP4 where you get an index
> scan instead of index seek.
Sorry, I mean vice versa ofcourse:
Is the behaviour same as in SQL 2000 SP4 where you get an index _seek_
instead of index _scan_?|||I ran the "scenario 2" query from the article under SQL 2005 SP2 and got an
index seek instead of scan. It looks like the issue has been addressed.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Stefan Solender" <StefanSolender@.discussions.microsoft.com> wrote in
message news:5D27738A-4DAC-4EBF-9F2E-B4DAC489C65F@.microsoft.com...
>> Is the behaviour same as in SQL 2000 SP4 where you get an index
>> scan instead of index seek.
> Sorry, I mean vice versa ofcourse:
> Is the behaviour same as in SQL 2000 SP4 where you get an index _seek_
> instead of index _scan_?
>|||Yes I did the same test too and got index scan. But that does not have to
mean the there is no other performance penalty. I just want some final
statement that I can give our customers.

Performace penality when selecting on a numeric column in SQL 2005

In SQL 2000 SP4 there was introduced a performance penalty when doing select
on numeric columns (article: http://support.microsoft.com/kb/899976/en-us).
It was possible to revert to the behaviour before SP4 by running the server
with a trace flag (-T9059).
Now I'm trying to find out the behaviour in SQL 2005. Is the behaviour same
as in SQL 2000 SP4 where you get an index scan instead of index seek. Or hav
e
they fixed this in some other way? I know that the trace flag does no longer
exist in 2005.> Is the behaviour same as in SQL 2000 SP4 where you get an index
> scan instead of index seek.
Sorry, I mean vice versa ofcourse:
Is the behaviour same as in SQL 2000 SP4 where you get an index _seek_
instead of index _scan_?|||I ran the "scenario 2" query from the article under SQL 2005 SP2 and got an
index seek instead of scan. It looks like the issue has been addressed.
Hope this helps.
Dan Guzman
SQL Server MVP
"Stefan Solender" <StefanSolender@.discussions.microsoft.com> wrote in
message news:5D27738A-4DAC-4EBF-9F2E-B4DAC489C65F@.microsoft.com...
> Sorry, I mean vice versa ofcourse:
> Is the behaviour same as in SQL 2000 SP4 where you get an index _seek_
> instead of index _scan_?
>

Performace montior

HI..
I have window s server 2003 with swl server 2000 sp3
I need to monitor some sql memory counter for that i have start preformace
counter for sql but when i will try to start that couter in prefomance
monitor it gives me following error
"The service was unable to add the counter '\SQLServer:Memory
Manager\Connection Memory (KB)' to the SQLServerMemory log or alert. This
log or alert will continue, but data for that counter will not be collected.
The error returned is: The specified object is not found on the system.
.. "
also i check that i am administrator and i login as administrator
pls help
-TP
Hi,
I had problem with SQL performance counters and following article helped me
to solve my problem.
If you see any error messages in SQL Error Log like:
Performance monitor shared memory setup failed: -1
Then check the article:
http://support.microsoft.com/default...812915&sd=tech
Which account are you using in Performance Logs and Alerts service?
Did you try LODCTR, UNLODCTR?
"tp" wrote:

> HI..
> I have window s server 2003 with swl server 2000 sp3
> I need to monitor some sql memory counter for that i have start preformace
> counter for sql but when i will try to start that couter in prefomance
> monitor it gives me following error
> "The service was unable to add the counter '\SQLServer:Memory
> Manager\Connection Memory (KB)' to the SQLServerMemory log or alert. This
> log or alert will continue, but data for that counter will not be collected.
> The error returned is: The specified object is not found on the system.
> . "
> also i check that i am administrator and i login as administrator
> pls help
> -TP
>

Performace montior

HI..
I have window s server 2003 with swl server 2000 sp3
I need to monitor some sql memory counter for that i have start preformace
counter for sql but when i will try to start that couter in prefomance
monitor it gives me following error
"The service was unable to add the counter '\SQLServer:Memory
Manager\Connection Memory (KB)' to the SQLServerMemory log or alert. This
log or alert will continue, but data for that counter will not be collected.
The error returned is: The specified object is not found on the system.
. "
also i check that i am administrator and i login as administrator
pls help
-TPHi,
I had problem with SQL performance counters and following article helped me
to solve my problem.
If you see any error messages in SQL Error Log like:
Performance monitor shared memory setup failed: -1
Then check the article:
http://support.microsoft.com/defaul...;812915&sd=tech
Which account are you using in Performance Logs and Alerts service?
Did you try LODCTR, UNLODCTR?
"tp" wrote:

> HI..
> I have window s server 2003 with swl server 2000 sp3
> I need to monitor some sql memory counter for that i have start preforma
ce
> counter for sql but when i will try to start that couter in prefomance
> monitor it gives me following error
> "The service was unable to add the counter '\SQLServer:Memory
> Manager\Connection Memory (KB)' to the SQLServerMemory log or alert. This
> log or alert will continue, but data for that counter will not be collecte
d.
> The error returned is: The specified object is not found on the system.
> . "
> also i check that i am administrator and i login as administrator
> pls help
> -TP
>

Performace montior

HI..
I have window s server 2003 with swl server 2000 sp3
I need to monitor some sql memory counter for that i have start preformace
counter for sql but when i will try to start that couter in prefomance
monitor it gives me following error
"The service was unable to add the counter '\SQLServer:Memory
Manager\Connection Memory (KB)' to the SQLServerMemory log or alert. This
log or alert will continue, but data for that counter will not be collected.
The error returned is: The specified object is not found on the system.
. "
also i check that i am administrator and i login as administrator
pls help
-TPHi,
I had problem with SQL performance counters and following article helped me
to solve my problem.
If you see any error messages in SQL Error Log like:
Performance monitor shared memory setup failed: -1
Then check the article:
http://support.microsoft.com/default.aspx?scid=kb;en-us;812915&sd=tech
Which account are you using in Performance Logs and Alerts service?
Did you try LODCTR, UNLODCTR?
"tp" wrote:
> HI..
> I have window s server 2003 with swl server 2000 sp3
> I need to monitor some sql memory counter for that i have start preformace
> counter for sql but when i will try to start that couter in prefomance
> monitor it gives me following error
> "The service was unable to add the counter '\SQLServer:Memory
> Manager\Connection Memory (KB)' to the SQLServerMemory log or alert. This
> log or alert will continue, but data for that counter will not be collected.
> The error returned is: The specified object is not found on the system.
> . "
> also i check that i am administrator and i login as administrator
> pls help
> -TP
>

Performace Monitor in Windows 2000

PerfMon under NT has a 'Always on top' option but the same
option can not be found in Windows 2000. Any ideas?
thanksHello,
My name is Michael and I would like to thank you for using Microsoft
newsgroup.
Under Windows NT, this feature is application-specific. So far as I know,
the option 'Always on top' does not exist in the performance application in
Windows 2000. That is by design so what we are able to do unfortunately is
very limited in Windows 2000.
Thanks for using Microsoft newsgroup.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||Hello,
Due to the anture of this issue, I think the current newsgroup is not the
best one for this problem. I would suggest that you post this question in
the microsoft.public.win2000.general newsgroup, which is primarily for
issues involving windows 2000 problem. The reason why we recommend posting
appropriately is you will get the most qualified pool of respondents, and
other partners who read the newsgroups regularly can either share their
knowledge or learn from your interaction with us. I hope the problem can be
resolved quickly.
Thank you for using our newsgroup.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

Performace Monitor and SQL Profiler

Hi,
I was running Performace Monitor and SQL Profiler to keep track of
changes on the server for period of a week. And for some reason this ONE
table keeps getting locked out. The third party vendor suggested that SQL
Profiler creates locks again the users database.
I though the SQL Profiler acquire any locks against user databases, it is
getting information form SQL API directly?
Also the performace counters I read had no affect on the SQL Server...
Is my understanding wrong? Can someone clearify it for me? Thanks!
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005Well... I'm not sure if SQL Profiler may cause locks or not however
Microsoft recommends using these kind of tools (SQL Profiler, Performance
Monitor) from another machine. Because they effect performance on the
machine they work. However, in this case they also recommend using a
dedicated connection between those two machines to avoid network traffic
because of using these tools if possible.
So, in your case, yes, they have affect on the SQL Server somehow.
--
Ekrem Ã?nsoy
"Mohit K. Gupta" <mohitkgupta@.msn.com> wrote in message
news:E838BE3D-DDAF-46C3-A9B0-7F102C59D806@.microsoft.com...
> Hi,
> I was running Performace Monitor and SQL Profiler to keep track of
> changes on the server for period of a week. And for some reason this ONE
> table keeps getting locked out. The third party vendor suggested that SQL
> Profiler creates locks again the users database.
> I though the SQL Profiler acquire any locks against user databases, it is
> getting information form SQL API directly?
> Also the performace counters I read had no affect on the SQL Server...
> Is my understanding wrong? Can someone clearify it for me? Thanks!
> --
> Mohit K. Gupta
> B.Sc. CS, Minor Japanese
> MCTS: SQL Server 2005|||I see, I will have to check that out on if it has any affect on user database
or not. But today I did a trace that logged about 250,000 records on
another database, and that server didn't notice any deadlocks or lock
timeouts. But I did take your advice and this time ran the SQL Monitor on a
different computer. So maybe that had some affect.
So I do suspect me running it on the serer caused locks but might have
caused some slow down. So I think the application is either timing out too
quickly or something else is a miss. Thank-you for your reply.
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Ekrem Ã?nsoy" wrote:
> Well... I'm not sure if SQL Profiler may cause locks or not however
> Microsoft recommends using these kind of tools (SQL Profiler, Performance
> Monitor) from another machine. Because they effect performance on the
> machine they work. However, in this case they also recommend using a
> dedicated connection between those two machines to avoid network traffic
> because of using these tools if possible.
> So, in your case, yes, they have affect on the SQL Server somehow.
> --
> Ekrem Ã?nsoy
>
> "Mohit K. Gupta" <mohitkgupta@.msn.com> wrote in message
> news:E838BE3D-DDAF-46C3-A9B0-7F102C59D806@.microsoft.com...
> > Hi,
> >
> > I was running Performace Monitor and SQL Profiler to keep track of
> > changes on the server for period of a week. And for some reason this ONE
> > table keeps getting locked out. The third party vendor suggested that SQL
> > Profiler creates locks again the users database.
> >
> > I though the SQL Profiler acquire any locks against user databases, it is
> > getting information form SQL API directly?
> >
> > Also the performace counters I read had no affect on the SQL Server...
> >
> > Is my understanding wrong? Can someone clearify it for me? Thanks!
> >
> > --
> > Mohit K. Gupta
> > B.Sc. CS, Minor Japanese
> > MCTS: SQL Server 2005
>|||Hi
SQL Profiler will not create database locks unless you are logging to a
database table, which is not usually recommended. In whioch case it is
unlikely that other applications are using that table (or database!)
If you can't log from a separate machine on a dedicated lan, you can use a
server side trace. Make sure that any trace files are not using the same
discs as SQL Server preferrably have fast spindles for it's exclusive use
(although this is rarely possible!)
If you use the SQL Profiler GUI it will use space on the TEMP directory
(even if logging to a file) make sure that this is not the system disc as
running out of space on that, will cause the system to hang.
John
"Mohit K. Gupta" wrote:
> I see, I will have to check that out on if it has any affect on user database
> or not. But today I did a trace that logged about 250,000 records on
> another database, and that server didn't notice any deadlocks or lock
> timeouts. But I did take your advice and this time ran the SQL Monitor on a
> different computer. So maybe that had some affect.
> So I do suspect me running it on the serer caused locks but might have
> caused some slow down. So I think the application is either timing out too
> quickly or something else is a miss. Thank-you for your reply.
> --
> Mohit K. Gupta
> B.Sc. CS, Minor Japanese
> MCTS: SQL Server 2005
>
> "Ekrem Ã?nsoy" wrote:
> > Well... I'm not sure if SQL Profiler may cause locks or not however
> > Microsoft recommends using these kind of tools (SQL Profiler, Performance
> > Monitor) from another machine. Because they effect performance on the
> > machine they work. However, in this case they also recommend using a
> > dedicated connection between those two machines to avoid network traffic
> > because of using these tools if possible.
> >
> > So, in your case, yes, they have affect on the SQL Server somehow.
> >
> > --
> > Ekrem Ã?nsoy
> >
> >
> >
> > "Mohit K. Gupta" <mohitkgupta@.msn.com> wrote in message
> > news:E838BE3D-DDAF-46C3-A9B0-7F102C59D806@.microsoft.com...
> > > Hi,
> > >
> > > I was running Performace Monitor and SQL Profiler to keep track of
> > > changes on the server for period of a week. And for some reason this ONE
> > > table keeps getting locked out. The third party vendor suggested that SQL
> > > Profiler creates locks again the users database.
> > >
> > > I though the SQL Profiler acquire any locks against user databases, it is
> > > getting information form SQL API directly?
> > >
> > > Also the performace counters I read had no affect on the SQL Server...
> > >
> > > Is my understanding wrong? Can someone clearify it for me? Thanks!
> > >
> > > --
> > > Mohit K. Gupta
> > > B.Sc. CS, Minor Japanese
> > > MCTS: SQL Server 2005
> >|||Thanks Guys ;-).
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"John Bell" wrote:
> Hi
> SQL Profiler will not create database locks unless you are logging to a
> database table, which is not usually recommended. In whioch case it is
> unlikely that other applications are using that table (or database!)
> If you can't log from a separate machine on a dedicated lan, you can use a
> server side trace. Make sure that any trace files are not using the same
> discs as SQL Server preferrably have fast spindles for it's exclusive use
> (although this is rarely possible!)
> If you use the SQL Profiler GUI it will use space on the TEMP directory
> (even if logging to a file) make sure that this is not the system disc as
> running out of space on that, will cause the system to hang.
> John
> "Mohit K. Gupta" wrote:
> > I see, I will have to check that out on if it has any affect on user database
> > or not. But today I did a trace that logged about 250,000 records on
> > another database, and that server didn't notice any deadlocks or lock
> > timeouts. But I did take your advice and this time ran the SQL Monitor on a
> > different computer. So maybe that had some affect.
> >
> > So I do suspect me running it on the serer caused locks but might have
> > caused some slow down. So I think the application is either timing out too
> > quickly or something else is a miss. Thank-you for your reply.
> >
> > --
> > Mohit K. Gupta
> > B.Sc. CS, Minor Japanese
> > MCTS: SQL Server 2005
> >
> >
> > "Ekrem Ã?nsoy" wrote:
> >
> > > Well... I'm not sure if SQL Profiler may cause locks or not however
> > > Microsoft recommends using these kind of tools (SQL Profiler, Performance
> > > Monitor) from another machine. Because they effect performance on the
> > > machine they work. However, in this case they also recommend using a
> > > dedicated connection between those two machines to avoid network traffic
> > > because of using these tools if possible.
> > >
> > > So, in your case, yes, they have affect on the SQL Server somehow.
> > >
> > > --
> > > Ekrem Ã?nsoy
> > >
> > >
> > >
> > > "Mohit K. Gupta" <mohitkgupta@.msn.com> wrote in message
> > > news:E838BE3D-DDAF-46C3-A9B0-7F102C59D806@.microsoft.com...
> > > > Hi,
> > > >
> > > > I was running Performace Monitor and SQL Profiler to keep track of
> > > > changes on the server for period of a week. And for some reason this ONE
> > > > table keeps getting locked out. The third party vendor suggested that SQL
> > > > Profiler creates locks again the users database.
> > > >
> > > > I though the SQL Profiler acquire any locks against user databases, it is
> > > > getting information form SQL API directly?
> > > >
> > > > Also the performace counters I read had no affect on the SQL Server...
> > > >
> > > > Is my understanding wrong? Can someone clearify it for me? Thanks!
> > > >
> > > > --
> > > > Mohit K. Gupta
> > > > B.Sc. CS, Minor Japanese
> > > > MCTS: SQL Server 2005
> > >

Performace Monitor and SQL Profiler

I see, I will have to check that out on if it has any affect on user database
or not. But today I did a trace that logged about 250,000 records on
another database, and that server didn't notice any deadlocks or lock
timeouts. But I did take your advice and this time ran the SQL Monitor on a
different computer. So maybe that had some affect.
So I do suspect me running it on the serer caused locks but might have
caused some slow down. So I think the application is either timing out too
quickly or something else is a miss. Thank-you for your reply.
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Ekrem ?nsoy" wrote:

> Well... I'm not sure if SQL Profiler may cause locks or not however
> Microsoft recommends using these kind of tools (SQL Profiler, Performance
> Monitor) from another machine. Because they effect performance on the
> machine they work. However, in this case they also recommend using a
> dedicated connection between those two machines to avoid network traffic
> because of using these tools if possible.
> So, in your case, yes, they have affect on the SQL Server somehow.
> --
> Ekrem ?nsoy
>
> "Mohit K. Gupta" <mohitkgupta@.msn.com> wrote in message
> news:E838BE3D-DDAF-46C3-A9B0-7F102C59D806@.microsoft.com...
>
Hi
SQL Profiler will not create database locks unless you are logging to a
database table, which is not usually recommended. In whioch case it is
unlikely that other applications are using that table (or database!)
If you can't log from a separate machine on a dedicated lan, you can use a
server side trace. Make sure that any trace files are not using the same
discs as SQL Server preferrably have fast spindles for it's exclusive use
(although this is rarely possible!)
If you use the SQL Profiler GUI it will use space on the TEMP directory
(even if logging to a file) make sure that this is not the system disc as
running out of space on that, will cause the system to hang.
John
"Mohit K. Gupta" wrote:
[vbcol=seagreen]
> I see, I will have to check that out on if it has any affect on user database
> or not. But today I did a trace that logged about 250,000 records on
> another database, and that server didn't notice any deadlocks or lock
> timeouts. But I did take your advice and this time ran the SQL Monitor on a
> different computer. So maybe that had some affect.
> So I do suspect me running it on the serer caused locks but might have
> caused some slow down. So I think the application is either timing out too
> quickly or something else is a miss. Thank-you for your reply.
> --
> Mohit K. Gupta
> B.Sc. CS, Minor Japanese
> MCTS: SQL Server 2005
>
> "Ekrem ?nsoy" wrote:
|||Thanks Guys ;-).
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> SQL Profiler will not create database locks unless you are logging to a
> database table, which is not usually recommended. In whioch case it is
> unlikely that other applications are using that table (or database!)
> If you can't log from a separate machine on a dedicated lan, you can use a
> server side trace. Make sure that any trace files are not using the same
> discs as SQL Server preferrably have fast spindles for it's exclusive use
> (although this is rarely possible!)
> If you use the SQL Profiler GUI it will use space on the TEMP directory
> (even if logging to a file) make sure that this is not the system disc as
> running out of space on that, will cause the system to hang.
> John
> "Mohit K. Gupta" wrote:

performace issue

In my company, we have a central database and 4 remote databases. the
central database replicate same database into 4 remote database in parallel.
the replication is transaction replication and the data is input in the
central database and replicated into remote databases. there are some
triggers in the remote database that retrieve data that was replicated from
central database into other table. the design is like this. it is wierd that
one of remote database is very slow and replication locking the table and
client application is blocked out. the client application is hang. the
replication is very slow, 4 rows/minute. other 3 remote db are ok. I don't
know why only one remote db is ofen happened such thing. The other remote
databases were no problem, and the replication doesn't lock the table. I know
the triggers are bad, especially work with replication. But why other remote
db were ok. I don't know if there are some other problem. can dbcc dbreindex
be help? Thanks.
I would make sure indexes on the keys exist. Replication is not magic
either, you can monitor it just like normal database activity. I would
suggest running the profiler. Use the duration template, but filter
for say >2000ms.

Performace Bug with Sage MMS

Andy Goodwin wrote:
> I'm just about to implement SQL 2005 in my business to run Sage MMS 3.5. I
've
> just been informed that 2005 has a performance bug (making SQL 2005 six ti
mes
> slower than 2000) that will microsoft will not release a fix for until the
> next release of SQL Server. Does anyone know any details?
Care to share your source? I'm sorry, but if you truly believe such a
thing to be true, I have some all-natural male enhancement products you
might be interested in, guaranteed to have no side-effects...I'm just about to implement SQL 2005 in my business to run Sage MMS 3.5. I'v
e
just been informed that 2005 has a performance bug (making SQL 2005 six time
s
slower than 2000) that will microsoft will not release a fix for until the
next release of SQL Server. Does anyone know any details?
--
Andy Goodwin|||Andy Goodwin wrote:
> I'm just about to implement SQL 2005 in my business to run Sage MMS 3.5. I
've
> just been informed that 2005 has a performance bug (making SQL 2005 six ti
mes
> slower than 2000) that will microsoft will not release a fix for until the
> next release of SQL Server. Does anyone know any details?
Care to share your source? I'm sorry, but if you truly believe such a
thing to be true, I have some all-natural male enhancement products you
might be interested in, guaranteed to have no side-effects...

Performace Bug with Sage MMS

I'm just about to implement SQL 2005 in my business to run Sage MMS 3.5. I've
just been informed that 2005 has a performance bug (making SQL 2005 six times
slower than 2000) that will microsoft will not release a fix for until the
next release of SQL Server. Does anyone know any details?
--
Andy GoodwinAndy Goodwin wrote:
> I'm just about to implement SQL 2005 in my business to run Sage MMS 3.5. I've
> just been informed that 2005 has a performance bug (making SQL 2005 six times
> slower than 2000) that will microsoft will not release a fix for until the
> next release of SQL Server. Does anyone know any details?
Care to share your source? I'm sorry, but if you truly believe such a
thing to be true, I have some all-natural male enhancement products you
might be interested in, guaranteed to have no side-effects...