February 23, 2021 at 2:49 am
Report Server URL is used within the application. The SP used within the report when executed from SSMS returns data in about 10-20 seconds, also it returns data in about the same time when the report is called within the application when accessed from the application server.But, when the application is accessed from the desktop the is generating reports very slowly or for bigger reports not completing. (Large reports use pagination.)
I checked the execution log and found that sometimes the byte count is 166MB for about 100 thousand row count.
Could this be because of parameter sniffing? I am thinking of running profiler to see what is taking time.
What other could be causing this issue? And what would be approach to look into this issue.
Thanks in advance.
February 23, 2021 at 7:20 pm
I would expect that to be network related or memory related (experts correct me if I am way out to left field here).
If the network connection on the server is a Gb connection (1000 Mbps), the report is 166 MB. Different units, but lets pretend they are the same for simplicity sake (MB and Mb are not the same thing). that report is using 10% of the bandwidth to send the data to the end user. Plus using 10% of the bandwidth to pull the data to the server prior to processing it and sending it to the end user. Now, lets say you have 100 users on the server all pulling data at the same time, with 10 of them being users pulling that specific report. That will fill up the network bandwidth pretty quickly.
Rendering it locally (within the application) uses half the bandwidth as you pull data from SQL to local and then do all the other processing local.
Now, before jumping on the "network is slow" problem, I would investigate it and determine if it is the cause. Check perfmon/task manager on the server while you are loading the report. Does the network get saturated? If it does, you need to reduce the data set for the report or learn to live with it being slow. If it isn't saturated, check out the CPU, disk, memory, etc.
It could also be that the application server has more memory than the SSRS server and the SSRS server can't handle the load from that report and it is paging to disk.
I would also look to see if you can improve that SQL query. 10-20 seconds sounds like a long time to get the data, especially for only 100,000 rows. Now, 100,000 rows at 166MB 10-20 seconds doesn't sound horrible, but also sounds like it MAY have room for improvement.
I would first try to decide which problem you want to solve first. SSRS being slow, SQL query taking 10-20 seconds, 166 MB of data being pulled for the report. To me it doesn't sound like parameter sniffing UNLESS you are calling a stored procedure for the data AND you are using different parameters on SSRS and from the application/SSMS. If the parameters are the same, then it is (likely) not parameter sniffing.
As another thought, it could also be blocking. If it is sometimes slow from SSRS and sometimes quick, I would guess that blocking is a bigger problem than anything else. If it is consistently slow in SSRS, then it is sounding like either too much data or some resource constraints.
Just my 2 cents.
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.
February 23, 2021 at 9:03 pm
Thanks for the reply.
Just to be clear, same report\parameter is being used. When the application is launched from the app server and report is called it is said to be faster but the same report is slow when app is launched on the desktop and report is being called.
Will check to see if there is any resource contention both on the database server where it is pulling data from and on the report server where the report resides.
February 24, 2021 at 8:50 am
Longshot: Report is pulling the resultset row-by-row, rather than "bulk"
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply