Often you might be hearing complaints that SQL queries running slow on Windows Server 2008 R2 or Windows Server 2012. You will be puzzled after finding CPU, Memory, Disk, and Network utilization normal but still the queries are running slow when compared with other Windows Server 2003 boxes.
Many people I talked to said they have this problem though their server is built with high Hardware configuration. I has similar chat with one of my previous college when on vacation last week. Since this is more hindering problem for Windows Administrators, I want to put up an article based on this.
Both Windows Server 2008(and R2) and Windows Server 2012(and R2) comes with a default Power Plan of “Balanced” and this is the culprit in most slow performance cases. All latest processors has something called P-States (Power States) which will allow low power utilization by Processor by reducing the processor clock speed when Server is not loaded with any tasks and appears ideal. When a new process starts that requires high CPU processing power, the Operating system switches the processor from lower P-States to higher P-States to increase the CPU performance. This is in a Nut Shell. Read the articles posted at the end of this article to read more about these P-States.
Switching to lower P-States (Balanced Power plan) helps you in saving energy bills at the cost of slow running application. To get rid of this new Power optimization thing and let your processor run at full frequency, follow the below procedure.
Change the Power plan in Windows OS to “High Performance”:
To change a power plan:
- Click on Start and then Control Panel.
- From the list of displayed item under Control Panel click on Power Options, which takes you to select a power plan page. If you do not see Power Options, type the word ‘power’ in the Search Control Panel box and then select choose a power plan.
- By default, the option to change power plans is disabled. To enable this, click the Change settings that are currently unavailable link.
- Choose the High Performance option
- Close the Power Option window
Change the Power plan in Hardware to “High Performance”:
There are a similar settings available at the Hardware level as well. I recommend you change them at hardware level as to “High Performance”. In case of HP hardware, you can manage them from ILO Power option. Below are the options available in HP hardware and make sure to select HP Static High Performance mode.
You can configure Power Regulator for any of four operating modes:
- HP Static High Performance mode
- HP Static Low Power mode
- HP Dynamic Power Savings mode
- OS Control mode
As you can see there is an OS control mode available as well. By selecting this option, you are configuring hardware to allow OS to change the hardware level settings. When this option is selected, and you switch the OS to “high performance” power plan, the hardware also automatically switched to high performance mode.
A restart is required after the above changes. It is not mandatory that this particular solution will help in all cases but it is one of the things you should try when you notice slow SQL query times on a Windows Server.
There is a KB article (http://support.microsoft.com/kb/2207548/en-us) on this topic which recommends setting the server to high performance mode when performance degradation is observed.
If you want tune your Windows Server 2008 R2 or Windows Server 2012 further, you might find this article useful (http://www.microsoft.com/whdc/system/sysperf/Perf_tun_srv-R2.mspx)
Related links:
https://software.intel.com/en-us/blogs/2008/05/29/what-exactly-is-a-p-state-pt-1