deployed SSRS report is slow, dev report is fast

• Sergei Zarembo

  Hall of Fame

  Points: 3887

  More actions

  August 9, 2012 at 12:20 pm

  #280062

  platform: SQL Server 2008 R2 Enterp Ed with an SSRS instance installed on the same 2-box Windows Server 2008 cluster where SQL Server instance is set.

  issue: an SSRS report runs to completion in 10 min when run in Visual Studio in development mode.

  Same report when deployed never finishes when run adhoc in Report Manager. In both cases, same set of parameters is used.

  param1 varchar(max): @company_list, comma-delimited, length 86,000 bytes, 1500 company numbers

  param2: start_date

  param3: end_date

  I checked and this long param1 is actualy being passed OK to the underlying stored procedure. So varchar(max) is working OK in this regard.

  It is just the deployed report never finishes. SSRS log says that *processing aborted* after 40 min of running.

  this is from SSRS log for a failed session of a deployed report:

  <AdditionalInfo>

  <ProcessingEngine>2</ProcessingEngine>

  <ScalabilityTime>

  <Processing>0</Processing>

  </ScalabilityTime>

  <EstimatedMemoryUsageKB>

  <Processing>624745</Processing>

  </EstimatedMemoryUsageKB>

  <DataExtension>

  <SQL>1</SQL>

  </DataExtension>

  </AdditionalInfo>

  Any ideas why the deployed dreport could behave so diffrently from the report run in Visual Studio on a workstation?

  Thanks for your time.

• rVadim

  Hall of Fame

  Points: 3969

  More actions

  August 10, 2012 at 2:24 pm

  #1524666

  Just a few gueses/ideas:

  When you run it in VS it probably renders on your local machine. Deployed report rendered on server. If i'm not mistaken SSRS service runs outside of SQL, so on busy server with most memory allocated to SQL it may have memory shortage.

  What SSRS log shows in Time Data retrival, Rendering, Processing? Check those it may give you idea where it spends most time.

  What if you narrow dataset (send just few IDs)? Will it finish?

  --Vadim R.

• Sergei Zarembo

  Hall of Fame

  Points: 3887

  More actions

  August 14, 2012 at 1:51 pm

  #1525773

  Thank you for your response, rVadim. I do have some contention on the box that runs SSRS. Some huge QlikView application lives there as well. I need to think of the ways to move that QlikView off the box.

  Sergei