Slow performance in Controller due to inability to use available CPU cores in SQL server
Users experience performance issues in Controller. During the diagnosis, the root cause is found that the database (SQL) server has plenty of CPU cores (e.g. 16) but only some (e.g. 4) are being utilised.
I.T. administrator logs onto the SQL server, and looks in Task Manager, inside tab "Performance". Inside there, there are many (e.g. 16) separate CPU graphs, indicating that Windows can successfully see many (e.g. 16) CPU cores.
However, during the 'busy' periods of time, only some (e.g. 4) of these graphs actually are active.
There are lots of possible causes for performance problems.
- For more examples, see separate IBM Technotes #1365257 & 1347497.
This IBM Technote specifically relates to the scenario where the SQL server does not utilise all the available CPU cores.
Again, there are several possible causes including:
- Scenario #1 - SQL server has more than 4 CPU sockets, and customer has reached the limit of their edition of SQL.
- For example, SQL Server 2008 R2 is licensed per processor socket, and not per logical CPU basis.
- Scenario #2 - SQL server has 4 or fewer CPU sockets (e.g. 4 sockets) and each one is multi-core (e.g. 4 CPU cores in total) which means that Task Manager sees lots of CPU cores (e.g. 16 cores in total) however:
- Scenario #3 - SQL server is trying to utilise all CPU cores, but its automatic CPU affinity process is failing due to the fact that the server has been created from an image.
Resolving the problem
Upgrade your edition of Microsoft SQL server (for example from "Standard" to "Enterprise" or even "DataCenter" editions.
Reconfigure your virtual host platform (e.g. ESX server) so that the guest images are aware that the CPUs are multi-core.
- For example:
- if using VMWare ESX, utilise the setting "cpuid.coresPerSocket".
- if using KVM, utilise the setting "topology sockets" inside the guest XML file.
In one real-life example, the solution was to disable Microsoft's ' automatic' CPU affinity system, and instead manually inform SQL to utilise all CPUs (e.g. all 4 CPUs that the system had installed).
- Logon to the SQL server as an administrator
- Launch "SQL Server Management Studio"
- Right-click on the server, and choose "properties"
- Click tab "Processors"
- Untick the box "Automatically set processor affinity mask for all processors"
- Tick the "Processor Affinity" box next to all of your CPUs
- Obtain a short period of downtime, and restart your SQL server (to ensure that the changes are actioned).
More support for:
Software version: 8.3, 8.4, 8.5, 8.5.1, 10.1, 10.1.1
Operating system(s): Windows
Reference #: 1615289
Modified date: 30 November 2012