Delayed Durability. Der Mythos über die Grenze des maximalen Datenverlusts

Delayed Durability ist ein Feature, mit dem man das D in ACID ausschalten bzw. verzögern kann (verfügbar ab SQL Server 2014). Mit Delayed Durability werden die Commits asynchron ausgeführt, der Client bekommt also eine Erfolgsmeldung bevor die Protokolldatensätze für die Transaktion auf den Datenträger geschrieben wurden.

MSDN sagt sehr deutlich, dass das Feature nur dann verwendet werden kann, wenn der Datenverlust aus Anwendungssicht akzeptabel ist. Manchmal trifft man im Netz Aussagen (SQL SentryOne, SQL Performance), dass der Datenverlust bei aktivierter Delayed Durability auf etwa 7Mb begrenzt ist. Diese Zahl kommt durch Multiplizierung der Größe eines Log Buffers (60Kb) mit der maximalen Anzahl der gestarteten, aber noch nicht abgeschlossenen IO Operationen (ab SQL Server 2012 max. 112 ausstehende IO Operationen. S. dazu die SQL PASS Session SQL Server Transaction Log Internals).

Diese Überlegung hat aber einen logischen Fehler. Falls eine Transaktion es nicht schafft, ihren Commit-Status (LOP_COMMIT_XACT Satz) zu persistieren, wird sie in der Rollbackphase während der Wiederherstellung zurückgerollt. Die gesamten Daten der Transaktion (und sie können beliebig groß sein) gehen dann verloren.

Damit es nicht bei der gedanklichen Übung bleibt, habe ich die Situation nachgespielt.

Aufbau der Testumgebung

Als Erstes benötigen wir eine virtuelle Maschine (z.B. Hyper-V) mit 2 virtuellen Festplatten.

image

In der Hyper-V habe ich den SQL Server installiert (2014 in diesem Beispiel, aber auch 2016 sollte sich identisch verhalten). Folgendes T-SQL Skript legt mir eine Datenbank an; die LDF-Datei (Transaction Log) wird auf der zweiten Festplatte angelegt, damit ich später ein langsames IO System und einen Systemcrash simulieren kann. Mit DELAYED_DURABILITY = FORCED verwenden alle Transaktionen das neue Feature. Das automatische Anlegen und Aktualisieren von Statistiken ist ausgeschaltet, um mögliche Nebeneffekte zu minimieren. In der Datenbank wird eine Tabelle mit einer Integer-Spalte angelegt.

CREATE DATABASE DelayedDurabilityTest ON PRIMARY 
( NAME = N'DelayedDurabilityTest', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\DelayedDurabilityTest.mdf' , SIZE = 204800KB , FILEGROWTH = 0)
LOG ON 
( NAME = N'DelayedDurabilityTest_log', FILENAME = N'E:\DelayedDurabilityTest_log.ldf' , SIZE = 30720KB , FILEGROWTH = 0)
GO
ALTER DATABASE [DelayedDurabilityTest] SET COMPATIBILITY_LEVEL = 120
ALTER DATABASE [DelayedDurabilityTest] SET AUTO_CREATE_STATISTICS OFF
ALTER DATABASE [DelayedDurabilityTest] SET AUTO_UPDATE_STATISTICS OFF 
ALTER DATABASE [DelayedDurabilityTest] SET RECOVERY SIMPLE 
ALTER DATABASE [DelayedDurabilityTest] SET DELAYED_DURABILITY = FORCED 
GO
USE [DelayedDurabilityTest]
CREATE TABLE SimpleTable(MyInt INT NOT NULL);

 

Um die Schreibaktivität im Transaction Log zu erzeugen, werden Datensätze in die Tabelle SimpleTable eingefügt. Es ist nicht ganz trivial auszurechnen, wie viel Transaction Log im Durchschnitt ein INSERT produzieren wird. Ich habe stattdessen die Größe der LDF-Datei auf 30 Mb begrenzt und habe die Datensätze eingefügt, bis nach dem 99846. INSERT die Fehlermeldung “Msg 9002, Level 17, State 4, Line 10. The transaction log for database ‘DelayedDurabilityTest’ is full due to ‘ACTIVE_TRANSACTION’” erschienen ist. Mit folgendem Skript führe ich in einer Transaktion 70000 INSERTs durch, was ungefähr 21 Mb Transaction Log entspricht.

SET NOCOUNT ON;
BEGIN TRAN
DECLARE @counter INT = 0;

WHILE @counter<70000
BEGIN
    INSERT dbo.SimpleTable VALUES(@counter);
    SET @counter=@counter+1;
END
COMMIT

 

Bevor ich das obige Skript ausführe, reduziere ich über den Hyper-V Manager die IO Performanz der virtuellen Festplatte, auf der sich der Transaction Log befindet, auf mickrige 3 IOPS (24 Kb pro Sekunde).

image

Das Skript wird über SQLCMD ausgeführt.

echo %time% > before.txt
copy before.txt \\HYPERVHOST\TestForDelayedDurability\before.txt
sqlcmd -S localhost -E -d DelayedDurabilityTest -i InsertSimpleTable.sql
echo %time% > after.txt
copy after.txt \\HYPERVHOST\TestForDelayedDurability\after.txt
rem /*SQLCMD returned, commit successful from client point of view. Shutdown VM*/
copy after.txt \\HYPERVHOST\TestForDelayedDurability\trigger_vm_shutdown.txt

 

Um den Systemcrash zu simulieren, habe ich zuerst versucht nach dem SQLCMD Aufruf in Hyper-V den SQL Server Dienst herunterzufahren (net stop MSSQLSERVER) oder mit Diskpart die Festplatte offline zu setzen (select disk 1 | offline disk).

Im ersten Fall hat der SQL Server Dienst gewartet, bis Protokolldatensätze doch auf die Festplatte geschrieben wurden, bevor er gestoppt wurde. Laut MSDN ist jedoch dieses Verhalten nicht garantiert:

“For delayed durability, there is no difference between an unexpected shutdown and an expected shutdown/restart of SQL Server. Like catastrophic events, you should plan for data loss. In a planned shutdown/restart some transactions that have not been written to disk may first be saved to disk, but you should not plan on it.”

Im zweiten Fall wurde die virtuelle Festplatte auch nicht sofort offline genommen. Vermutlich, weil zuerst die ausstehenden IO Kommandos vom SQL Server abgearbeitet wurden.

Um den Ablauf doch zeitlich möglichst eng zu machen und Reproduzierbarkeit zu erleichtern, kopiere ich nach dem SQLCMD Aufruf eine Datei “trigger_vm_shutdown.txt” auf das Host System. Auf dem Host läuft ein Powershell-Skript, das die Hyper-V herunterfährt und somit einen Systemcrash simuliert.

while (!(Test-Path 'C:\Temp\TestForDelayedDurability\trigger_vm_shutdown.txt')) { Start-Sleep -m 200 }
Remove-Item 'C:\Temp\TestForDelayedDurability\trigger_vm_shutdown.txt'
Stop-VM –Name SQL2014 –TurnOff

 

Nach dem erneuten Starten der virtuellen Maschine sieht man, dass die Transaktion, obwohl aus Clientsicht erfolgreich abgeschlossen, den Neustart doch nicht überlebt hat.

image

Fazit

Datenverlust bei Transaktionen, die im Delayed Durability-Kontext ausgeführt werden, ist nicht durch eine bestimmte Größe begrenzt und kann theoretisch beliebig groß sein. Deswegen kommt die Verwendung von Delayed Durability nur dann in Frage, wenn der Datenverlust der kompletten Daten solcher Transaktionen akzeptabel ist.

Update 2017-03-19: Den Datenverlust kann man auf unterschiedliche Weise definieren. Im Artikel ist unter Datenverlust der Fall gemeint, wenn die Benutzerdaten nach einem Ausfall nicht in dem Stand sind, den man nach einer aus Benutzersicht abgeschlossenen Transaktion erwarten würde. Wenn die Datenbank im Full Recovery Model betrieben wird, kann man die Daten einer aus SQL Server Sicht nicht abgeschlossenen Transaktion aus dem Transaction Log mit nicht zu unterschätzenden Aufwand rekonstruieren. Die letzten 7Mb (unter Umständen weniger) des Transaction Logs gehen endgültig verloren.

Redundante Prädikate als Optimierungsmittel für SQL-Abfragen

Meistens leistet SQL Server eine sehr gute Arbeit bei der logisch äquivalenten Umformulierung der Abfragen, aber wie mit jeder Technologie gibt’s Fälle, in denen eine algorithmische Optimierung nicht das bestmögliche Ergebnis liefert. In diesem Artikel zeige ich einen von diesen Fällen und wie man dem SQL Server helfen kann, einen besseren Ausführungsplan zu finden.

Setup

Als Spielwiese nutze ich ein fiktives Beispielschema mit 3 Tabellen: DimDate (Stichtage), FactPosition (mehrere Positionen pro Tag) und FactTrade (mehrere Trades pro Position an einem Tag). Die Definition der Tabellen sieht wie folgt aus.

/*This is a date table in integer format YYYYMMDD*/
CREATE TABLE dbo.DimDate
(
    DateId INT NOT NULL,
    CONSTRAINT PK_DimDate PRIMARY KEY (DateId)    
)

GO

/*Open positions per day. Position 4711 can be open for days 20160401, 20160402, and 20160403 etc.*/
CREATE TABLE dbo.FactPosition
(
    DateId INT NOT NULL,
    PositionId INT NOT NULL, 
    Currency CHAR(3) NOT NULL,
    Rating VARCHAR(10) NOT NULL,
    PresentValue FLOAT NOT NULL
    /*Other payload columns*/
    CONSTRAINT PK_FactPosition PRIMARY KEY (DateID, PositionID)
    FOREIGN KEY(DateID) REFERENCES dbo.DimDate(DateId)    
)

GO

/*Ongoing trades per day and position with their status at the end of the day, amount etc.
Position 4711 can have trades 1, 2, 3 on day 20160401 and 1, 3, 5 on day 20160402
*/
CREATE TABLE dbo.FactTrade
(
    DateID INT NOT NULL,
    PositionID INT NOT NULL, 
    TradeID INT NOT NULL, 
    TradeType CHAR(1) NOT NULL,
    IsCompleted BIT NOT NULL,
    Amount Float NOT NULL,
    /*Other payload columns*/
    CONSTRAINT PK_FactTrade PRIMARY KEY (DateID, PositionID, TradeID),
    FOREIGN KEY(DateID, PositionID) REFERENCES dbo.FactPosition(DateId, PositionId)    
)

Mit folgendem Skript befülle ich die Tabellen mit Beispieldaten:

/*This is just a numbers table with numbers from 1 to 10^5 filled with the 
same logic as by Itzik Ben-Gan http://sqlmag.com/sql-server/virtual-auxiliary-table-numbers */
CREATE TABLE dbo.Numbers
(
 Number INT NOT NULL
,CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number)
);

