Bericht (Reporting Services 2008) benötigt zwischen 1,5 Sekunden und 28 Minuten

Question

Hallo zusammen,
wir haben das ReportserverWebControll in unsere aspx-Seiten eingebaut und bieten verschiedene Berichte zur Ansicht und zum Export an. Wir haben einen Bericht, der im Normalfall zum Aufruf 1,5 Sekunden benötigt. Leider tritt nun immer mehr das Problem auf, dass dieser Bericht in einen Timeout läuft, weil die Ladezeit massiv steigt. Über das ExecutionLog können wir analysieren, dass die Ladezeit bei gleichen Parametern bis zu 28 Minuten benötigt. Dies tritt immer dann auf, wenn mehr Leute diesen Bericht benötigen. Wir haben den IsolationLevel auf "Read uncommited" gesetzt ("WITH (NOLOCK)") und die Abfrage in eine StoredProcedure ausgelagert (und dann auch mit "SET NOCOUNT ON;") versehen. Zuerst dachten wir, dass wir das Problem behoben hätten, da nach dem neuen bereitstellen des Berichts plötzlich alles lief, einen Tag später ging es dann allerdings wieder los. Wir haben den Eindruck, dass sich diese "Verklemmer" meistens nachts lösen, wenn durch die Sicherung der Datenbank irgendwelche Logs oder Transaktionen bereinigt werden. Uns gehen langsam die Ideen aus. Geblockte Transaktionen gibt es eigentlich keine (nach ca. 2 Minuten erscheint eine in der ReportServerTempDB, nach der Exception verschwindet sie jedoch von alleine) und wenn man den Select, der dem Bericht zugrunde liegt) direkt auf der Datenbank absetzt geht es nicht länger als 2 Sekunden. Die Menge der zurückgelieferten Zeilen ist kleiner als 1000 und TimeRendering ist flott, TimeDataRetrieval schießt exorbitant hoch. Eine Meldung, dass die MaxPoolSize der Datasource ausgeschöpft sei, erhalten wir keine, nach dem Kopieren der Produktiv-Datenbank nach Integration läuft der Bericht einwandfrei.

Kann uns jemand einen Tipp geben, was wir noch versuchen können oder woran dieses Verhalten liegt? Wir sind mittlerweile völlig ratlos.

Gruß
Kerstin
Habt ihr eine Idee woran es liegen könnte?

Answer 1

Hallo Kerstin,
nein ich habe erst mal keine Idee woran es liegen könnte, aber noch Fragen! ;-(

Was meinst Du mit "Dies tritt immer dann auf, wenn mehr Leute diesen Bericht benötigen". Wieviel ist "mehr" ?

Was ich noch versuchen würde:
1.) Im Berichts-Manager kann man ja unter Eigenschaften/Ausführung angeben, dass der Bericht nicht immer mit den aktuellsten Daten laufen muss, sondern diese z. B. 5 Minuten zwischengespeichert werden.
2.) Man könnte in der Prozedur eine Protokollierung einbauen, die eine Aussage über die Laufzeit ermöglicht.
3.) Auf der Integrations-Datenbank mit entsprechend vielen Benutzern testen. 

Ist der Server auf einem aktuellen ServicePack-Stand?

---

Einen schönen Tag noch,
Christoph Muthmann
Microsoft SQL Server MVP, http://www.insidesql.org

Answer 2

Hallo Christoph,

vielen Dank für die Rückmeldung. Wie unsere weiteren Erfahrungen zeigen, liegt es nicht wirklich an der "Last" auf dem Produktivsystem wie unten aufgeführte Zahlen belegen. Wir haben diesen Effekt mittlerweile auch schon einmal auf Integration gehabt und da befinden sich max. 3 Personen zum Testen drauf.
Übersicht Berichtsaufruf:

	28.02.2010	27.02.2010	26.02.2010
Gesamtabfragen Berichte	2000	1217	1157
Abfrage "Wareneingangsbuch"	737	169	206
Berichtsaufruf > 1 Minute	10	14	34
Berichtsaufruf > 2 Minute	0	9	28
Berichtsaufruf > 5 Minute	0	5	22

Leider könnte die Berichtseigenschaft aus deinem Punkt 1 nur mit Bauchschmerzen auf temporäre Speicherung gesetzt werden, denn die Mitarbeiter wollen Lieferscheine und Rechnungen erfassen und sich dann direkt im Anschluss den Bericht des Wareneingangsbuches ausdrucken. Wenn hier die Daten nicht aktuell sind, bekommen wir ein Problem.

Zu Punkt 2: was bringt mir diener Meinung nach eine Protokollierung, die Auswertung können wir ganz einfach in der ReportServer DB mit ExecutionLog machen. Obigen Zahlen sind folgendermaßen entstanden:

select COUNT(*) from ExecutionLog2
where TimeStart >= '26.02.2010' and TimeStart < '27.02.2010'
select COUNT(*) from ExecutionLog2
where TimeStart >= '26.02.2010' and TimeStart < '27.02.2010'
and ReportPath like '%eingangsb%'
select COUNT(*) from ExecutionLog2
where TimeStart >= '26.02.2010' and TimeStart < '27.02.2010'
and ReportPath like '%eingangsb%'
and DATEDIFF(Minute,[TimeStart],[TimeEnd]) > 5

Das Problem tritt jedoch nach wie vor verstärkt am Ende vom Monat und am Anfang auf, wenn der Bericht ausgedruckt an die Buchhaltung gesendet werden muss. Wie oben schon erwähnt ist es kein Problem des eigentlichen "Daten holens", da das Absetzen des zugrundeliegenden Selcts auf der Datenbank trotz hängendem Report innerhalb von 1-2 Sekunden ausgeführt ist.

Soweit ich informiert bin, ist der Stand mehr oder weniger aktuell:
Microsoft Windows Server 2003 R2
Standard x64 Edition
Service Pack 2

Dazu wäre noch anzumerken, dass ausschließlich dieser eine Report solch große Probleme macht. Allerdings haben andere mit direkter SQL Abfrage nicht solch hohe Frequentierung oder die Daten werden über einen Cube gespeist:

	08.03.2010	09.03.2010	10.03.2010
Gesamtabfragen Berichte	1696	1006	808
Abfrage "Freizeitkontrolle"	168	77	48
Berichtsaufruf > 1 Minute	0	0	0

Mit freundlichem Gruß
Kerstin

Answer 3

Hallo Kerstin,
Punkt 1 verstehe ich soweit, wobei durch eine asynchrone Verarbeitung sicherlich Last reduziert werden könnte. Das bedeutet aber eine andere Arbeitsweise.

Verwendet der Bericht eigentlich Parameter? Werden die Parameter an die Prozedur weitergereicht? Das Frage ich, da evtl. Parameter-Sniffing ein Thema für Euch sein könnte.

Zu Punkt 2 könnte man dann sagen, wo die Zeit verbraten wird, in der Prozedur, oder beim Berichtsserver.

Jetzt wissen wir zwar, welche Server-Version ihr habt, aber ich meinte eigentlich SQL Server-Version. ;-)

---

Einen schönen Tag noch,
Christoph Muthmann
Microsoft SQL Server MVP, http://www.insidesql.org

Answer 4

Hallo Christoph,

also:
SqlServer 2008 SP 1 64 bit

und hier noch weitere Zusatzinfos:
Microsoft SQL Server Management Studio 10.0.2531.0
Microsoft Analysis Services-Clienttools 10.0.1600.22
Microsoft Data Access Components (MDAC) 3.86.3959
Microsoft MSXML    2.6 3.0 6.0
Microsoft Internet Explorer  7.0.5730.13
Microsoft .NET Framework  2.0.50727.3082
Betriebssystem    5.2.3790

