March 7, 2017 at 3:04 pm
Let me start by saying that I am new to SSRS. I inherited this and am trying to solve some weird issues I am seeing with it.
The server is a VM with 32 GB RAM. The SQL version is SQL 2012 SP3 and the OS is Windows Server 2012 R2. SQL Server is set to use a MAX of 21 GB. So that should leave 11 GB for the SSRS and OS.
The issue is that after the server has been running awhile, it slows down and starts acting weird. Today it would not export a report to Excel about 3/4 ths of the time. After we restarted the SSRS service, it worked fine again. Checking the SQL error logs, it did not seem to the issue.
Looking at the rsreportserver.config file on the server I see this.
<MemorySafetyMargin>80</MemorySafetyMargin>
<MemoryThreshold>90</MemoryThreshold>
<WorkingSetMaximum>25165824</WorkingSetMaximum>
So the way I read this is that the SSRS config file is setting the max working memory to 24 GB (I am assuming that it is set in kilobytes). According to BOL that means it will keep taking requests until that limit is hit, which is never since on 11 GB is left. Am I reading that correctly?
I have a process running that tracks memory usage for the server and it was down to 1,734 MB right before we restarted the SSRS service. Then it went backup up to about 4 GB free. My guess is that this is a memory issue and SSRS needs more memory, but I need to be sure I am right before I suggest adding memory to try to fix this. Normally how much memory does SSRS need to run smoothly? The environment is pretty report heavy with users and automatic reports running constantly. Can anyone recommend a good blog or book to help me wade through this?
Any help would be greatly appreciated. Thank you!!!
Jim
March 7, 2017 at 3:29 pm
SSRS runs in separate memory space from the SQL Server Instance. So if you have 24 GB allocated to SQL Server and 24 GB limit set to SSRS, it is going to hit low memory issues and likely have your SSRS paging to disk. I am willing to bet that is what is happening here.
When you say that you ahve 4 GB free, is that 4 GB free or 4 GB available? Cached memory is released on a as needed basis. Windows does some optimizations to try to have applications start faster and have faster file access by having things cached, but flushes that cache as needed.
If possible, I would recommend having SSRS and SQL Server on different physical machines, but as this requires a second license, most companies don't do that. If the SQL instance isn't all that busy, you could cut back on it's memory and ensure that SSRS has the full set it is requesting.
As for how much memory you need, it depends. That is always the answer for SQL things, eh? I'd give it as much memory as you can. SQL will use as much memory as you allow it and SSRS does the same. And for that matter, so does SSIS. I am not sure about SSAS, but it wouldn't surprise me.
If you have the budget for it, giving it all the memory you can. But try to make sure you are managing the memory not letting SQL just eat it all up and then windows needs to page it to disk. And be sure to keep some memory for the OS. You fill up your memory and you will have major performance issues.
one article I found about someone setting up SQL Server, SSRS, SSIS and SSAS all on one box with 24 GB of memroy:
http://blog.in2bi.com/microsoft-business-intelligence/sql-server-ssis-ssas-and-ssrs-on-one-server/
but YMMV... SQL is an interesting beast. Each case is unique. I'd recommend at LEAST 64 GB of memory so you have 28 GB for SQL Server and 28 GB for SSRS and 8 GB for Windows and windows services. But if you have users that log into the server on a regular basis, I'd try to have at least 4 GB extra per user.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
March 7, 2017 at 3:50 pm
Jim Youmans-439383 - Tuesday, March 7, 2017 3:04 PMLet me start by saying that I am new to SSRS. I inherited this and am trying to solve some weird issues I am seeing with it.The server is a VM with 32 GB RAM. The SQL version is SQL 2012 SP3 and the OS is Windows Server 2012 R2. SQL Server is set to use a MAX of 21 GB. So that should leave 11 GB for the SSRS and OS.
The issue is that after the server has been running awhile, it slows down and starts acting weird. Today it would not export a report to Excel about 3/4 ths of the time. After we restarted the SSRS service, it worked fine again. Checking the SQL error logs, it did not seem to the issue.
Looking at the rsreportserver.config file on the server I see this.
<MemorySafetyMargin>80</MemorySafetyMargin>
<MemoryThreshold>90</MemoryThreshold>
<WorkingSetMaximum>25165824</WorkingSetMaximum>So the way I read this is that the SSRS config file is setting the max working memory to 24 GB (I am assuming that it is set in kilobytes). According to BOL that means it will keep taking requests until that limit is hit, which is never since on 11 GB is left. Am I reading that correctly?
I have a process running that tracks memory usage for the server and it was down to 1,734 MB right before we restarted the SSRS service. Then it went backup up to about 4 GB free. My guess is that this is a memory issue and SSRS needs more memory, but I need to be sure I am right before I suggest adding memory to try to fix this. Normally how much memory does SSRS need to run smoothly? The environment is pretty report heavy with users and automatic reports running constantly. Can anyone recommend a good blog or book to help me wade through this?
Any help would be greatly appreciated. Thank you!!!
Jim
How much memory it needs...it depends of course.
But WorkingSetMaximum is the available memory on the computer when service starts.
MemorySafetyMargin and MemoryThreshold are both percentages of WorkingSetMaximum used to determine the boundaries for low, medium and high memory pressure.
You can monitor memory pressure notifications but only if you set the tracing to verbose - which is 4.
You may want to start there. Here is more information on the tracing file and it's settings:
Report Server Service Trace Log
It's not very straight forward to monitor potential memory issues. If you run out of memory, SSRS will die on you with the error
Failed allocate pages: FAIL_PAGE_ALLOCATION
But you probably don't want to get there. Monitoring the memory pressure with the trace file is probably the best way to start at this point since it will give you an idea of any components hitting memory pressure notifications.
There is also a pretty good article about troubleshooting memory issues with SSRS which also has a link to the memory configurations you can do with SSRS.
Troubleshooting Memory Issues with Reporting Services
Sue
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply