How Green is your SQL Server?

  • Article

The possibilities for consolidating SQL Server are many and varied, and one of the key drivers for all of these is the cost of energy which curiously doesn’t seem to have fallen in line with the price of oil & gas.

Anyway I found this detailed whitepaper on how Microsoft applies the principles of sustainable IT as applied to SQL Server to its data centres.  The numbers in this document are staggering - 5,000 instances of SQL Server providing over 100,000 databases, but what is more interesting is the drive to virtualise a lot of this while recognising that there will be some workloads more suitable than others.

There’s also a lot of tips that could well apply to other enterprises:

  • Crawl, walk, run. Don’t go for the maximum host consolidation ratio right away. Begin with the smaller workloads, validate your deployment, and refine the plan. Maximize your resource utilization in phases after establishing and evaluating your actual utilization.
  • Use Hyper-V pass-through disk or fixed Virtual Hard Disks (VHDs) for storage. Fixed VHDs offer some manageability benefits but provide slightly lower performance. Moving a guest to another host, for example, is simplified when using VHDs.
  • Do not over commit processors for SQL Server guests. Begin with one logical processor for one physical processor. Verify your performance and refine your configuration as needed. At some point, this may include over commitment, but begin without over commitment to manage risks.
  • Avoid the use of emulated devices in Hyper-V. Favour synthetic devices which provide better performance and lower processor overhead.
  • Establish an operating level agreement (OLA) with performance requirements for your storage provider if they are a separate service. Microsoft IT SQL Server Utility has requirements for 1ms average disk/second read/write for log and 8ms for OLTP data.

Most of this has come form the SQL CAT paper on best practices for running SQL Server on Hyper-V, so Microsoft does actually follow its own advice!

Technorati Tags: Green IT,SQL Server,Virtualisation,Hyper-V