SQL SERVER 2005 Storage Performance

585 pts.
Tags:
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

Answer Wiki

Thanks. We'll let you know when a new response is added.

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?

Discuss This Question: 6  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Rick Martinez
    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 )
    585 pointsBadges:
    report
  • Hlx
    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.
    690 pointsBadges:
    report
  • Rick Martinez
    [...] SQL Server 2005 storage performance [...]
    0 pointsBadges:
    report
  • Denny Cherry
    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?
    66,010 pointsBadges:
    report
  • Rick Martinez
    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.
    585 pointsBadges:
    report
  • Rick Martinez
    [...] 2. Keep track of the informative back and forth happening between RickMartinez and Mrdenny on SQL Server 2005 storage performance. [...]
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following