Der Bericht hat Parameter (bei diesem mandantenfähigen Warenwirtschaftsprogramm auch nicht vermeidbar) in der Prozedur mit folgenden Eigenschaften (und null-Vorbelegungen), wobei die Prozedur erst beim Versuch das Problem zu beheben entstanden ist und der Select vorher direkt im DataSet des Berichts hinterlegt war:
->Von, Bis und Betrieb sind Pflichtfelder

 

ALTER PROCEDURE [dbo].[Wareneingangsbuch] 
@vom DateTime, @bis DateTime, @BetriebId VARCHAR(255), @BetriebsstelleId VARCHAR(255)=null, @AbteilungsstammId VARCHAR(255)=null, @LieferantId VARCHAR(255)=null, @GenericBool int, @GenericBool2 int AS BEGIN

Gruß
Kerstin

Answer 5

Hallo Kerstin,
meine Vermutung geht dahin, dass der Aufruf nur bei ungünstigen Parametern lange Laufzeiten hervorruft. Schau Dir dazu auch mal diese Seiten an:
http://elegantcode.com/2008/05/17/sql-parameter-sniffing-and-what-to-do-about-it/
http://blogs.msdn.com/queryoptteam/archive/2006/03/31/565991.aspx

Kannst Du herausfinden, welche Läufe lange dauerten? Hierfür wäre dann doch eine Protokollierung der Zeiten inklusive Parameter notwendig.

---

Einen schönen Tag noch,
Christoph Muthmann
Microsoft SQL Server MVP, http://www.insidesql.org

Answer 6

Hallo Christoph,
vielen Dank für die Links, doch glaube ich nicht ganz daran. Wie du den unteren Parametern entnehmen kannst, sind beide Aufrufe identisch gewesen und haben einmal 10 Min. und einmal 19 Min. gebraucht.

10 Minuten: "Ersteller=xxx&culture=de&GenericBool=False&Jahr:isnull=true&Wochennummer:isnull=true&BetriebsstelleId:isnull=true&BetriebId=8&AbteilungsstammId:isnull=true&LieferantId:isnull=true&vom=30.11.2009 00:00:00&bis=27.02.2010 00:00:00&GenericBool2=True 1 2010-03-03 15:25:45.147 2010-03-03 15:35:44.087 598766 12 45 1 rsSuccess 23291 75 "

19 Minuten:  "Ersteller=xxx&culture=de&GenericBool=False&Jahr:isnull=true&Wochennummer:isnull=true&BetriebsstelleId:isnull=true&BetriebId=8&AbteilungsstammId:isnull=true&LieferantId:isnull=true&vom=30.11.2009 00:00:00&bis=27.02.2010 00:00:00&GenericBool2=True 1 2010-03-03 15:21:24.553 2010-03-03 15:40:40.117 1155296 31 42 1 rsSuccess 23291 75 "

Das ist auch das, was wir immer wieder feststellen: ein Bericht für einen definierten Zeitraum für einen bestimmten Betrieb, lässt sich mal innerhalb von wenigen Sekunden und mal nicht innerhalb des ablaufenden TimeOuts aufrufen. Nach der nächtlichen Sicherung rufe ich ihn wieder auf und er funktioniert, bis er sich wieder "verklemmt". Das hat er jetzt seit 10 Tagen nicht mehr getan, aber aus Erfahrung weiß ich ganz genau, Richtung Ende Monat, wnn alle diesen Bericht brauchen, geht wieder gar nichts mehr.

Gruß
Kerstin

Answer 7

So, ich habe die Prozedur mal angpasst und die Parameterwerte in lokale Variablen zwischengespeichet...jetzt harre ich der Dinge die da kommen. ;-)
Gruß
Kerstin

Answer 8

Hallo Kerstin,
das klingt für mich danach, als ob der Optimizer ab einem bestimmten Zeitpunkt einen schlechten Zugriffsplan generiert und diesen dann erst mal weiter verwendet.
Mit der Umstellung der Parameterwerte, könntet ihr hier evtl. dem Optimizer den Wind aus dem Segel genommen haben und die Pläne bleiben stabil.

Wie sieht es denn mit den Statistiken auf den Tabellen aus? Werden die automatisch gepflegt? Macht ihr regelmässig einen Index-Reorg bzw. Index-Rebuild?

---

Einen schönen Tag noch,
Christoph Muthmann
Microsoft SQL Server MVP, http://www.insidesql.org

Answer 9

Hallo,

zum jetzigen Zeitpunkt werden die Indizes noch nicht reorganisiert oder erneuert. Das ist ein Punkt, den wir noch auf unserer Tagesordnung haben, zusammen mit dem zusätzlichen Setzen von weiteren Indizes (welche über die normale referenzielle Integrität hinaus gehen). Die Datenbank läuft jetzt erst seit 2,5 Monaten produktiv und bis vor kurzem waren noch nicht alle Betriebe aufgeschaltet. Das ist aber ein Punkt, der von uns demnächst angegangen wird.

Welche Vorteile bieten regelmäßige Index-Reorg bzw. Index-Rebuilds? Kann man das im Live-Betrieb durchführen? Da wir einige 24-Stundenbetriebe darunter haben, gibt es für uns keinen Zeitraum, in dem wir wissen, dass niemand mehr darauf arbeitet...

Gruß
Kerstin

Answer 10

Hallo Kerstin,
wenn ihr die Enterprise Edition habt, könnt ihr auch die meisten Indizes Online reorganisieren oder neu aufbauen. Details dazu in der Online-Doku.

Ich sehe hier zwei Vorteile:
1.) Die Fragmentierung der Indizes und über den gruppierten Index auch der Tabelle wird reduziert. Schau Dir z. B. mal im Performance Monitor die Page Splits an, bzw. die Sicht sys.dm_db_index_physical_stats.
Schau auch mal hier rein, das kann im Umfeld der Indizes sehr hilfreich sein:
http://msdn.microsoft.com/en-us/magazine/cc135978.aspx


2.) Die Statistiken werden zu einem geregelten Zeitpunkt neu aufgebaut. Hiermit kann man die Gefahr reduzieren, dass dies im laufenden Betrieb mal passiert, was bei einem User, dann zu etwas verlangsamten Antworten führen kann. Wichtiger ist aber sicher Punkt 1.

Über welche Datenmengen sprechen wir hier eigentlich?

---

Einen schönen Tag noch,
Christoph Muthmann
Microsoft SQL Server MVP, http://www.insidesql.org

Answer 11

Hallo Christoph,
für den Kunden haben wir leider nur die Standard-Edition zur Verfügung, intern (develop) können wir jedoch mit der Enterprise arbeiten. Unsere eine Tabelle (die jedoch nicht von dem oben genannten Bericht angesprochen wird) wächst wöchentlich um ca. 1 Mio. Zeilen, Daten müssen mindestens 2 Jahre vorgehalten werden. Wir müssen hier allein schon wegen des Cube-Processings Änderungen vornehmen (ob in Richtug DataWarehouse oder andere Varianten sind wir zur Zeit am analysieren)

Ein Mitarbeiter von uns hat letzte Woche angefangen sich intensiv mit diesem Thema zu beschäftigen und wir hoffen hier bald auf Ergebnisse.

Gruß
Kerstin

Answer 12

Hallo Kerstin,
das riecht ja schon nach Partitionierung.

Falls Du weitere Fragen zu Eurer Umstellung hast, mach am besten einen neuen Thread auf.

 

Falls Du eine Antwort von mir hilfreich fandest, würde ich mich freuen, wenn Du sie als Antwort akzeptierst. Das erleichtert es anderen den mittlerweile längeren Thread schneller zu lesen.

---

Einen schönen Tag noch,
Christoph Muthmann
Microsoft SQL Server MVP, http://www.insidesql.org