GO

DECLARE @number_of_numbers INT = 100000;

WITH 
a AS (SELECT 1 AS i UNION ALL SELECT 1), 
b AS (SELECT 1 AS i FROM a AS x, a AS y), 
c AS (SELECT 1 AS i FROM b AS x, b AS y),
d AS (SELECT 1 AS i FROM c AS x, c AS y), 
e AS (SELECT 1 AS i FROM d AS x, d AS y),
f AS (SELECT 1 AS i FROM e AS x, e AS y),
cte_numbers AS 
( 
    SELECT TOP(@number_of_numbers)
    ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS Number 
    FROM f 
)
INSERT dbo.Numbers 
SELECT *
FROM cte_numbers

GO

/*Produce 300 days in year 2015*/
DECLARE @start DATE = '2015-01-01'
,@number_of_days INT = 300;


WITH first_numbers AS
(
    SELECT TOP (@number_of_days) Number
    FROM dbo.Numbers
    ORDER BY Number
)    
INSERT dbo.DimDate
SELECT 
    YEAR(DATEADD(DAY, Number, @start)) * 10000 + 
    MONTH(DATEADD(DAY, Number, @start)) * 100 +
    DAY(DATEADD(DAY, Number, @start))
FROM first_numbers;

GO


WITH randomlyChosenPositionRanges AS
(
    /*
    The logic here is not that important. Just generating about 10000 positions per day.
    Their ids are not really random but tend heavily to be ascending, but that's not relevant 
    for this example.    
    */
    SELECT
        d.DateId
        ,a.Number
    FROM
        dbo.DimDate d
    CROSS APPLY (SELECT TOP 10000 * FROM dbo.Numbers WHERE Number>(ABS(CHECKSUM(d.DateId, NEWID()))%100000)) a
)
INSERT dbo.FactPosition
SELECT
    randomlyChosenPositionRanges.DateId
    ,randomlyChosenPositionRanges.Number AS PositionId
    ,'EUR' AS Currency
    ,'AAA' AS Rating
    ,123456.78 AS PresentValue
FROM randomlyChosenPositionRanges


GO

WITH randomlyChosenTradeRanges AS
(
    /*
    Same as for positions. Generate about 2 trades for each position.
    This method is not quite koscher because it results in trade 73 belonging to different positions 
    even on different days, which is nonsense from business point of view. 
    But it's just ids, we can live with it for this case.
    */
    SELECT
        fp.DateId
        ,fp.PositionId
        ,a.Number
    FROM
        dbo.FactPosition fp
    CROSS APPLY (SELECT TOP 2 * FROM dbo.Numbers WHERE Number>(ABS(CHECKSUM(fp.DateId, fp.PositionId, NEWID()))%100000)) a
)
INSERT dbo.FactTrade
SELECT
    randomlyChosenTradeRanges.DateId
    ,randomlyChosenTradeRanges.PositionId
    ,randomlyChosenTradeRanges.Number AS TradeID    
    ,'R' AS TradeType
    ,1 AS IsCompleted
    ,987.65 AS Amount
FROM randomlyChosenTradeRanges

Zugriff auf die Daten

Für die Abfrage, die alle Trades mit Positionsdaten für einen bestimmten Stichtag ermittelt, produziert SQL Server einen recht plausiblen Ausführungsplan.

DECLARE @dueday INT = 20151028;
SELECT
    fp.*
   ,ft.TradeID
   ,ft.IsCompleted
   ,ft.Amount
FROM
    dbo.FactPosition fp
INNER JOIN dbo.FactTrade ft ON 
    ft.DateID=fp.DateId
    AND ft.PositionID=fp.PositionId
WHERE
    fp.DateId=@dueday;

 

image

Die Einschränkung auf den Stichtag wird dabei auf den Leseoperator der FactPosition übertragen (Predicate Pushdown), so dass nur der relevante Teil des Clustered Indexes mit dem Seek gelesen wird. Da die Tabellen mit INNER JOIN zusammengeknüpft werden, kann diese Einschränkung auch auf FactTrade erweitert werden (transitive Hülle).

Eine leicht andere Bedingung – Filterung auf den letzten vorhandenen Tag – erzeugt einen völlig anderen Ausführungsplan.

SELECT
    fp.*
   ,ft.TradeID
   ,ft.IsCompleted
   ,ft.Amount
FROM
    dbo.FactPosition fp
INNER JOIN dbo.FactTrade ft ON 
    ft.DateID=fp.DateId
    AND ft.PositionID=fp.PositionId
WHERE
    fp.DateId=(SELECT MAX(DateId) FROM dbo.DimDate);

 

image

Diesmal wurde nur eine Tabelle (FactTrade) für den gewählten Stichtag gelesen. Die FactPosition las SQL Server komplett (Clustered Index Scan vs. Seek), was sich auch sofort bei den logical Reads und Laufzeit bemerkbar macht.

Wenn man jetzt dem SQL Server ein wenig unter die Arme greift und die Bedingung auf FactTrade explizit erweitert, dann sieht der Ausführungsplan wieder deutlich besser aus.

SELECT
    fp.*
   ,ft.TradeID
   ,ft.IsCompleted
   ,ft.Amount
FROM
    dbo.FactPosition fp
INNER JOIN dbo.FactTrade ft ON 
    ft.DateID=fp.DateId
    AND ft.PositionID=fp.PositionId
WHERE
    fp.DateId=(SELECT MAX(DateId) FROM dbo.DimDate)    
    AND ft.DateId=(SELECT MAX(DateId) FROM dbo.DimDate);

 

image

Unter SQL Server 2016 sieht die nicht optimierte Variante etwas anders aus (weniger CPU Zeit, mehr logische Reads), erreicht aber nicht die Qualität vom manuellen Fix.

image

Fazit

Mit jeder Version kann SQL Server die Abfragen immer besser verstehen und verarbeiten. In diesem Artikel wurde gezeigt, wo in der algorithmischen Logik noch Lücken bestehen und wie man mit ein paar WHERE-Bedingungen – die fürs menschliche Auge völlig überflüssig sind – SQL Server beibringen kann, bestimmte Abfragen deutlich effizienter auszuführen.

Zum Schluss möchte ich mich bei meinem guten Freund Martin Preiss für die Hilfe bei der Erklärung des Phänomens sowie für die Analyse des Verhaltens aus Oracle Sicht bedanken.

SQL Server 2016 SP 1: Standard Edition wieder interessant

In den letzten Jahren hat Microsoft die Standard Edition eher stiefmütterlich behandelt: die meisten neuen Features flossen in die Enterprise Edition und waren, wenn überhaupt, in den günstigeren Varianten in einer sehr abgespeckten Form vorhanden. Mit SQL Server 2016 Service Pack 1 macht Microsoft eine ganze Reihe von Features, die früher nur in der teuersten Enterprise Edition enthalten waren, auch in Standard, Web, Express und LocalDB Editions verfügbar. Das wird für viele Administratoren, die bis jetzt ältere SQL Server Standard Versionen betrieben haben, doch ein guter Grund sein, den SQL Server zu aktualisieren. Auch dem Trend “Entwicklung für das kleinste gemeinsame Feature-Set“ wird dadurch entgegengewirkt.

Folgende Feature kommen somit in Standard Edition dazu:

  • Always Encrypted
  • Change Data Capture
  • Columnstore
  • Data Compression
  • Database Snapshots
  • Dynamic Data Masking
  • Fine grained Auditing
  • In-Memory OLTP
  • Multiple Filestream Containers
  • Partitioning
  • PolyBase
  • Row-Level Security

Selbstverständlich sind aber weiterhin manche Features nur in der Enterprise Edition verfügbar. Zum Beispiel:

  • Online Operationen (wie ALTER INDEXREBUILD WITH (ONLINE = ON))
  • Availability Groups / Failover Cluster Instances ohne Einschränkungen (read-only Replicas, mehr als 2 Clusterknoten)
  • Resource Governor
  • Parallelism und DWH Optimierungen (Star Join Optimierung, Advanced (“merry-go-round”) Scans) usw.

Auch Einschränkungen bei CPU und RAM (max. 4 Sockets oder 24 Kerne, max. 128Gb RAM für Standard Edition) gelten weiterhin. Die Details sind auf MSDN zu finden.

Dies ist aber nicht die einzige Neuerung in SP1. Auch ein paar lang ersehnte Features wie CREATE OR ALTER Syntax oder eine punktuelle Aktivierung von Trace Flag 4199 ohne sysadmin Berechtigungen wurden umgesetzt. Die Details dazu können auf dem SQL Server Release Services Blog nachgelesen werden.

Analytics Platform System: Adaptive Query Processing

Schon in der Vergangenheit war es oft so, dass viele neue Features in SQL Server erst im “großen Bruder” APS (Analytics Platform System aka Parallel Data Warehouse) implementiert und ein paar Jahre später in den SQL Server Code migriert wurden (z.B. Columnstore Indexes oder Anbindung an Big Data Systeme – Polybase). Wenn man also die APS Ankündigungen verfolgt, kann man grob erahnen, was uns im übernächsten SQL Server Release erwartet.

