Auch in diesem Jahr bin ich wieder auf der BASTA! in Mainz als Speaker im Einsatz.
Bereits am Dienstag halte ich von 10:00 bis 11:15 Uhr einen Vortrag zum Thema Performante Datenbankzugriffe mit T-SQL, in dem ich altbewährte aber auch neuere Methoden vorstellen will um effektive SQL-Abfragen zu schreiben.
Am Mittwoch unterstütze ich meinen Kollegen von 14:00 bis 15:15 Uhr Florian Nattermann bei seinem Vortrag zum Thema In-Memory Datenbanken vs. Columnstore Index, in dem er diese beiden neuen Technologien gegenüberstellt.
Die BASTA! läuft wie gewohnt ganze 5 Tage wovon am Pre-Conference-Day (Montag) und Post-Conference-Day (Freitag) ganztägige Workshops angeboten werden, während der Rest der Konferenz mit meist 75-minütigen Vorträge aus verschiedenen Bereichen rund um .NET, Windows und Javascript gefüllt ist. Weitere Infos zur Veranstaltung gibt es auf der offiziellen Veranstaltungs-Website: http://www.basta.net
Am 28.06. veranstaltete die PASS zum dritten Mal einen SQL Saturday in Deutschland. Dabei wurde zum zweiten Mal die Hochschule Bonn-Rhein-Sieg in Sankt Augustin als Veranstaltungsort gewählt. Die gute Erreichbarkeit mit dem Auto sowie (am Wochenende) ausreichenden Parkplatzmöglichkeiten sprechen für den Veranstaltungsort. Lediglich die etwas unbequemen Hochschul-Sitzbänke hinterließen einen suboptimalen Eindruck, der durch die qualitativ hochwertigen Vorträge aber mehr als wettgemacht wurde. So fanden sich dann auch ca. 250 Teilnehmer ein, die sich die 30 Vorträge (verteilt auf 5 parallele Tracks zu den Bereichen BI, DBA, Development, Mixed und Open) anhörten. Unter den Speakern waren viele international renommierte Experten, bekannte Buchautoren und MVPs vertreten, darunter Scott Klein, Andreas Wolter, Dr. Holger Schwichtenberg, Dejan Serka, Chris Testa-O’Neill, Matija Lah, Uwe Ricken, Christoph Muthmann, Oliver Engels, Konstantin Klein (um nur einige zu nennen). Die Vorträge deckten verschiedene Themenbereiche und Niveaus ab, so dass für jeden sicherlich etwas Interessantes dabei war. Zwischen den Vorträgen gab es Gelegenheit sich bei den verschiedenen Sponsoren der Veranstaltung über deren Produkte aus dem SQL Server Umfeld zu informieren. Zum Abschluss der Veranstaltung gab es noch diverse Verlosungen der Sponsoren, so dass sich viele Teilnehmer über diverse Sachpreise (vom Buch bis hin zu Hardware) freuen konnten. Doch bereits am Vortag der Veranstaltung trafen sich bereits einige SQL-Enthusiasten an der Hochschule um sich beim Big Data Hackathon intensiv mit Microsoft BigData-Technologien wie HDInsight und PowerBI (PowerQuery, Power View, Power Map und andere) auseinanderzusetzen.
Insgesamt war dieser SQL Saturday eine runde Veranstaltung, die jedem zu empfehlen ist. Schließlich gibt es nicht oft die Möglichkeit quasi zum Nulltarif an so viele interessante Informationen zu kommen. Auf der Website zur Veranstaltung kann man sich zu einigen Vorträgen die PowerPoint-Slides ansehen: http://www.sqlsaturday.com/313/eventhome.aspx
Beim SQL Saturday handelt es sich um eine Veranstaltungsreihe, die von der Professional Association for SQL Server (kurz PASS), weltweit durchgeführt wird. Das Besondere an dieser Veranstaltung ist, dass keinerlei Teilnahmegebühr erhoben wird, die Veranstaltung also komplett durch Sponsoren finanziert wird. Dafür, dass die Teilnehmer einen Samstag Freizeit opfern, bekommen diese hochkarätige Vorträge zu allen möglichen Themen rund um SQL Server von ausgewiesenen Experten zu hören (darunter viele MVPs).
Bei entwickler.press ist gerade mein neues Buch – oder genauer Büchlein – zum Thema Optimierung von SQL-Abfragen erschienen. Das Buch erklärt in kompakter Form am Beispiel von Microsoft SQL Server, wie man performante SQL-Abfragen formuliert. Dabei wird auch auf die verschiedenen Möglichkeiten hingewiesen, die verschiedene Versionen des Produkts (bis hin zu SQL Server 2014) bieten. Viele Optimierungsansätze sind jedoch auch auf SQL-basierte Datenbank-Management-Systeme anderer Hersteller anwendbar. Auch wenn der Text sich primär an Anwendungs- und Datenbankentwickler richtet, dürfte der Inhalt auch für Administratoren interessant sein, zumal die Praxisbeispiele auch diesen Teil mit abdecken.
Das Buch ist bewusst kompakt gehalten und bildet eine auf SQL-Optimierung konzentrierte (aber auch aktualisierte und um neue Inhalte ergänzte) Teilausgabe meines – ebenfalls bei entwickler.press erschienenen – SQL Performance-Ratgebers.
Robert Panther
SQL-Abfragen optimieren
Was Entwickler über Performance wissen müssen
entwickler.press
176 Seiten (Softcover)
ISBN: 978-3-86802-123-3
Preis: € 12,90 inkl. MwSt.
Alternativ auch als eBook erhältlich:
PDF-ISBN: 978-3-86802-310-7
PDF-Preis: 9,99 €
EPUB-ISBN: 978-3-86802-650-4
EPUB-Preis: 9,99 €
Weitere Infos:
Zeitgleich mit dem Erscheinungstermin von SQL Server 2014 sind in Insider-Kreisen vereinzelte Infos über geplante Features für die nächsten Versionen von SQL Server durchgesickert. Da die Features alle noch in einem frühen Entwicklungsstadium sind, ist allerdings leider noch nicht abzusehen, in welcher Version von SQL Server diese implementiert werden.
Neues Energiespar-Feature – “Always-Off”
Die Energiepreise steigen in den letzten Jahren stetig. So ist es nur konsequent, dass im Zuge der “Green-IT” auch über Energiesparmöglichkeiten bei SQL Servern nachgedacht wird. Unter dem Codenamen “Always-Off” wird derzeit an einer Technologie gearbeitet, mit der SQL Server überwiegend quasi im Standby-Betrieb laufen und damit kaum Strom verbrauchen. Sobald Datenbank-Abfragen eintreffen, wechseln diese möglichst schnell wieder in den aktiven Modus zurück. Die daraus resultierenden erhöhten Latenzzeiten werden durch die kürzeren Boot-Zeiten der aktuellen Betriebssystemgeneration sowie durch hochperformante SSD-Platten möglichst gering gehalten. Erfolgt dann innerhalb einer gewissen Zeit keine weitere Abfrage, wechselt der Server wieder in den Standby-Betrieb zurück. Natürlich ist dieses Feature nicht für Umgebungen sinnvoll, die hochverfügbar sein müssen. Interessanter dürfte das Feature beispielsweise für Archivlösungen sein, in denen nur sporadisch Datenbankzugriffe erfolgen und eine Latenzzeit von wenigen Sekunden verschmerzbar ist.
Neue Form des Index-Alignment
Performance-Themen spielen seit einigen Jahren eine große Rolle bei jeder neuen SQL Server Version. So wird zur Zeit an einer neuen Variante des Index-Alignment gearbeitet, bei der – basierend auf Zugriffsstatistiken – die am meisten genutzten Daten an den Anfang des Indexes verschoben werden. Da das Erstellen dieser Statistiken selbst einen gewissen Verwaltungsoverhead mit sich bringt, muss dieses Feature über eine Datenbankoption erst einmal generell aktiviert werden. Anschließend können einzelne Indizes über eine ALTER-Anweisung so konfiguriert werden, dass diese das neue Index Alignment nutzen. Dafür ist bisher die folgende Syntax vorgesehen:
ALTER INDEX IX_IndexName ON Schema.Tabelle ALIGN FOR FASTSEEK
Neuer Datentyp: Pocket Money
Für Kleinbeträge, bei denen auch eine Genauigkeit von lediglich 3 Stellen (nicht 4 wie bei money und smallmoney) hinter dem Komma ausreicht ist ein neuer Datentyp namens Pocket Money geplant. Vorteil dabei ist, dass dieser Datentyp lediglich 2 Bytes benötigt. Hier eine Übersicht der dann verfügbaren Währungs-Datentypen mit deren Speicherbedarf und Wertebereichen:
- money (8 Bytes): -922,337,203,685,477.5808 bis 922,337,203,685,477.5807
- smallmoney (4 Bytes): – 214,748.3648 bis 214,748.3647
- pocketmoney (2 Bytes): -32.768 bis 32.767
Neuer Datentyp: varchar(min) / nvarchar(min)
Auch im Bereich der alphanumerischen Datentypen sind neue Varianten angedacht. Allerdings handelt es sich dabei streng genommen lediglich um Aliasse für bereits bestehende Datentypen, mit denen verhindert werden soll, dass unbedarfte SQL Server Anwender varchar/nvarchar-Spalten mit Längen definieren, bei denen der Speicherbedarf höher ist, als mit den vergleichbaren char/nchar-Varianten. Die neuen Datentypen varchar(min) und nvarchar(min) definieren damit die kleinstmögliche Breite, ab der die Verwendung eines alphanumerischen Datentyps variabler Länge sinnvoll sein kann. Somit entspricht varchar(min) dem Datentyp varchar(4), während nvarchar(min) eigentlich dasselbe ist, wie nvarchar(2).
Die hier aufgezählten Features sind nur eine kleine Auswahl von geplanten oder teilweise bereits in Entwicklung befindlichen Neuerungen. Die Zeit wird zeigen, welche davon auch morgen noch in der Planung bleiben.
Kurz vor Erscheinen des neuen SQL Servers hat Microsoft nun eine neue Version der SQL Server Data Tools veröffentlicht. Diese beinhaltet bereits die Unterstützung für den kommenden SQL Server 2014 (und installiert sogar schon die SQL Server 2014 Express LocalDB mit). Es gibt aber auch ein paar Neuerungen, die sich selbst in Zusammenhang mit älteren SQL Server Versionen nutzen lassen:
- Erstellung von eigenen Regeln für die statische Code-Analyse über eine SSDT API
- nachträgliches Filtern von Daten, die über den SQL Server-Objekt-Explorer angezeigt werden
- erweiterte Azure Integration (Link von Visual Studio auf Management Portal)
- neue Features für den Transact SQL Editor: Verbindung ändern, Alle Abfragen trennen
- Data Compare: Einstellungen in einer .dcmp-Datei speichern
Insbesondere die Möglichkeit, die Data Compare Settings zu speichern, ist ein Feature, das von vielen Benutzern gewünscht wurde. Allerdings ist die Umsetzung noch etwas fraglich, denn scheinbar werden vor allem die Connections, nicht aber die Auswahl der zu vergleichenden Tabellen gespeichert.
Im Gegensatz zur Vorgängerversion wird nun neben Visual Studio 2012 auch Visual Studio 2013 unterstützt. Dafür fällt die Variante für Visual Studio 2010 weg.
Weitere Infos:
- Download: http://msdn.microsoft.com/en-us/data/hh297027
- Beispiele zur Nutzung der SSDT API: http://dacsamples.codeplex.com
Bekannterweise ist der SQL Server Profiler bereits abgekündigt, so dass er in einer der kommenden SQL Server Versionen nicht mehr verfügbar sein wird. Stattdessen empfiehlt Microsoft die Extended Events zu nutzen, die es bereits schon länger gibt. Während diese in früheren Versionen aber nur per T-SQL nutzbar waren, sind die Extended Events seit SQL Server 2012 endlich auch mehr oder minder komfortabel in das SQL Server Management Studio integriert.
Für die Leute, denen das aber noch zu umständlich ist, oder denen der Umstieg vom gewohnten SQL Server Profiler schwer fällt hat Tool-Anbieter Idera gerade den SQL XEvent Profiler als kostenfreies Tool veröffentlicht. Dabei lehnt sich die Oberfläche stark an den SQL Server Profiler an, nutzt aber intern die Extended Events, so dass der Server deutlich weniger belastet wird.
Ich habe das Tool gerade kurz getestet und bin soweit positiv beeindruckt. Zwar stehen je nach gewählter Vorlage wohl nur 10 verschiedene Events zur Verfügung, aber durch diese bewusste Reduktion ist es auf der anderen Seite sehr schnell und einfach möglich einen Überblick über die laufenden Aktivitäten eines SQL Servers zu bekommen. Was der SQL Server Profiler selbst nicht konnte ist die Möglichkeit, die Übersicht anhand einer Eigenschaft (z.B. der Datenbank) zu gruppieren. Ein Export der Liste nach Excel rundet das Ganze noch ab.
Die Reduktion aus wesentliche Features ist erfahrungsgemäß typisch für die kostenfreien Tools von Idera. Dadurch kommt man einerseits sehr schnell zu nutzbaren Ergebnissen. Andererseits bleibt so noch genug Spielraum für die kostenpflichtigen Tools, die einen wesentlich höheren Leistungsumfang bieten.
Verwendbare Extended Events:
- existing_connection
- login
- logout
- module_end
- module_start
- rpc_starting
- rpc_completed
- sp_statement_starting
- sql_batch_completed
- sql_batch_starting
Das Tool kann nach einer Registrierung bei Idera (www.idera.com) kostenfrei heruntergeladen und genutzt werden. Eine Installation erfolgt nur auf dem Client. Allerdings können nur SQL Server ab Version 2012 ausgewertet werden, da die älteren SQL Server noch nicht alle benötigten Extended Events bereitstellen.
Download Link: http://www.idera.com/productssolutions/freetools/sqlxeventprofiler
Manchmal ist es tatsächlich sinnvoll, Datenbanken zu verkleinern, damit ungenutzter Platz wieder freigegeben wird. Auch wenn viele ausgewiesene SQL-Experten wie Brent Ozar, Paul Randal das Verkleinern von Datenbanken generell verteufeln, gibt es doch Ausnahmefälle, in denen eine Verkleinerung angebracht ist, sofern man diese richtig durchführt und sich der Auswirkungen bewusst ist.
“normale” Vergrößerung und Verkleinerung des Datenvolumens
In den meisten Datenbanken, werden Daten nicht nur gelesen, sondern auch geändert, hinzugefügt und gelöscht. Dabei sind die Datenbanken oft so konfiguriert, dass sie automatisch vergrößert werden, wenn durch Hinzufügen von neuen Daten mehr Platz benötigt wird. Das ist zwar nicht die optimale Variante (effektiver ist es, die Datenbank explizit so zu vergrößern, dass der benötigte Platz von vornherein schon zur Verfügung steht), aber immer noch besser als zu riskieren, dass keine Daten mehr geschrieben werden können, da das Datenfile komplett gefüllt ist. Wenn dagegen Daten aus der Datenbank gelöscht werden, so wird das eigentliche Datenvolumen zwar kleiner, das Datenfile aber nicht, da lediglich Lücken innerhalb des Datenfiles entstehen. Diesen Platz kann man nun für andere Dateien zur Verfügung stellen, indem man das Datenfile per SHRINK verkleinert. Dabei werden die Datenseiten vom Ende der Datei in die Lücken verschoben um diese zu schließen. Anschließend kann dann der nicht mehr verwendete Platz am Ende des Datenfiles freigegeben werden. Das klingt zwar auf den ersten Blick ganz gut, bringt jedoch diverse Probleme mit sich, weshalb allgemein empfohlen wird, Datenbanken nicht zu shrinken:
- Durch das Verschieben der Datenseiten im File werden die Indizes massiv fragmentiert. Während also die externe Fragmentierung der Datendatei aufgelöst wird, entsteht eine interne Fragmentierung der Indizes, was schnell dazu führen kann, dass diese nicht mehr verwendet werden können.
- Wenn später wieder neue Daten in die Datenbank geschrieben werden, muss das Datenfile wieder wachsen, was zusätzlichen Aufwand bedeutet und außerdem zu erneuter externer Fragmentierung führt.
Aus diesen Gründen ist insbesondere das regelmäßige Verkleinern von Datenbanken über SQL Agent Jobs im Zuge von Wartungstasks keine gute Idee. Schlimmer noch ist die Verwendung der AUTO SHRINK Option in den Datenbankeigenschaften, da dies in Kombination mit der Automatischen vergrößerung zu einem ständigen Verkleinern und Vergrößern der Datenbankdatei führt, was eine hohe I/O-Last zur Folge hat und die Datenbank extrem fragmentiert. Geoff N. Hiten zieht daher in seinem Blog treffenderweise die Analogie von Auto-Shrink zu Auto-Fragmenting.
Wenn schon verkleinern, dann aber richtig!
Aber wie bereits eingangs erwähnt, bin ich der festen Überzeugung, dass es gelegentlich auch Situationen gibt, in denen ein Shrink sinnvoll sein kann. Das ist genau dann der Fall, wenn aufgrund einer größeren Bereinigungsaktion große Datenmengen aus der Datenbank gelöscht werden. Achtung: Ich rede hier nicht von regelmäßigen (z.B. jährlichen) Bereinigungsaktionen, denn dann kann man sich den Aufwand für den Shrink sparen, da der Platz bis zur nächsten Bereinigungsaktion ohnehin wieder benötigt wird. Es geht vielmehr um einmalige Aktionen, beispielsweise wenn eine große Tabelle mit mehreren hundert GB komplett entfernt wird, so dass der damit frei werdende Platz auch in absehbarer Zeit nicht durch das natürliche Wachstum der Datenbank benötigt wird.
Was ist nun zu beachten, um bei dieser SHRINK-Aktion keinen Schaden anzurichten:
- Nach dem Shrink eine Defragmentierung der Indizes durchführen.
- Genügend Zeit einplanen (sowohl Shrink als auch Index-Defrag können sehr lange dauern).
- Notfalls in mehreren kleinen Steps (z.B. 50 GB) Shrinken und danach jeweils Defragmentierung durchführen, dann kann die Aktion auf mehrere Wartungsfenster verteilt werden. Allerdings muss man sich dann bewusst sein, dass sich die Gesamtdauer der Aktion durch die mehrfach notwendige Index-Defragmentierung weiter erhöht.
- Wenn der Shrink unbeaufsichtigt (z.B. am Wochenende) läuft, einen separaten SQL Agent-Job einplanen, der den Shrink-Prozess rechtzeitig abbricht (falls dieser noch nicht fertig ist) und den Index-Defrag startet. Das Shrinken selbst kann jederzeit problemlos abgebrochen werden (selbst durch einen KILL des Prozesses). Die bis dahin verschobenen Speicherseiten bleiben an der neuen Position, auch wenn sich das Datenfile selbst erst nach komplettem Abschluss einer Shrink-Anweisung verkleinert. Bei erneuter Ausführung der Shrink-Anweisung wird dann im Prinzip an derselben Stelle weiter gemacht.
- Der Shrink sollte nie bis zur kleinstmöglichen Datenbankgröße durchgeführt werden. Stattdessen immer das natürliche Wachstum der Datenbank mit einplanen, so dass nicht kurz danach wieder ein Vergrößern des Datenfiles erforderlich wird (und zu erneuter externer Fragmentierung führt).
Zusammenfassend kann man also festhalten:
- Regelmäßiges SHRINKen ist böse!
- Auto-Shrink ist sehr böse!
- Aber: Gezieltes Shrinken kann in Ausnahmefällen sinnvoll sein, wenn man danach auch die Indizes defragmentiert!
Weitere Blog-Beiträge zum Thema:
- Brent Ozar: http://www.brentozar.com/archive/2009/08/stop-shrinking-your-database-files-seriously-now
- Paul Randal: http://www.sqlskills.com/blogs/paul/why-you-should-not-shrink-your-data-files
- Geoff N. Hiten: http://weblogs.sqlteam.com/geoffh/archive/2009/03/19/Auto-Fragmenting-Databases.aspx
- Thomas Larock: http://thomaslarock.com/2009/03/when-to-use-auto-shrink
- Mike Walsh: http://www.straightpathsql.com/archives/2009/01/dont-touch-that-shrink-button
