Friday, September 26, 2014

Prevent SQL Server Reporting Services Slow Startup


Problem: 

I've installed a SQL Reporting server 2012, with some reports. But I've some performances issues. 

The first call of the day to the server(report is access in MVC application), is VERY slow(something like 20-35 seconds at best), The report generation is then fast(1-2 seconds). I've the impression that it loads alll supporting dlls in the memory on first call the use cached dlls for rest of calls. But what can takes 20-35 seconds to be loaded in memory? And how to load it only once? 

These reports will be launched along with web application and will be accessed frequently, so they will always be slow on first load if I can't change this. And since it's available for customer, I just can't make them understand this(and the report is called through a website, so I risk to have timeout).

Solution:

I have solved the problem by Using the following articles as guidance:
  • Technet – “RSReportServer Configuration File” – http://technet.microsoft.com/en-us/library/ms157273.aspx
  • MSSQL Tips – “Prevent SQL Server Reporting Services Slow Startup” –http://www.mssqltips.com/sqlservertip/2735/prevent-sql-server-reporting-services-slow-startup/

I Scheduled a SQL Server job that will handle a report call periodically (for example every morning before everyone rushes to the office like I schedule it for 6:00 AM), for this I have written a powershell script which will warm up MS reporting server.

# URL to your SSRS. As example localhost is used.
[string] $url = "http://localhost/ReportServer";
$httpRequest = [System.Net.HttpWebRequest]::Create($url) ;
$httpRequest.Credentials =[System.Net.CredentialCache]::DefaultCredentials;
$httpRequest.Method = "GET";
# Time out has been set to 5 minutes to make sure the call is properly completed
$httpRequest.Timeout = 300000;
$objResponseReader = [System.IO.StreamReader]($httpRequest.GetResponse().GetResponseStream());
$objResponseReader.Dispose();

I Scheduled this job for daily at 6:00 in the morning.

No comments:

Post a Comment

Popular Posts