Showing posts with label performing. Show all posts
Showing posts with label performing. Show all posts

Wednesday, March 28, 2012

Performance testing - tips

I'm suspecting our new Dell 2850/ SQL Server 2005 machine is not performing
like it should. What we expericence is ok performance running single long
running queries. But running 2-3 long running queries at the same time
really makes the whole server very slow. Imagine this:
1) Start 1 long running query accessing 2 large tables. CPU is used 25-30%
2) At the same time, run a select on a small table not involved in the query
above: Sometimes the result is coming after 15-30 secs. Sometimes it will
not display until the first query is done. There is no lock-situation
involved.
I have done the same thing on SQL Server 2000 with slower hardware for
several years. Sadly I don't have access to the old machine anymore, so it's
hard to tell the real difference.
CPU is not an issue - SQL server is using only 25-50 percent. So my first
conclusion is that the disk-system is not performing well. We separate RAIDs
for system (Raid1), tempdb (raid1), log (raid1), data (raid5), index
(raid1). The raids are spread on 2 controllers with 2 channels each.
But where to start? Does anyone have some stored procedures+testdata which I
can use as a start to compare with?
Per SchjetneOne place I'd start is to look at the disk-related perfmon counter values. In
particular, I'd look at Disk Sec/Read and Disk sec/Write; these are disk
latency counters. If there is a significantly higher I/O latency during the
period you are running your queries concurrently as compared to when you are
not running them at the same time, you may have a disk I/O issue.
To benchmark the disk subsystem, I have found sqlio.exe from Microsoft to be
very useful. Every time when you get a new disk system, run sqlio.exe to get
a comprehensive performance profile of the disk system. This establishes a
baseline for you. Later, if you suspect something is not behaving properly in
your disk subsystem, you can run the same benchmark and compare the results
with the saved baseline.
Linchi
"Per Schjetne" wrote:
> I'm suspecting our new Dell 2850/ SQL Server 2005 machine is not performing
> like it should. What we expericence is ok performance running single long
> running queries. But running 2-3 long running queries at the same time
> really makes the whole server very slow. Imagine this:
> 1) Start 1 long running query accessing 2 large tables. CPU is used 25-30%
> 2) At the same time, run a select on a small table not involved in the query
> above: Sometimes the result is coming after 15-30 secs. Sometimes it will
> not display until the first query is done. There is no lock-situation
> involved.
> I have done the same thing on SQL Server 2000 with slower hardware for
> several years. Sadly I don't have access to the old machine anymore, so it's
> hard to tell the real difference.
> CPU is not an issue - SQL server is using only 25-50 percent. So my first
> conclusion is that the disk-system is not performing well. We separate RAIDs
> for system (Raid1), tempdb (raid1), log (raid1), data (raid5), index
> (raid1). The raids are spread on 2 controllers with 2 channels each.
> But where to start? Does anyone have some stored procedures+testdata which I
> can use as a start to compare with?
> Per Schjetne
>
>|||Thanks a lot for the tip! I'll try it.
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:92E26405-BF1A-4BBD-9E80-BED6D7CA246E@.microsoft.com...
> One place I'd start is to look at the disk-related perfmon counter values.
> In
> particular, I'd look at Disk Sec/Read and Disk sec/Write; these are disk
> latency counters. If there is a significantly higher I/O latency during
> the
> period you are running your queries concurrently as compared to when you
> are
> not running them at the same time, you may have a disk I/O issue.
> To benchmark the disk subsystem, I have found sqlio.exe from Microsoft to
> be
> very useful. Every time when you get a new disk system, run sqlio.exe to
> get
> a comprehensive performance profile of the disk system. This establishes a
> baseline for you. Later, if you suspect something is not behaving properly
> in
> your disk subsystem, you can run the same benchmark and compare the
> results
> with the saved baseline.
> Linchi
> "Per Schjetne" wrote:
>> I'm suspecting our new Dell 2850/ SQL Server 2005 machine is not
>> performing
>> like it should. What we expericence is ok performance running single long
>> running queries. But running 2-3 long running queries at the same time
>> really makes the whole server very slow. Imagine this:
>> 1) Start 1 long running query accessing 2 large tables. CPU is used
>> 25-30%
>> 2) At the same time, run a select on a small table not involved in the
>> query
>> above: Sometimes the result is coming after 15-30 secs. Sometimes it will
>> not display until the first query is done. There is no lock-situation
>> involved.
>> I have done the same thing on SQL Server 2000 with slower hardware for
>> several years. Sadly I don't have access to the old machine anymore, so
>> it's
>> hard to tell the real difference.
>> CPU is not an issue - SQL server is using only 25-50 percent. So my first
>> conclusion is that the disk-system is not performing well. We separate
>> RAIDs
>> for system (Raid1), tempdb (raid1), log (raid1), data (raid5), index
>> (raid1). The raids are spread on 2 controllers with 2 channels each.
>> But where to start? Does anyone have some stored procedures+testdata
>> which I
>> can use as a start to compare with?
>> Per Schjetne
>>

