Hoe ververs je de cache van Reporting Services?

Ik werk voor een klant met een datawarehouse waarin de data één keer per dag ’s nachts wordt geladen. De rest van de dag verandert er niets in het datawarehouse. Na een paar jaar blijft de database maar groeien en worden rapporten steeds trager. Een ander probleem is dat ze Reporting Services als front-end tool gebruiken, met een paar rapporten die erg slecht presteren, ook na het tunen van indexen en queries. Dit artikel geeft je een methode om met caching de performanceproblemen op te lossen en je Reporting Services-rapport 100% sneller te maken!

We gebruiken een aantal zware rapporten in Microsoft Reporting Services om data aan de business users te tonen, en ze cachen is dan echt een uitkomst. Caching betekent dat de server een tijdelijke “snapshot” van het rapport in zijn cache bewaart, zodat het meteen verschijnt! De performancewinst is groot, maar er is één probleem. Ik weet niet precies wanneer de data “klaar is voor gebruik”, en ik moet de gecachte rapporten dynamisch verversen. Met dynamisch bedoel ik: op basis van parameters. Ik moet ook eerst de SSRS-cache opschonen.

In Microsoft Reporting Services zijn er twee opties om rapporten vooraf te laden: caching en snapshots. Een snapshot is als een vel papier, een statische versie van het rapport. Na het maken van de snapshot kun je niets meer aan de parameters veranderen. Caching werkt anders. Het kan verschillende “versies” van het rapport bewaren (hetzelfde rapport met verschillende parameterinstellingen) en die in TempDB opslaan tot ze verlopen. Een gecachte versie wordt uit de cache verwijderd als hij verloopt. Je kunt dingen op geen enkele manier “opnieuw cachen” (alleen met een geavanceerd cache refresh plan, dat valt buiten dit artikel). Als je de caching opnieuw draait, zoekt SSRS de versie in de database, ziet dat die al in de cache staat en laat de oude resultaten in de database staan. Dit doet SSRS op basis van de opgegeven parameters (fingerprint). Dit is heel belangrijk om te begrijpen. Een rapport blijft niet eeuwig in de cache. Het wordt verwijderd als het rapport is ingesteld om te verlopen, als het rapport wordt aangepast, als de parameters worden aangepast, als de credentials van de databron veranderen, als er andere uitvoeropties veranderen of als je het rapport verwijdert.

Houd bij Null Delivery dus in gedachten dat de kopie van het rapport in de cache verlopen moet zijn voordat je met deze methode een nieuwe kopie maakt. Ik raad aan de rapportcache te laten verlopen op een vast schedule, kort voordat deze subscription draait.

Er zijn twee methoden om de data van de cache of een snapshot op te halen (niet verversen, dat is een verkeerde term): op een vast tijdstip OF met een shared schedule. Zoals je ziet werkt geen van deze opties voor mij. Daarom heb ik wat beter gekeken welke opties ik heb om de cache handmatig te verversen.

Concept: we gebruiken twee shared schedules. Dit zijn dummy shared schedules. Aan deze shared schedules koppelen we de rapporten die we willen cachen/laten verlopen. De eerste gebruiken we om het verlopen van de rapportcache te starten. De tweede gebruiken we om de resultaten te cachen. Aan het eind van de load van ons datawarehouse starten we de shared schedules op een bepaald moment. Alle caching van de gekoppelde rapporten wordt verwijderd en opnieuw gevuld!

Ik neem je mee door de stappen om dit voor elkaar te krijgen!

Stap 1 - Maak twee dummy shared schedules
Je hebt de juiste rechten in SSRS manager nodig om de knop “Site Settings” boven in Report Manager te krijgen:

Klik op Site Settings, Schedules, New Schedule. Geef het een duidelijke naam, zoiets als

“DWH_SharedSchedule_ForCaching_DoNotChangeOrDelete” “DWH_SharedSchedule_ForExpireCaching_DoNotChangeOrDelete”

Hiermee activeren we de caching en het opschonen van de cache voor alle rapporten.

Je kunt “Run Once” niet instellen met een tijdstip in het verleden, anders werkt het nooit. Dan verloopt de subscription meteen nadat de caching heeft gedraaid! Ik ontdekte dit omdat mijn caching niet werkte. Het maakt dubbele cacherecords in de database met dezelfde hashwaarde, allemaal verlopen. Mijn conclusie is dus dat SSRS het Shared Schedule controleert dat aan de “expire value” hangt, om te zien wanneer de cache moet verlopen. Is dit shared schedule al verlopen, dan verloopt de cache meteen zodra hij in de tabel is opgeslagen. De datum is dan 1970-01-01!

1
SELECT C.Name, EC.*
2
FROM ReportServerTempDB.dbo.ExecutionCache AS EC
3
INNER JOIN ReportServer.dbo.Catalog AS C ON EC.ReportID = C.ItemID

Belangrijk

Geef het Shared Schedule een geldige waarde. Verlopen shared schedules werken NIET!!. Maak er bijvoorbeeld een met een startdatum van gisteren en laat hem één keer per jaar draaien, op 31 december.

De Next run ligt in de toekomst, het schedule ook, en de kolom expired is leeg! Controleer dit goed!

SSRS maakt voor dit schedule een agent job aan. Je ziet deze job in de Agent. De planning van deze job staat op het tijdstip dat je hebt ingesteld. Je kunt ook de step bekijken die SSRS maakt! Voor nu verwijderen we deze job. Hij vervuilt je agent jobs en we starten de job niet via de agent. We starten de job in SSIS, aan het eind van ons ETL-proces.

Stap 2 - Stel een NULL Delivery Subscription in

Ga (via Report Manager) naar het SSRS-rapport dat slecht presteert. Dit rapport stellen we in voor caching via een NULL Delivery subscription.

Klik op het tabblad “Subscription” en maak een nieuwe data driven subscription van het type “Null Delivery Provider”.

Je hebt een SQL Server Enterprise-licentie nodig voor deze functie. Staat de knop er niet, dan heb je waarschijnlijk de Standard Edition.

Let op: je moet het wachtwoord elke keer invullen als je de subscription aanpast.

Zoals je ziet gebruik ik een view in de database. In deze view genereer ik alle parametercombinaties waarvoor ik het rapport wil cachen. Dat kan één regel zijn of heel veel regels. Voorbeeld:

Klik op Validate om te controleren of alles goed is. Klik op next, next.

Koppel nu elke parameter uit de view of het SQL-commando aan de rapportparameter. Elke combinatie wordt gecachet. Elke keer dat je deze subscription aanpast, moet je het wachtwoord van de connectie invullen.

In de laatste stap koppelen we deze subscription aan het dummy shared schedule:

Stap 3 - Configureer de caching

Ga terug naar het scherm “Manage Report” en kies links “Processing Options”. Kies:

Cache a temporary copy of the report. Expire copy of report on the following schedule:Shared schedule -> het schedule dat we in de eerste stap hebben gemaakt, het expire shared schedule.

Stap 4 - De Shared Schedules draaien

We hebben nu een Data Driven Subscription van het type “NULL Delivery” ingesteld. Er wordt niets per mail verstuurd, het rapport wordt alleen gegenereerd om het te cachen. Nu moeten we het shared schedule een duwtje geven zodat het draait. Dat doe je met de procedure AddEvent op de Report Server-database. Eerst halen we de GUID van het dummy shared schedule op. Daarna voegen we het event SharedSchedule toe aan de event-tabel. SSRS leest deze tabel elke seconde en voert het event uit! Maar let op: schoon eerst de cache op van alle rapporten die via dit shared schedule verlopen.

1
-- Cleanup Cache
2
DECLARE @ExpiredScheduleID UNIQUEIDENTIFIER
3
DECLARE @ExpiredScheduleName AS VARCHAR(128) = 'DWH_SharedSchedule_ForExpireCaching_DoNotChangeOrDelete'
4
-- Get the Schedule identifier
5
SET @ExpiredScheduleID = (SELECT ScheduleID FROM ReportServer_BENL_DWH_1.dbo.SCHEDULE WHERE NAME = @ExpiredScheduleName)
6
-- pass the Schedule identifier to the Event insert procedure
7
EXEC ReportServer_BENL_DWH_1.dbo.AddEvent @EventType='SharedSchedule', @EventData=@ExpiredScheduleID
8

