SQL Server Performance

Poor SAN performance

Discussion in 'SQL Server 2008 Performance Tuning for Hardware' started by newworld1, Jan 31, 2011.

  1. newworld1 New Member

    I am having very high read wait times on our OLTP system and would like to prove to our SAN vendor that we need to make changes. I recently modified our disk configuration but the changes didn’t make any difference. Our current configuration is as follows:

    Primary data file - dedicated 8 disk raid 10 lun
    Index file (which contains all of the non-clustered indexes) - dedicated 6 disk raid 10 lun
    Log file – dedicated 4 disk raid 10 lun
    TempDB – dedicated 4 disk raid 10 lun.

    Here are my performance numbers:

    Primary Data File (50 GB)
    Avg Read Wait -113 ms
    Avg Write Wait - 16 ms

    Index File (50 GB)
    Avg Read Wait - 119 ms
    Avg Write Wait - 53 ms

    Log File
    Avg Read Wait - 9 ms
    Avg Write Wait - 3 ms

    TempDB - split into 4 files but here is the average
    Avg Read Wait - 4 ms
    Avg Write Wait - 30 ms.

    Our system does about 10 million reads and 2.5 million writes a day.

    My main concern are the read wait times on the Primary and Index files. I also am concerned with the Write wait times on the Index and TempDB files. My understanding is that all wait times should be less than 20 ms.

    As mentioned, I would like some comparison numbers to show my SAN vendor. Would you please run the following code and post the results? It would be fine if you would like to just post the Avg. wait times.

    Let me know if any one has suggestions on how to get better performance outside of the SAN configuration. Are there any different SQL setting, configurations that I could implement to improve read write performance?

    Thanks for the help.

    select db_name(mf.database_id) as databaseName, mf.physical_name,
    num_of_reads, io_stall_read_ms, AvgReadWait=io_stall_read_ms/num_of_reads,
    num_of_writes, io_stall_write_ms, AvgWriteWait=io_stall_write_ms/num_of_writes,
    from sys.dm_io_virtual_file_stats(null,null) as divfs
    join sys.master_files as mf
    on mf.database_id = divfs.database_id
    and mf.file_id = divfs.file_id
    WHERE db_name(mf.database_id) in ('EnterDatabaseName','tempdb')

  2. Luis Martin Moderator

    Please could you show all server hardware and software configuration?
  3. ghemant Moderator

    IMO you should have RAID 5 array if your application is read intensive since it will give you an edge for sequential read over random access on RAID 10!

Share This Page