Home Forums Database Design Hardware Placement of SQL Server Data and TLog files in a SAN Infrastructure and Monolith Storage Environment RE: Placement of SQL Server Data and TLog files in a SAN Infrastructure and Monolith Storage Environment

  • Difficult to say if, performance wise, it would be better to split the mdf/ndf files from the ldf files. It would depend on how the database is used (eg. mainly reads or mainly writes?). But the discussion here is more about SAN performance, and knowing where the bottlenecks are.

    In the absence of a SAN guru, an alternative approach, also suggested by SQLBuddy, would be to run a stress test tool to get an idea of the real-world situation. Depending on whether you like your SAN Admin, you may want to check with them first!

    The simplest, command-line tool I know is SQLIO from Microsoft:

    http://www.microsoft.com/en-us/download/details.aspx?id=20163

    Brent Ozar has a nice, concise intro to SQLIO:

    http://www.brentozar.com/archive/2008/09/finding-your-san-bottlenecks-with-sqlio/

    To more accurately test real-world MSSQL activity, try SQLIOSim (which replaced SQLIOStress)

    http://support.microsoft.com/kb/231619

    Or the slightly nicer GUI tool CrystalDiskMark

    http://crystalmark.info/download/index-e.html#CrystalDiskMark

    SQLIO should at least tell you if your LUNs have all been created equally; and also tell you if you can expect better performance by splitting the database files across multiple LUNs. With tiered storage, tests could get more difficult to interpret.