9
-- Execute a Shared Schedule:
10
DECLARE @ScheduleID UNIQUEIDENTIFIER
11
DECLARE @ScheduleName AS VARCHAR(128) = 'DWH_SharedSchedule_ForCaching_DoNotChangeOrDelete'
12
-- Get the Schedule identifier
13
SET @ScheduleID = (SELECT ScheduleID FROM ReportServer_BENL_DWH_1.dbo.SCHEDULE WHERE NAME = @ScheduleName)
14
-- pass the Schedule identifier to the Event insert procedure
15
EXEC ReportServer_BENL_DWH_1.dbo.AddEvent @EventType='SharedSchedule', @EventData=@ScheduleID

Stap 5- Testen

Dat is alles! Nu kun je testen of het werkt door het rapport te draaien. Het cachen van het rapport kan even duren, dus heb geduld! Op dit scherm zie je wanneer de subscription heeft gedraaid:

Je kunt ook in de database controleren of het resultaat in de cache staat en wanneer het verloopt (veld AbsoluteExpiration).

1
SELECT C.Name, EC.*
2
FROM ReportServerTempDB.dbo.ExecutionCache AS EC
3
INNER JOIN ReportServer.dbo.Catalog AS C ON EC.ReportID = C.ItemID

Belangrijk

Houd er rekening mee dat de cache, na het instellen van “Cache a temporary copy of the report. Expire copy of report after a number of minutes” of met het shared schedule, na 9999 minuten verloopt en NIET als je een NULL Delivery opnieuw draait. Ik dacht dat SSRS de nieuwe resultaten zou cachen, maar dat doet het niet! SSRS bewaart een fingerprint van de parameterinstellingen in de historie naast het gecachte resultaat (kolom ParamsHash in de tabel ExectutionCache). Als je de subscription opnieuw draait, ziet SSRS dat het resultaat al in de cache staat, ook als er nieuwe data is. Het is heel belangrijk om eerst de cache op te schonen!

Let op: er is geen SQL-commando om de cache van een NULL Delivery Provider te verversen. Dat moet via een shared schedule dat de cache laat verlopen. Er is wel een commando om een cache refresh plan te verversen, maar wij doen de caching met de subscription. Je kunt de cache ook opschonen met dit commando (alle caching wordt verwijderd, ook snapshots):

1
DELETE ReportServer_TempDB.dbo.SnapshotData
2
WHERE SnapshotDataID IN (SELECT EC.SnapshotDataID
3
FROM ReportServer_BENL_DWH_1.dbo.Catalog C
4
INNER JOIN ReportServer_TempDB.dbo.ExecutionCache EC ON EC.ReportID = C.ItemID
5
WHERE LEFT(C.Path,8) = '/DWH_NL/')
6

7
DELETE ReportServer_TempDB.dbo.ExecutionCache
8
WHERE SnapshotDataID IN (SELECT EC.SnapshotDataID
9
FROM ReportServer_BENL_DWH_1.dbo.Catalog C
10
INNER JOIN ReportServer_TempDB.dbo.ExecutionCache EC ON EC.ReportID = C.ItemID
11
WHERE LEFT(C.Path,8) = '/DWH_NL/')

Je kunt ook nog wat extra controles doen in de SSRS-database om de cache-instellingen te zien:

1
SELECT *
2
FROM ReportServe.dbo.CachePolicy
3

4
ExpireFlags: 1 = Expire after XXX minutes,  2 = Using a Shared Schedule

Controleer in TempDB of het rapport in de cache staat:

1
SELECT *
2
FROM ReportServerTempDB.dbo.ExecutionCache

Nog een heel handige optie is om één NULL Delivery Subscription (of een andere subscription) te draaien met een T-SQL-statement:

1
EXEC [ReportServer].dbo.AddEvent @EventType = 'TimedSubscription', @EventData = 'E7F3A09D-8576-4EC7-A1DB-86E244E0A670'

Vervang de UID door de UID van de subscriptionID uit de tabel dbo.Subscription.

Update 12 nov 2013

Vandaag meldde een klant dat SSRS hem verkeerde resultaten liet zien. Na wat onderzoek ontdekte ik dat alle ETL-processen geladen waren en dat de rapportcache ververst was. Wat ging er mis? Mijn ETL-package om de cache te verversen zag er zo uit:

Ik ontdekte dit toen ik in de execution log keek. De byte count voor de NULL-delivery subscription was 0. Dat betekent dat het rapport uit de cache kwam. Toen de NULL delivery werd geactiveerd, had SSRS dus al een fingerprint van de parameters (er was dus al cache).

