Zum Inhalt springen

Neue Features für künftige Versionen von SQL Server

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.

 

SQL Server Data Tools für SQL Server 2014 veröffentlicht

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:

 

Neues kostenfreies SQL Server Tool: Idera SQL XEvent Profiler

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

IderaSqlXEventProfiler

Tipps & Tricks: Datenbanken richtig verkleinern

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:

  1. 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.
  2. 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:

  1. Nach dem Shrink eine Defragmentierung der Indizes durchführen.
  2. Genügend Zeit einplanen (sowohl Shrink als auch Index-Defrag können sehr lange dauern).
  3. 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.
  4. 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.
  5. 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:

SQL Server 2012 Integration Services – Lessons Learned (Vortragsslides)

Die BASTA! in Mainz liegt nun schon einen Monat zurück. Für alle, die es interessiert (und die eventuell nicht dabei sein konnten), möchte ich die Slides zu meinem Vortrag an dieser Stelle veröffentlichen. Es ging dabei um Erfahrungen, die in echten Projekten mit SSIS 2012 gemacht wurden, welche neuen Features sich bewährt haben, wo nach wie vor Verbesserungspotential besteht, aber auch was nach wie vor zu beachten ist, um performante SSIS-Pakete zu entwickeln.

Beim Veranstalter laufen bereits die Vorbereitungen für die BASTA! Spring Edition, die vom 24.-28.02.2014 in Darmstadt stattfindet, auf Hochtouren.

Nähere Infos unter http://basta.net

Tipps & Tricks: Große Datenmengen in einer Produktionsumgebung aktualisieren

Gelegentlich lässt es sich nicht vermeiden, dass große Datenmengen in einer Produktionsumgebung aktualisiert werden müssen. Beispielsweise dann, wenn ein Feld einen neuen Standardwert bekommen soll (ohne ein Default-Constraint zu nutzen). Das ließe sich im Prinzip mit der folgenden SQL-Anweisung erledigen:

  UPDATE dbo.Protokoll
SET DeletedFlag = 0
WHERE DeletedFlag IS NULL

Problematisch ist dabei, dass aufgrund der Lock-Escalation dann die gesamte Tabelle solange exklusiv gesperrt wird, bis die Änderung komplett durchgelaufen ist. Gerade bei produktiven Umgebungen, kann dies dazu führen, dass andere Transaktionen in Timeouts laufen, was möglichst zu vermeiden ist. Natürlich kann man die Aktualisierung in die Nacht verlagern, aber auch da könnten eventuell irgendwelche Batches laufen, die versuchen, auf die genannte Tabelle zuzugreifen und dann nach einem Timeout abbrechen.

Wie also kann man den UPDATE trotzdem ausführen, und das Produktivsystem dabei möglichst wenig belasten?

Die Lösung liegt darin, dass UPDATE in mehreren kleinen Schritten auszuführen. Damit werden die Transaktionen kleiner, so dass auch nur kurzzeitig Sperren erzeugt und möglichst schnell wieder freigegeben werden. Mit der folgenden Anweisung werden beispielsweise nur die ersten 10.000 Zeilen aktualisiert, bei denen das DeletedFlag noch auf NULL steht:

  UPDATE TOP (10000) p
SET DeletedFlag = 0
FROM dbo.Protokoll p (NOLOCK)
WHERE DeletedFlag IS NULL

Natürlich kann der Hinweis “NOLOCK” nicht dafür sorgen, dass auf der Tabelle gar keine Sperren stattfinde. Er sorgt aber dafür, dass die Zeilen bei der Ermittlung der zu ändernden Zeilen (also der Auswertung der WHERE-Klausel) noch nicht, sondern erst bei der eigentlischen Schreibaktion gesperrt werden. Wenn Sie die Anweisung erneut ausführen, werden die nächsten 10.000 Zeilen aktualisiert. Die Wahl der optimalen Schrittweite hängt von verschiedenen Faktoren ab und ist am besten herauszufinden, indem man verschiedene Werte ausprobiert und dabei im Aktivitätsmonitor die Suspended Processes beobachtet (unter “Prozesse” den Taskstatus auf “SUSPENDED” filtern). Es ist in der Regel unproblematisch, wenn hier ein paar auftreten, solange diese einerseits irgendwann wieder abgebaut werden können und andererseits die Menge nicht zu sehr anwächst, weil die blockierende Transaktion zu lange dauert. Zwischen den einzelnen Ausführungen der UPDATE-Anweisung sollte der Server die Gelegenheit bekommen, die bis dahin angestauten SUSPENDED-Prozesse abzubauen. Wenn man den UPDATE also in eine Schleife einbaut, sollte nach jeder Ausführung eine kleine Pause eingelegt werden. Davor wird über die Systemvariable @@ROWCOUNT ermittelt, wie viele Zeilen vom letzten UPDATE geändert wurden. Die Schleife wird nun solange ausgeführt, wie die Zahl größer 0 ist. Sobald der Wert 0 erreicht ist, sind alle Zeilen aktualisiert.

  DECLARE @Schrittweite INT  
  DECLARE @Zeilen INT  
  DECLARE @Pause DATETIME

  SET @Zeilen = 1 –  nur Startwert
  SET @Schrittweite = 5000
  SET @Pause = ’00:00:10′

  WHILE @Zeilen > 0

  BEGIN

    UPDATE TOP (@Schrittweite) p
    SET DeletedFlag = 0
    FROM dbo.Protokoll p (NOLOCK)
    WHERE DeletedFlag IS NULL

    SET @Zeilen=@@ROWCOUNT

    WAITFOR DELAY @Pause

  END

