MS SQL Server - What does this look like to you?

Author

What does this look like to you?

Gabe Green

2005-10-31, 1:23 pm

I'm at a site that is using a single SQL Server on an IBM x445 server.
Everything was running fine for a while, and then IBM support made some
changes to the client's environment. Now all of my data mart builds are
taking longer to complete.

Without modifying the SQL processes themselves... I was hoping to get some
opinions on what could be done to improve performance on the server (from a
hardware perspective).

The server has :
Windows 2003 Standard
4 hyperthreaded Xeon 3.6 GHz processors
4 GB RAM
1 large drive (carved up as 3 disks) located on an IBM Fast-T

Here's a sample of what I see from the performance monitor while everything
is running :

PhysicalDisk
% Disk Read Time : 321.516
% Disk Write Time : 42.369
% Disk Time : 363.885
% Disk Idle : 16.910
Avg. Disk Read Queue Length : 3.215
Avg. Disk Write Queue Length : 0.424
Processor
% Processor Time : 25.782
SQLServer:Latches
Latch Waits/sec : 11.024
Total Latch Wait Time (ms) : 31.068
SQLServer:Locks
Lock Requests/sec : 5831.759
Number of Deadlocks/sec : 0.000

Again, I'd like to disregard the SQL Server application issues... and look
at where the bottlenecks are on the hardware side only.

I've got my own opinions about what IBM must have changed... and what they
need to fix... but I wanted some other opinions as well.

Thanks to anyone that responds.

--
Sincerely,

Gabe Green
Senior Consultant
Sky Solutions

david

2005-10-31, 8:23 pm

From a HW perspective only, I would look at the drive as a bottleneck. The
x445 should have a Ultra 320 bus in it, so it should be sufficient. Was their
processor tuning on IBM part? When you say they made changes to the client
environment, what was done, any idea?
--
Thanks,

David

"Gabe Green" wrote:

> I'm at a site that is using a single SQL Server on an IBM x445 server.
> Everything was running fine for a while, and then IBM support made some
> changes to the client's environment. Now all of my data mart builds are
> taking longer to complete.
>
> Without modifying the SQL processes themselves... I was hoping to get some
> opinions on what could be done to improve performance on the server (from a
> hardware perspective).
>
> The server has :
> Windows 2003 Standard
> 4 hyperthreaded Xeon 3.6 GHz processors
> 4 GB RAM
> 1 large drive (carved up as 3 disks) located on an IBM Fast-T
>
> Here's a sample of what I see from the performance monitor while everything
> is running :
>
> PhysicalDisk
> % Disk Read Time : 321.516
> % Disk Write Time : 42.369
> % Disk Time : 363.885
> % Disk Idle : 16.910
> Avg. Disk Read Queue Length : 3.215
> Avg. Disk Write Queue Length : 0.424
> Processor
> % Processor Time : 25.782
> SQLServer:Latches
> Latch Waits/sec : 11.024
> Total Latch Wait Time (ms) : 31.068
> SQLServer:Locks
> Lock Requests/sec : 5831.759
> Number of Deadlocks/sec : 0.000
>
> Again, I'd like to disregard the SQL Server application issues... and look
> at where the bottlenecks are on the hardware side only.
>
> I've got my own opinions about what IBM must have changed... and what they
> need to fix... but I wanted some other opinions as well.
>
> Thanks to anyone that responds.
>
> --
> Sincerely,
>
> Gabe Green
> Senior Consultant
> Sky Solutions

David Gugick

2005-10-31, 8:23 pm

Gabe Green wrote:
> I'm at a site that is using a single SQL Server on an IBM x445 server.
> Everything was running fine for a while, and then IBM support made
> some changes to the client's environment. Now all of my data mart
> builds are taking longer to complete.
>
> Without modifying the SQL processes themselves... I was hoping to get
> some opinions on what could be done to improve performance on the
> server (from a hardware perspective).
>
> The server has :
> Windows 2003 Standard
> 4 hyperthreaded Xeon 3.6 GHz processors
> 4 GB RAM
> 1 large drive (carved up as 3 disks) located on an IBM Fast-T

You have a very powerful server with a lame hard drive setup. A single
drive for database reads, database writes, log writes, tempdb, the OS,
and all services is surefire way to cause disk contention. Now, your
queue length seems fine, so it's possible the disk subsystem is not the
issue. Maybe there's just some SQL that needs to be optimized.

--
David Gugick
Quest Software
www.imceda.com
www.quest.com

Sponsored Links