SQL Server Performance

SQL Server SAN

Discussion in 'Performance Tuning for Hardware Configurations' started by lghollow, Jan 11, 2005.

  1. lghollow New Member

    we have a SAN solution with 100 72gb 10k drives on a Raid 5 configuration. The database is on 1 logical drive letter and spread across all drives. The drives are also shared by a number of other non sql servers. My first inclination is why? won't this cause performance issue for sql server? or will the SAN be able to handle the load? Anyone have any experience with this
  2. Luis Martin Moderator

    Are you sure disk are the problem?
    Run Performance Monitor looking for CPU, Memory and disk counters.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.

  3. joechang New Member

    if you have high transaction volume, this will be a problem,
    fundamentally, all databases depend on fast writes to log, relying on the sequential nature of log write for sub-ms latency.
    it does not matter if you have a 1000 disk drives, when you mix a random work load with sequential, you get random IO characteristics, which even on a lightly loaded disk system (meaning you wasted most of the disks) ends up around 5ms disk latency
  4. derrickleggett New Member

    That's surely not ONE RAID 5 configuration. I don't even think that's possible. The biggest I've ever seen is 15 disks in one RAID 5 configuration. It was EXTREMELY slow, but they used it for archive so nobody cared. They might have several RAID 5 configurations with MetaLUNs built from multiple RAID 5 groups. That's a fairly common scenario in the SAN world. You build a bunch of 5 disk RAID 5 groups, then create RAID 5 LUNs pieced together from several of the RAID 5 LUNs. Theoretically, you can get some good performance out of this type of setup.

    Just curious (resulting in the above lol), but I fundamentally agree with Joe on this. Some "storage engineers" thing they can throw the world on the SAN without planning the disk latency, IO needs, etc of the disk system. That's foolish. It's math. If the numbers don't add up, your system will suck!

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  5. lghollow New Member

    Thanks to everyone for the comments. at this point I am not sure if peformance issues are related to the SAN. My initial impression was that the SAN configuration really wasn't setup for high DB through put. This paticular shop is mostly batch...but they do have some trnsactional (OLTP) processes occuring. Thanks for all the comments.
  6. lghollow New Member

    here are some of the stat averages I have recieved over the past week<br /><br />Memory<img src='/community/emoticons/emotion-4.gif' alt=':p' />age Faults/sec - 1279 (hi of 14386)<br />Memory<img src='/community/emoticons/emotion-4.gif' alt=':p' />age Reads/sec - 142 (hi of 15950<br />Memory<img src='/community/emoticons/emotion-4.gif' alt=':p' />age Reads/sec - .65 (hi of 1.55)<br />Memory<img src='/community/emoticons/emotion-4.gif' alt=':p' />ages/sec - 1095.81 (hi of 11880)<br />PhysicalDisk:%Disk Time:_Total - 92.64 (hi of 650)<br />PhysicalDisk:Avg Disk Queue Length:_Total - 4.63 (hi of 32)<br />SQLServer:Buffer Manager<img src='/community/emoticons/emotion-4.gif' alt=':p' />age reads/sec - 821<br />SQLServer:Buffer Manager<img src='/community/emoticons/emotion-4.gif' alt=':p' />age writes/sec - 56<br />SQLServer<img src='/community/emoticons/emotion-7.gif' alt=':S' />QL Statistics: Batch Requests/sec - 17<br /><br /><br />I think there is a combination of disk contentaion and shortage of memory. The server has 2gb of <br />ram running on std version. The SAN has 100-72gb drives with as I indicated above 22+ other servers none of which are segmented. Does anyone see anything wrong with these stats and are there others that should be monitored to get a better picture. <br /><br />TIA
  7. Luis Martin Moderator

    All memory counters are bad.
    For example, one of them, pages/sec shoud <= 20 for long time. You have 1095, so you need more memory.
    I suggest to get more memory first (of course with SQL 2000 Enterprise).


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell


    All postings are provided “AS IS” with no warranties for accuracy.



  8. derrickleggett New Member

    You need an IT department. All of your counters are horrible. Is the entire environment running this bad????

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.

Share This Page