Vor kurzem fand ein neues APS Release mit einer interessanten Preview Funktion statt:

Analytics Platform System Appliance Update 5

In addition to the above, we are also offering an early preview of Adaptive Query Processing which can automatically re-optimize query execution mid-flight. Please note that Adaptive Query Processing is currently in beta. Any customers wishing to participate in the beta should contact their support representative.

Das würde bedeuten, dass die Engine z.B. nicht mehr hartnäckig versucht, tausendfach dieselben Daten zu lesen, nur weil sie sich in der Kardinalitätsschätzung verschätzt und für den Ausführungsplan einen suboptimalen Join Algorithmus genommen hat, sondern schaltet nach einem bestimmten Grenzwert automatisch vom Nested Loop Join auf ein Hash Join um. Auch andere Optimierungen z.B. in den Aggregatfunktionen oder im Parallelisierungsgrad sind denkbar.

Ganz neu ist die Idee nicht, Oracle hat ähnliche Funktionalitäten – Adaptive Plans und Adaptive Statistics – schon seit der Version 12.1 (Release 2013) im Arsenal.

Es bleibt noch genug Zeit bis zum Release von SQL Server 2018 (ungefähr 2 Jahre), in wenigen Wochen kommt erst die erste offizielle Version von SQL Server 2016.

Hash Join Hint und seine Konsequenzen

Grundsätzlich gilt, dass Query Hints nur als letzter Ausweg verwendet werden sollen und man SQL Server die Informationen über die zu Grunde liegenden Daten liefern soll, sodass er in der Lage ist, selbst einen optimalen Ausführungsplan zu finden.

Das in diesem Artikel beschriebene Problem ist auf einem älteren Kundensystem mit SQL Server 2008R2 aufgetreten.

Als Beispiel nehme ich wie gewohnt eine Adventure Works DWH Datenbank von Codeplex, die ich mit dem Skript von Bob Duffy auf eine realistische Größe vergrößert habe.

Folgende Abfrage liefert die Umsätze aggregiert nach Ländern und Promotionen zurück.

SELECT
    dst.SalesTerritoryCountry
    ,dp.EnglishPromotionName
    ,SUM(fis.SalesAmount) AS SalesAmount
FROM
    dbo.DimPromotion dp
    INNER JOIN dbo.DimSalesTerritory dst
    INNER JOIN dbo.FactInternetSalesBig fis 
ON fis.SalesTerritoryKey = dst.SalesTerritoryKey 
ON fis.PromotionKey = dp.PromotionKey
GROUP BY dst.SalesTerritoryCountry, dp.EnglishPromotionName;

 

Für den gesamten Zeitraum, ohne jegliche Einschränkung, wird die Abfrage mit folgendem Ausführungsplan ausgeführt:

image

Dieser führt dann zu folgenden IO-Aktivitäten:
Table ‘DimSalesTerritory’. Scan count 1, logical reads 2
Table ‘Worktable’. Scan count 0, logical reads 0
Table ‘FactInternetSalesBig’. Scan count 41, logical reads 230944
Table ‘Worktable’. Scan count 0, logical reads 0
Table ‘DimPromotion’. Scan count 0, logical reads 46

Im Großen und Ganzen ein üblicher Ausführungsplan für ein Sternschema: Parallelität, Bitmap-Indexes und Hash Match-Operatoren für Joins sind alle vorhanden.

Jetzt simulieren wir den Fall von nicht aktuellen Statistiken dadurch, dass wir für einen noch nicht vorhandenen Tag (OrderDateKey=20080801) 200K neue Datensätze einfügen. Das triggert noch kein automatisches Aktualisieren von Statistiken, da der Schwellenwert 500 Zeilen+20% der aktuellen Sätze nicht erreicht wird.
Der Ausführungsplan der Abfrage mit dem Filter auf OrderDateKey=20080801 sieht jetzt ganz anders aus:

SELECT
    dst.SalesTerritoryCountry
    ,dp.EnglishPromotionName
    ,SUM(fis.SalesAmount) AS SalesAmount
FROM  
    dbo.DimPromotion dp
    INNER JOIN dbo.DimSalesTerritory dst
    INNER JOIN dbo.FactInternetSalesBig fis 
ON fis.SalesTerritoryKey = dst.SalesTerritoryKey 
ON fis.PromotionKey = dp.PromotionKey
WHERE fis.OrderDateKey=20080801
GROUP BY dst.SalesTerritoryCountry, dp.EnglishPromotionName

 

image

Keine Bitmap-Indexes und Zwischenaggregation mehr und aus dem ersten Hash Match (Join der Faktentabelle mit der DimPromotion) ist ein Nested Loops Operator geworden. Der Grund dafür ist selbstverständlich eine viel zu niedrige Kardinalitätsschätzung für die relevanten Datensätze der Faktentabelle (geschätzte Anzahl der Datensätze – 1, tatsächlich – 200000).
Die Auswirkung auf den IO-Zugriff ist nicht zu übersehen:
Table ‘FactInternetSalesBig’. Scan count 41, logical reads 232835
Table ‘DimSalesTerritory’. Scan count 0, logical reads 400000
Table ‘DimPromotion’. Scan count 0, logical reads 400000
Table ‘Worktable’. Scan count 0, logical reads 0

