March 28, 2017 at 10:24 am
Hello,
We use SSRS to send a large number of reports via email using data-driven subscriptions. Each subscription is triggered for one client at a time when the data processing that populates the back end is complete. Our processing often completes for many clients at around the same time, so we end up with a few dozen subscriptions sending a few thousand emails all being processed at once, and it can take 30 minutes or more for things to catch up. There don't appear to be performance bottlenecks on the server (a window sserver 2012 VM with 2vCPUs and 16GB of RAM).
What I see in the ExecutionLog views are the "TimeStart" field itself is delayed in relation to the time that the SQL Agent job for the schedule in question is fired. We have an internal report which is supposed to go out every 30 minutes. What I find is that when external reports are being processed, the "TimeStart" entry for this internal report is delayed. The 8:30 execution of the sql agent job that calls the schedule occurs at 8:30 but the TimeStart for that execution was 8:35. The 9am execution of the sql agent job was complete at 9 but the TimeStart for the execution was 9:36...
Can subscription processing be done in parallel? Is there some other way around this?
Thanks
March 28, 2017 at 11:14 am
I am fairly confident that subscription processing is done in parallel. We have a lot of report subscriptions that occur in parallel.
Is the 16 GB of RAM for SSRS, the back end SQL Server for SSRS or both?
SSRS operates in separate memory space as SQL Server, so if you allocate 14 GB of that memory to SQL Server (for example), that leaves 2 GB for SSRS AND windows and any other tools you have running on that box such as antivirus.
If this ONLY hosts SSRS, then 16 GB should be plenty.
I would look at the SQL Server instance and look at what time the SQL Agent Job started compared to the time that the execution of the report started. The Agent Job tells SQL to start the SSRS job and SSRS will process jobs as it has resources.
When you said that it didn't appear to be a performance bottleneck, is that on the SSRS SQL Server, the SSRS Server or the data SQL Server (ie where you get the data for the report)? Any of those can cause delays in report processing. If the SSRS SQL Server is busy/slow, it might take longer to process that it should be starting the job. If the SSRS Server is slow, it can take a while to process the report before sending it out. If the data SQL Server is slow, it could be waiting for a connection to get the data so it can start processing the report.
I would be checking memory pressure on all of the pieces of this puzzle and looking at both the SQL logs (on both of the instances) and the windows logs (on all 3, presuming there is 3).
16 GB of memory is not really a lot for SQL Server and SSRS. If you have SQL Server configured to use all of the memory, you will have issues with SSRS (presuming they are all on the same box).
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 28, 2017 at 11:26 am
thanks for the reply
reporting services back-end and reporting services front-end are on the same server. reporting services sql server is configured for 10GB memory max (but it hasn't gone over 4GB of actual usage). Reporting services service doesn't use more than 3GB of RAM at any one point. It seems to bounce around between 1 and 3 GB usage (it's 64-bit). there is no memory pressure as far as i can tell--the machine is pretty consistently claiming that it's using 8GB of RAM. the heavy-lifting data processing is all done on a separate physical machine.
The SQL Agent job executes (and finishes) on time but the execution log views show the TimeStart much later than the sql job which triggers the underlying schedule.
March 28, 2017 at 11:38 am
agerard 65681 - Tuesday, March 28, 2017 11:26 AMthanks for the replyreporting services back-end and reporting services front-end are on the same server. reporting services sql server is configured for 10GB memory max (but it hasn't gone over 4GB of actual usage). Reporting services service doesn't use more than 3GB of RAM at any one point. It seems to bounce around between 1 and 3 GB usage (it's 64-bit). there is no memory pressure as far as i can tell--the machine is pretty consistently claiming that it's using 8GB of RAM. the heavy-lifting data processing is all done on a separate physical machine.
The SQL Agent job executes (and finishes) on time but the execution log views show the TimeStart much later than the sql job which triggers the underlying schedule.
I'm fairly certain that BMG002 is correct about it already running in parallel. One possibility of what you may be hitting, especially with what you are seeing with the job firing and the subscription processing later, is that the jobs put the subscriptions in the event table, the report server polls that table regularly but there is a limit of 2 threads per core that poll the the table and process subscriptions. The calculation for the thread number is set in the RsReportServer.config file MaxQueueThreads option which has a default of 0 - use SQL Servers calculation. That number can be changed but it can also have adverse affects, particularly on the memory usage, so you'd want to test the changes.
Sue
March 28, 2017 at 11:53 am
interesting, thanks Sue!
March 28, 2017 at 12:01 pm
Another thing to watch for is Windows "Available" memory vs "Free" memory. Available memory is Free + Cached and Cached doesn't get cleaned up all that quickly unless there is memory pressure and I've had my desktop workstation complain it is out of memory due to it having less than 1 GB of "Free" memory but having 10 GB of cached. If your system is reporting 8 GB of FREE memory, I wouldn't be overly concerned.
But you say you have things configured so that 13GB out of 16GB. I had thought that 4 GB of OS space was the recommended. Might want to try dropping your SQL Instance down to 8 or 9 GB and see if it helps.
Also, check out the view "dbo.ExecutionLog2" for your reports. I see that identical reports are running in parallel on our systems. You can filter that by RequestType to just get subscriptions and you can see the start and end times as well as time for data retrieval, processing and rendering.
I see that subscriptions we have set for 5:00 AM actually start at 5:00:03.807 AM. For us, a 3 second delay isn't a concern. A 5 minute delay though seems rather large. Do your windows event logs tell you anything interesting? If this is consistantly delayed start, might not hurt to watch perfmon on that server while things are running. This will add extra overhead, but not a lot and will give you a better idea on if disk, network, memory, or CPU is being pressured.
Also, you indicated that these are VM's, do your VM infrastructure guys notice anything strange during these times? Like is vmWare reclaiming any unused memory or doing any balooning or CPU resource throttling or anything that would cause slowdown on the VM during the report execution time?
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 28, 2017 at 12:06 pm
Edit:
I knew I had these somewhere just found a couple of the posts. Goes into details of some of what you are seeing:
Limiting number of simultaneously running jobs
Troubleshooting Subscriptions: Part II, Using the Reporting Services Trace Log File
Sue
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply