Ever noticed SQL using most of the Memory (RAM) on the server and all your other applications start slowing down? This could be because SQL is using the maximum amount of memory it can get a hold of.
When SQL is installed the default max amount of memory it is allowed to use is set to 2147483647 MB. You can check this by right-clicking on the server at the top in the Object Explorer, selecting Properties, then selecting memory.
To decide what to set the max memory of SQL to use you can use this simple rule; leave 4GB or 10% of total memory free, whichever is larger on your instance to start with, and adjust this as needed. The free memory will be consumed by the OS and other applications on the server.
To calculate this, remember 1GB is 1024MB. If your server has 32GB of RAM then you would take 28 x 1024 = 28672MB. You would then insert this into the max memory.
NB! This does not require restarting your SQL Server instance, but it will cause execution plans to recompile.
Many people complain about their reports taking a long time to load when they access them first thing in the day. This is due to the reporting server’s recycle time. What happens is SSRS regularly restarts the application domain after a specific time period. Upon first request to the report server after the application domain has been restarted, SSRS needs to load all the settings and this takes quite a long time.
The default recycle time for the application domain is 720 minutes. This can be increased to a more reasonable time, however this does still not solve the problem. The first request to the report server will still take a long time to process.
If you would like to increase the recycle time you can do this in the rsreportserver.config file. The file can be located in “C:\Program Files\Microsoft SQL Server\MSRS13.MSSQLSERVER\Reporting Services\ReportServer”
You can search for the below line of code in the config file and increase the time:
The better solution would be to schedule a PowerShell script that refreshes the report server twice a day. This will make the access to the report server for the first user fast. They will no longer have to wait long for the report to load.
There are two PowerShell scripts that you can use. The first script will access the report server:
$root = 'https://Hostname/reports/'
The second script will start up IE and open up a report that is deployed on the server. You can specify which report to open up.
v=$root = 'http://Hostname/reports/report/ReportNameToAccess'
$IE=new-object -com internetexplorer.application
You can schedule either of these scripts to run with Windows Task Scheduler.
Hope this helps you with speeding up the load time of the reports!