Showing posts with label hiwe. Show all posts
Showing posts with label hiwe. Show all posts

Monday, March 26, 2012

Performance Question

Hi

We have a SQL server on Win2k. the physical size of the db is about
40G and the main table has approx 65m rows in it. At the moment the
entire database is on one data file. The entire server including the
OS is on a RAID 0 array with one RAID controller.

My question is would I get any performance benefit if I was to have
more than several data file rather than one big data file, bearing in
mind that there is only one disk controller.

I am mainly interested in read performance rather than write.

I am just starting out with MS SQL

Thanks for any info<rc@.die@.you@.!spammers.sandworm.demon.co.uk> wrote in message
news:3ephc0pgs6bq4rpfrmphsi0ibck95factj@.4ax.com...
> Hi
> We have a SQL server on Win2k. the physical size of the db is about
> 40G and the main table has approx 65m rows in it. At the moment the
> entire database is on one data file. The entire server including the
> OS is on a RAID 0 array with one RAID controller.
> My question is would I get any performance benefit if I was to have
> more than several data file rather than one big data file, bearing in
> mind that there is only one disk controller.

My understanding is with SQL 6.0 and I think 7.0 this would make a
difference (as SQL could have multiple file pointers).

This is no longer the case with SQL 2000.

Also, RAID 0 is a disaster waiting to happen.

Remember, if you have a 2 disk RAID 0 set, you've just doubled your chances
of a catastrophic failure.

If you have a 3 disk set, it's 3x, etc.

> I am mainly interested in read performance rather than write.

Well, first move to a "real" RAID solution, 1, 10, 5 or the like.

Separate out files (say OS, data, logs) to separate PHYSICAL arrays (doesn't
help to repartition your existing array into 3 logical disks... you don't
get any more physical heads.)

And make sure you have RAM. Lots of it.
> I am just starting out with MS SQL
> Thanks for any info|||Hi

To add to Gregs advice you may want to get yourself the following book:
http://www.microsoft.com/mspress/books/4944.asp

and this article and other:
http://www.sql-server-performance.c...re_planning.asp

This may also help
http://www.acnc.com/04_01_10.html

John

<rc@.die@.you@.!spammers.sandworm.demon.co.uk> wrote in message
news:3ephc0pgs6bq4rpfrmphsi0ibck95factj@.4ax.com...
> Hi
> We have a SQL server on Win2k. the physical size of the db is about
> 40G and the main table has approx 65m rows in it. At the moment the
> entire database is on one data file. The entire server including the
> OS is on a RAID 0 array with one RAID controller.
> My question is would I get any performance benefit if I was to have
> more than several data file rather than one big data file, bearing in
> mind that there is only one disk controller.
> I am mainly interested in read performance rather than write.
> I am just starting out with MS SQL
> Thanks for any info|||Sorry, the server is on a RAID 0+1 array, i.e the strip is mirrored
On Fri, 11 Jun 2004 06:16:55 GMT, "John Bell"
<jbellnewsposts@.hotmail.com> wrote:

>Hi
>To add to Gregs advice you may want to get yourself the following book:
>http://www.microsoft.com/mspress/books/4944.asp
>and this article and other:
>http://www.sql-server-performance.c...re_planning.asp
>This may also help
>http://www.acnc.com/04_01_10.html
>John
><rc@.die@.you@.!spammers.sandworm.demon.co.uk> wrote in message
>news:3ephc0pgs6bq4rpfrmphsi0ibck95factj@.4ax.com...
>> Hi
>>
>> We have a SQL server on Win2k. the physical size of the db is about
>> 40G and the main table has approx 65m rows in it. At the moment the
>> entire database is on one data file. The entire server including the
>> OS is on a RAID 0 array with one RAID controller.
>>
>> My question is would I get any performance benefit if I was to have
>> more than several data file rather than one big data file, bearing in
>> mind that there is only one disk controller.
>>
>> I am mainly interested in read performance rather than write.
>>
>> I am just starting out with MS SQL
>>
>> Thanks for any info
>>
>>
>|||Hi

If the controller has multiple channels it will help if you configure a
different sub-systems for OS and logs using new disks. These will share
resources on the controller so if you can add memory to it that will also
help. Adding extra disks to the current array will help spread the disk
usage more, but may not show as much gain as having a new sub-system there
is an example in "Microsoft SQL Server 2000T Performance Tuning Technical
Reference" that shows how adding a disk to an existing array can help.

John

Using multiple filegroups in their own files will help backup and
organisation.
"rc" <rc@.spam.com> wrote in message
news:i3pic0pca4dg6ga3cj4e1vr3as98e558uu@.4ax.com...
> Sorry, the server is on a RAID 0+1 array, i.e the strip is mirrored
> On Fri, 11 Jun 2004 06:16:55 GMT, "John Bell"
> <jbellnewsposts@.hotmail.com> wrote:
> >Hi
> >To add to Gregs advice you may want to get yourself the following book:
> >http://www.microsoft.com/mspress/books/4944.asp
> >and this article and other:
> >http://www.sql-server-performance.c...re_planning.asp
> >This may also help
> >http://www.acnc.com/04_01_10.html
> >John
> ><rc@.die@.you@.!spammers.sandworm.demon.co.uk> wrote in message
> >news:3ephc0pgs6bq4rpfrmphsi0ibck95factj@.4ax.com...
> >> Hi
> >>
> >> We have a SQL server on Win2k. the physical size of the db is about
> >> 40G and the main table has approx 65m rows in it. At the moment the
> >> entire database is on one data file. The entire server including the
> >> OS is on a RAID 0 array with one RAID controller.
> >>
> >> My question is would I get any performance benefit if I was to have
> >> more than several data file rather than one big data file, bearing in
> >> mind that there is only one disk controller.
> >>
> >> I am mainly interested in read performance rather than write.
> >>
> >> I am just starting out with MS SQL
> >>
> >> Thanks for any info
> >>
> >>
> >|||"rc" <rc@.spam.com> wrote in message
news:i3pic0pca4dg6ga3cj4e1vr3as98e558uu@.4ax.com...
> Sorry, the server is on a RAID 0+1 array, i.e the strip is mirrored

Ok, that's good. :-)sql

Wednesday, March 21, 2012

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:
>

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,
markus
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/d...rt5/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:
[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
> http://www.microsoft.com/resources/d...rt5/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 performance
> monitor during the test. the cpu load is about 7% and it's the same with ram.
> 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) +
'blablablablablablablablablablablablablablabla' + 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 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,
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__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,
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:
[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 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:
|||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/default...b;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...b;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:
sql

Monday, February 20, 2012

Performance monitoring

Hi
We are running a Windows 2003 Std. and a MSSQL std. on a 2xPIII having 2Gb
RAM and SCSI disks in RAID1.
The server handles 80 Databases and has some 80-100 connections from
webapplications.
I guess most of you are used to 4xXeon and 128Gb Ram ;-) anyway - the
performance is at the moment very good, but I am of cause a bit concernt
about the future. Does et actually perfom OK as we think it does? Will it
keep performing if we add 10 databases for it to handle? And so on. When
will "it break" performance wise.
I was looking at windows "performance monitor" adding and deleting all sorts
of performance counters - but which one should I look at and what is "in
range"? Any general ideas?
And of cause - any handson books/tutorials that describes this?
Best regards
Jan
Here are some links to have a look at:
http://www.microsoft.com/sql/techinf...perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.co...ance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.co...mance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/de...rfmon_24u1.asp
Disk Monitoring
Andrew J. Kelly SQL MVP
<msdn@.csite.com> wrote in message
news:OrxgP391FHA.1028@.TK2MSFTNGP12.phx.gbl...
> Hi
> We are running a Windows 2003 Std. and a MSSQL std. on a 2xPIII having 2Gb
> RAM and SCSI disks in RAID1.
> The server handles 80 Databases and has some 80-100 connections from
> webapplications.
> I guess most of you are used to 4xXeon and 128Gb Ram ;-) anyway - the
> performance is at the moment very good, but I am of cause a bit concernt
> about the future. Does et actually perfom OK as we think it does? Will it
> keep performing if we add 10 databases for it to handle? And so on. When
> will "it break" performance wise.
> I was looking at windows "performance monitor" adding and deleting all
> sorts of performance counters - but which one should I look at and what is
> "in range"? Any general ideas?
> And of cause - any handson books/tutorials that describes this?
> Best regards
> Jan
>

Performance monitoring

Hi
We are running a Windows 2003 Std. and a MSSQL std. on a 2xPIII having 2Gb
RAM and SCSI disks in RAID1.
The server handles 80 Databases and has some 80-100 connections from
webapplications.
I guess most of you are used to 4xXeon and 128Gb Ram ;-) anyway - the
performance is at the moment very good, but I am of cause a bit concernt
about the future. Does et actually perfom OK as we think it does? Will it
keep performing if we add 10 databases for it to handle? And so on. When
will "it break" performance wise.
I was looking at windows "performance monitor" adding and deleting all sorts
of performance counters - but which one should I look at and what is "in
range"? Any general ideas?
And of cause - any handson books/tutorials that describes this?
Best regards
JanHere are some links to have a look at:
http://www.microsoft.com/sql/techin.../perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.c...mance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.c...rmance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/d.../>
on_24u1.asp
Disk Monitoring
Andrew J. Kelly SQL MVP
<msdn@.csite.com> wrote in message
news:OrxgP391FHA.1028@.TK2MSFTNGP12.phx.gbl...
> Hi
> We are running a Windows 2003 Std. and a MSSQL std. on a 2xPIII having 2Gb
> RAM and SCSI disks in RAID1.
> The server handles 80 Databases and has some 80-100 connections from
> webapplications.
> I guess most of you are used to 4xXeon and 128Gb Ram ;-) anyway - the
> performance is at the moment very good, but I am of cause a bit concernt
> about the future. Does et actually perfom OK as we think it does? Will it
> keep performing if we add 10 databases for it to handle? And so on. When
> will "it break" performance wise.
> I was looking at windows "performance monitor" adding and deleting all
> sorts of performance counters - but which one should I look at and what is
> "in range"? Any general ideas?
> And of cause - any handson books/tutorials that describes this?
> Best regards
> Jan
>