IBM Support

Slow performance in Controller due to inability to use available CPU cores in SQL server

Technote (troubleshooting)


Problem(Abstract)

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.

Symptom

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.



Cause

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

Scenario #1

Upgrade your edition of Microsoft SQL server (for example from "Standard" to "Enterprise" or even "DataCenter" editions.

Scenario #2
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.

Scenario #3
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).

Steps:
  1. Logon to the SQL server as an administrator
  2. Launch "SQL Server Management Studio"
  3. Right-click on the server, and choose "properties"
  4. Click tab "Processors"
  5. Untick the box "Automatically set processor affinity mask for all processors"
  6. Tick the "Processor Affinity" box next to all of your CPUs
  7. Obtain a short period of downtime, and restart your SQL server (to ensure that the changes are actioned).

Related information

THIRD PARTY (Microsoft) - Maximum Number of Processors
1365257 - Performance Hints and Tips (tuning) when usin
1347497 - Controller Excel Report Performance Tips

Document information

More support for: Cognos Controller
Controller

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