|Subject:||Re: SQL Server I/O performance issue|
|Posted by:||Geoff N. Hiten (SQLCraftsm…@gmail.com)|
|Date:||Thu, 20 Sep 2007|
This may be due to your disk subsystem.
If your storage unit relies on a caching controller on the host computer to
implement RAID technology, then your performacne will definitely suffer in a
clustered environment. If your storage array has its own cache, then you
should see no difference. When you have a failover cluster, you cannot
enable write caching on a controller in a host node. The controller is
considered part of the host environment and will not be available during a
failure. Enabling write cache will result in corrupt data during a failover
event. You can easily test this by disabling write cache in a stand-alone
environment and comparing the results with your clustered system.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"YaHozna" <YaHoz…@discussions.microsoft.com> wrote in message
> Hi. Wonder if anyone can offer me some advice?
> I'm currently involved in setting up some new kit prior to an upgrade of
> Server 2000 Standard on a single Win 2000 server to SQL Server 2005
> x64 on a single node Failover Cluster. Additionally databases are to be
> on FAS storage.
> Setting up has gone reasonably smoothly - SQL Server installed OK and
> failover is working fine. The only issue I've got is I/O performance
> server and FAS which seems very poor compared to I/O on local machine. To
> stress test the setup I'm running SQLIOSim with the recommended config
> for our setup and running Performance Monitor with the recommended
> Performance Audit counters. All results are fine except those for Physical
> Disk. Both % Disk Time and Avg Disk Queue Length are showing very poor
> performance compared to results shown when running the same test against
> local disks. Average values are 10227 and 102 respectively. I'm not quite
> sure how to calculate the % Disk percentage however I think Avg Disk Queue
> Length is measured by dividing the value by the number of disks in the
> - i.e. 20 in this case. This gives an average of approx 5 which, given
> this is pretty much constant, would seem to suggest poor performance.
> So, finally, the question is, is there something fundamentally amiss with
> the setup or do the figures look OK? Any suggestions as to improving
> or other stress tools/approaches to investigate? The hardware spec is as
> Each server has 1 NIC connected to a 1Gb Switch on a private LAN which is
> turn connected to a NetApp file server. Other NICs are for heartbeat and
> public LAN connection. All are using 1Gb Ethernet. iSCSI Initiator
> LUN created to the Netapp box of 100Gb - partitioned into 99Gb and 1Gb
> drives - Shows up as E: for data and Q: for quorum in Windows Explorer.
> N.B. Above info supplied by network guys and is stuff I'm not overly
> familiar with so be gentle with me :)
SQL Server I/O performance issue posted by YaHozna on Thu, 20 Sep 2007