Today I’m debuting a series of posts on SQL Server best practices and lessons learned on flash. For those who joined my webinar last month, this will be a great next step in looking how to implement better practices for flash implementation. (If you missed the webinar, you can stream it here: https://www.brighttalk.com/webcast/12587/185445)
Introduction: Optimizing vs. Conserving Power
By default, processors are configured for energy efficiency. For example, SanDisk® Fusion ioMemory PCIe application accelerators are shipped with energy conservation enabled. Windows Server is likewise by default configured to conserve power. Most Windows servers aren’t expected to deliver peak performance, as such settings makes sense in order to minimize energy costs—including not only what’s required to power the server, but also for cooling costs.
However, these default settings are undesirable for servers from which high performance is expected—and this is especially true for SQL Server servers—whether the storage is spinning media or flash.
The problem—not exploiting the performance that you paid for—is widespread. Results of a 2011 survey revealed that 56% of DBAs weren’t even aware of hardware power settings. It has long been a best practice for maximum performance to elevate default power settings, as summarized with supporting references in a post by my fellow MCM and current member of the Microsoft SQL Server Customer Advisory Team (SQL CAT), Cindy Gross (blog|twitter).
Optimizing Power for SQL Server: Overview
Optimizing power is not merely a best practice, but mandatory for servers from which high performance is expected.
[Tweet “High performance from your #SQLServer? You’ll need to optimize power!”]
Do so in these three separate locations: the BIOS, the Windows Control Panel, and ioMemory PCIe configuration settings:
- BIOS
The BIOS of contemporary servers are set to throttle the voltage delivered to processor cores. Even servers subjected to an apparently high workload may suffer from thousands of voltage fluctuations per second. Mitigation is simple:
- Disable C-State/P-State settings
- Maximize fan or cooling, if available
Note that not every BIOS will expose a separate setting for cooling.
Former Intel engineer & SQL Server expert Joe Chang has an amusing rant on this topic.
- Windows Control Panel
Set Power Options to “High performance”. Do so in either of three ways:
- Windows UI: Control Panel > Hardware > Power Options > High performance
- Command Shell: POWERCFG /SETACTIVE 8c5e7fda-e8bf-4a96-9a85-a6e23a8c635c
- PowerShell: Powercfg -SETACTIVE SCHEME_MIN
- PCIe ioMemory configuration
Those using ioMemory PCIe application accelerators typically have a bias for maximizing their performance. To do so requires elevating the out-of-the-box power settings to 50W:
fio-config –p FIO_EXTERNAL_POWER_OVERRIDE SN:MW
Where < SN > is the serial number of the card which can be obtained from the fio-status command, < MW > is the power in milliwatts, for example, the following configures the device with the given serial number to 50W.
fio-config –p FIO_EXTERNAL_POWER_OVERRIDE 1410G0092:50000
Multiple cards can be configured with the same command:
fio-config -p FIO_EXTERNAL_POWER_OVERRIDE 1407G0327:50000,1504G0154:50000
Important Notes:
- The override command must be done for each card in the system and all cards must be done at once using the same command. That means that when adding a new card to the system and implementing power override, you must do so for all cards in a single command—the new one as well as pre-existing preconfigured cards.
- A reboot is required.
Other Notes:
- The syntax must be exactly as described, with no spaces other than those shown.
- Fusion-io ioMemory Generation 3 and Fusion ioMemory Generation 3.5 PCIe application accelerators are more efficient than previous generation hardware, yet our tests show that maximum performance requires this configuration change.
- For more information, especially for details & caveats relevant to older hardware—PCIe application accelerators, servers, PCIe power availability, and power supplies—refer to the official support documentation for your card and driver version.
Trust but Verify
Whether installing brand new hardware or reconfiguring existing storage, always validate that you’re getting the performance expected. I’ll provide additional guidance in a forthcoming post.
Summary
To maximize performance for SQL Servers from which high performance is expected, it’s an essential best practice to maximize power. Do so by reconfiguring default settings found in the BIOS, in the Windows Control Panel, and on your SanDisk Fusion ioMemory card.
I invite your comments and questions. Reach out to me directly at jimmy.may@sandiskoneblog.wpengine.com and @aspiringgeek on Twitter.
Join our Webinar: Best Practices for SQL Server on Flash
Join me for the SpiceWorks Partner Webinar on February 10th at 1PM CT, discussing best practices for SQL Server on flash storage and how IT environments everywhere are integrating flash into their architecture — bare metal or virtualized.
What you’ll walk away with:
- Best Practices for SQL Server on Flash: A brief overview of fundamentals as well as highlights requiring special attention.
- SQL 2016 AlwaysOn Availability Group Performance Improvements: In SQL Server 2016, the network transport & log redo bottlenecks have been mitigated.
- AlwaysOn AGs are now enterprise ready.
- Consolidation on Flash: An idle CPU isn’t doing nothing—it’s burning licensing dollars. Move the I/O bottleneck to the CPU—where it belongs—& literally save hundreds of thousands of dollars per instance (with real-life case studies).
- Solution Components: Review of SanDisk ioMemory application accelerators & Optimus MAX SAS SSDs.
I look forward to you joining me there! Register here.
