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