[Note from Pinal]: This is a 95th episode of Notes from the Fields series. When it is about tuning SQL Server, we always look at the configuration of the SQL Server. However, there are few settings of the operating system can also impact the performance of the SQL Server. I recently asked John Sterrett a very simple question – “Which is the one option from the OS, would you check first when you are tuning SQL Server?”
In this episode of the Notes from the Field series database expert John Sterrett (Group Principal at Linchpin People) explains a very interesting story about how a simple setting of the OS impacts performance of SQL Server. Read the experience of John in his own words.
Doing several SQL Server health checks I have noticed that the operating system settings can slow you down. Today, we are going to focus on power options. By default many companies have the balanced power plan as the default configured option. You will see that it is also the recommended setting. This is recommended to save power usage which reduces which reduces the amount of money required to power your servers. Please keep in mind that your virtualization software or BIOS might also have Power Option settings that should be verified and adjusted. Today, we are focusing on the Windows Operating System.
The balanced power option is great for most servers where CPU power is not mission critical to the applications living on your server. SQL Server is an exception and you will want to get as much as you can out of your CPU Power. In fact Enterprise edition licensing is now licensed by core so you want to make sure you are getting the most out of your SQL Server licensing.
How Do We Script Out The Change?
While you can load the power option GUI and manually change the setting I like to automate as much as possible. The following script could be executed via PowerShell or Command Prompt to make sure High Performance power option is enabled.
Powercfg -SETACTIVE SCHEME_MIN
Conclusion
SQL Server places a different set of demands on the operating system. OS default settings are not always optimal and should be reviewed.
Are your servers running at optimal speed or are you facing any SQL Server Performance Problems? If you want to get started with the help of experts read more over here: Fix Your SQL Server.
Reference: Pinal Dave (https://blog.sqlauthority.com)
