SQL 2016 SSRS Native Mode - Report Loading Time

  • Joseph M. Steinbrunner

    Mr or Mrs. 500

    Points: 503

    We are running SQL 2016 SSRS in native mode using the web portal. If a user is part of the local administrators group (windows) and runs a report they have permission to, the report works correctly and renders within an expected period of time (in most cases, 3 seconds). If the user is NOT a member of the local administrators group (windows) and runs the same report, it will eventually run, but it literally is "Loading..." for 10 minutes or longer. I kid you not. If I add that user to local admin, it takes 3 seconds. Remove the user from local admin, it goes back to 10 minutes or longer. Any suggestions? I've burned 2 days on this bad boy and I need to get this server into production.

    Things I have tried:

    Adding the windows user as a sysadmin on the SQL instance that has the ReportServer databases on it. That didn't help.

    I also looked at the ExecutionLog3 view and the TimeStart and TimeEnd are match when the report actually does show up. So, as an example, I launch the report at 3:14 and it finally works at 3:24ish and the execution log shows a TimeStart of 15:23:39.020 and TimeEnd of 15:23:39.260.

    Any assistance is appreciated.

  • Sue_H

    SSC Guru

    Points: 90695

    Joseph M. Steinbrunner - Thursday, September 6, 2018 1:51 PM

    We are running SQL 2016 SSRS in native mode using the web portal. If a user is part of the local administrators group (windows) and runs a report they have permission to, the report works correctly and renders within an expected period of time (in most cases, 3 seconds). If the user is NOT a member of the local administrators group (windows) and runs the same report, it will eventually run, but it literally is "Loading..." for 10 minutes or longer. I kid you not. If I add that user to local admin, it takes 3 seconds. Remove the user from local admin, it goes back to 10 minutes or longer. Any suggestions? I've burned 2 days on this bad boy and I need to get this server into production.

    Things I have tried:

    Adding the windows user as a sysadmin on the SQL instance that has the ReportServer databases on it. That didn't help.

    I also looked at the ExecutionLog3 view and the TimeStart and TimeEnd are match when the report actually does show up. So, as an example, I launch the report at 3:14 and it finally works at 3:24ish and the execution log shows a TimeStart of 15:23:39.020 and TimeEnd of 15:23:39.260.

    Any assistance is appreciated.

    I've seen something similar and the delay was on the client side which makes sense if you look at the times in the Execution log view. But it was never as long as 10 mins. I only remember one being due to enterprise encryption and permission checks...if you have anything similar in your domain.
    You might need to run a network trace to see if you can find something affecting the traffic between the client and the report server portal.
    You may want to test if it makes a difference if the user runs the browser doing a Run As Administrator. Or if a different browser makes a difference. And check the event logs on the client PC.

    Sue

  • Joseph M. Steinbrunner

    Mr or Mrs. 500

    Points: 503

    Sue_H - Thursday, September 6, 2018 2:14 PM

    Joseph M. Steinbrunner - Thursday, September 6, 2018 1:51 PM

    We are running SQL 2016 SSRS in native mode using the web portal. If a user is part of the local administrators group (windows) and runs a report they have permission to, the report works correctly and renders within an expected period of time (in most cases, 3 seconds). If the user is NOT a member of the local administrators group (windows) and runs the same report, it will eventually run, but it literally is "Loading..." for 10 minutes or longer. I kid you not. If I add that user to local admin, it takes 3 seconds. Remove the user from local admin, it goes back to 10 minutes or longer. Any suggestions? I've burned 2 days on this bad boy and I need to get this server into production.

    Things I have tried:

    Adding the windows user as a sysadmin on the SQL instance that has the ReportServer databases on it. That didn't help.

    I also looked at the ExecutionLog3 view and the TimeStart and TimeEnd are match when the report actually does show up. So, as an example, I launch the report at 3:14 and it finally works at 3:24ish and the execution log shows a TimeStart of 15:23:39.020 and TimeEnd of 15:23:39.260.

    Any assistance is appreciated.

    I've seen something similar and the delay was on the client side which makes sense if you look at the times in the Execution log view. But it was never as long as 10 mins. I only remember one being due to enterprise encryption and permission checks...if you have anything similar in your domain.
    You might need to run a network trace to see if you can find something affecting the traffic between the client and the report server portal.
    You may want to test if it makes a difference if the user runs the browser doing a Run As Administrator. Or if a different browser makes a difference. And check the event logs on the client PC.

    Sue

    Thanks for the quick response.  I need to clarify a point.  When I say add the user to the local windows Administrator's group, I'm actually talking about the SSRS server, not the client.  So I make the user a local admin on the server and it works fine.  Local admin on the client does not impact it one way or the other.  I'll see if the event logs on the server have anything to say but so far the logging hasn't seem to be helpful.

  • Sue_H

    SSC Guru

    Points: 90695

    Joseph M. Steinbrunner - Thursday, September 6, 2018 2:25 PM

    Sue_H - Thursday, September 6, 2018 2:14 PM

    Joseph M. Steinbrunner - Thursday, September 6, 2018 1:51 PM

    We are running SQL 2016 SSRS in native mode using the web portal. If a user is part of the local administrators group (windows) and runs a report they have permission to, the report works correctly and renders within an expected period of time (in most cases, 3 seconds). If the user is NOT a member of the local administrators group (windows) and runs the same report, it will eventually run, but it literally is "Loading..." for 10 minutes or longer. I kid you not. If I add that user to local admin, it takes 3 seconds. Remove the user from local admin, it goes back to 10 minutes or longer. Any suggestions? I've burned 2 days on this bad boy and I need to get this server into production.

    Things I have tried:

    Adding the windows user as a sysadmin on the SQL instance that has the ReportServer databases on it. That didn't help.

    I also looked at the ExecutionLog3 view and the TimeStart and TimeEnd are match when the report actually does show up. So, as an example, I launch the report at 3:14 and it finally works at 3:24ish and the execution log shows a TimeStart of 15:23:39.020 and TimeEnd of 15:23:39.260.

    Any assistance is appreciated.

    I've seen something similar and the delay was on the client side which makes sense if you look at the times in the Execution log view. But it was never as long as 10 mins. I only remember one being due to enterprise encryption and permission checks...if you have anything similar in your domain.
    You might need to run a network trace to see if you can find something affecting the traffic between the client and the report server portal.
    You may want to test if it makes a difference if the user runs the browser doing a Run As Administrator. Or if a different browser makes a difference. And check the event logs on the client PC.

    Sue

    Thanks for the quick response.  I need to clarify a point.  When I say add the user to the local windows Administrator's group, I'm actually talking about the SSRS server, not the client.  So I make the user a local admin on the server and it works fine.  Local admin on the client does not impact it one way or the other.  I'll see if the event logs on the server have anything to say but so far the logging hasn't seem to be helpful.

    Thanks...that's different than what I was thinking. But still worth checking the event logs as having the user on that server is what makes a difference. 
    If you are used to using Process Monitor, it might be worth it running on the report server to see if it picks up any errors or permissions issues when the user is not in the local admins. Did you already check the reporting services logs? And another thought...are these reports accessing any external resources such as images?

    Sue

  • Joseph M. Steinbrunner

    Mr or Mrs. 500

    Points: 503

    Sue_H - Thursday, September 6, 2018 2:44 PM

    Thanks...that's different than what I was thinking. But still worth checking the event logs as having the user on that server is what makes a difference. 
    If you are used to using Process Monitor, it might be worth it running on the report server to see if it picks up any errors or permissions issues when the user is not in the local admins. Did you already check the reporting services logs? And another thought...are these reports accessing any external resources such as images?

    Sue

    Thanks for the reply.  The event logs unfortunately do not show anything.  The Reporting Services logs have the following below.  I know the threads are reused so I don't know if the rendering is done on the same thread as the render for new session, but the timing shown there seems to be fairly accurate.

    library!ReportServer_0-1!858!09/06/2018-15:53:22:: i INFO: Call to GetItemTypeAction(/Accounting/_Test). User: xxxxxx\testuser.
    library!ReportServer_0-1!858!09/06/2018-15:53:22:: i INFO: RenderForNewSession('/Accounting/_Test')
    library!ReportServer_0-1!858!09/06/2018-16:00:56:: i INFO: Entering StreamRequestHandler.ExecuteCommand - Command = Render
    library!ReportServer_0-1!858!09/06/2018-16:00:57:: i INFO: Exiting StreamRequestHandler.ExecuteCommand - Command = Render (success)
    library!ReportServer_0-1!2510!09/06/2018-16:32:03:: i INFO: Call to GetItemTypeAction(/Accounting/_Test). User: xxxxxx\testuser.
    library!ReportServer_0-1!2510!09/06/2018-16:32:03:: i INFO: RenderForNewSession('/Accounting/_Test')
    library!ReportServer_0-1!2510!09/06/2018-16:41:28:: i INFO: Entering StreamRequestHandler.ExecuteCommand - Command = Render
    library!ReportServer_0-1!2510!09/06/2018-16:41:29:: i INFO: Exiting StreamRequestHandler.ExecuteCommand - Command = Render (success)


    Unfortunately I do not have experience with Process Monitor.  Forgive my ignorance; is that a SQL tool, windows server tool ?

  • Joseph M. Steinbrunner

    Mr or Mrs. 500

    Points: 503

    I should also point out another strange thing.  We have a .NET application with the report viewer in it, so we execute the reports via the ReportServer API and pull it back into the application.  The users can execute through that (as themselves, not a proxy account) and the reports return quickly.  It is the web portal execution that seems defunct.

  • Sue_H

    SSC Guru

    Points: 90695

    Joseph M. Steinbrunner - Thursday, September 6, 2018 3:08 PM

    I should also point out another strange thing.  We have a .NET application with the report viewer in it, so we execute the reports via the ReportServer API and pull it back into the application.  The users can execute through that (as themselves, not a proxy account) and the reports return quickly.  It is the web portal execution that seems defunct.

    Process monitor is one of the tools with sysinternals.that Microsoft bought out years ago. It's a set of great tools that dig into various things. 
    Process monitor can capture activity at the file system level, registry, network activity, threads, etc. You can filter in various ways (process, path, duration, user, etc). It's incredibly useful for permissions issues. You might want to give it a try: 
    Process Monitor v3.50

    You may want to see if you can find the same call to the report from the report view application and compare it to the call interactively from the portal. There really shouldn't be significant differences as the portal uses the same API. But the calls are being fired from different locations.

    Sue

  • Joseph M. Steinbrunner

    Mr or Mrs. 500

    Points: 503

    Thanks for your continued assistance.  I have tried process monitor and there is a lot of data in there!  It actually helped me with a different issue, but in this case I'm having a difficult time tracking this issue down in it.  I can confirm that the report execution via API and the Web Portal both show < 1 second data loading, rendering, and processing time.  The issue appears specifically with the Native Mode Web Portal in calling the report and then displaying it after it is returned.  It takes about 5 minutes to call it showing a "Loading..." screen.  Once it hits the ReportServer database, it creates an execution log record and then changes to "Loading..." with a cancel option and takes another 5 minutes before displaying.

    I'm working with my team to contact Microsoft Support at this point, since I don't know where the web portal code and files are stored, how they are run, and how it works in general.  If you or anyone else happens to have insight on this side of SSRS, thoughts would be appreciated.

    Again, thanks for your help.

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply