Optimierung Reporting Server MS SQL 2008 R2 Teil 2

Nach der Optimierung des Reporting Servers aus Teil 1 http://blog.volkerbachmann.de/2015/01/31/optimierung-reporting-server/ war es nur wenige Monate ruhig um den Reporting Server.
Die Anzahl der Reports steigt nahezu täglich und die Reports können nicht weit genug auseinander gezogen werden damit diese sich nicht in die Quere kommen.

Deshalb haben wir die Analyse mit der Unterstützung von Dell – als Lieferant des SQL Servers – noch einmal durchgeführt.

Ausgangslage: Reports brauchen bis zu 2 Stunden bis sie versendet werden

Analyse durch Dell mit DPack Tool
Flaschenhals:
1. Festplatten IOPS
2. RAM

Lösungsmöglichkeiten
1. Mehr IOPS zur Verfügung stellen
a. Vorhandene 6 Festplatten durch SSD ersetzen
b. Zwei SSD zusätzlich im RAID 1 und Verlagerung der meist frequentierten DBs auf diese SSD
c. Eine oder zwei SSD als CacheCade (erweiterter Cache des RAID Controllers) einrichten
2. Mehr Arbeitsspeicher zur Verfügung stellen
a. Zusätzlicher Speicher inkl. Enterprise Lizenz und ggf. Neuinstallation des Servers.

Das waren die Lösungsansätze bezüglich Hardware.

Weitere Möglichkeit:
3. Optimierung der Abfragen mit Unterstützung eines SQL Server Spezialisten

Kosten für die Umbauten:Kosten Optimierung Reporting Server 2008 R2

Durchgeführte Maßnahmen

1. Arbeitsspeicher +32GB / Windows Server Enterprise Edition
2. Eine SSD im RAID 0 für ReportServerTempDB
3. Optimierungen der Reports
durch Snapshot Technologie

Der Performance Monitor zeigt die Antwortzeiten für die TempDB Dateien sowie die 100% Aktivität der entsprechenden Festplatten im RAID 10.

Vor dem Umbau:

Performance Monitor vorher

Nach dem Umbau:

Performance Monitor nachher

Deutlich zu sehen ist dass die Antwortzeiten für die TempDB-Dateien (nun auf der SSD) auf “0” runter gegangen und auch die Aktivität auf der SSD (Partition S:) ist auf 3% runter gegangen ist.

Als abschließender Vergleich nun noch die Laufzeiten ausgewählter Reports: Laufzeiten der Reports im Vergleich

Ergebnis: Seit Einbau der SSD sind die Laufzeiten der Reports deutlich runter gegangen. Auch die Snapshot Technologie hat für einige Reports deutliche Verbesserungen gebracht.

Der Einbau des Arbeitsspeichers alleine hat nicht dafür gesorgt dass die Reports schneller ausgeliefert werden konnten, hat aber dafür gesorgt, dass der Server im Ganzen nicht mehr so eng an den Grenzen des vorhandenen Arbeitsspeichers arbeitet, sondern noch ein wenig Luft hat.

Bisher ist das Ergebnis soweit ausreichend, dass keine weiteren Maßnahmen mehr notwendig sind.

Weitere Möglichkeiten wären:

  1. Umsetzung der Snapshot Techniken für weitere Reports
    2. Verlagerung weiterer (temporärer) Datenbanken auf die SSD
    1. ReportServer TempDB
    2. ReportCodes DB
    3. Sonstiges
    1. Optimierung von Abfragen, Indizes
    2. Ggf. In-Memory / Columnstore Index

Danke fürs Lesen.
Über Kommentare würde ich mich sehr freuen.

Viele Grüße
Volker

Studying for Exam 70-461 (MCSA: SQL Server 2012)

Just beginning to collect information and material for Studying the Exam 70-461

Reminder: there will be no Exam for MCSA SQL Server 2014 just for 2012. Only the Exams for the MCSE SQL Server are upgraded to 2014!

First of all a List of Links that helped me decide how to learn – hopefully, I’m in the process right now.

