Ausflug SQL Server

Relationale Datenbanken

Ich konnte mich in letzter Zeit mit Microsoft SQL Server auseinandersetzen. Wie im Produkt schon zu sehen ist, spricht dieser Server “SQL” (Structured Query Language). Auch wenn jedes RDBMS einen eigenen Dialekt hat, so sind die Gemeinsamkeiten doch sehr groß. Jemand der in PostgreSQL CRUD-Operationen schafft, wird es auch in SQL Server schaffen. Auch wird man viele Konzepte immer wieder finden:

  • Indextypen (Clustered, Non-Clustered)
  • Interne Funktionsweisen (Heap Tables, Pages, …)
  • Funktionsweise von Transaktion
  • Query Planer (Cardinality Estimation bzw. Table Statistics, …)

Lustigerweise bin ich mit dem Gedanken gleich gegen eine Wand gefahren. Bei gewissen Aufgabenstellungen ist es nicht mehr ausreichend, 10% der Werkzeuge eines Produkts zu kennen – man muss die Werkzeuge verstehen, die SQL Server bietet – und das ist eine Menge. Rückblickend zahlt es sich – je nach Problemstellung – durchaus aus, sich externe Hilfe zu holen. Jemanden zu holen, der eine breite Palette an Werkzeugen und Problemstellungen kennt und Tipps geben kann, mit welchen Werkzeugen man am meisten Chancen hat. Eine Tabelle mit 600 Millionen Datensätze mit diversen Operatoren zu aggregieren ist kein triviales Unterfangen. Vor allem, wenn die Abfrage durch ein Frontend angestoßen wird und der Endbenutzer sich zeitnah eine Antwort erwartet. Auch das Updaten von großen Datenmengen (2 Millionen Datensätzen) in einer Transkation, kann Kopfzerbrechen machen.

if all you have is a hammer, everything looks like a nail

Daten aggregieren

Hat man viele ähnliche Daten in einer Tabelle (600 Millionen) und muss z.B. 6 Millionen aggregieren, so stößt man recht schnell auf den Columnstore Indexes. “Normale” Indizes auf B-Tree Basis sind gut, wenn man wenige / einzelne Datensätze schnell finden muss. Columstore Indizes sind super, wenn man große Datenmengen über Spalten aggregieren muss. Ich konnte in großen Tabellen mit durchschnittlicher Hardware 6 Millionen Datensätze nach 5 Spalten in unter 4000 Millisekunden aggregieren – ohne CCI dauerte es mehr als 10-20 Mal so lange. Auch war die Größe der Tabelle und des Index plötzlich sehr klein.

