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 ou
r
server (dual xenon board, 2,6 mhz, 2 gb mem) it lasts over 3 minutes! when w
e
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 article
2061.mspx" target="_blank">http://www.microsoft.com/resources/...r />
2061.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:
[vbcol=seagreen]
> 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
> /c2061.mspx" target="_blank">http://www.microsoft.com/resources/.../>
/c2061.mspx
> In this article you will find plenty of information regarding performance
in
> RDBMS
> HTH
> Andreas
> "markus" 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:
[vbcol=seagreen]
> 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 performanc
e
> monitor during the test. the cpu load is about 7% and it's the same with r
am.
> regards,
> markus
> "Andreas Mavrogenis" 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) +
'blablablablablablablablablablablablabla
blabla' + convert(varchar(10), @.i),
rand(@.i)
)
set @.i = @.i + 1
end
go
select * from performance
go
"Andreas Mavrogenis" wrote:
[vbcol=seagreen]
> 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 histogra
ms.
> 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 th
e
> 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 fo
r
> both mdf and ldf files?
> What is the configuration ?
> HTH
> Andreas
>
> "markus" 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:
[vbcol=seagreen]
> 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__112076
38
> 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) +
> 'blablablablablablablablablablablablabla
blabla' + convert(varchar(10), @.i)
,
> rand(@.i)
> )
> set @.i = @.i + 1
> end
> go
> select * from performance
> go
> "Andreas Mavrogenis" wrote:
>|||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 i
n
the performance monitor. it's really confusing.
regards,
markus
"Andreas Mavrogenis" wrote:
[vbcol=seagreen]
> 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 no
t
> 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:
>|||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...
>
>|||Marcus Hi,
Please check this article, and follow it's steps to optimize your query
http://support.microsoft.com/defaul...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/defaul...kb;en-us;283886
HTH
Andreas
"markus" wrote:
[vbcol=seagreen]
> 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:
>