Tuesday, August 7, 2018

SQL Server Reporting Services - Memory Configuration & Management




1)   What is Memory Pressure
Memory pressure simply is the percentage of physical memory less than free and reclaimable memory of total memory. If there is no free or reclaimable memory in the system, memory pressure is 100%.

2)   SQL Server Memory Management
SQL Server Services (DB Engine, SSIS, SSAS and SSRS) can use all available memory, we can override default behavior by configuring an upper limit on the total amount of memory resources that are allocated to particular service/ applications. We can also set thresholds that cause server to change how it prioritizes and processes requests depending on whether it is under low, medium, or heavy memory pressure.
3)   SSRS Memory Management
For SQL Server Reporting Services, at low levels of memory pressure, the server responds by giving a slightly higher priority to interactive or on-demand report processing. At high levels of memory pressure, the report server uses multiple techniques to remain operational using the limited resources available to it.
Memory pressure
Server response
Low
Current requests continue to process. New requests are almost always accepted. Requests that are directed to the background processing application are given a lower priority than requests directed to the Report Server Web service.
Medium
Current requests continue to process. New requests might be accepted. Requests that are directed to the background processing application are given a lower priority than requests directed to the Report Server Web service.
High
Current requests are slowed down and take longer to complete. New requests are not accepted. The report server swaps in-memory data files to disk.
4)   Memory management for SQL Server Reporting Services
There are mainly four Configuration settings that control memory allocation for the report server
                  1)      WorkingSetMaximum
                  2)      WorkingSetMinimum
                  3)      MemorySafetyMargin
                  4)      MemoryThreshold

a.       WorkingSetMaximun: Specifies a memory threshold after which no new memory allocations requests are granted to report server applications. By default, the report server sets WorkingSetMaximum to the amount of available memory on the computer. This value is detected when the service start
b.       WorkingSetMinimum: Specifies a lower limit for resource consumption; the report server will not release memory if overall memory use is below this limit. By default, the value is calculated at service startup. The calculation is that the initial memory allocation request is for 60 percent of WorkingSetMaximum.
c.       MemoryThreshold: Specifies a percentage of WorkingSetMaximum that defines the boundary between high and medium pressure scenarios. If report server memory use reaches this value, the report server slows down request processing and changes the amount of memory allocated to different server applications. The default value is 90
d.       MemorySafetyMargin: Specifies a percentage of WorkingSetMaximum that defines the boundary between medium and low pressure scenarios. This value is the percentage of available memory that is reserved for the system and cannot be used for report server operations. The default value is 80.

5)   How to configure memory for Reporting Services
a.       Open RSReportServer.config file from C:\Program Files\Microsoft SQL Server\MSRS13.MSSQLSERVER\Reporting Services\ReportServer or other directory where SQL Server Reporting Service  is installed
b.       Search the required attribute in RSReportServer.config and change the value per requirement:
<MemorySafetyMargin>80</MemorySafetyMargin> 
      <MemoryThreshold>90</MemoryThreshold> 
      <WorkingSetMaximum>4000000</WorkingSetMaximum> 
      <WorkingSetMinimum>2400000</WorkingSetMinimum>

c.       The values are integers that express kilobytes of RAM you are allocating to the server applications.


No comments:

Post a Comment