DBI317: Optimizing SQL in a Virtual Environment

Denny Cherry, Independent Consultant (www.mrdenny.com @mrdenny)
vExpert 2012, Microsoft Certified Master, MVP

This session¬†covered some helpful tips for virtualizing SQL server, be it on Hyper-V or VMware. Yes you can virtualize SQL and still get excellent performance. But there are some special considerations that you need to be aware of. Most of the tips apply to many applications, but some are SQL specific. The speaker’s slides and commentary were pretty high level, so this wasn’t quite as technical as I was expecting.

Session summary:

  • High level Topics
    • Diagnosing Performance Problems
    • Balloon Memory Drivers
    • Memory deduplication options
    • Storage Configuration options
  • Diagnosing Performance Problems
    • Check host and Guest CPU numbers
    • Check host for CPU thrashing
    • Check host and guest for disk IO latency
    • On VMware check % Used % Rdy time
  • Balloon Memory Drivers
    • Only does something when the host is out of memory. Under normal conditions it does nothing.
    • Prevents host from paging physical memory to the host’s swap file
    • Should be enabled
    • Lock pages in memory within the SQL server config should be disabled unless enabled for a specific reason
  • Memory reservations
    • Recommended that it be set to a portion of the allocated memory (SQL server + some for OS)
  • Memory deduplication Options
    • Great for OS memory
    • Doesn’t work at all for SQL server
    • Doesn’t hurt performance, but don’t count on it to conserve host memory
  • Storage Configuration Options
    • IO is the same if the disks are physical or virtual
    • Use automatic tier adjusting technology if possible except for SQL logs (use RAID 10)
    • Keep OS, data, logs, tempdb on separate disks
    • Use 64K NTFS allocation size
    • Make sure partitions are aligned (default in Server 2008 and later)

    Buy me a coffee

    Related Posts

    Subscribe
    Notify of
    0 Comments
    Inline Feedbacks
    View all comments