Tipp from Kendra in the above Video

  • Create your own Test Lab on a Laptop/Remote Session (Azure?) to be accessible from everywhere.
    => Windows 10 Laptop with Hyper-V? Lenovo X300 doesn’t work with Win 10 and Hyper-V!

How to learn?

Microsoft is testing to do online certification tests  – “Online proctored” – maybe that’s a choice?

Do you have additional ressources? Feel free to contact me..

Regards,
Volker

Base Monitoring

TSQLTuesdayT-SQL Tuesday #66: Base Monitoring

Cathrine Wilhemsen (b|t) is hosting the T-SQL Tuesday blog party of this month.

I will describe what’s my understanding of Base Monitoring for SQL Server.

Last Thursday 5/7/2015 I presented these thoughts at a PASS regional Chapter in Cologne/Germany.

“My” Base Monitoring consists of monitoring all Events that come up from too few hard disk space, memory or CPU. It is also about monitoring of locking, blocking or deadlocks, long running queries/jobs and failure in logins.
I will also do additional tasks that didn’t belong to the monitoring itself but help in failure check and reorganization of indexes and so on (…)

All this was arranged and written down after an installation of a new SQL Server.
These are the steps I did:

1. create a DBA Database for several scripts and tables (see below)
2. install the main script from dbWarden for Monitoring
3. install Integrity check and Maintenance scripts from Ola Halengren into DBA Database
4. create GetErrorLogEvent Stored Procedures and Job for Mails out of the SQL Errorlog into DBA Database
5. create Jobs for recycling ErrorLogs
6. Performance Monitoring, Event Logs with an external Tool called Host Monitor

The dbWarden Script (2.) is a great ressource for real time monitoring blocking, deadlocks, long running querys, jobs and so on – best of all: it is free of charge (!). It also brings a detailed and configurable health report of the server that will be sent every morning.

The scripts from Ola Halengren (3) are well known in the SQL Server Community for bringing us perfect maintenance tasks for index and statistic updates with rebuild online/offline or reorganize where it is neccessary.
Additionally there is a script for integrity check – and backup of course. 🙂

The GetErrorLogEvent Script (4.) was originally written by Jonathan Kehayias and published on an MSDN Archive site. I didn’t found the site any more on MSDN so the script is attached together with the presentation (Sorry, only german these days) below.

For recycling the Error Log (5.) it is only neccessary to create a job with the two commands (exec dbo.sp_cycle_errorlog and exec dbo.sp_cycle_agent_errorlog).

The only tool that is not free of charge is the last one, called Host Monitor.
It is perfect for more monitoring especially for long term monitoring.

Ressources

dbWarden
Description: http://www.sqlservercentral.com/articles/Monitoring/98106/
Download, Wiki: http://sourceforge.net/projects/dbwarden/

Ola Halengren
https://ola.hallengren.com/

Hostmonitor (€)
http://www.ks-soft.net

GetErrorLogEvents and Basis Monitoring Presentation from the PASS Chapter in Cologne (German)
BaseMonitoring_20150507

Thanks for reading,
Volker

Optimierung Reporting Server MS SQL 2008 R2

Meldung der Report-Abteilung: täglich zwischen 9 und 9:20 Uhr ist der Reporting Server völlig ausgelastet, d.h. die Reaktionszeiten beim Erstellen und Bearbeiten von Reports sind inakzeptabel.

Meine Recherchen nach den Ursachen:

1. Ausgangssituation

  • Aus Performancegründen wurde vor 1 ½ Jahren ein neuer Reporting Server angeschafft.
  • Danach lief der Restore der Datenbanken (Befüllung des Reporting Server über Backup&Restore aus den Produktiv Datenbanken) über Nacht und ebenso sämtliche Abfragen deutlich schneller.
  • Ein Zwang zu weiterer Optimierung der Abfragen usw. war damit nicht mehr notwendig

2. Analyse

  • Datenbank und Tabellen sind für OLTP ausgelegt, d.h. Eingabe von Daten, Update usw.
  • Auf dem Reporting Server handelt es sich um OLAP, d.h. Analyse von Daten inkl. Aggregierung
    => andere Art der Abfrage => andere Art der Indizierung notwendig
  • Es laufen Reports gleichzeitig die auf die gleichen Daten zugreifen – mit der fehlenden Indizierung laufen diese Reports dann so lange, dass sie sich gegenseitig blockieren.
    => Ergebnis: ~ 400.000 Wartende Tasks, mäßige Prozessorauslastung, viele SperrenAktivitätsmonitor

3. Lösungsmöglichkeiten

  1. Anzahl der Reports verkleinern.
  2. Reports besser verteilen damit diese sich zeitlich nicht so sehr in die Quere kommen.
  3. Views (general_view usw.) materialisieren, d.h. in Tabellen umwandeln und darauf dann Indizes anwenden.
    Erklärung: Für die Abfrage über alle Datenbanken wurde ein View entworfen, der einen Union All über alle Datenbanken macht. Die Idee ist, das Ergebnis aus diesem View, der mittlerweile in fast allen Reports (~300 Stück) verwendet wird, in eine Tabelle zu speichern, damit bei jeder Abfrage nur noch die Tabelle verwendet wird und nicht mehr sämtliche Datenbanken. Passende Indizes gehören dann dazu.
  4. Vorhandene Indizes durch OLAP Indizes ersetzen.
  5. zusätzliche SQL Instanz vom Server verlagern -> zusätzlicher SQL Server 2008R2 notwendig.
  6. Replikation zur Verringerung der Wiederherstellungszeiten und für Intraday Reports.
    Erklärung: durch Backup und Restore als Aktualisierung des Reporting Servers ist der Server während der entsprechenden Restore-Zeit nicht für Reports verfügbar. Da wir an dieser Stelle aber abhängig vom Backup auf den Produktiv Servern sind, kann der Restore nicht früher laufen.
    Lösungsansatz: Backup und Restore durch Replikation ersetzen.
  7. größerer Server mit mehr Speicher – Investition ca. 20.000€

4. Umsetzung

Mittlerweile sind die Punkte 1-3 realisiert. Schon alleine durch konsequentes auseinanderziehen der Reports konnte die Verfügbarkeit des Servers zu den angegebenen Zeiten eindeutig verbessert werden.
Zuletzt wurde dann noch Punkt 3 umgesetzt. Vermutlich sollten noch weitere Indizes hinzugefügt werden um die Geschwindigkeit der Abfragen deutlich zu erhöhen, aber das ist so schon um längen besser als vorher.

Die weitern Punkte werden vermutlich nicht umgesetzt werden, da die ersten 3 Punkte schon eine deutliche Verbesserung gebracht haben. Zusätzlich ist das Thema DBA nicht mein einziges Thema und ich kann mich nicht so darauf konzentrieren, wie ich gerne würde.

Danke fürs Lesen,

Volker

 

 

In-Memory Techniken – Teil 2 – open HPI

Hallo,

im ersten Teil zu In-Memory Techniken habe ich meine erste Erfahrung mit dem Thema beschrieben.

Danach wollte ich mehr zu dem Thema wissen.

Nach etwas längerer Suche habe ich dann per Zufall einen Hinweis auf die Online Platform des Hasso Plattner Instituts in Potsdam gefunden.

Dort wird ein kostenloses 6-wöchiges Seminar angeboten was über “In-Memory Data Management – Implications on Enterprise Systems” informiert. Zwar basiert das ganze auf der vom Hasso Plattner Institut mitentwickelten Open Source SanssouciDB bzw. der SAP HANA Appliance, vieles ist aber auch auf den MS SQL Server übertragbar. Auch in 2015 wird dieser Kurs wieder angeboten:
https://open.hpi.de/courses/imdb2015

Das Seminar wird mit wöchentlichen Selbsttests und einem Examen und Zeugnis abgeschlossen. Das ganze ist nicht so ganz einfach, aber durchaus nebenberuflich zu schaffen.

