Home / server / Questions / 51511
Accepted
dance2die
Asked: 2009-08-08 07:11:47 +0800 CST

What optimizations can be performed on SQL Server with SAN?

  • 772

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?

sql-server

3 Answers

  1. Best Answer

    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.

    • 5

  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

    • 2

  3. 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

    • 1