Idealerweise würde man in diesem Fall die Statistiken aktualisieren, aber nehmen wir an, dass man sich stattdessen für ein explizites Hash Join Hint entschieden hat:

SELECT
    dst.SalesTerritoryCountry
    ,dp.EnglishPromotionName
    ,SUM(fis.SalesAmount) AS SalesAmount
FROM  
    dbo.DimPromotion dp
    INNER HASH JOIN dbo.DimSalesTerritory dst
    INNER HASH JOIN dbo.FactInternetSalesBig fis 
ON fis.SalesTerritoryKey = dst.SalesTerritoryKey 
ON fis.PromotionKey = dp.PromotionKey
WHERE fis.OrderDateKey=20080801
GROUP BY dst.SalesTerritoryCountry, dp.EnglishPromotionName

 

Auf den ersten Blick sieht das Ergebnis einigermaßen gut aus:
image

Table ‘DimPromotion’. Scan count 1, logical reads 3
Table ‘DimSalesTerritory’. Scan count 1, logical reads 2
Table ‘Worktable’. Scan count 0, logical reads 0
Table ‘FactInternetSalesBig’. Scan count 41, logical reads 232835
Table ‘Worktable’. Scan count 0, logical reads 0

Doch was passiert, wenn wir uns nur für die Umsätze in Deutschland interessieren (dst.SalesTerritoryKey=8)? Mit dem zusätzlichen Filterprädikat verweigert der SQL Server die Ausführung der Abfrage mit dem Hash Join Hint:
Msg 8622, Level 16, State 1, Line 1
Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.

Die etwas kryptische Fehlermeldung hat eine einfache Erklärung. SQL Server versucht die Filterprädikate in dem Ausführungsplan möglichst weit nach unten zu schieben. In den meisten Fällen ist es eine sehr sinnvolle Performanzoptimierung, da nicht relevante Datensätze möglichst früh eliminiert werden. In diesem Fall führt die Optimierung aber dazu, dass das Filterprädikat in den Clustered Index Scan (DimSalesTerritory) integriert wird und der folgende (erzwungene) Hash Join-Operator keine Werte für die Hashoperation hat (die Spalte SalesTerritoryKey wird aus dem Datenfluss komplett eliminiert).
In diesem kleinen Beispiel wäre es möglich, den Join mit DimSalesTerritory komplett zu entfernen und stattdessen auf fis.SalesTerritoryKey=8 zu filtern. Das ist aber nicht immer machbar, wenn Join Hints in Views/Table Valued Functions “versteckt” sind.

Die aktuelleren Versionen (SQL Server 2014/2016) sind übrigens nicht so leicht aus dem Tritt zu bringen. Das Filterprädikat wird immer noch weiter unten, in dem Clustered Index Scan Operator platziert, aber die für das Hash Join notwendige Spalte SalesTerritoryKey wird nicht aus dem Datenfluss entfernt und die Abfrage wird erfolgreich ausgeführt.

Eine weitere wenig bekannte Eigenschaft von Join Hints ist, dass sie auch die Join Reihenfolge festlegen (wie ein explizites FORCE ORDER Hint).
Wenn wir die Tabellenreihenfolge in der Abfrage mit Hints ändern,

SELECT
      dst.SalesTerritoryCountry
      ,dp.EnglishPromotionName
      ,SUM(fis.SalesAmount) AS SalesAmount
FROM  
    dbo.DimPromotion dp
    INNER HASH JOIN dbo.FactInternetSalesBig fis 
    INNER HASH JOIN dbo.DimSalesTerritory dst    
ON fis.SalesTerritoryKey = dst.SalesTerritoryKey 
ON fis.PromotionKey = dp.PromotionKey
WHERE OrderDateKey=20080801
GROUP BY dst.SalesTerritoryCountry, dp.EnglishPromotionName

 

wird sie einen anderen Ausführungsplan erzeugen

image
Dieser macht allerdings viel weniger Sinn als die ursprüngliche Fassung (Hash Build auf kleinen Dimensionstabellen, Probing auf der großen Faktentabelle ist CPU- und RAM-technisch eine viel effizientere Lösung).

Auch hier sind die aktuellen SQL Server Versionen benutzerfreundlicher und produzieren eine explizite Warnung:
Warning: The join order has been enforced because a local join hint is used.

Fazit:
Query Hints nehmen dem SQL Server die Flexibilität, eine deklarativ definierte Abfrage optimal auszuführen und bergen manchmal auch andere nicht offensichtliche Nebeneffekte. Manchmal ist ein Query Hint tatsächlich die einzige Lösung, vorher sollte man aber weniger invasive Möglichkeiten ausschließen.

Links:
http://blogs.msdn.com/b/craigfr/archive/2009/04/28/implied-predicates-and-query-hints.aspx