Wenn Sie nun noch ein Feedback über den Fortschritt der Ausführung haben möchten, ist eine Ausgabe sowohl per SELECT als auch per PRINT nicht sinnvoll, da beide nicht synchron ausgeführt werden. Sie würden die Meldung also erst verzögert oder vielleicht sogar erst dann sehen, wenn das Skript ohnehin fertig ist. Wenn man stattdessen die RAISERROR-Anweisung verwendet und damit eine reine Info-Meldung (Severity = 0) erzeugt, kann man das aber auch umgehen:

  DECLARE @Schrittweite INT
  DECLARE @Zeilen INT
  DECLARE @Pause DATETIME
  DECLARE @Meldung varchar(255)

  SET @Zeilen = 1 –  nur Startwert
  SET @Schrittweite = 5000
  SET @Pause = ’00:00:10′

  WHILE @Zeilen > 0

  BEGIN

    UPDATE TOP (@Schrittweite) p
    SET DeletedFlag = 0
    FROM dbo.Protokoll p (NOLOCK)
    WHERE DeletedFlag IS NULL

    SET @Zeilen=@@ROWCOUNT

    SET @Meldung=convert(varchar, getdate(), 114) + ‘ : ‘ + convert(varchar, @Zeilen) + ‘ Zeilen verarbeitet.’
    RAISERROR (@Meldung, 0, 0) WITH NOWAIT

    WAITFOR DELAY @Pause

  END

Nun kann das Skript problemlos laufen. Es empfiehlt sich aber trotzdem hin und wieder im Activity Monitor einen Blick auf die Suspended Processes zu werfen. Hier kann man gut beobachten, wie sich diese mit der Zeit anstauen und während der WAITFOR DELAY-Anweisung wieder abbauen. Sollte es hier Probleme geben, können Sie das Skript jederzeit unterbrechen (es wird dann nur der letzte begonnene UPDATE zurückgerollt) und die Werte für Schrittweite (wenn zu viele Suspended Processes auftreten) und Pause (wenn die Zeit nicht reicht, um die Suspended Processes abzubauen) anpassen. Nach einem Neustart des Skripts geht es dann wieder an derselben Stelle weiter.

Tipps & Tricks: Performantes Löschen von großen Datenmengen

Sicherlich stand fast jeder schon mal vor der Aufgabe, anhand einer vorgegebenen Bedingung Zeilen aus einer Tabelle zu löschen. Problematisch wird es aber dann, wenn die Tabelle sehr groß ist und die Löschbedingung auf ein Großteil der Zeilen zutrifft. Wenn zum Beispiel aus einer Log-Tabelle alle Einträge entfernt werden sollen, die nicht aus dem aktuellen Jahr sind, würde von der Logik dazu folgende SQL-Anweisung passen:

  DELETE FROM dbo.Protokoll
  WHERE year(CreatedAt)<year(getdate())

Das Problem dabei ist, dass die DELETE-Anweisung extrem lange läuft und auch das DB-Protokoll – insbesondere im Full-Recovery-Mode – dabei stark anwächst. Ein TRUNCATE als Alternative funktioniert hier nicht, da ja nicht alle Zeilen gelöscht werden sollen.

Ein besserer Lösungsansatz liegt darin, den kleinen Teil von Datensätzen, der beibehalten werden soll in eine separate Tabelle zu kopieren, danach die alte Tabelle zu löschen und schließlich die neue Tabelle umzubenennen:

  SELECT Id, CreatedAt, Beschreibung
INTO dbo.ProtokollNeu
FROM dbo.Protokoll
WHERE year(CreatedAt)=year(getdate())

  DROP TABLE dbo.Protokoll

  EXEC sp_rename ‘dbo.ProtokollNeu’, ‘Protokoll’

Das funktioniert soweit recht performant. Problem dabei ist, dass alle Indizes und weitere Eigenschaften (z.B. Identitätsspezifikation) der Protokolltabelle dabei verloren gehen, da sie beim SELECT INTO nicht mitkopiert werden. Um dies zu umgehen verwendet man eine temporäre Tabelle lediglich als Zwischenspeicher für die Zeilen, die erhalten bleiben sollen, löscht anschließend die Protokolltabelle per TRUNCATE komplett und verschiebt schließlich die Daten aus der temporären Tabelle zurück in die Protokolltabelle:

  SELECT Id, CreatedAt, Beschreibung
INTO #ProtokollTemp
FROM dbo.Protokoll
WHERE year(CreatedAt)=year(getdate())

  TRUNCATE TABLE dbo.Protokoll

  SET IDENTITY_INSERT dbo.Protokoll ON

  INSERT INTO dbo.Protokoll (Id, CreatedAt, Beschreibung)
SELECT Id, CreatedAt, Beschreibung
FROM #ProtokollTemp

  SET IDENTITY_INSERT dbo.Protokoll OFF

  DROP TABLE #ProtokollTemp

Bei dieser Variante müssen die zu erhaltenden Zeilen zwar zweimal bewegt werden, was aber – wenn es sich wirklich nur um einen kleinen Teil der Daten handelt – immer noch deutlich schneller sein sollte, als alle anderen Daten per DELETE zu löschen.

Ein Problem bleibt allerdings auch noch bei dieser Lösung: Wenn Foreign Key Constraints definiert sind, die auf die Protokoll-Tabelle verweisen, müssen diese vorher gelöscht werden, damit die TRUNCATE TABLE Anweisung funktioniert. Anschließend sollten auch die Daten in den referenzierenden Tabellen angepasst werden, damit diese nicht mehr auf inzwischen gelöschte Datenzeilen verweisen. Zuletzt können dann die Foreign Key Constraints wieder angelegt werden. Bei einer Protokoll-Tabelle ist dies aber normalerweise nicht der Fall, so dass das oben gezeigte Skript ausreichen sollte.

Follow

Erhalte jeden neuen Beitrag in deinen Posteingang.