Die Performance beim Ändern (UPDATE) von Daten ist relativ bescheiden. 100.000 Records dauerten 5 Sekunden (Row-Store auf selber Maschine war performanter). Das hängt anscheinend mit den Interna zusammen (Delta Store, Compress Vorgänge etc. http://www.nikoport.com bzw. Microsoft). Einfüge Vorgänge waren besser – allerdings erzeugen auch Overhead:

During a large bulk load, most of the rows go directly to the columnstore without passing through the deltastore. Some rows at the end of the bulk load might be too few in number to meet the minimum size of a rowgroup which is 102,400 rows. When this happens, the final rows go to the deltastore instead of the columnstore. For small bulk loads with less than 102,400 rows, all of the rows go directly to the deltastore.

Interessant fand ich auch die Sache mit den Batch-Mode. Auf einem Xeon (4 Kerne zugewiesen) kann man mit MAXDOP sehr schön damit spielen. Die Parallelisierung ist genial. Links die ich gefunden hab:

Zusammengefasst nochmal die wichtigsten Schlagworte rund um Indizes:

  • Heap Tables: “A heap is a table without a clustered index” – anders gesagt: Die Daten liegen wild durcheinander im Speicher. sqlpassion.at. Fürs ablegen von Log-Events hat es gut funktioniert.
  • Clustered Index: Ist ähnlich einem Lexikon: Daten werden nach dem Key sortiert und gespeichert. Logischerweise kann eine Tabelle nur 1 Clustered Index haben.
  • Nonclustered Index: Hier kann man mehrere pro Tabelle haben: Sie funktionieren (gleich wie der Clustered Index) über B-Trees – aber die Daten werden nicht nach dem Key sortiert und die Blätter sind keine Daten-Knoten sondern Index-Knoten. Zu beachten: Zusätzliche Indizes erhöhen die Leseperformance, verringern aber die Schreibperformance.
  • Columnstore Index Clustered und Nonclustered: siehe oben

Wann man welchen Index nutzen sollte steht auch hier gut zusammengefasst: https://www.amazon.de/gp/product/B01F5MYKOE/ - das Buch ist wirklich ein super Einstieg.

Wichtig ist noch zu beachten: “Use Narrow Indexes”. VARCHAR, CHAR, GUIDs in Indizes machen die Indizes unnötig groß. Ich konnte mit INT Schlüssel merkbar bessere Ergebnisse feststellen (je nach Aufgabenstellung möglich). Es gibt noch weitere Index-Arten – die hab ich mir aber nie angeschaut Microsoft. Des Weiteren ist ein großes Thema OLTP – dafür gibt es u.a. Memory Optimized Tables. Wenn mehrere Sessions eine Data-Page modifizieren wollen, werden sie über sogenannte Latches synchronisiert. In Memory ist eine Latch-Free Architektur und eignet sich daher für hohe Transaktionsraten (auch viele andere Details dürften anders umgesetzt sein). Hab ich allerdings auch nie ausprobiert.

Infrastruktur und Wait Statistics

Das nächste interessante Phänomen mit dem ich konfrontiert war: VMWare und SAN – u.a. nicht nachvollziehbare langsame Queries. Ist das SAN überhaupt schnell? Wieviel IOPS kann es? Sehr schnell findet man zwei Tools:

Einige Hilfsstellungen habe ich im Buch SQL Server Query Performance Tuning gefunden. Interessant wird es dann, wenn es um die Parameter der Tools geht. Diskspd hat sehr viele Parameter:

  • Was will man testen? Random Read?
  • Will man ein Transaction-Log Operationen testen?

Am Ende des Tages hatte ich ein paar Zahlenwerte und hab nach Referenzwerten gesucht – anscheinend wars ok – aber mehr konnte ich auch nicht rausfinden – weit davon entfernt, die Zahlenwerte sinnvoll zu interpretieren. Selbst hier kratzt man mit moderaten Aufwand nur an der Oberfläche. Den VMWare-Impact beurteilen – wo soll ich anfangen?

Hat man das Thema mit der Infrastruktur hinter sich gelassen, stößt man schnell auf Wait-Statistics. Eine sehr gute Seite dafür: sqlskills.com. Es gibt lustigerweise auch ein eigens Buch dafür Amazon – habs aber nie angeschaut – aber es muss wohl wichtig sein, wenn dem Thema wer ein Buch widmet ;-) Auf der SQL-Skills Seite findet man zumindest ein super Skript, welches die 0815 Wait-Statistics filtert und zu den möglicherweise relevanten dann einen erklärenden Link anzeigt. Sehr auffällig war ASYNC_NETWORK_IO - aber gut – nach Recherche von RBAR (Row-By-Agonizing-Row) wusste ich dann auch nicht mehr, wo genau das Problem ist. Der restliche Wert zeigte eine hohe (?) Wait-Time wegen dem Always-On (wenn vorhanden). Passt das so oder nicht? Konnte ich auch nicht beantworten …

Fazit: Sicher ein interessantes Instrument, wenn man es lesen kann.

Statistiken, Fragmentierung