SQL Server 2016: Verbesserungen in DBCC CHECK* Operationen

SQL Server 2016 kommt nicht nur mit vielen neuen Features wie z.B. Query Store oder JSON Unterstützung, auch unter der Haube werden einige alte Funktionalitäten neu aufgebaut und optimiert.

So wurden DBCC CHECK* Operationen (Prüfungen der physischen und logischen Konsistenz der Datenbank) wesentlich besser skalierbar und effizienter gemacht (Details sind unter SQL 2016 – It Just Runs Faster: DBCC Scales 7x Better und SQL 2016 – It Just Runs Faster: DBCC Extended Checks zu finden).

Man kann auch endlich den Parallelisierungsgrad der DBCC Kommandos mit MAXDOP Einstellungen steuern (Connect Ticket), was zu deutlich besseren DBCC CHECK* Laufzeiten auf OLTP Systemen, die oft mit MAXDOP=1 laufen, führen wird (Sharepoint ist ein Paradebeispiel dafür).

Das erste Release Candidate ist seit wenigen Tagen zum Herunterladen verfügbar. Es lohnt sich auf jeden Fall einen Blick darauf zu werfen.

Tipp für performantes SQL Server Monitoring

Ein paar Hundert SQL-Server Instanzen (und viele andere Server) werden vom kundeninternen Betriebsteam mit Nagios überwacht. Das Team hat sich beschwert, dass auf einigen SQL Servern die Überwachungsskripte sporadisch spürbar länger brauchen. Dabei sollen die Skripte sehr harmlos sein und nur ein paar Metriken wie “Verfügbarer Speicher in DB” oder “Größe der Log-Datei” abfragen.

Nagios ist ein Open Source Monitoring Framework (vergleichbar mit SCOM, System Center Operations Manager von Microsoft), das durch diverse Plug-Ins zur Überwachung von unterschiedlichen Diensten (DNS, DHCP, Exchange), unter anderem auch SQL Server Instanzen erweitert werden kann.

Die Abfrage des Views sys.dm_tran_locks hat ergeben, dass das Skript auf eine Ressource vom Typ KEY in tempdb wartet. Warum ausgerechnet der Zugriff auf tempdb ein Problem ist, wurde beim Blick auf den Skriptcode klar.

Folgendes Konstrukt führt zu Locks auf tempdb Objekten:

IF EXISTS ( SELECT * FROM tempdb..sysobjects WHERE name LIKE '#LogSpaceStats%')

BEGIN

    DROP TABLE #LogSpaceStats;

END;

Dieses Muster kommt leider relativ häufig vor und wird immer wieder auf Blogs und Foren empfohlen (z.B. SQL Authority oder MSDN Forum). Mit folgendem Versuch kann man im SQL Server Management Studio nachvollziehen, dass das Anlegen einer temporären Tabelle in einer Transaktion die oben erwähnte Abfrage blockt:

Tab/Session 1

BEGIN TRAN

SELECT object_id INTO #harmlose_temp_tabelle FROM sys.objects;

/* Oder CREATE TABLE #harmlose_temp_tabelle(objectid INT);*/

-- ROLLBACK

Tab/Session 2

IF EXISTS ( SELECT * FROM tempdb..sysobjects WHERE name LIKE '#LogSpaceStats%')

BEGIN

    DROP TABLE #LogSpaceStats;

END;

PRINT 'Erst jetzt fertig'

Das SELECT-Statement muss dabei die Systemtabelle sysschobjs lesen und wird geblockt, wenn in einer anderen Transaktion ein temporäres Objekt angelegt oder gelöscht wird.

Außer diesem unschönen Verhalten mit unnötigen Locks hat das Konstrukt andere Mankos:

  • Offensichtlich nicht parallel ausführbar, da #LogSpaceStats-Tabellen in anderen Sessions auch gefunden werden. Der Versuch die temporäre Tabelle in der aktuellen Session zu droppen schlägt dann fehl.
  • Findet auch andere temporäre Tabellen (falls so wie oben die sysobjects DMV angesprochen wird, auch andere Objekte wie z.B. temporäre SPs), die überhaupt nichts mit dem Skript zu tun haben. Z.B. eine temporäre Tabelle “#LogSpaceStats_this_is_not_the_table_you_are_looking_for” wird mit dem Pattern ‘#LogSpaceStats%’ auch gefunden. Der DROP-Versuch schlägt dann fehl.

Folgende Prüfung braucht so gut wie keine Locks und besitzt auch keine der zwei oben erwähnten Schwächen:

IF OBJECT_ID('tempdb..#LogSpaceStats') IS NOT NULL

BEGIN

    DROP TABLE # LogSpaceStats;

END;

SQL Server 2016 (aktuell als CTP 3.2) bietet noch mehr Komfort und unterstützt die Syntax DROP TABLE IF EXISTS …, damit werden die oben genannten Probleme früher oder später der Vergangenheit angehören.

Blick über den Tellerrand:

