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