Fragmentierung kann u.a. passieren, wenn Daten geändert (INSERT, DELETE, UPDATE) werden, und Pages (8KB) Datenlöcher haben. Das kann dann zu unnötigen Leseoperationen führen. Informationen findet man hier: brentozar.com. Diese Skript entscheidet, welche Strategie (e.g. REBUILD vs. REORGANIZE) angebracht ist: https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

Query Planer

Ein sehr wichtiges Instrument sind Statistics und Cardinality. Ab und zu hab ich gesehen, dass Estimated und Actual Rows weit auseinander gehen – updaten von Statistics hat geholfen – aber nicht immer. Dann kamen wieder diese Table-Spools – ich musste mit Sub-Queries arbeiten, um bessere Performance zu bekommen. Mein Fazit: Wenn man mit großen Daten arbeitet, sollte man sich den Query Plan anschauen. Selbst mit nicht wirklich vorhandenen Wissen sieht man zumindest schnell, wo die meiste Zeit verloren geht und kann dort eventuell die Daten vorfiltern. All in All ist das aber eine sehr, sehr komplexe Materie.

Snapshot Isolation

Ich war das erste Mail mit Deadlocks in SQL Server konfrontiert – während eines Lasttests. SQL Server bietet hier zwei interessante Methoden an:

  • Read Committed Snapshot Isolation: Wie im Video erklärt wird, ist das eine Optimistic Version des Read-Commited Isolation Level – welches Persimistic Concurrency verwendet. Der Vorteil ist, dass lesende Statements nicht mehr blockieren. Ein gutes Beispiel ist hier: blog.sqlauthority.com. Sich änderte Daten werden im Version Store gespeichert – dadurch wächst TempDB an. Ich habe beobachtet, dass die TempDB plötzlich > 100GB war – hier sollte man sich dann auch Gedanken machen.
  • Snapshot Isolation: Diese Feature erlaubt “Read Stability” – man bekommt innerhalb der Transaktion immer der gleichen Daten zurück: “SNAPSHOT isolation specifies that data read within a transaction will never reflect changes made by other simultaneous transactions”

Allgemein siehe:

Paging

SELECT * FROM TableName ORDER BY id OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

Das hat mit extrem komplexen Queries und vielen Datensätzen immer gut funktioniert – k.a. wie sie das intern machen – aber für Endless-Scrolling ging es immer schnell (auch wenn er initial mal ein paar Sekunden denken musste).

Gruppierungen extrem schnell machen

Eine interessante Herausforderung war, eine Gruppierung von Daten über die gesamte Datenbank (600 Millionen Datensätze) zu machen. Das Ergebnis wurde sehr oft aufgerufen. Mit Materialized Views ging das super.

CREATE TABLE Test
(
	FirstValue VARCHAR(255) NOT NULL,
	SecondValue VARCHAR(255) NOT NULL
)
GO

INSERT INTO Test VALUES
	('A', 'B'),
	('F', 'G'),
	('A', 'B'),
	('C', 'D'),
	('F', 'G');
GO

CREATE VIEW FilterView
WITH SCHEMABINDING
AS
	SELECT
		FirstValue, SecondValue, COUNT_BIG(*) AS count
	FROM
		dbo.Test
	GROUP BY
		FirstValue, SecondValue
GO

CREATE UNIQUE CLUSTERED INDEX UCI_Filter ON FilterView(FirstValue, SecondValue);
GO

Siehe auch blog.sqlauthority.com warum COUNT_BIG erforderlich ist.

CTE Recursion

CREATE TABLE Nachbarn
(
  Haus VARCHAR(2) NOT NULL,
  Nachbar VARCHAR(2) NOT NULL
)
GO
INSERT INTO Nachbarn VALUES
  ('A1', 'B1'),
    ('B1', 'C1'),
      ('C1', 'D1'),
      ('C1', 'D2'),
  ('A2', 'B2'),
    ('B2', 'C2');	