Saturday, February 25, 2012

Performance Monitoring

I ran performance monitoring on one of my server that is
performing slow in order to determine if there is a memory
or hardware bottleneck and came up with the following
statistics. Please help diagnose this and let me know if
we need to increase memory or configure something in sql
server. This is an average of statistics collected
during peak periods 19 times. Thanks for your help. I
already suggested that ram needs to be added to the
server, but I need more assured technical justification
as I am new to this. Thx.
PROCESSOR TIME
max 36.42%
Average 9.53%
System
Processor Queue Length (max) 1
MEMORY
Pages/sec 121
Available Mbytes 156
Page Faults/sec 735
total physical memory 1986302
total sql server memory - mb 1589485
Processes page fault - Sql Server 73
Maximum workspace memory 1081725
DISK
Disk Reads per second 244
Disk Writes per second 99
Average Disk Queue length 4
SQL SERVER GENERAL STATISTICS
# connections 15
SQL Server Buffer Manager cache hit ratio 94
Hardware Info
Hard drive :
C' drive total hd space = 33.9G space
available = 20.2 G
D' drive total hd space = 273G space
available = 272G
E' drive total hd space = 273G space
available = 172 G
F' drive total hd space = 273G space
available = 127 G
F' drive total hd space = 819G space
available = 431 G
CPU = 2.80 GHz
RAM = 2.09G
Operating System = Windows 2000 SP4
Sql Server 2000 sp3aUnfortunatly its a bit hard to determine whether your
server is running slow or not, because there is no point
of reference. Probably the best way would be to run it
once on a weekly basis and get the average.
You may want to monitor network trafic, read writes and
memory (can anyone think of more ?), personally though
7/10 its the network thats slow and not SQL.
Be that as it may there are some options that may speed
you SQL Server up, if you feel its underperforming.
Firstly have a look at the DBCC SHOWCONTIG command. This
will show how dragmented your indexes are, if they are
defragmented then run DBCC DBREINDEX.
Secondly set up a profile trace for performance
enhancement. Allow it to run for at least a couple of days
then run the Index Tuning wizard. This will put in some
indexes for you based upon the queries your users have
been making.
Finally mark you store procedures for re-compulation in
case the execution plan is out of date.
If you would like any further help my email in reverse
order is moc.liamtoh@.em_yrewolf_elttil
J
>--Original Message--
>I ran performance monitoring on one of my server that is
>performing slow in order to determine if there is a
memory
>or hardware bottleneck and came up with the following
>statistics. Please help diagnose this and let me know if
>we need to increase memory or configure something in sql
>server. This is an average of statistics collected
>during peak periods 19 times. Thanks for your help. I
>already suggested that ram needs to be added to the
>server, but I need more assured technical justification
>as I am new to this. Thx.
>PROCESSOR TIME
>max 36.42%
>Average 9.53%
>System
>Processor Queue Length (max) 1
>MEMORY
>Pages/sec 121
>Available Mbytes 156
>Page Faults/sec 735
>total physical memory 1986302
>total sql server memory - mb 1589485
>Processes page fault - Sql Server 73
>Maximum workspace memory 1081725
>DISK
>Disk Reads per second 244
>Disk Writes per second 99
>Average Disk Queue length 4
>SQL SERVER GENERAL STATISTICS
># connections 15
>SQL Server Buffer Manager cache hit ratio 94
>Hardware Info
>Hard drive :
>C' drive total hd space = 33.9G space
>available = 20.2 G
>D' drive total hd space = 273G space
>available = 272G
>E' drive total hd space = 273G space
>available = 172 G
>F' drive total hd space = 273G space
>available = 127 G
>F' drive total hd space = 819G space
>available = 431 G
>CPU = 2.80 GHz
>RAM = 2.09G
>Operating System = Windows 2000 SP4
>Sql Server 2000 sp3a
>.
>|||Julie,
Thank you so much for response you gave to my performance
problems.
The statistics I sent in the Microsoft newsgroup is an
average of every thing I log for 9 days now. Would you
rather I send you what I collected today? The network may
notbe the issue because I have an identical server with
more memory that returns stuff quicker. I will try the
trace profiler also. I reindex the indexes weekly - but I
will still run dbcc showcontig. Finally, how do you
recompile stored procedures? Thank you.
Jasmine
>--Original Message--
>Unfortunatly its a bit hard to determine whether your
>server is running slow or not, because there is no point
>of reference. Probably the best way would be to run it
>once on a weekly basis and get the average.
>You may want to monitor network trafic, read writes and
>memory (can anyone think of more ?), personally though
>7/10 its the network thats slow and not SQL.
>Be that as it may there are some options that may speed
>you SQL Server up, if you feel its underperforming.
>Firstly have a look at the DBCC SHOWCONTIG command. This
>will show how dragmented your indexes are, if they are
>defragmented then run DBCC DBREINDEX.
>Secondly set up a profile trace for performance
>enhancement. Allow it to run for at least a couple of
days
>then run the Index Tuning wizard. This will put in some
>indexes for you based upon the queries your users have
>been making.
>Finally mark you store procedures for re-compulation in
>case the execution plan is out of date.
>If you would like any further help my email in reverse
>order is moc.liamtoh@.em_yrewolf_elttil
>J
>>--Original Message--
>>I ran performance monitoring on one of my server that is
>>performing slow in order to determine if there is a
>memory
>>or hardware bottleneck and came up with the following
>>statistics. Please help diagnose this and let me know
if
>>we need to increase memory or configure something in sql
>>server. This is an average of statistics collected
>>during peak periods 19 times. Thanks for your help. I
>>already suggested that ram needs to be added to the
>>server, but I need more assured technical justification
>>as I am new to this. Thx.
>>PROCESSOR TIME
>>max 36.42%
>>Average 9.53%
>>System
>>Processor Queue Length (max) 1
>>MEMORY
>>Pages/sec 121
>>Available Mbytes 156
>>Page Faults/sec 735
>>total physical memory 1986302
>>total sql server memory - mb 1589485
>>Processes page fault - Sql Server 73
>>Maximum workspace memory 1081725
>>DISK
>>Disk Reads per second 244
>>Disk Writes per second 99
>>Average Disk Queue length 4
>>SQL SERVER GENERAL STATISTICS
>># connections 15
>>SQL Server Buffer Manager cache hit ratio 94
>>Hardware Info
>>Hard drive :
>>C' drive total hd space = 33.9G space
>>available = 20.2 G
>>D' drive total hd space = 273G space
>>available = 272G
>>E' drive total hd space = 273G space
>>available = 172 G
>>F' drive total hd space = 273G space
>>available = 127 G
>>F' drive total hd space = 819G space
>>available = 431 G
>>CPU = 2.80 GHz
>>RAM = 2.09G
>>Operating System = Windows 2000 SP4
>>Sql Server 2000 sp3a
>>.
>.
>|||also check out the kb below for a more general overview of how to
troubleshoot performance issues
http://support.microsoft.com/default.aspx?scid=kb;en-us;298475
Andy.
"Jasmine Gwendle" <anonymous@.discussions.microsoft.com> wrote in message
news:173c901c448ab$17949f00$a001280a@.phx.gbl...
> I ran performance monitoring on one of my server that is
> performing slow in order to determine if there is a memory
> or hardware bottleneck and came up with the following
> statistics. Please help diagnose this and let me know if
> we need to increase memory or configure something in sql
> server. This is an average of statistics collected
> during peak periods 19 times. Thanks for your help. I
> already suggested that ram needs to be added to the
> server, but I need more assured technical justification
> as I am new to this. Thx.
> PROCESSOR TIME
> max 36.42%
> Average 9.53%
> System
> Processor Queue Length (max) 1
> MEMORY
> Pages/sec 121
> Available Mbytes 156
> Page Faults/sec 735
> total physical memory 1986302
> total sql server memory - mb 1589485
> Processes page fault - Sql Server 73
> Maximum workspace memory 1081725
> DISK
> Disk Reads per second 244
> Disk Writes per second 99
> Average Disk Queue length 4
> SQL SERVER GENERAL STATISTICS
> # connections 15
> SQL Server Buffer Manager cache hit ratio 94
> Hardware Info
> Hard drive :
> C' drive total hd space = 33.9G space
> available = 20.2 G
> D' drive total hd space = 273G space
> available = 272G
> E' drive total hd space = 273G space
> available = 172 G
> F' drive total hd space = 273G space
> available = 127 G
> F' drive total hd space = 819G space
> available = 431 G
> CPU = 2.80 GHz
> RAM = 2.09G
> Operating System = Windows 2000 SP4
> Sql Server 2000 sp3a
>|||Hi Jasmine
Firstly the store procedures
EXEC sp_recompile <store procedure name>
Personally I create a cursor with
select name from sysobjects where xtype = 'P' and status
> 0
That gets out all the sp for your database, then I add
the EXEC sp_recompile
As for the performance could you give me somthing like
Mondays 9am to 5pm Average, Tuesdays average 9am to 5pm
ect.
You can also configure the performance monitor to sent
you pop-ups when for instance network is at 100%.
Other things.
1 Is there anything else running on your server?
2 Is your memory set up the same
3 Do you have autoclose turned off your dbs
4 Is the problem saving, loading or both
J
>--Original Message--
>Julie,
>Thank you so much for response you gave to my
performance
>problems.
>The statistics I sent in the Microsoft newsgroup is an
>average of every thing I log for 9 days now. Would you
>rather I send you what I collected today? The network
may
>notbe the issue because I have an identical server with
>more memory that returns stuff quicker. I will try the
>trace profiler also. I reindex the indexes weekly - but
I
>will still run dbcc showcontig. Finally, how do you
>recompile stored procedures? Thank you.
>Jasmine
>
>>--Original Message--
>>Unfortunatly its a bit hard to determine whether your
>>server is running slow or not, because there is no
point
>>of reference. Probably the best way would be to run it
>>once on a weekly basis and get the average.
>>You may want to monitor network trafic, read writes and
>>memory (can anyone think of more ?), personally though
>>7/10 its the network thats slow and not SQL.
>>Be that as it may there are some options that may speed
>>you SQL Server up, if you feel its underperforming.
>>Firstly have a look at the DBCC SHOWCONTIG command.
This
>>will show how dragmented your indexes are, if they are
>>defragmented then run DBCC DBREINDEX.
>>Secondly set up a profile trace for performance
>>enhancement. Allow it to run for at least a couple of
>days
>>then run the Index Tuning wizard. This will put in some
>>indexes for you based upon the queries your users have
>>been making.
>>Finally mark you store procedures for re-compulation in
>>case the execution plan is out of date.
>>If you would like any further help my email in reverse
>>order is moc.liamtoh@.em_yrewolf_elttil
>>J
>>--Original Message--
>>I ran performance monitoring on one of my server that
is
>>performing slow in order to determine if there is a
>>memory
>>or hardware bottleneck and came up with the following
>>statistics. Please help diagnose this and let me know
>if
>>we need to increase memory or configure something in
sql
>>server. This is an average of statistics collected
>>during peak periods 19 times. Thanks for your help.
I
>>already suggested that ram needs to be added to the
>>server, but I need more assured technical justification
>>as I am new to this. Thx.
>>PROCESSOR TIME
>>max 36.42%
>>Average 9.53%
>>System
>>Processor Queue Length (max) 1
>>MEMORY
>>Pages/sec 121
>>Available Mbytes 156
>>Page Faults/sec 735
>>total physical memory 1986302
>>total sql server memory - mb 1589485
>>Processes page fault - Sql Server 73
>>Maximum workspace memory 1081725
>>DISK
>>Disk Reads per second 244
>>Disk Writes per second 99
>>Average Disk Queue length 4
>>SQL SERVER GENERAL STATISTICS
>># connections 15
>>SQL Server Buffer Manager cache hit ratio 94
>>Hardware Info
>>Hard drive :
>>C' drive total hd space = 33.9G space
>>available = 20.2 G
>>D' drive total hd space = 273G space
>>available = 272G
>>E' drive total hd space = 273G space
>>available = 172 G
>>F' drive total hd space = 273G space
>>available = 127 G
>>F' drive total hd space = 819G space
>>available = 431 G
>>CPU = 2.80 GHz
>>RAM = 2.09G
>>Operating System = Windows 2000 SP4
>>Sql Server 2000 sp3a
>>.
>>.
>.
>

Monday, February 20, 2012

performance monitoring

i m performing performance testing of application which uses SQL server as backend. can any body say what all the things we basically monitor while we do performance monitoring? As of now sql server shows instantaneous spike in cpu utilization.I m looking for what all things we need to monitor so that we can pinpoint the problem in increase in CPU utilization!can any tell me?Hi,
Have a look into the below link to get the useful performance monitor
counters to identify the bottleneck:-
http://www.computerperformance.co.uk/HealthCheck/SQL.htm
Thanks
Hari
MCDBA
"Sham" <Sham@.discussions.microsoft.com> wrote in message
news:D3BFB6EF-479B-479E-A774-C9AFE184DBD5@.microsoft.com...
> i m performing performance testing of application which uses SQL server as
backend. can any body say what all the things we basically monitor while we
do performance monitoring? As of now sql server shows instantaneous spike in
cpu utilization.I m looking for what all things we need to monitor so that
we can pinpoint the problem in increase in CPU utilization!can any tell me?

performance monitoring

i m performing performance testing of application which uses SQL server as backend. can any body say what all the things we basically monitor while we do performance monitoring? As of now sql server shows instantaneous spike in cpu utilization.I m looking
for what all things we need to monitor so that we can pinpoint the problem in increase in CPU utilization!can any tell me?
Hi,
Have a look into the below link to get the useful performance monitor
counters to identify the bottleneck:-
http://www.computerperformance.co.uk...hCheck/SQL.htm
Thanks
Hari
MCDBA
"Sham" <Sham@.discussions.microsoft.com> wrote in message
news:D3BFB6EF-479B-479E-A774-C9AFE184DBD5@.microsoft.com...
> i m performing performance testing of application which uses SQL server as
backend. can any body say what all the things we basically monitor while we
do performance monitoring? As of now sql server shows instantaneous spike in
cpu utilization.I m looking for what all things we need to monitor so that
we can pinpoint the problem in increase in CPU utilization!can any tell me?

performance monitoring

i m performing performance testing of application which uses SQL server as b
ackend. can any body say what all the things we basically monitor while we d
o performance monitoring? As of now sql server shows instantaneous spike in
cpu utilization.I m looking
for what all things we need to monitor so that we can pinpoint the problem i
n increase in CPU utilization!can any tell me?Hi,
Have a look into the below link to get the useful performance monitor
counters to identify the bottleneck:-
http://www.computerperformance.co.u...thCheck/SQL.htm
Thanks
Hari
MCDBA
"Sham" <Sham@.discussions.microsoft.com> wrote in message
news:D3BFB6EF-479B-479E-A774-C9AFE184DBD5@.microsoft.com...
> i m performing performance testing of application which uses SQL server as
backend. can any body say what all the things we basically monitor while we
do performance monitoring? As of now sql server shows instantaneous spike in
cpu utilization.I m looking for what all things we need to monitor so that
we can pinpoint the problem in increase in CPU utilization!can any tell me?