Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

This question is a bit general since I am not exposed to SAN (Storage Area Network).

Are there any optimizations or configuration changes that can be performed with SAN to improve SQL Server performance?

share|improve this question

3 Answers 3

up vote 5 down vote accepted

The tuning that you'd do with a SAN would include.

  1. Make sure that you have enough disks to support your IO requirements.
  2. Make sure that mdf, ldf and tempdb are seperated.
  3. Make sure that sequential IO databases (data warehouses) are on different spindles than random IO databases (OLTP).
  4. Lower or disable the amount of read cache on LUNs which supprt OLTP databases as they usually can't make any use of read cache.
  5. Maxamize the amount of white cache to avoid a force flush situation.

If you have a chance to make it up to the SQLPASS Summit in Seatle in November I'll be giving a presentation on Storage for the DBA which is all about SAN storage.

share|improve this answer
    
+1 for maximizing wRite cache. SQL Server has awesome read-ahead & buffer manager so configure your SAN's cache to be 100% write –  Nick Kavadias Aug 10 '09 at 15:20

Take a look at Brent Ozar's tutorial on SAN tuning using SQLIO:

http://sqlserverpedia.com/wiki/SAN_Performance_Tuning_with_SQLIO

I used this technique to identify a bottleneck on an older remote SAN where we manage an OLTP system (the bottleneck was only having one Host Bus Adapter card).

Check out the following best practices white paper:

http://technet.microsoft.com/en-us/library/cc966412.aspx

Touched on in the white paper - remember to set the correct offsets on your volumes if you're not using Windows 2008:

http://msdn.microsoft.com/en-us/library/dd758814.aspx

Separate your databases' log and data files and tempdb as mrdenny has explained, and use an appropriate RAID level.

Not SAN specific, but useful - when creating your databases' transaction logs pre-grow them to avoid Virtual Log File fragmentation:

http://www.sqlskills.com/BLOGS/KIMBERLY/post/8-Steps-to-better-Transaction-Log-throughput.aspx

share|improve this answer

This a generalzied question however the best optimzation you can do with a SAN in my opinion is the original deployment of the LUNS and RAID configurations.

For example, if you have a 24bay SAN, determining the size, raid configuration and amount of disks used per LUN. from there you can evenly distribute the disk i/o for the SQL server by optimizing what goes where.

dedicated lun for the read databases, the heavy write or mixed databases, tempdb, etc...

let me know if this helps point in the right direction, if you have anymore questions feel free to ask, have a great day.

Best, Nick

share|improve this answer
    
btw Sung I noticed you live in flushing, non related to the question but my fiancee lives out there, I'm a native new yorker. I work downtown in Soho, do you have a linkedin? add me at linkedin.com/in/noneil –  Nick O'Neil Aug 7 '09 at 15:43
    
@Nicholas Thanks Nicholas. I haven't thought about placing transaction log file on SAN... By the way, I don't have linkedin account. sorry but let me try it out. –  Sung Aug 7 '09 at 17:09
    
@Nicholas: I have added you to my linkedin network –  Sung Aug 8 '09 at 4:04

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.