3

We have been receiving complaints from a particular SSRS user that her reports run slow. I investigated in the ExecutionLog table in the ReportServer database and I observed something strange.

Fairly consistently, the TimeDataRetrieval is much longer than other users. Both TimeProcessing and TimeRendering are near the averages of other users.

I am puzzled. The reports all use the same shared data source that runs as a service account. I would think if it was a crappy user computer issue, I would see differences in the rendering time. Same if there was a network issue.

I don't know where else to look - any ideas?

  • Does the report accept any input parameters from the user? If so, try two separate users supplying the exact same parameters. – datagod May 23 '12 at 15:41
  • No input parameters... good idea, though! – JHFB May 23 '12 at 16:10
  • 1
    Check the SET settings for her session (sys.dm_exec_sessions). I wonder if she has different SET ARITHABORT or other settings compared to the other users, in which case she might be getting a different plan for the same underlying query. – Aaron Bertrand May 23 '12 at 16:28
  • 1
    Try logging in to her workstation under a different user - if it is still slow you know it has nothing to do with the user and is probably workstation/network specific. – Jimbo May 23 '12 at 16:30
  • 1
    @JHFB - If you came to the conclusion that ARITHABORT itself was to blame then yes. The issue was likely parameter sniffing. The reason why ARITHABORT appeared to fix it was because changing the value of that means you get a different cached plan that was compiled with different parameter values. – Martin Smith Jun 20 '12 at 11:17
5

Converted to answer, per OP.

Check the SET settings for her session (sys.dm_exec_sessions). I wonder if she has different SET ARITHABORT or other settings compared to the other users, in which case she might be getting a different plan for the same underlying query.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.