Oracle hat auch ein Konzept von temporären Tabellen. Sie werden allerdings deutlich seltener in expliziter Form eingesetzt als in SQL Server, da Oracle viele Probleme, für die in SQL Server temporäre Tabellen verwendet werden, anders löst (z.B. mit Subquery Materialisierung, was aber auch nicht immer automagisch funktioniert).

Auch die Funktionalität von temporären Tabellen unterscheidet sich. So werden sie in der Regel statisch deklariert und können die Daten entweder bis zum nächsten COMMIT (ON COMMIT DELETE ROWS) oder bis die Session geschlossen wird (ON COMMIT PRESERVE ROWS) persistieren. Gleichzeitige Sessions sehen natürlich nur eigene Daten.

Dadurch, dass die Tabelle vorab deklariert wird und nicht ständig gedropped und neu angelegt werden muss, vermeidet man eine unschöne Vermischung von DDL (CREATE/DROP) und DML (INSERT/UPDATE/DELETE) Operationen in einer Ausführung.

Sequentielle vs parallele Ausführung von SSRS Datasets

Wie allgemein bekannt, werden die einzelnen Datasets in SSRS parallel ausgewertet, soweit eine Parallelisierung überhaupt möglich ist.

Wenn ein Dataset von einem Parameter abhängt, der von einem anderen Dataset ermittelt wird, ist selbstverständlich nur eine sequentielle Ausführung möglich. Auf dem Screenshot unten (SQL Server Profiler Log für einen einfachen Bericht, der Internet Sales aus der Adventure Works Datenbank nach Farbe und Größe anzeigt) sieht man, dass die SQL-Abfragen in der Tat gleichzeitig ausgeführt werden.

image

Manchmal braucht man allerdings eine sequentielle Ausführung der DataSets, wenn die Abfragen Nebeneffekte haben. Klassisches Beispiel: Man möchte in der Abfrage des ersten Datasets die Zwischenergebnisse vorberechnen und in der Datenbank persistieren, die von mehreren Datasets des Reports wiederverwendet werden. Dafür kann man eine wenig bekannte Eigenschaft der SSRS Datasources nutzen – Use Single Transaction. Dann werden die Datasets in der Reihenfolge abgefragt, in der sie im Report stehen (bei Bedarf die Reihenfolge in der .rdl Datei im Editor anpassen). Das Ergebnis sieht man auf dem Screenshot unten.

image

Aufpassen muss man mit der Preview-Funktion in SSTDT-BI (aka BIDS). Sie führt nämlich die Abfragen immer sequentiell aus und ist aus diesem Grund zum Testen der Abfragen mit Nebeneffekten oder Performanzmessungen nicht geeignet.

CPU Upgrade eines SSAS Servers: Lessons Learned

Nach Aufrüstung eines SSAS-Servers um eine weitere CPU (initial ein Dual-Socket Rechner, aus Kostengründen wurde nur eine CPU eingebaut) ist die Cubeverarbeitung nicht schneller, sondern spürbar langsamer geworden. Die Verlangsamung äußerte sich durch mehrminütige Pausen, während deren der Server scheinbar nichts tat.

Da es sich um eine ältere 2008R2-Instanz handelte, war die erste Hypothese, dass es an der fehlenden NUMA-Unterstützung liegen könnte (Details hier und hier nachzulesen), allerdings passte die Vermutung nicht zu dem ruckeligen Verarbeitungsablauf.

Tatsächlich waren die Übeltäter in der msmdsrv.ini Datei versteckt. Sie enthält viele wichtige SSAS-Konfigurationsparameter (manche sind auch undokumentiert). Außer MSDN liefert der Analysis Services Operations Guide wertvolle Informationen zu den einzelnen Konfigurationsparametern (sehr empfohlen zusammen mit dem Analysis Services Performance Guide).

Die optimalen Einstellungen für manche Parameter wie "ThreadPoolProcessMaxThreads" oder "ThreadPoolQueryMaxThreads" sind abhängig von der Anzahl der CPU-Kerne auf dem Server. Diese Einstellungen werden bei der SSAS-Installation initialisiert, nach der CPU-Aufrüstung wurde aber vergessen auch die msmdsrv.ini entsprechend anzupassen.

SSAS entschied sich ausgehend von verfügbaren Hardware Ressourcen (CPU, RAM) für eine höhere Parallelisierung in der Verarbeitung. Das führte im Endeffekt dazu, dass oft keine zusätzlichen Threads angelegt werden konnten. Verfügbare Threads wurden aber schon für die Steuerung verwendet. Dadurch ist im Endeffekt die ganze Verarbeitung ins Stocken geraten.

Besonders deutlich war dies im Performance Monitor ersichtlich. Die kumulierten Werte der Indikatoren "Processing Pool Idle Threads", "Processing Pool Busy Threads" und "Processing Pool Job Queue Length" lagen über einen langen Zeitraum gleich oder knapp über dem konfigurierten Wert für "ThreadPoolProcessMaxThreads."

ssas_process_maxthreads

Nach Verdopplung der MaxThreads in den Einstellungen waren die Aussetzer verschwunden und die Verarbeitung lief um 30% schneller, als mit einer CPU.

Hilfreiche Links zum Thema: