performance problems

hi!
we have developed an application with sql server 2000 as database server. we
still have enormous performance problem and so we started to search for
reasons.
now we made a test were we send a sql query (about 10.000 data records) to
the server and were quite wondering. when we made the test with a lokal pc,
sometimes really old boys, it took some seconds to finish the query. with our
server (dual xenon board, 2,6 mhz, 2 gb mem) it lasts over 3 minutes! when we
check the performance monitor of the server he will not have any ressource
troubles. i really dont know which processor the server is using ;-)
how could that happen? are there any troubles with dual boards?
i have also check the processor properties of the server. there are still
two processors listed. the settings should be ok unless i could not change
the settings.
also the disk is ok.
would be great if someone could give me a hint.
greetings,
markusMarkus hi,
Consider that there are many things to check in order to issue a best
performance in SQL 2000. First of all, you need to check the disk I/O. The
partitioning is one other thing.
In the Microsoft SQL 2000 resource kit there is the following articl
http://www.microsoft.com/resources/documentation/sql/2000/all/reskit/en-us/part5/c2061.mspx
In this article you will find plenty of information regarding performance in
RDBMS
HTH
Andreas
"markus" wrote:
> hi!
> we have developed an application with sql server 2000 as database server. we
> still have enormous performance problem and so we started to search for
> reasons.
> now we made a test were we send a sql query (about 10.000 data records) to
> the server and were quite wondering. when we made the test with a lokal pc,
> sometimes really old boys, it took some seconds to finish the query. with our
> server (dual xenon board, 2,6 mhz, 2 gb mem) it lasts over 3 minutes! when we
> check the performance monitor of the server he will not have any ressource
> troubles. i really dont know which processor the server is using ;-)
> how could that happen? are there any troubles with dual boards?
> i have also check the processor properties of the server. there are still
> two processors listed. the settings should be ok unless i could not change
> the settings.
> also the disk is ok.
> would be great if someone could give me a hint.
> greetings,
> markus
>
>|||hi andreas,
thanks for the link. i went through it quickly. sure there are a lot of
important things mentioned. but this still can't explain the gap of my
performance test. and i did not notice hardly any change in the performance
monitor during the test. the cpu load is about 7% and it's the same with ram.
regards,
markus
"Andreas Mavrogenis" wrote:
> Markus hi,
> Consider that there are many things to check in order to issue a best
> performance in SQL 2000. First of all, you need to check the disk I/O. The
> partitioning is one other thing.
> In the Microsoft SQL 2000 resource kit there is the following article
> http://www.microsoft.com/resources/documentation/sql/2000/all/reskit/en-us/part5/c2061.mspx
> In this article you will find plenty of information regarding performance in
> RDBMS
> HTH
> Andreas
> "markus" wrote:
> > hi!
> >
> > we have developed an application with sql server 2000 as database server. we
> > still have enormous performance problem and so we started to search for
> > reasons.
> >
> > now we made a test were we send a sql query (about 10.000 data records) to
> > the server and were quite wondering. when we made the test with a lokal pc,
> > sometimes really old boys, it took some seconds to finish the query. with our
> > server (dual xenon board, 2,6 mhz, 2 gb mem) it lasts over 3 minutes! when we
> > check the performance monitor of the server he will not have any ressource
> > troubles. i really dont know which processor the server is using ;-)
> >
> > how could that happen? are there any troubles with dual boards?
> >
> > i have also check the processor properties of the server. there are still
> > two processors listed. the settings should be ok unless i could not change
> > the settings.
> > also the disk is ok.
> >
> > would be great if someone could give me a hint.
> >
> > greetings,
> > markus
> >
> >
> >
> >|||Hi,
In your test, you run the query in a single processor pc with no-one loged
in. In your server is always a little bit slower. If your server is a HT
server, you will see in the task manager (tab performance), 4 CPU histograms.
Notice if running the query, captures only one CPU. If this is happening,
check your code if there is a max dop = 1 option. This option is to run the
query only in one processor.
Also, you must tell us if your table has any indexes. If there are, try to
re-index them.
FInally, you must check your disks. Is it possible that you have RAID-5 for
both mdf and ldf files?
What is the configuration ?
HTH
Andreas
"markus" wrote:
> hi andreas,
> thanks for the link. i went through it quickly. sure there are a lot of
> important things mentioned. but this still can't explain the gap of my
> performance test. and i did not notice hardly any change in the performance
> monitor during the test. the cpu load is about 7% and it's the same with ram.
> regards,
> markus
> "Andreas Mavrogenis" wrote:
> > Markus hi,
> >
> > Consider that there are many things to check in order to issue a best
> > performance in SQL 2000. First of all, you need to check the disk I/O. The
> > partitioning is one other thing.
> >
> > In the Microsoft SQL 2000 resource kit there is the following article
> > http://www.microsoft.com/resources/documentation/sql/2000/all/reskit/en-us/part5/c2061.mspx
> >
> > In this article you will find plenty of information regarding performance in
> > RDBMS
> >
> > HTH
> > Andreas
> >
> > "markus" wrote:
> >
> > > hi!
> > >
> > > we have developed an application with sql server 2000 as database server. we
> > > still have enormous performance problem and so we started to search for
> > > reasons.
> > >
> > > now we made a test were we send a sql query (about 10.000 data records) to
> > > the server and were quite wondering. when we made the test with a lokal pc,
> > > sometimes really old boys, it took some seconds to finish the query. with our
> > > server (dual xenon board, 2,6 mhz, 2 gb mem) it lasts over 3 minutes! when we
> > > check the performance monitor of the server he will not have any ressource
> > > troubles. i really dont know which processor the server is using ;-)
> > >
> > > how could that happen? are there any troubles with dual boards?
> > >
> > > i have also check the processor properties of the server. there are still
> > > two processors listed. the settings should be ok unless i could not change
> > > the settings.
> > > also the disk is ok.
> > >
> > > would be great if someone could give me a hint.
> > >
> > > greetings,
> > > markus
> > >
> > >
> > >
> > >|||hi,
yes, we use a raid 5. the other traffic load on the server is not really
heavy.
at the end of my post i attached the query, nothing sophisticated.
thanks for your help so far,
***********************
if exists (select 'TRUE' from sysobjects where name = 'performance' and
type = 'u')
drop table performance
go
CREATE TABLE performance (
ident int NOT NULL ,
blabla varchar (250) COLLATE Latin1_General_CI_AS NOT NULL ,
datum datetime NOT NULL CONSTRAINT DF__performan__datum__102C51FF DEFAULT
(getdate()),
rowguid uniqueidentifier NOT NULL CONSTRAINT DF__performan__rowgu__11207638
DEFAULT (newid()),
Cash money not null,
CONSTRAINT pk_performance PRIMARY KEY CLUSTERED (ident),
CONSTRAINT ux_performance UNIQUE NONCLUSTERED (blabla)
)
GO
go
set nocount on
go
declare @.i int,
@.count int
set @.count = 10000
select @.i = isnull(max(ident) + 1, 1) from performance
set @.count = @.count + @.i
while @.i < @.count begin
insert into performance (ident, blabla, cash)
values (@.i,
convert(varchar(10), @.i) +
'blablablablablablablablablablablablablablabla' + convert(varchar(10), @.i),
rand(@.i)
)
set @.i = @.i + 1
end
go
select * from performance
go
"Andreas Mavrogenis" wrote:
> Hi,
> In your test, you run the query in a single processor pc with no-one loged
> in. In your server is always a little bit slower. If your server is a HT
> server, you will see in the task manager (tab performance), 4 CPU histograms.
> Notice if running the query, captures only one CPU. If this is happening,
> check your code if there is a max dop = 1 option. This option is to run the
> query only in one processor.
> Also, you must tell us if your table has any indexes. If there are, try to
> re-index them.
> FInally, you must check your disks. Is it possible that you have RAID-5 for
> both mdf and ldf files?
> What is the configuration ?
> HTH
> Andreas
>
> "markus" wrote:
> > hi andreas,
> >
> > thanks for the link. i went through it quickly. sure there are a lot of
> > important things mentioned. but this still can't explain the gap of my
> > performance test. and i did not notice hardly any change in the performance
> > monitor during the test. the cpu load is about 7% and it's the same with ram.
> >
> > regards,
> > markus
> >
> > "Andreas Mavrogenis" wrote:
> >
> > > Markus hi,
> > >
> > > Consider that there are many things to check in order to issue a best
> > > performance in SQL 2000. First of all, you need to check the disk I/O. The
> > > partitioning is one other thing.
> > >
> > > In the Microsoft SQL 2000 resource kit there is the following article
> > > http://www.microsoft.com/resources/documentation/sql/2000/all/reskit/en-us/part5/c2061.mspx
> > >
> > > In this article you will find plenty of information regarding performance in
> > > RDBMS
> > >
> > > HTH
> > > Andreas
> > >
> > > "markus" wrote:
> > >
> > > > hi!
> > > >
> > > > we have developed an application with sql server 2000 as database server. we
> > > > still have enormous performance problem and so we started to search for
> > > > reasons.
> > > >
> > > > now we made a test were we send a sql query (about 10.000 data records) to
> > > > the server and were quite wondering. when we made the test with a lokal pc,
> > > > sometimes really old boys, it took some seconds to finish the query. with our
> > > > server (dual xenon board, 2,6 mhz, 2 gb mem) it lasts over 3 minutes! when we
> > > > check the performance monitor of the server he will not have any ressource
> > > > troubles. i really dont know which processor the server is using ;-)
> > > >
> > > > how could that happen? are there any troubles with dual boards?
> > > >
> > > > i have also check the processor properties of the server. there are still
> > > > two processors listed. the settings should be ok unless i could not change
> > > > the settings.
> > > > also the disk is ok.
> > > >
> > > > would be great if someone could give me a hint.
> > > >
> > > > greetings,
> > > > markus
> > > >
> > > >
> > > >
> > > >|||Hi,
Try to change your while statement with cursor. You will find sample code in
BOL. It works better!. Also, the RAID-5 is mostly for lots of reads and not
for writes. If you can, add two more disks in your array, make them RAID 1
and detach/attach the log (ldf ) file to this new drive.
HTH
Andreas
"markus" wrote:
> hi,
> yes, we use a raid 5. the other traffic load on the server is not really
> heavy.
> at the end of my post i attached the query, nothing sophisticated.
> thanks for your help so far,
> ***********************
> if exists (select 'TRUE' from sysobjects where name = 'performance' and
> type = 'u')
> drop table performance
> go
> CREATE TABLE performance (
> ident int NOT NULL ,
> blabla varchar (250) COLLATE Latin1_General_CI_AS NOT NULL ,
> datum datetime NOT NULL CONSTRAINT DF__performan__datum__102C51FF DEFAULT
> (getdate()),
> rowguid uniqueidentifier NOT NULL CONSTRAINT DF__performan__rowgu__11207638
> DEFAULT (newid()),
> Cash money not null,
> CONSTRAINT pk_performance PRIMARY KEY CLUSTERED (ident),
> CONSTRAINT ux_performance UNIQUE NONCLUSTERED (blabla)
> )
> GO
> go
> set nocount on
> go
> declare @.i int,
> @.count int
> set @.count = 10000
> select @.i = isnull(max(ident) + 1, 1) from performance
> set @.count = @.count + @.i
> while @.i < @.count begin
> insert into performance (ident, blabla, cash)
> values (@.i,
> convert(varchar(10), @.i) +
> 'blablablablablablablablablablablablablablabla' + convert(varchar(10), @.i),
> rand(@.i)
> )
> set @.i = @.i + 1
> end
> go
> select * from performance
> go
> "Andreas Mavrogenis" wrote:
> > Hi,
> >
> > In your test, you run the query in a single processor pc with no-one loged
> > in. In your server is always a little bit slower. If your server is a HT
> > server, you will see in the task manager (tab performance), 4 CPU histograms.
> > Notice if running the query, captures only one CPU. If this is happening,
> > check your code if there is a max dop = 1 option. This option is to run the
> > query only in one processor.
> >
> > Also, you must tell us if your table has any indexes. If there are, try to
> > re-index them.
> >
> > FInally, you must check your disks. Is it possible that you have RAID-5 for
> > both mdf and ldf files?
> >
> > What is the configuration ?
> >
> > HTH
> > Andreas
> >
> >
> > "markus" wrote:
> >
> > > hi andreas,
> > >
> > > thanks for the link. i went through it quickly. sure there are a lot of
> > > important things mentioned. but this still can't explain the gap of my
> > > performance test. and i did not notice hardly any change in the performance
> > > monitor during the test. the cpu load is about 7% and it's the same with ram.
> > >
> > > regards,
> > > markus
> > >
> > > "Andreas Mavrogenis" wrote:
> > >
> > > > Markus hi,
> > > >
> > > > Consider that there are many things to check in order to issue a best
> > > > performance in SQL 2000. First of all, you need to check the disk I/O. The
> > > > partitioning is one other thing.
> > > >
> > > > In the Microsoft SQL 2000 resource kit there is the following article
> > > > http://www.microsoft.com/resources/documentation/sql/2000/all/reskit/en-us/part5/c2061.mspx
> > > >
> > > > In this article you will find plenty of information regarding performance in
> > > > RDBMS
> > > >
> > > > HTH
> > > > Andreas
> > > >
> > > > "markus" wrote:
> > > >
> > > > > hi!
> > > > >
> > > > > we have developed an application with sql server 2000 as database server. we
> > > > > still have enormous performance problem and so we started to search for
> > > > > reasons.
> > > > >
> > > > > now we made a test were we send a sql query (about 10.000 data records) to
> > > > > the server and were quite wondering. when we made the test with a lokal pc,
> > > > > sometimes really old boys, it took some seconds to finish the query. with our
> > > > > server (dual xenon board, 2,6 mhz, 2 gb mem) it lasts over 3 minutes! when we
> > > > > check the performance monitor of the server he will not have any ressource
> > > > > troubles. i really dont know which processor the server is using ;-)
> > > > >
> > > > > how could that happen? are there any troubles with dual boards?
> > > > >
> > > > > i have also check the processor properties of the server. there are still
> > > > > two processors listed. the settings should be ok unless i could not change
> > > > > the settings.
> > > > > also the disk is ok.
> > > > >
> > > > > would be great if someone could give me a hint.
> > > > >
> > > > > greetings,
> > > > > markus
> > > > >
> > > > >
> > > > >
> > > > >|||hi,
unfortunately i have to use raid 5. i should not mention that there are
running all windows things and no only the database stuff.
but can it be that the raid 5 causes such a gap? with a normal pc it took
appr. 10 sec to carry out the statement. with the server i need more than 3
minutes! and when raid 5 causes that delay i should even notice some tasks in
the performance monitor. it's really confusing.
regards,
markus
"Andreas Mavrogenis" wrote:
> Hi,
> Try to change your while statement with cursor. You will find sample code in
> BOL. It works better!. Also, the RAID-5 is mostly for lots of reads and not
> for writes. If you can, add two more disks in your array, make them RAID 1
> and detach/attach the log (ldf ) file to this new drive.
> HTH
> Andreas
> "markus" wrote:
> > hi,
> >
> > yes, we use a raid 5. the other traffic load on the server is not really
> > heavy.
> > at the end of my post i attached the query, nothing sophisticated.
> >
> > thanks for your help so far,
> >
> > ***********************
> > if exists (select 'TRUE' from sysobjects where name = 'performance' and
> > type = 'u')
> > drop table performance
> > go
> > CREATE TABLE performance (
> > ident int NOT NULL ,
> > blabla varchar (250) COLLATE Latin1_General_CI_AS NOT NULL ,
> > datum datetime NOT NULL CONSTRAINT DF__performan__datum__102C51FF DEFAULT
> > (getdate()),
> > rowguid uniqueidentifier NOT NULL CONSTRAINT DF__performan__rowgu__11207638
> > DEFAULT (newid()),
> > Cash money not null,
> > CONSTRAINT pk_performance PRIMARY KEY CLUSTERED (ident),
> > CONSTRAINT ux_performance UNIQUE NONCLUSTERED (blabla)
> >
> > )
> > GO
> > go
> > set nocount on
> > go
> > declare @.i int,
> > @.count int
> >
> > set @.count = 10000
> > select @.i = isnull(max(ident) + 1, 1) from performance
> > set @.count = @.count + @.i
> >
> > while @.i < @.count begin
> > insert into performance (ident, blabla, cash)
> > values (@.i,
> > convert(varchar(10), @.i) +
> > 'blablablablablablablablablablablablablablabla' + convert(varchar(10), @.i),
> > rand(@.i)
> > )
> > set @.i = @.i + 1
> > end
> >
> > go
> > select * from performance
> > go
> >
> > "Andreas Mavrogenis" wrote:
> >
> > > Hi,
> > >
> > > In your test, you run the query in a single processor pc with no-one loged
> > > in. In your server is always a little bit slower. If your server is a HT
> > > server, you will see in the task manager (tab performance), 4 CPU histograms.
> > > Notice if running the query, captures only one CPU. If this is happening,
> > > check your code if there is a max dop = 1 option. This option is to run the
> > > query only in one processor.
> > >
> > > Also, you must tell us if your table has any indexes. If there are, try to
> > > re-index them.
> > >
> > > FInally, you must check your disks. Is it possible that you have RAID-5 for
> > > both mdf and ldf files?
> > >
> > > What is the configuration ?
> > >
> > > HTH
> > > Andreas
> > >
> > >
> > > "markus" wrote:
> > >
> > > > hi andreas,
> > > >
> > > > thanks for the link. i went through it quickly. sure there are a lot of
> > > > important things mentioned. but this still can't explain the gap of my
> > > > performance test. and i did not notice hardly any change in the performance
> > > > monitor during the test. the cpu load is about 7% and it's the same with ram.
> > > >
> > > > regards,
> > > > markus
> > > >
> > > > "Andreas Mavrogenis" wrote:
> > > >
> > > > > Markus hi,
> > > > >
> > > > > Consider that there are many things to check in order to issue a best
> > > > > performance in SQL 2000. First of all, you need to check the disk I/O. The
> > > > > partitioning is one other thing.
> > > > >
> > > > > In the Microsoft SQL 2000 resource kit there is the following article
> > > > > http://www.microsoft.com/resources/documentation/sql/2000/all/reskit/en-us/part5/c2061.mspx
> > > > >
> > > > > In this article you will find plenty of information regarding performance in
> > > > > RDBMS
> > > > >
> > > > > HTH
> > > > > Andreas
> > > > >
> > > > > "markus" wrote:
> > > > >
> > > > > > hi!
> > > > > >
> > > > > > we have developed an application with sql server 2000 as database server. we
> > > > > > still have enormous performance problem and so we started to search for
> > > > > > reasons.
> > > > > >
> > > > > > now we made a test were we send a sql query (about 10.000 data records) to
> > > > > > the server and were quite wondering. when we made the test with a lokal pc,
> > > > > > sometimes really old boys, it took some seconds to finish the query. with our
> > > > > > server (dual xenon board, 2,6 mhz, 2 gb mem) it lasts over 3 minutes! when we
> > > > > > check the performance monitor of the server he will not have any ressource
> > > > > > troubles. i really dont know which processor the server is using ;-)
> > > > > >
> > > > > > how could that happen? are there any troubles with dual boards?
> > > > > >
> > > > > > i have also check the processor properties of the server. there are still
> > > > > > two processors listed. the settings should be ok unless i could not change
> > > > > > the settings.
> > > > > > also the disk is ok.
> > > > > >
> > > > > > would be great if someone could give me a hint.
> > > > > >
> > > > > > greetings,
> > > > > > markus
> > > > > >
> > > > > >
> > > > > >
> > > > > >|||Have you looked at the execution plans for both queries on the server and
client? Is there any difference?
Also check the query Analyzer version on the client and the server.
"markus" <markus@.discussions.microsoft.com> wrote in message
news:5F17AC7D-EA4A-400C-A67C-22F578AE016D@.microsoft.com...
> hi!
> we have developed an application with sql server 2000 as database server.
> we
> still have enormous performance problem and so we started to search for
> reasons.
> now we made a test were we send a sql query (about 10.000 data records) to
> the server and were quite wondering. when we made the test with a lokal
> pc,
> sometimes really old boys, it took some seconds to finish the query. with
> our
> server (dual xenon board, 2,6 mhz, 2 gb mem) it lasts over 3 minutes! when
> we
> check the performance monitor of the server he will not have any ressource
> troubles. i really dont know which processor the server is using ;-)
> how could that happen? are there any troubles with dual boards?
> i have also check the processor properties of the server. there are still
> two processors listed. the settings should be ok unless i could not change
> the settings.
> also the disk is ok.
> would be great if someone could give me a hint.
> greetings,
> markus
>
>|||both tests took place on a sql server 2k, just the hardware is really
different ;-)
the query analyzer version is the same.
"Richard Ding" wrote:
> Have you looked at the execution plans for both queries on the server and
> client? Is there any difference?
> Also check the query Analyzer version on the client and the server.
>
> "markus" <markus@.discussions.microsoft.com> wrote in message
> news:5F17AC7D-EA4A-400C-A67C-22F578AE016D@.microsoft.com...
> > hi!
> >
> > we have developed an application with sql server 2000 as database server.
> > we
> > still have enormous performance problem and so we started to search for
> > reasons.
> >
> > now we made a test were we send a sql query (about 10.000 data records) to
> > the server and were quite wondering. when we made the test with a lokal
> > pc,
> > sometimes really old boys, it took some seconds to finish the query. with
> > our
> > server (dual xenon board, 2,6 mhz, 2 gb mem) it lasts over 3 minutes! when
> > we
> > check the performance monitor of the server he will not have any ressource
> > troubles. i really dont know which processor the server is using ;-)
> >
> > how could that happen? are there any troubles with dual boards?
> >
> > i have also check the processor properties of the server. there are still
> > two processors listed. the settings should be ok unless i could not change
> > the settings.
> > also the disk is ok.
> >
> > would be great if someone could give me a hint.
> >
> > greetings,
> > markus
> >
> >
> >
> >
>
>|||Marcus Hi,
Please check this article, and follow it's steps to optimize your query
http://support.microsoft.com/default.aspx?scid=kb;en-us;243589
If you don't have any results, try to disable one of your two processors,
stop-start SQL Services and run the query again. Notice the task manager
performance to see it's activity.
If not, try to create a trace file and capture what your query is doing.
Use this article to analyze your performance data
http://support.microsoft.com/default.aspx?scid=kb;en-us;283886
HTH
Andreas
"markus" wrote:
> both tests took place on a sql server 2k, just the hardware is really
> different ;-)
> the query analyzer version is the same.
>
> "Richard Ding" wrote:
> > Have you looked at the execution plans for both queries on the server and
> > client? Is there any difference?
> >
> > Also check the query Analyzer version on the client and the server.
> >
> >
> > "markus" <markus@.discussions.microsoft.com> wrote in message
> > news:5F17AC7D-EA4A-400C-A67C-22F578AE016D@.microsoft.com...
> > > hi!
> > >
> > > we have developed an application with sql server 2000 as database server.
> > > we
> > > still have enormous performance problem and so we started to search for
> > > reasons.
> > >
> > > now we made a test were we send a sql query (about 10.000 data records) to
> > > the server and were quite wondering. when we made the test with a lokal
> > > pc,
> > > sometimes really old boys, it took some seconds to finish the query. with
> > > our
> > > server (dual xenon board, 2,6 mhz, 2 gb mem) it lasts over 3 minutes! when
> > > we
> > > check the performance monitor of the server he will not have any ressource
> > > troubles. i really dont know which processor the server is using ;-)
> > >
> > > how could that happen? are there any troubles with dual boards?
> > >
> > > i have also check the processor properties of the server. there are still
> > > two processors listed. the settings should be ok unless i could not change
> > > the settings.
> > > also the disk is ok.
> > >
> > > would be great if someone could give me a hint.
> > >
> > > greetings,
> > > markus
> > >
> > >
> > >
> > >
> >
> >
> >|||hi andreas!
thank you for your information. i will optimize the query in the way
described but thats still another topic. today i tried to change the server
properties but therefore i have to restart teh server and that's possible in
the evening hours :-(
i analyzed the server via the profiler and compared the result of the server
and another pc:
reads writes duration
server: 43151 322 228796
"normal pc": 43148 325 333
so far so good. bat how can I identify where the bottleneck really is. are
there special event catagories which concern I/O.
my further activities are:
1) disable von cpu
2) reinstall sql server on existing server
3) install sql server on "stand-alone server"
what conerns issue 2 i will also check if there are existing problems with
an old msde installation.
regards,
markus
"Andreas Mavrogenis" wrote:
> Marcus Hi,
> Please check this article, and follow it's steps to optimize your query
> http://support.microsoft.com/default.aspx?scid=kb;en-us;243589
> If you don't have any results, try to disable one of your two processors,
> stop-start SQL Services and run the query again. Notice the task manager
> performance to see it's activity.
> If not, try to create a trace file and capture what your query is doing.
> Use this article to analyze your performance data
> http://support.microsoft.com/default.aspx?scid=kb;en-us;283886
> HTH
> Andreas
>
>
> "markus" wrote:
> > both tests took place on a sql server 2k, just the hardware is really
> > different ;-)
> >
> > the query analyzer version is the same.
> >
> >
> > "Richard Ding" wrote:
> >
> > > Have you looked at the execution plans for both queries on the server and
> > > client? Is there any difference?
> > >
> > > Also check the query Analyzer version on the client and the server.
> > >
> > >
> > > "markus" <markus@.discussions.microsoft.com> wrote in message
> > > news:5F17AC7D-EA4A-400C-A67C-22F578AE016D@.microsoft.com...
> > > > hi!
> > > >
> > > > we have developed an application with sql server 2000 as database server.
> > > > we
> > > > still have enormous performance problem and so we started to search for
> > > > reasons.
> > > >
> > > > now we made a test were we send a sql query (about 10.000 data records) to
> > > > the server and were quite wondering. when we made the test with a lokal
> > > > pc,
> > > > sometimes really old boys, it took some seconds to finish the query. with
> > > > our
> > > > server (dual xenon board, 2,6 mhz, 2 gb mem) it lasts over 3 minutes! when
> > > > we
> > > > check the performance monitor of the server he will not have any ressource
> > > > troubles. i really dont know which processor the server is using ;-)
> > > >
> > > > how could that happen? are there any troubles with dual boards?
> > > >
> > > > i have also check the processor properties of the server. there are still
> > > > two processors listed. the settings should be ok unless i could not change
> > > > the settings.
> > > > also the disk is ok.
> > > >
> > > > would be great if someone could give me a hint.
> > > >
> > > > greetings,
> > > > markus
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> > >|||As I said before, try comparing the query execution plans probably will
unveil the huge performance difference.
"markus" <markus@.discussions.microsoft.com> wrote in message
news:E64A2A20-2258-4EC8-B557-527549F57272@.microsoft.com...
> hi andreas!
> thank you for your information. i will optimize the query in the way
> described but thats still another topic. today i tried to change the
> server
> properties but therefore i have to restart teh server and that's possible
> in
> the evening hours :-(
> i analyzed the server via the profiler and compared the result of the
> server
> and another pc:
> reads writes duration
> server: 43151 322 228796
> "normal pc": 43148 325 333
>
> so far so good. bat how can I identify where the bottleneck really is. are
> there special event catagories which concern I/O.
> my further activities are:
> 1) disable von cpu
> 2) reinstall sql server on existing server
> 3) install sql server on "stand-alone server"
> what conerns issue 2 i will also check if there are existing problems with
> an old msde installation.
> regards,
> markus
> "Andreas Mavrogenis" wrote:
>> Marcus Hi,
>> Please check this article, and follow it's steps to optimize your query
>> http://support.microsoft.com/default.aspx?scid=kb;en-us;243589
>> If you don't have any results, try to disable one of your two processors,
>> stop-start SQL Services and run the query again. Notice the task manager
>> performance to see it's activity.
>> If not, try to create a trace file and capture what your query is doing.
>> Use this article to analyze your performance data
>> http://support.microsoft.com/default.aspx?scid=kb;en-us;283886
>> HTH
>> Andreas
>>
>>
>> "markus" wrote:
>> > both tests took place on a sql server 2k, just the hardware is really
>> > different ;-)
>> >
>> > the query analyzer version is the same.
>> >
>> >
>> > "Richard Ding" wrote:
>> >
>> > > Have you looked at the execution plans for both queries on the server
>> > > and
>> > > client? Is there any difference?
>> > >
>> > > Also check the query Analyzer version on the client and the server.
>> > >
>> > >
>> > > "markus" <markus@.discussions.microsoft.com> wrote in message
>> > > news:5F17AC7D-EA4A-400C-A67C-22F578AE016D@.microsoft.com...
>> > > > hi!
>> > > >
>> > > > we have developed an application with sql server 2000 as database
>> > > > server.
>> > > > we
>> > > > still have enormous performance problem and so we started to search
>> > > > for
>> > > > reasons.
>> > > >
>> > > > now we made a test were we send a sql query (about 10.000 data
>> > > > records) to
>> > > > the server and were quite wondering. when we made the test with a
>> > > > lokal
>> > > > pc,
>> > > > sometimes really old boys, it took some seconds to finish the
>> > > > query. with
>> > > > our
>> > > > server (dual xenon board, 2,6 mhz, 2 gb mem) it lasts over 3
>> > > > minutes! when
>> > > > we
>> > > > check the performance monitor of the server he will not have any
>> > > > ressource
>> > > > troubles. i really dont know which processor the server is using
>> > > > ;-)
>> > > >
>> > > > how could that happen? are there any troubles with dual boards?
>> > > >
>> > > > i have also check the processor properties of the server. there are
>> > > > still
>> > > > two processors listed. the settings should be ok unless i could not
>> > > > change
>> > > > the settings.
>> > > > also the disk is ok.
>> > > >
>> > > > would be great if someone could give me a hint.
>> > > >
>> > > > greetings,
>> > > > markus
>> > > >
>> > > >
>> > > >
>> > > >
>> > >
>> > >
>> > >|||Hi,
Check the following article from technet, in order to specify your counters
for logging. Try to monitor also the Disk cashe.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlops5.mspx
It's an article that requires a lot of time reading.
Let me Know of your status,
HTH
Andreas
"Richard Ding" wrote:
> As I said before, try comparing the query execution plans probably will
> unveil the huge performance difference.
> "markus" <markus@.discussions.microsoft.com> wrote in message
> news:E64A2A20-2258-4EC8-B557-527549F57272@.microsoft.com...
> > hi andreas!
> >
> > thank you for your information. i will optimize the query in the way
> > described but thats still another topic. today i tried to change the
> > server
> > properties but therefore i have to restart teh server and that's possible
> > in
> > the evening hours :-(
> >
> > i analyzed the server via the profiler and compared the result of the
> > server
> > and another pc:
> >
> > reads writes duration
> > server: 43151 322 228796
> > "normal pc": 43148 325 333
> >
> >
> > so far so good. bat how can I identify where the bottleneck really is. are
> > there special event catagories which concern I/O.
> >
> > my further activities are:
> >
> > 1) disable von cpu
> > 2) reinstall sql server on existing server
> > 3) install sql server on "stand-alone server"
> >
> > what conerns issue 2 i will also check if there are existing problems with
> > an old msde installation.
> >
> > regards,
> > markus
> >
> > "Andreas Mavrogenis" wrote:
> >
> >> Marcus Hi,
> >>
> >> Please check this article, and follow it's steps to optimize your query
> >> http://support.microsoft.com/default.aspx?scid=kb;en-us;243589
> >>
> >> If you don't have any results, try to disable one of your two processors,
> >> stop-start SQL Services and run the query again. Notice the task manager
> >> performance to see it's activity.
> >>
> >> If not, try to create a trace file and capture what your query is doing.
> >> Use this article to analyze your performance data
> >> http://support.microsoft.com/default.aspx?scid=kb;en-us;283886
> >>
> >> HTH
> >> Andreas
> >>
> >>
> >>
> >>
> >> "markus" wrote:
> >>
> >> > both tests took place on a sql server 2k, just the hardware is really
> >> > different ;-)
> >> >
> >> > the query analyzer version is the same.
> >> >
> >> >
> >> > "Richard Ding" wrote:
> >> >
> >> > > Have you looked at the execution plans for both queries on the server
> >> > > and
> >> > > client? Is there any difference?
> >> > >
> >> > > Also check the query Analyzer version on the client and the server.
> >> > >
> >> > >
> >> > > "markus" <markus@.discussions.microsoft.com> wrote in message
> >> > > news:5F17AC7D-EA4A-400C-A67C-22F578AE016D@.microsoft.com...
> >> > > > hi!
> >> > > >
> >> > > > we have developed an application with sql server 2000 as database
> >> > > > server.
> >> > > > we
> >> > > > still have enormous performance problem and so we started to search
> >> > > > for
> >> > > > reasons.
> >> > > >
> >> > > > now we made a test were we send a sql query (about 10.000 data
> >> > > > records) to
> >> > > > the server and were quite wondering. when we made the test with a
> >> > > > lokal
> >> > > > pc,
> >> > > > sometimes really old boys, it took some seconds to finish the
> >> > > > query. with
> >> > > > our
> >> > > > server (dual xenon board, 2,6 mhz, 2 gb mem) it lasts over 3
> >> > > > minutes! when
> >> > > > we
> >> > > > check the performance monitor of the server he will not have any
> >> > > > ressource
> >> > > > troubles. i really dont know which processor the server is using
> >> > > > ;-)
> >> > > >
> >> > > > how could that happen? are there any troubles with dual boards?
> >> > > >
> >> > > > i have also check the processor properties of the server. there are
> >> > > > still
> >> > > > two processors listed. the settings should be ok unless i could not
> >> > > > change
> >> > > > the settings.
> >> > > > also the disk is ok.
> >> > > >
> >> > > > would be great if someone could give me a hint.
> >> > > >
> >> > > > greetings,
> >> > > > markus
> >> > > >
> >> > > >
> >> > > >
> >> > > >
> >> > >
> >> > >
> >> > >
>
>