En hier is het SQL-statement waarmee je controleert of een NULL-Delivery Subscription heeft gedraaid. Dit is een voorbeeld voor één rapport. Controleer de rowcount! Is die 0, dan kwam het rapport uit de cache.

1
SELECT C.Name, EL.TimeStart, EL.TimeEnd,EL.*
2
FROM dbo.ExecutionLogStorage AS EL
3
INNER JOIN dbo.Catalog AS C ON EL.ReportID = C.ItemID
4
WHERE TimeStart > '2013-11-12'
5
AND C.Name = 'PS_ManagementRapportage Productiviteit'

Ik denk dat SSRS wat vertraging nodig heeft tussen het verwerken van de caching en het laten verlopen van de cache, omdat ik deze SQL vanuit een stored procedure draai. Dit is lastig te testen. Mijn oplossing voor nu is om alle cache te verwijderen, hard-coded. Dat doe je met deze SQL. In dit voorbeeld verwijder ik alleen de cache van de map DWH_NL.

1
DELETE ReportServerTempDB.dbo.ExecutionCache
2
WHERE SnapshotDataID IN (SELECT EC.SnapshotDataID
3
FROM ReportServer.dbo.Catalog C
4
INNER JOIN ReportServerTempDB.dbo.ExecutionCache EC ON EC.ReportID = C.ItemID
5
WHERE LEFT(C.Path,8) = '/DWH_NL/')

Update:

Ik heb een vertraging toegevoegd tussen de Expire Cache en het NULL Delivery shared schedule:

1
-- RUN SHARED SCHEDULE: FOREXPIRECACHING TO ACTIVATE ALL NULL DELIVERY SUBSCRIPTIONS
2
DECLARE @ExpiredScheduleID UNIQUEIDENTIFIER
3
DECLARE @ExpiredScheduleName AS VARCHAR(128) = 'DWH_SharedSchedule_ForExpireCaching_DoNotChangeOrDelete'
4
-- Get the Schedule identifier
5
SET @ExpiredScheduleID = (SELECT ScheduleID FROM ReportServer.dbo.SCHEDULE WHERE NAME = @ExpiredScheduleName)
6
-- pass the Schedule identifier to the Event insert procedure
7
EXEC ReportServer.dbo.AddEvent @EventType='SharedSchedule', @EventData=@ExpiredScheduleID
8

9
-- There can be an issue with caching if the expiration is not 100% complete. We set a WAIT
10
-- command to know sure that all cache has been expired.
11
--The format is hh:mi:ss.mmm.
12
WAITFOR DELAY '00:02:00.000';
13

14
-- RUN SHARED SCHEDULE: FORCACHING TO ACTIVATE ALL NULL DELIVERY SUBSCRIPTIONS
15
DECLARE @ScheduleID UNIQUEIDENTIFIER
16
DECLARE @ScheduleName AS VARCHAR(128) = 'DWH_SharedSchedule_ForCaching_DoNotChangeOrDelete'
17
SET @ScheduleID = (SELECT ScheduleID FROM ReportServer.dbo.SCHEDULE WHERE NAME = @ScheduleName)
18
EXEC ReportServer.dbo.AddEvent @EventType='SharedSchedule', @EventData=@ScheduleID

**Mogelijke problemen

**We gebruiken deze methode nu zeven jaar. Na een migratie naar een nieuwe server ontdekten we dat het cachemechanisme van SSRS niet meer werkte. Op de pagina met subscriptions in Report Manager leek de subscription goed te werken. Er stond dat alle subscriptions waren uitgevoerd. Als ik een rapport draaide, was het heel, heel traag. Toen ik een rapport opende, controleerde ik de tabel dbo.ExecutionCache. Er verscheen één record in de tabel, dus het was gecachet. Toen ik hetzelfde rapport opnieuw opende, werd er een nieuw record met dezelfde data gecachet! Best vreemd! Na lang zoeken ontdekte ik dat het verlopen van de cache aan een verlopen schedule hing!

Als je de cache van een subscription koppelt aan een verlopen shared schedule, verwijdert Reporting Services de cache automatisch na XXX minuten! Het beste wat je kunt doen is het shared schedule een jaar in de toekomst zetten, zodat het niet verloopt!