After a timespan of inactivity (e.g. over night) SSRS "falls asleep"; the first request after this will be extremly slow, up to several minutes.
This PowerShell script performs a simple HTTP request to the Report Manager url of SSRS, to simulate a normal activity.
If you run it frequently you avoid the SSRS worker shutdown and a slow access on the next morning.
There are 3 scripts available in the attachments:
 
SsrsWakeUpCall.ps1:
   A PoSh script with exception handling and information printout to test the script in a PoSh console.
 
SsrsWakeUpCall_Simplified.ps1
   A PoSh script without exception handling / output. This script could e.g. used in Windows Task Schedule to execute it frequently.
 
SsrsWakeUpCall_SQLServerAgentJob.sql
   A Transact-SQL to create a scheduled SQL Server-Agent job that frequently executes the PoSh script "SsrsWakeUpCall_Simplified.ps1".
Works with SQL Server 2008 and higher version; SQL Server 2005 don't support agent subsystem "Powershell".
Requires Standard Edition or higher; the Express Edition is not supported (no SQL Server-Agent).
Requires SysAdmin permission or membership in all SQLAgent... roles in msdb database to create the job.
Please modify the "sp_add_jobstep" part in the T-SQL script below:
@proxy_name:
The PoSh scripts uses the "current" Windows account (UseDefaultCredentials) to logon to SSRS. Therefore the job should be executed with a Windows account which do have permissions to logon to SSRS. For this create a "Credential" and a SQL Server-Agent proxy account with permission for subsystem "PowerShell". If you don't want to use a proxy account, e.g. because the Agent runs under a valid Windows Account with permissions for SSRS then simply remove the parameter.
 
PoSh script variable $url:
Modify it to the url of your Report Manager url.
Links:
  SQL Server Credential:  http://msdn.microsoft.com/en-us/library/ms188067.aspx
  SQL Server-Agent Proxy: http://msdn.microsoft.com/en-us/library/ms183491.aspx
PowerShell
Skript bearbeiten|Remove
<# 
  .SYNOPSIS 
    SSRS Wake-Up Call 
#> 
 
# Root url of SSRS Report Manager 
# Example: 
#   http://localhost/Reports               for a local default SSRS instance 
#   http://servername/Reports_SQLEXPRESS   for a remote named SSRS instance "SQLEXPRESS" 
[string] $url = "http://localhost/Reports"; 
 
# Create new WebClient. 
[System.Net.WebClient] $wc = New-Object System.Net.WebClient; 
# Use current account for authorization. 
$wc.UseDefaultCredentials = $true; 
 
try 
{ 
    # Download start page. 
    [string] $result = $wc.DownloadString($url); 
    # Just to check the response: 
    [int] $pos = $result.IndexOf("META Name=""Report Server"""); 
    if ($pos -gt -1) 
    { 
        Write-Output "Response is ok."; 
    } 
    else 
    { 
        Write-Output "Got response, but not as expected."; 
    } 
} 
catch 
{ 
    Write-Verbose $_.Exception.Message; 
} 
 
$wc.Dispose();
 
SQL
Skript bearbeiten|Remove
USE [msdbGO 
 
DECLARE @jobname sysnameSET @jobname = N'Ssrs Wake-Up Call Job';  -- Jobname to reference in all create parts. 
 
-- Create SQL Server-Agent Job 
EXEC  msdb.dbo.sp_add_job 
        @job_name = @jobname, 
        @description = N'Frequently Ssrs Wake-Up Call with a PowerShell Script.', 
        @category_name = N'Report Server'; 
 
-- Define target SQL Server = LOCAL for the job. 
EXEC msdb.dbo.sp_add_jobserver 
        @job_name = @jobname, 
        @server_name = N'(LOCAL)'; 
 
-- Add a job step with the PowerShell script. 
-- Modify the @proxy_name and Report Server url here. 
EXEC msdb.dbo.sp_add_jobstep 
        @job_name  = @jobname, 
        @step_name = N'Ssrs Wake-Up Call Step', 
        @subsystem = N'PowerShell', 
        @proxy_nameN'PowerShellProxy', 
        @commandN'[string$url = "http://localhost/Reports"; 
 
[System.Net.WebClient$wc = New-Object System.Net.WebClient$wc.UseDefaultCredentials = $true$result = $wc.DownloadString($url); 
$wc.Dispose();'; 
 
-- The job should start with step 1 
EXEC msdb.dbo.sp_update_job 
        @job_name = @jobname, 
        @start_step_id = 1; 
 
-- Define a schedule for the job. 
-- Every day hourly. 
EXEC msdb.dbo.sp_add_jobschedule 
        @job_name = @jobname, 
        @name = N'Ssrs Wake-Up Call Schedule', 
        @freq_type = 4, 
        @freq_interval = 1, 
        @freq_subday_type = 8, 
        @freq_subday_interval = 1, 
        @freq_relative_interval = 0, 
        @freq_recurrence_factor = 1, 
        @active_start_date = 19900101, 
        @active_end_date = 99991231, 
        @active_start_time = 0, 
        @active_end_time = 235959