Problem 3: Generating my reports (SSRS) takes forever
A quick rummage through the environment using the Performance Monitor (matching counters in brackets) revealed:
- 8GB RAM installed
- approx. 4GB RAM available (MemoryAvailable Mbytes)
- CPU load negligible (Processor%ProcessorTime)
- SQL Server and Reporting Services installed.
Nothing so far indicated performance problems. Only parallel installation of SQL Server reporting services is not recommended.
But a glance at the paging file (Paging File%Usage) did reveal a lurking issue. File usage was extremely high. This was caused by the 7GB limit on RAM assigned to SQL Server. It was needed for a monthly import process, reserved and therefore not released. It meant that only 1GB was available for the operating system and Reporting Services, and this was what caused the high load on the paging file when loading Reporting Services.
The maximum permitted RAM was reduced from 7GB to 5GB, resolving the issue in the short term. But SQL Server still needed 7GB once a month. This has to be dealt with downstream. Options include
- more RAM
- increase in RAM for SQL Server only during the monthly import
- import optimization
It is important to remember when configuring the maximum permitted RAM for SQL Server that SQL Server will reserve the RAM, even when it is not currently in use. This is sensible otherwise, as it prevents repeat loading of data from storage when it is accessed. Moreover, we should also point out that performance counters that can be monitored by the Windows Server Performance Monitor are also created when installing SQL Server.