It is common to determine and implement best practices while setting up new servers or while trying to identify and fix performance problems with SQL Server running on Windows Server 2008 R2 or higher. Some of these practices are effective and are meant to upgrade the hardware of the server if the server is not performing well as expected. However there are may tips and tricks which can be followed to improve the performance of the server instead of just upgrading the hardware to improve the performance. One of the common issues we generally see is high CPU usage on server running SQL Server instance.
On Windows Server 2008 or more, set the “High Performance” power plan in Control Panel from Power Options. Of course, Windows Server sets the “Adjusted” force arrangement, which empowers vitality protection by scaling the processor execution in view of current CPU use. From Intel X5500 and other last-era CPUs, the clock is throttled down to spare force (Processor P-state), and just increments when CPU use achieves a certain point. The Minimum and Maximum Processor Performance State parameters are communicated as a rate of greatest processor recurrence, with a quality in the reach 0 – 100.
In the event that a server obliges ultra-low inertness, invariant CPU recurrence, or the extremely most astounding execution levels, for example, a database servers like SQL Server, it may not be useful that the processors continue changing to lower-execution states. In that capacity, the High Performance force arrangement tops the base processor execution state at 100 percentage.
Also, it is important to keep in mind that changing this setting alone from control panel does not help, as some servers have this enabled at BIOS level, thus need to make the change at BIOS level too if enabled in BIOS. Also for VM systems, it is important to enable this at guest and host level and on BIOS on host server. Although this is a simple change, but shows greater improvement in performance of busy servers running SQL Server.
Hope this was helpful.
This is applicable for below versions of SQL Server
SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings