Results 1 to 8 of 8
  1. #1
    Join Date
    May 2008
    Posts
    97

    Unanswered: Microsoft SQL Performance

    Hi Guys,

    I am running into a performance problem with our MS SQL database. Admist doing some hardware benchmark investigation I have found the following HDD bechmark using HD Tune.

    benchmark read graph
    random read graph

    I have a few questions:
    - As we get our server hosting via a Web Hosting company, does this data look normal for a production server?
    - Would we see a benifit moving to SSD for Database performance?

    Any insight into this problem/ resolution would be much appreciated as I am still learning.
    Last edited by Eric the Red; 08-06-11 at 16:14.

  2. #2
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    Please excuse my wording, but that disk performance suck. A single SATA drive should perform better.

    So, to the real world. Pity have not finished my article on it (would have linked to it otherwise). Disk systems for data and log files should be optimized for 64kB disk operations. That disk of yours can handle an astonishing 47 IOPS. As a guideline, a 10k tends to handle some 200 IOPS, a 15k disk approximately 250 IOPS.

    So, to your questions:
    - Yes, it seems to be "normal". Few VMWare servers focused on memory and disk size, not performance.
    - You would benefit moving the database to virtually anything.
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  3. #3
    Join Date
    May 2008
    Posts
    97
    Thank you! I really appreciate the input!!

    Also, if anyone has anything to add about SSD + DB performance than I am all for listening!

    I hear 80,000 IOPS with SSD's but does this really translate to this much of a DB improvement?

  4. #4
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    Well, SSD disks allows for a lot more IOs than regular drives, but there is a few things I would like to point out. "Regular" SSD drives are made for laptop or desktop computers, not for continuous load in a server. There are indeed SSD disks that are made for servers as well, but I'm afraid they've got quite a different pricing.
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  5. #5
    Join Date
    May 2008
    Posts
    97
    Ok, got it. So I'm thinking of upgrading the server to the 10k drive which as you said, tends to handle some 200 IOPS. Awesome!!!

    This may sound like a dumb question but.. what happens when I get to the point where my server demands more IOPS commands than what my single MS SQL DB server can handle?? Are there MS SQL features which allow me to split the DATA workload over many DB servers to handle such a scenario?

  6. #6
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    There are database servers han dling at least 100k IOPS out there. If I were to change the disks system for a database server, I would at least put in 4 10k disks configured in RAID 10. That would give some 800 read iops, or 400 write iops. And, most importantly: A disk (in fact up to two) may fail without loosing any data. Some may argue that you should use RAID 5 instead. It would give 50% more space, the same read iops but only about 200 read iops and only one disk may fail without loosing any data.

    The iops you may get on a server depends on a lot of factors, from block sizes (at least 64k), stripe size (the same), partition offset (multiple of 64k), number of physical disks, latency, number of simulatneous disk operations, bandwith of the disk bus, caching... You get it Of course, a really fast disk system does have its cost. But, happily, 4 disk raid 10 solution should not be expensive. Keep in mind though (for data integrity):

    * Battery backed cache on the raid controller
    * Disable write cache in the OS
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Paul Randal just found a new SSD Toy that has him thrilled. Paul is quite probably the best informed human being on the planet when it comes to SQL Server disk performance (for obvious reasons).

    Your disk performance is pretty common for ISP hosted SQL Servers. That doesn't say much, since my laptop is more than twice that fast and my desktop is much faster still. I would ask your ISP if these numbers are acceptable to them based on your COS (Class Of Service). If those numbers are acceptable, I'd ask how much it would cost to upgrade to faster disk (which may require a step up to a more powerful virtual server).

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  8. #8
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    When you look at the spec sheet, you donæt get anywhere near 200k iops for SQL Server Access, you're stuck with some lousy 23k iops. Jeez, it's the same as some 100+ 10k SAS drives. Mesa wanna.
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •