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. I
n
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 i
n
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 performin
g
> 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 que
ry
> 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 RAI
Ds
> 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...[vbcol=seagreen]
> 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:
>