2

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?

dance2die
  • 2,011
  • 7
  • 32
  • 41

3 Answers3

5

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.

mrdenny
  • 27,174
  • 4
  • 41
  • 69
  • +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
  • `Make sure that mdf, ldf and tempdb are seperated.` - do you mean separated via drives on the sql server or via physical disks at the SAN level? – variable Jul 16 '22 at 18:41
  • If you have a traditional SAN with RAID groups and you can separate the physical IO from each other, then separate it at the physical layer. If you have a newer SAN where everything is physically together then this doesn't really matter anymore. Things have changed a LOT since I write this in 2009. – mrdenny Jul 17 '22 at 19:59
2

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

SuperCoolMoss
  • 1,262
  • 11
  • 20
1

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

Nick O'Neil
  • 1,771
  • 11
  • 10
  • 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 07 '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. – dance2die Aug 07 '09 at 17:09
  • @Nicholas: I have added you to my linkedin network – dance2die Aug 08 '09 at 04:04