Ich kann das nur empfehlen!

Danke fürs Lesen
Volker

 

In-Memory Techniken – Teil 1

Hallo Leute,
wie in meiner “über mich” Seite angekündigt werde ich mich dem Thema In-Memory Datenbanken des SQL Server 2014 widmen.

Die beeindruckende Beschleunigung, die ein Insert Statement durch In-Memory Techniken erfahren kann, habe ich zum ersten Mal nach dem Lesen eines Artikels von Pinal Dave ausprobiert. Das ist dieser Artikel:
http://blog.sqlauthority.com/2014/09/15/sql-server-beginning-in-memory-oltp-with-sample-example/

Da wir in der Firma noch “Lichtjahre” von dem Einsatz des SQL Server 2014 entfernt sind, bzw. für diesen bisher keine Notwendigkeit besteht, musste ich mich zuerst mit einer Testversion des SQL 2014 zufrieden geben mit der ich testen konnte.
Das reichte auf einer virtuellen Maschine aus um den Effekt zu sehen.

Von dem Erfolg angetrieben habe ich dann natürlich sofort versucht vorhandene Datenbanken auf In-Memory umzubauen 🙂 . Das Umbauen der Datenbank war dabei nicht das Problem, allerdings der Switch von einzelnen, viel genutzen Tabellen war dann nicht mehr so einfach. Da so manche Details der Tabellen nicht in Einklang mit den Vorgaben der In-Memory Tabellen stand, musste ich dort einiges ändern.

Das betraf vor allem Indizes. Bei dem Umbau half mir der Migrationsassistent des SSMS. Ich hab dann einfach alle Indizes gelöscht die nicht den Vorstellungen des Assistenten entsprachen und dann die Tabellen zu In-Memory Tabellen umgebaut.

Die erhoffte Beschleunigung beim Zugriff mit unserer Anwendung blieb allerdings aus. 🙁

Im Nachhinein erklärte sich das ganze auch:
Memory-optimierte Tabellen sind nicht unbedingt für das reine Lesen optimiert, sondern für Schreiben/Updates, so wie es in dem kleinen Beispiel von Pinal ja auch umgesetzt ist.

Da ich aber nur Tabellen umgebaut hatte, die ausschließlich lesende Zugriffe zu verarbeiten hatten, stellte sich keine Beschleunigung ein.

Meine weiteren Erfahrungen folgen im nächsten Teil…

Danke fürs Lesen,
Volker

Rückblick auf Vorträge zum SQL Server Monitoring dbWarden

In 2013 habe ich in den Regionalgruppen Köln/Bonn/Düsseldorf sowie Ruhrgebiet einen Vortrag zu einem kostenlosen Monitoring Skript (dbWarden) halten können welches ich Euch nicht vorenthalten möchte.

Es handelt sich dabei um eine Kombination aus Skripten und Jobs zum SQL Server Monitoring – Warden = Wächter, Aufseher

Voraussetzungen:

  • SQL Server Agent, SQL 2005 ff.
  • “This script assumes you already have DBMail setup (with a Global Public profile available.)”

Beschreibung: http://www.sqlservercentral.com/articles/Monitoring/98106/

Forum: http://www.sqlservercentral.com/Forums/Topic1441532-3362-1.aspx

Download: http://sourceforge.net/projects/dbwarden/

Hier dann noch die Präsentation von dem Termin in Köln am 3.6.2013:
0306 dbWarden Volker Bachmann

Viele Grüße
Volker

Erster Beitrag – ein Hallo Leute und was noch so kommt – SQL Server…

Hallo Leute,

ja, so fangen wohl die meisten Blogs einmal an.

Ich habe mich gerade dazu entschlossen hier meine Erfahrungen und Erkenntnisse mit dem Microsoft SQL Server niederzuschreiben. Da ich beruflich nur ein wenig Zeit habe mich den Themen des SQL Server zu widmen muss das nebenbei geschehen und die Schritte werden nur klein sein. 🙂

So, das soll es als Anfang schon gewesen sein.

Viele Grüße
Volker