Troubleshooting
Problem
Intermittently (but suddenly) all users simultaneously receive extremely poor performance in Controller.
Symptom
Typically users would experience perfectly good performance for a long time, and then suddenly the performance would be horrendously bad (not just slightly worse). 
or:
The exact symptoms that the user will 'feel' varies - see some real-life customer examples below.
Example #1 (Controller 10.4.0)
Intermittently users found that Excel-link reports took a very long time (for example 3 minutes) to run (via F9).
- This is different from the normal/expected time (less than 10 seconds).
Example #2 (Controller 8.5.x)
Users intermittently found that Controller froze for approximately 2 minutes, typically when closing menu windows.
- During this time, the popup windows appear but have no data/values populated in them, such as:
or:
Cause
There are many potential causes for slow performance in Controller.
This Technote specifically relates to the scenario where the cause is that SQL server is busy 'paging' memory used by SQL server to disk.
More Information:
NOTE: The following information relates to a third-party (non-IBM) software product, therefore, it should be considered as a basic guide only.
By default, a SQL server will try to utilise all the available RAM on a server. In many situations this can work OK, but (in some circumstances) the Windows operating system (OS) will require extra RAM memory, and therefore demand that the SQL server's memory (currently stored inside the RAM) is 'paged' to disk.
This time delay may be even worse the more RAM that exists inside the SQL server. Therefore (ironically) the default settings of SQL mean that the performance problem may be worse the more powerful (more RAM installed) the SQL server is.
In other words, the more memory you give SQL Server, the greater the need to set an upper limit on how much it uses. If you allocate too much memory to SQL Server, and not enough for other applications or the operating system, then the operating system may have no choice but to begin excessive paging, which will slow performance of your server.
NOTE:
- See other Technotes for more examples.
This Technote specifically relates to the scenario where the cause is that SQL server is busy 'paging' memory used by SQL server to disk.
- Depending on the server's configuration, this can take a long time to process
- During this time, its ability to serve requests is extremely limited. Therefore any software (such as Controller) which depends on the information inside the database will grind to a halt.
More Information:
NOTE: The following information relates to a third-party (non-IBM) software product, therefore, it should be considered as a basic guide only.
- For exact information/recommendations on this third-party (Microsoft) product, please consult a MS SQL expert DBA and/or the vendor (Microsoft) themselves.
By default, a SQL server will try to utilise all the available RAM on a server. In many situations this can work OK, but (in some circumstances) the Windows operating system (OS) will require extra RAM memory, and therefore demand that the SQL server's memory (currently stored inside the RAM) is 'paged' to disk.
- In other words, the SQL memory is written to the Windows pagefile
- Paging memory to disk can take some time to complete.
This time delay may be even worse the more RAM that exists inside the SQL server. Therefore (ironically) the default settings of SQL mean that the performance problem may be worse the more powerful (more RAM installed) the SQL server is.
- This may explain why the problem is more likely to be seen in more modern powerful 64-bit SQL servers (which can utilise much more RAM than older 32-bit versions).
In other words, the more memory you give SQL Server, the greater the need to set an upper limit on how much it uses. If you allocate too much memory to SQL Server, and not enough for other applications or the operating system, then the operating system may have no choice but to begin excessive paging, which will slow performance of your server.
NOTE:
- It is important to realise that the cause of this problem is not directly related to Controller.
- Instead, the problem can occur with any software that uses a SQL database
- For example see separate Third Party (non-IBM) websites mentioned below.
Environment
All of the following are true:
- Controller database hosted on Microsoft SQL server
- The Windows server (which hosts SQL) has a relatively large amount (typically 12Gb or more) of RAM
- Either:
- Scenario #1 - There is only one SQL instance, which has been configured to use all of the available RAM (the default setting).
- Scenario #2 - Or there are multiple SQL instances, which are (combined) configured to use more RAM (in total) than exists on the Windows server.
Diagnosing The Problem
SQL Error Log entries:
Depending on your environment, you may notice messages (in the SQL Server error logs) such as the following:
This is a common indication of performance problems due to the paging.
- By default, SQL Error logs are located somewhere similar to: ...Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\
- The files are "ERRORLOG", "ERRORLOG.1"... etc.
Depending on your environment, you may notice messages (in the SQL Server error logs) such as the following:
- A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 24484, committed (KB): 48036, memory utilization: 50%
This is a common indication of performance problems due to the paging.
Resolving The Problem
Reconfigure the Microsoft SQL Server's memory settings so that the memory paging does not occur.
- Specifically, reduce the value of the SQL server setting "Maximum server memory (in MB)" so that the SQL Service uses less RAM.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
IMPORTANT NOTE: The problem is caused by third party (Microsoft) settings/features of their SQL/Windows products. IBM cannot give any definitive advice on third-party products. Instead, we recommend customers talk to their third-party software providers (Microsoft). The guidance inside this Technote is provided simply on a 'best endeavour' basis, based on third-party websites and customer feedback. Customers should talk with their third-party (Microsoft) expert before implementing any advice based on this technote, to ensure that the advice is appropriate for their environment.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Scenario #1 - There is only one SQL instance (on the Windows server)
The exact value will vary between customers/environments, but the idea is to ensure that the non-SQL tasks (running on the SQL server) have sufficient memory 'reserved', which is not going to be used by the SQL server.
If the problem persists, try reducing the value of the maximum SQL memory accordingly.
2. Logon to the SQL server as an administrator
3. Launch "SQL Server Management Studio"
4. Right-click on the SQL server, and choose "Properties"
5. Click section "Memory"
6. Make a note of the current value of the setting "Maximum server memory (in MB)":
TIP: The default value is to use up to a maximum of 2147483647 Mb = 2 million Gb = 2000 Terabytes. Since this is such a large value, in practice it means to use ALL the available physical RAM in the server
7. Modify this setting to something less (see above for examples/guidance), for example 12228 (which equates to 12Gb RAM).
8. Click OK
9. Reboot the SQL server
With this in mind, the author suggests the following guidance:
- If your server physically has a very large (16Gb or more) amount of RAM installed (in total), try changing the maximum to TOTAL minus at least 4Gb (i.e. leaving at least 4Gb free for non-SQL operations).
- For less-powerful SQL servers (less than 16Gb total) configure your SQL server to use a maximum of approximately 75% (or 50% in extreme cases) of the total RAM.
In other words, start by using the following approximate guidelines:
| Server's | Suggested value for | [RAM left/spare for |
| total RAM | Maximum server memory (in MB) | non-SQL processes] |
| 8 Gb | 6144 | 2 Gb |
| 12 Gb | 9216 | 3 Gb |
| 16 Gb | 12288 | 4 Gb |
| 24 Gb | 20480 | 4 Gb |
| 32Gb | 28672 | 4 Gb |
Steps:
1. Obtain a short period of downtime (no users using any of the databases hosted by the SQL server)2. Logon to the SQL server as an administrator
3. Launch "SQL Server Management Studio"
4. Right-click on the SQL server, and choose "Properties"
5. Click section "Memory"
6. Make a note of the current value of the setting "Maximum server memory (in MB)":
TIP: The default value is to use up to a maximum of 2147483647 Mb = 2 million Gb = 2000 Terabytes. Since this is such a large value, in practice it means to use ALL the available physical RAM in the server
7. Modify this setting to something less (see above for examples/guidance), for example 12228 (which equates to 12Gb RAM).
8. Click OK
9. Reboot the SQL server
======================================================
Scenario #2 - There are multiple SQL instances (for example 2) on the single Windows server box.
Perform the same steps as above (Scenario #2) except:
- You will have to perform the same steps on each of the separate instances
- The total memory consumed (for all SQL instances) must not exceed the recommendations outlined above (for example leaving 4Gb RAM free for Windows).
Example:
Imagine a scenario where:
- Windows server has 16Gb in total
- It contains 2 SQL instances:
- servername
- servername\latin1
In this scenario, you could configure:
- instance "servername" has a maximum of 6Gb RAM
- instance "servername\latin1" has a maximum of 6Gb RAM
=> This leaves 4Gb RAM left for the rest of Windows operating system to use.
Related Information
[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SS9S6B","label":"IBM Cognos Controller"},"Component":"Controller","Platform":[{"code":"PF033","label":"Windows"}],"Version":"All Versions","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]
Was this topic helpful?
Document Information
Modified date:
15 August 2019
UID
swg21498586