Report Manager Odd Behavior

• jminton

  Valued Member

  Points: 68

  More actions

  March 4, 2015 at 2:28 pm

  #315166

  Hey folks, thanks in advance for any help with this puzzler. Let me lay out the scenario for you:

  - ReportBig is built in SSRS and is deployed to both ReportManagerProduction and ReportManagerDevelopment.

  - ReportBig runs two subreports.

  - ReportBig and the two subreports call a combined 6 stored procedures.

  - I have a ProductionServer and a DevelopmentServer. Both are 2012 SP1.

  - When I execute the 6 stored procedures in either ProductionServer or DevelopmentServer via SSMS they run fast (0 secs).

  - When I run ReportBig in SSRS while linked to either ProductionServer or DevelopmentServer, it renders quickly (3-4 secs).

  - When I run ReportBig from ReportManagerDevelopment, it runs fast (3-4 secs).

  - BUT, when I run ReportBig from ReportManagerProduction, it runs agonizingly slow (over 1 minute).

  I don't think it's parameter sniffing. I've done some tests and that doesn't seem to be it.

  I don't think it's an issue of locking. The report hits a static data warehouse that is only loaded/edited each night at 2 am.

  I don't think it's multiple users hitting the same report at the same time.

  Our DBA ran a trace and noticed that in each Server, the report creates and executes two odd-to-me stored procedures [dbo].[WriteLockSession]

  and [dbo].[CreateChunkSegment]. It seems that these two are bigger resource hogs on ProductionServer. I've researched these a little online, and am not sure that they're related to this issue, but I thought I'd throw it out there just in case it might ring a bell for someone.

  Any suggestions on where to look would be greatly appreciated.

  Thanks.

  JC