SQL SERVER 2005 Storage Performance
SQL Server, SQL Server 2005, SQL Server 2005 performance, SQL Server performance, Windows Server 2003, Xiotech
We have an IBM server xSeries 460 with a QLogic FC HBA card 4gb on the server with a PCI-X 266MHz bus speed (16 CPU 667MHz FSB, 32GB RAM). The HBA card connects to a Brocade 5000 switch then to the SAN Controller then to a Cisco 9124 Switch then to SAN Drives all through FC. On the IBM server we have SQL Server 2005 installed on windows 2003 32-bit.
IOPS = 750 MB/sec = 13 Latency = 10ms Disk Queue Length = 7 Block Size = 20KB
Users complain that the application is slow all the time, I noticed the application is slow on reads only that is where our high latency comes from. I did see that on our Brocade 5000 Switch all other servers on the network are connected to that switch (mail server, report servers, other application servers) but connect to another SAN. There is only one application connected to the XIOTECH 7000 SAN and that is the application that is slow.
Software/Hardware used:
sql server 2005, XIOTECH 7000 SAN, windows 2003 32-bit
IOPS = 750 MB/sec = 13 Latency = 10ms Disk Queue Length = 7 Block Size = 20KB
Users complain that the application is slow all the time, I noticed the application is slow on reads only that is where our high latency comes from. I did see that on our Brocade 5000 Switch all other servers on the network are connected to that switch (mail server, report servers, other application servers) but connect to another SAN. There is only one application connected to the XIOTECH 7000 SAN and that is the application that is slow.
Software/Hardware used:
sql server 2005, XIOTECH 7000 SAN, windows 2003 32-bit
ASKED:
January 11, 2011 6:30 PM
UPDATED:
January 18, 2011 7:46 PM
Answer Wiki:
First your block size is crap. It should be 64k. That is the size of 99% of the IO that SQL Server will generate. Are your disks correctly alligned?
How many disks are behind the SQL Server? How much RAM is in the SQL Server?
To see all answers submitted to the Answer Wiki: View Answer History.
Hello Mr. Denny thank you for your prompt response.
1. The disks are not aligned.
2. there are 32GB of RAM on the server 28GB for SQL using AWE and 4GB for OS
3. 6 SAN Drives dedicated to SQL Server RAID 10
(
A. Data
B. Backup
C.Indexes
D. Log
E. SystemDB
F. TempDB
)
4. 2 Local Drives dedicated to SQL Server
(
A.Misc Databases
B.Misc Databases
)
I would verify how much of the Xiotech controller cache is being used for reads and consider adding more drives and separating the data and logs. In addition, it is likely that you can run profiler to identify your offending queries (highest reads) and will be able to reduce your total I/O by targeting either bad code or missing indexes.
Are those 6 LUNs on the same spindles, or on different spindles?
Having the disks not correctly aligned will impact performance. How large are the databases on your server?
Hello Mr. Denny, yes all LUN’s (VDisk) are on the same spindles. Our main database is 500GB and there are 10 more databases below 10GB.