SQL Server Performance

SAN cache and performance-How much perf over SCS

Discussion in 'Performance Tuning for Hardware Configurations' started by bertcord, Jun 9, 2005.

  1. bertcord New Member

    A current server I have has 4 external RAID 10 arrays.

    The system as a whole will peak at about

    45MB per Sec generated by 4,436 Logical IOPS

    Based on the read write ratio and the fact that I am using RAID10 this equates to about

    56MBSec generated by 5,776 IOPS

    In the SCSI world I would see that the bottleneck would be the IOPS. Using a conservative number of 100 random IOPS per drive I would need 60 Drives.

    How much does the SAN cache help in this scenario? For example will it take these 5,776 IO's and make less IO's but of larger sizes?

    Bert
  2. joechang New Member

    i just sent brad the ppt file from my Spring 2005 SQL Connection topic: Storage performance or something like that.
    hopefully he will have time to put it up soon.
    it addresses the relationship between IOPS, queue depth, latency and short stroking.
    i will try to build a 1 day class on storage performance in the next few months
  3. derrickleggett New Member

    Joe, we had a fun event happen a couple weeks ago. We lost two drives on a SAN within 30 seconds of each other. They were both part of the same RAID 5 array. We lost everything; however, EMC was able to recover the data by manually piecing things together. After the recovery process though, our backups went from 1.5 hours to well over 10 hours. They forgot to re-enable our write cache on the SAN. We re-enabled the cache and backups returned to normal speed.

    Lots of fun!!! The cache can make a huge difference. We backup about 370GB during our main, nightly backup window from multiple databases across the environment. It was interesting to see the drastic difference based solely on cache.

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  4. joechang New Member

    i would be extremely concerned that 2 disk drives fail together, as this may indicate that something outside the disk drive (but possibly in inside the storage system) caused the failure.
    On a direct attach storage systems, the cache is not that important, but there is something about the EMC stuff.
    I suppose this is a CX, not the DMX?
    I heard from a Oracle DBA that EMC internal tests show that the best caching strategy for transactional DB apps is 2M per LUN for read (to allow read-ahead to work) and the rest for write caching. (50/50 for DW apps). This seems reasonable and should apply to SQL Server as well.
    I would bug your EMC rep to provide detailed database specific performance reports and not the usual Sales & Marketing BS
  5. derrickleggett New Member

    I agree. Drill down to a person inside EMC who actually knows something about SQL Server and database/SAN integration. It's hard to get them.

    Joe, we are checking into why we had two disks fail. We're extremely concerned also. I'll let you know the after-action.

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  6. bertcord New Member

    Thanks guys, I have found several good whitepapers on SQL Server on EMC. But to be honest I have heard several different things from different vendors. I am at the point now that I just need to run my own tests with my application workload. We have already purchased one CX700 and we need another SAN for different application. I am trying to convince management that it would be best to wait on SAN purchase number 2 till I can do some tests on the CX700.
  7. derrickleggett New Member

    Well, I would definitely agree with that approach. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />] They're not exactly cheap.<br /><br />MeanOldDBA<br />derrickleggett@hotmail.com<br /><br />When life gives you a lemon, fire the DBA.

Share This Page