Reporting Services – Max Memory

Background

Reviewing one of our SQL Server Instances and noticed this entry in the SQL Server Error Log.

ErrorLog

Image

Textual

2017-10-02 09:23:00.29 Server Detected 16371 MB of RAM. This is an informational message; no user action is required.
2017-10-02 09:23:00.29 Server Using locked pages in the memory manager.
2017-10-02 09:23:01.42 Server Large Page Allocated: 32MB
2017-10-02 09:23:01.99 Server Cannot use Large Page Extensions: Failed to allocate 32MB

 

TroubleShooting

Resource Monitor

Image

Explanation

  1. Total
    • 97% Used Physical Memory
    • 428 MB available
  2. Processes
    • ReportingServicesService.exe
      • Commit :- 2,495,860 KB ( 2.495 GB )
      • Working Set :- 2,243,072  KB ( 2.495 GB )
      • Sharable :- 52,700 KB ( 50 MB )
      • Private :- 2,190,372 KB ( 2.190 GB )
    • sqlservr.exe
      • Commit :- 795,204 KB ( 795 MB )
      • Working Set :- 123,712  KB ( 123 MB )
      • Sharable :- 32,376 KB ( 32 MB )
      • Private :- 90,378 KB ( 90 MB )

Remediation

Overview

  1. Identify location where Reporting Services is located
    • We can use Control Panel, Services applet
      • “C:\Program Files\Microsoft SQL Server\MSRS12.MSSQLSERVER\Reporting Services\ReportServer\bin\ReportingServicesService.exe”
        • MSRS12
          • SQL Server 2014
  2. Make backup of rsreportserver.config
  3. Edit rsreportserver.config
    • Add or amend
      • WorkingSetMaximum
        • Syntax
          • <WorkingSetMaximum>ValueInKB</WorkingSetMaximum>
        • Sample
          • <WorkingSetMaximum>1000000</WorkingSetMaximum>
            • 1000000 KB is 1 GB

 

Services Applet

 

rsreportserver.config

WorkingSetMaximum

Overview

Here we set maximum memory for Reporting Services to 1000000.

Value is in KB.

And, 1000000 KB translates to 1 GB.

 

Textual


<!-- Added by dadeniji on 2017-10-05 10:18 AM -->
<WorkingSetMaximum>1000000</WorkingSetMaximum> 

Image

 

Confirm

Restart MS Reporting Services and review through Log files and Resource Monitor.

ReportServerService*.log

Image

Textual

library!DefaultDomain!a5c!10/05/2017-10:17:28:: i INFO: Initializing MemorySafetyMargin to '80' percent as specified in Configuration file.
library!DefaultDomain!a5c!10/05/2017-10:17:28:: i INFO: Initializing MemoryThreshold to '90' percent as specified in Configuration file.

Explanation

  1. MemorySafetyMagin
    • Initializing MemorySafetyMargin to ’80’ percent as specified in Configuration file.
  2. MemoryThreshold
    • Initializing MemoryThreshold to ’90’ percent as specified in Configuration file.

 

Resource Monitor

Image

Explanation

  • Total
    • 85% Used Physical Memory
    • 2031 MB available
  • Processes
    • ReportingServicesService.exe
      • Commit :- 271,732 KB ( 270 MB )
      • Working Set :- 138,360 KB ( 140 MB )
      • Sharable :- 32,512 KB ( 34 MB )
      • Private :- 104,168 KB ( 104 GB )

Summary

We are satisfied with the change we made.

 

References

  1. CSS SQL Server Engineers
    • PSSQL
      • SQL Server and Large Pages Explained….
        Link
    • Adam W. Saxton
      • Troubleshooting Memory Issues with Reporting Services
        Link

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s