WITH NachbarListe AS
(
  SELECT
    Haus, 1 AS Level
  FROM
    Nachbarn
  WHERE
    Haus = 'A1' 

  UNION ALL

  SELECT
    n.Nachbar, l.Level + 1
  FROM
    Nachbarn AS n
  INNER JOIN
    NachbarListe AS l ON l.Haus = n.Haus
  WHERE
    l.Level <= 3
)
SELECT DISTINCT Haus FROM NachbarListe

Hat man eine Tabelle mit Nachbarn und will die rekursiv abfragen, so geht das mit CTE. Mit großen Datenmengen und vor allem großer Tiefe wird es leider langsam. Als Alternative hab ich Graph Datenbank gefunden – aber nie genutzt.

Große Datenmenge aus langsamen Source importieren

Ein interessantes Problem ist auch, wenn man große Datenmengen aus einem langsamen Source (e.g. über HTTP-REST) abfragen muss. Das kann durchaus eine Stunde dauern. Die Lösung mit zwischen Tabellen und nachgereichter Aufbereitung funktioniert super.

MERGE TargetTable target
USING (SELECT DISTINCT X FROM SourceTableImport) source
ON target.Key = source.Key
WHEN MATCHED THEN
  UPDATE SET ...
WHEN NOT MATCHED BY TARGET THEN
  INSERT (...) VALUES (...);

MERGE war mit großen Datenmengen sehr speicherhungrig – hab das aber nicht weiter untersucht. Achtung: MERGE ist nicht atomar.

Wichtige Skripts

Ein paar Skripts noch, die mehr hier und da geholfen haben:

  • Memory Grants: Hier sieht man anstehende Grants. Auf einem Shared Cluster hatte ich das Problem, dass ein komplexes Query 11h dauerte. 99% der Zeit wartete er auf den Speicher.
SELECT * FROM sys.dm_exec_query_memory_grants 
SELECT *
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Memory Manager%'
  • Aktuelle Queries: Ab und zu interessant, was gerade läuft:
SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time,
DB_NAME(req.database_id)
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
  • Duplikate Löschen:
WITH CTE AS(
   SELECT [col1], [col2], [col3], [col4], [col5], [col6], [col7],
       RN = ROW_NUMBER() OVER(PARTITION BY Col1, Col2, ... ORDER BY OrderColumn)
   FROM dbo.Table1
)
DELETE FROM CTE WHERE RN > 1
  • Super ist auch die Abfrage für die ungefähre Anzahl von Zeilen, ohne all zu großen Einfluss auf die Performance zu nehmen - vor allem interessant für Performance Tests:
SELECT  CAST(p.rows AS float)
  FROM sys.tables AS tbl
  INNER JOIN sys.indexes AS idx ON idx.object_id = tbl.object_id and idx.index_id < 2
  INNER JOIN sys.partitions AS p ON p.object_id=CAST(tbl.object_id AS int)
  AND p.index_id=idx.index_id
  WHERE ((tbl.name = N'MyTable'
  AND SCHEMA_NAME(tbl.schema_id)='dbo'))

Micro ORM vs Full-Blown ORM

Ich hatte das erste richtige Projekt mit einem Micro.ORM – in diesem Fall Dapper. Und ich glaube es war für dieses Projekt genau richtig. Sobald es über “einfaches” CRUD rausgeht und man mit der Performance kämpft, hat man einfach mehr Kontrolle, als z.B. bei Entity Framework. Dapper hat nie Probleme gemacht. Allerdings fand ich schade, dass es nie Column-Attribute gab – man musste die Namen der Klassen-Properties als Alias im Query übergeben.

Fazit

Hier schlägt das Pareto Prinzip wieder zu: 80% der Performance kann man wohl mit 20% des Aufwands raus holen – aber man muss eben die Werkzeuge kennen. Die Restlichen 20% tun weh. Ich würde sagen, wenn man frühzeitig erkennt, dass man mit dem Latein am Ende ist, sollte man sich externe Hilfe suchen. Es geht einfach extrem viel Zeit mit Halbwissen und experimentieren drauf.