Der von mir sehr geschätzte Kollege und Kenner der SQL Server Engine Torsten Strauss kam mit einer sehr interessanten Beobachtung auf mich zu. Dabei ging es um die Frage, wann Statistiken aktualisiert werden, wenn für die Datenbank die entsprechende Option aktiviert ist. Dieser Artikel zeigt, dass es bestimmte Situationen gibt, in denen eine automatische Aktualisierung der Statistiken nicht durchgeführt wird.

Statistiken

Der Abfrageoptimierer verwendet Statistiken zum Erstellen von Abfrageplänen, die die Abfrageleistung verbessern. In den meisten Fällen generiert der Abfrageoptimierer automatisch die erforderlichen Statistiken; in anderen Fällen müssen weitere Statistiken erstellen werden, um optimale Ergebnisse zu erzielen. Statistiken können veraltet sein, wenn die Datenverteilung in der Tabelle durch Datenänderungsvorgänge geändert wird.

Wenn die Option „AUTO_UPDATE_STATISTICS“ aktiviert ist, prüft der Abfrageoptimierer, wann Statistiken veraltet sein könnten, und aktualisiert diese Statistiken, sobald sie von einer Abfrage verwendet werden. Der Abfrageoptimierer stellt fest, wann Statistiken veraltet sein könnten, indem er die Anzahl der Datenänderungen seit des letzten Statistikupdates ermittelt und sie mit einem Schwellenwert vergleicht. Der Schwellenwert basiert auf der Anzahl von Zeilen in der Tabelle oder indizierten Sicht. Pauschal gilt eine Statistik als veraltet, wenn mehr als 20% + 500 Datenänderungen durchgeführt wurden. Weitere Informationen zu den Schwellenwerten finden sich hier: https://support.microsoft.com/de-de/kb/195565.

Hinweis

Im nachfolgenden Artikel werden Traceflags verwendet, die nicht von Microsoft dokumentiert sind. Es wird darauf hingewiesen, dass eigene Beispiele nicht in einer Produktionsumgebung ausgeführt werden. Folgende Traceflags werden in den Codes verwendet:

• 3604: Aktiviert die Ausgabe von Meldungen in den Client statt ins Fehlerprotokoll
• 9204: Zeigt die für den Abfrageoptimierer „interessanten“ Statistiken, die geladen werden
• 9292: Zeigt die Statistiken an, die der Abfrageoptimierer in der Kompilephase für „interessant“ hält
• 8666: Speichert Informationen über verwendete Statistiken im Ausführungsplan

Testumgebung

Das die obige Aussage bezüglich der Aktualisierung von Statistiken nicht pauschal angewendet werden kann, zeigt das nachfolgende Beispiel. Dazu wird eine Tabelle [dbo].[Customer] angelegt und mit ~10.500 Datensätzen gefüllt. Die Tabelle [dbo].[Customer] besitzt zwei Indexe; zum einen wird ein eindeutiger Clustered Index auf dem Attribut [Id] verwendet und zum anderen wird das Attribut [ZIP] mit einem nonclustered Index versehen.

-- Create the demo table 
IF OBJECT_ID(N'dbo.Customer', N'U') IS NOT NULL
   DROP TABLE dbo.Customer;
   GO

CREATE TABLE dbo.Customer  
(
   Id     INT          NOT NULL IDENTITY (1, 1),  
   Name   VARCHAR(100) NOT NULL,  
   Street VARCHAR(100) NOT NULL,  
   ZIP    CHAR(5)      NOT NULL,  
   City   VARCHAR(100) NOT NULL  
);
GO  

-- and fill it with ~10,000 records 
INSERT INTO dbo.Customer WITH (TABLOCK)
(Name, Street, ZIP, CIty)
SELECT 'Customer ' + CAST(message_id AS VARCHAR(10)),  
       'Street ' + CAST(severity AS VARCHAR(10)),  
       severity * 1000,  
       LEFT(text, 100)  
FROM   sys.messages  
WHERE  language_id = 1033;  
GO  

-- than we create two indexes for accurate statistics 
CREATE UNIQUE INDEX ix_Customer_ID ON dbo.Customer (Id);
CREATE NONCLUSTERED INDEX ix_Customer_ZIP ON dbo.Customer (ZIP);
GO 

-- what statistics will be used by different queries
-- result of implemented statistics 
SELECT S.object_id, 
       S.name, 
       DDSP.last_updated, 
       DDSP.rows, 
       DDSP.modification_counter 
FROM   sys.stats AS S 
       CROSS APPLY sys.dm_db_stats_properties(S.object_id, S.stats_id) AS DDSP 
WHERE  S.object_id = OBJECT_ID(N'dbo.Customer', N'U'); 
GO

Die Abbildung zeigt, dass für die Tabelle zwei Statistik-Objekte existieren. Insgesamt sind 10.557 Datensätze in der Tabelle und es wurden noch keine weiteren Modifikationen an den Daten vorgenommen. Da der zweite Index nicht eindeutig ist, gilt das Augenmerk der Verteilung der Daten in diesem Index. Dazu wird der folgende T-SQL-Befehl ausgeführt:

-- show the distribution of data in the statistics 
DBCC SHOW_STATISTICS ('dbo.Customer', 'ix_Customer_ZIP') WITH HISTOGRAM; 
GO 

Die Verteilung der Schlüsselwerte ist sehr heterogen. Während für den ZIP-Code „12000“ lediglich ein Eintrag vorhanden ist, sind es für den ZIP-Code „16000“ mehr als 7.500 Datensätze. Abhängig vom zu suchenden ZIP-Code besteht zusätzlich die Gefahr von „Parameter Sniffing“; das soll aber in diesem Beitrag nicht weiter thematisiert werden.

Abfragen

Sobald die Tabelle erstellt wurde, kann mit den Abfragen begonnen werden. Es werden zwei Abfragen auf die Tabelle ausgeführt, die jeweils unterschiedliche Indexe adressieren. Bei den Abfragen gilt die besondere Beachtung dem Umstand, dass sie hoch selektiv sind; sie verwenden einen „=“-Operator für die Suche nach Datensätzen.

DBCC TRACEON (3604, 9204, 9292, 8666);  
GO 

DECLARE @stmt NVARCHAR(1000) = N'SELECT * FROM dbo.Customer WHERE Id = @Id;';
DECLARE @parm NVARCHAR(100) = N'@Id INT';
EXEC sp_executesql @stmt, @parm, 10;
GO 

DECLARE @stmt NVARCHAR(1000) = N'SELECT * FROM dbo.Customer WHERE ZIP = @ZIP;';
DECLARE @parm NVARCHAR(100) = N'@ZIP CHAR(5)';
EXEC sp_executesql @stmt, @parm, '18000';
GO 

Die erste Abfrage verwendet den eindeutigen Index [ix_Customer_Id] während die zweite Abfrage einen performanten INDES SEEK auf den Index [ix_Customer_ZIP] ausführt. Die aus den Abfragen resultierenden Ausführungspläne stellen sich wie folgt dar:

Eindeutiger Index

Die Abfrage auf eine bestimmte ID in der Tabelle führt IMMER zu einem INDEX SEEK auf dem Index [ix_Customer_ID]. Durch den „=“-Operator in Verbindung mit dem eindeutigen Index ist gewährleistet, dass immer nur ein Datensatz geliefert werden kann.

Nicht eindeutiger Index

Die Abfrage auf einen bestimmten ZIP-Code kann zu unterschiedlichen Ausführungsplänen führen. Welcher Ausführungsplan verwendet wird, hängt von der Distribution der Kardinalitäten ab. Wenn es sich nur um sehr wenige Datensätze handelt, wird ein INDEX SEEK verwendet; sind jedoch die mit einem INDEX SEEK einhergehenden Lookups zu hoch, wird sich der Abfrageoptimierer für einen TABLE SCAN entscheiden. Man kann also beim ZIP-Code von einem „instabilen“ und „nicht vorhersehbaren“ Ausführungsplan sprechen.

Manipulation der Daten

Basierend auf den Statistiken entscheidet sich der Abfrageoptimierer von Microsoft SQL Server für eine entsprechende Ausführungsstrategie. Werden mehr als 20% der Daten einer Statistik (+500) geändert, so wird eine Statistik invalide.

Das nachfolgende Skript fügt weitere 4.000 Datensätze zur Tabelle hinzu. Bei 10.557 bereits in der Tabelle vorhandenen Datensätzen müssen mindestens 2.612 Datensätze geändert / hinzugefügt werden, damit die Statistiken als veraltet gekennzeichnet werden (10.557 * 20% + 500). Mit den hinzugefügten 4.000 Datensätzen ist dieser Schwellwert auf jeden Fall überschritten.

-- now additional 4,000 records will be filled into the table 
-- to make the stats invalid! 
INSERT INTO dbo.Customer WITH (TABLOCK)  
(Name, Street, ZIP, City)
SELECT TOP 4000 
       'Customer ' + CAST(message_id AS VARCHAR(10)),  
       'Street ' + CAST(severity AS VARCHAR(10)),  
       severity * 1000,  
       LEFT(text, 100)  
FROM   sys.messages  
WHERE  language_id = 1033;  
GO 

Wie in der Abbildung zu erkennen ist, wurden die 4.000 Datenmanipulationen registriert; diese Aktualisierungen verbleiben so lange in den Statistiken, bis sie erneut abgerufen werden und ggfls. aktualisiert werden.

In der Online-Dokumentation von Microsoft SQL Server heißt es: „Bevor der Abfrageoptimierer eine Abfrage kompiliert und einen zwischengespeicherten Abfrageplan ausführt, sucht er nach veralteten Statistiken. … Vor dem Ausführen eines zwischengespeicherten Abfrageplans überprüft die Database Engine, ob der Abfrageplan auf aktuelle Statistiken verweist.“.

Folgt man der Beschreibung aus der Online-Dokumentation, so müsste bei erneuter Ausführung der zuvor erstellten Abfragen eine Prüfung der Statistiken durchgeführt werden und die Statistiken – auf Grund der Änderungsquote von mehr als 20% – aktualisiert werden.

DBCC TRACEON (3604, 9204, 9292, 8666); 
GO 

DECLARE @stmt NVARCHAR(1000) = N'SELECT * FROM dbo.Customer WHERE Id = @Id;';
DECLARE @parm NVARCHAR(100) = N'@Id INT';
EXEC sp_executesql @stmt, @parm, 10;
GO 

DECLARE @stmt NVARCHAR(1000) = N'SELECT * FROM dbo.Customer WHERE ZIP = @ZIP;';
DECLARE @parm NVARCHAR(100) = N'@ZIP CHAR(5)';
EXEC sp_executesql @stmt, @parm, '18000'; 
GO 

DBCC TRACEOFF (3604, 9204, 9292, 8666); 
GO 

Die Abbildung zeigt, dass für die erste Abfrage auf die [ID] die Statistiken nicht erneut überprüft wurden. Für die zweite Abfrage wurden die Statistiken erneut überprüft. Im Ergebnis zeigt dieses Verhalten auch die Abfrage nach den Zuständen der Statistiken der betroffenen Tabelle.

 SELECT S.object_id, 
       S.name, 
       DDSP.last_updated, 
       DDSP.rows, 
       DDSP.modification_counter 
FROM   sys.stats AS S 
       CROSS APPLY sys.dm_db_stats_properties(S.object_id, S.stats_id) AS DDSP 
WHERE  S.object_id = OBJECT_ID(N'dbo.Customer', N'U'); 
GO

Die Statistiken für den Index [ix_Customer_ZIP] wurden aktualisiert und die 4.000 neuen Datensätze sind in der Statistik enthalten. Für den eindeutigen Index [ix_Customer_ID] wurde diese Aktualisierung jedoch nicht vorgenommen. Der Grund für dieses Verhalten ist relativ einfach zu erklären:

Begründung für das Verhalten

Eindeutiger Index

Wen ein eindeutiger Index auf dem Schlüsselattribut abgefragt wird, muss Microsoft SQL Server keine Statistiken bemühen, da IMMER davon ausgegangen werden kann, dass ein gesuchter Wert nur einmal in im Index erscheint. Bei der ersten Ausführung der Abfrage wurde ein NEUER Ausführungsplan generiert. Insofern stimmt die Aussage aus der Online-Dokumentation. Bevor die Abfrage kompiliert und ein Plan generiert werden kann, müssen die Statistiken überprüft werden. Bei der zweiten Ausführung dieser Abfrage lag der Plan bereits vor; warum sollte Microsoft SQL Server hier die Strategie ändern? Da auf Grund der Eindeutigkeit der Indexwerte niemals mehr als ein Datensatz im Ergebnis erscheinen kann, muss der Plan nicht erneut überprüft werden – er ist „stabil“

Nichteindeutiger Index

Bei der zweiten Abfrage sieht die Stabilität des Plans etwas anders aus. Der Index ist nicht als UNIQUE erstellt worden; es können also pro Schlüsselwert mehrere Daten im Index vorhanden sein. Wenn tatsächlich die Anzahl der Datensätze zu einem Schlüsselattribut variieren, dann ist der Plan „instabil“; er ist abhängig von der Anzahl der vorhandenen Datensätze. In diesem Fall trifft die zweite Aussage aus der Online-Dokumentation zu – der Plan muss auf Validität überprüft werden. Dazu gehört das Überprüfen der veralteten Statistiken. Nun stellt Microsoft SQL Server fest, dass die Statistiken Änderungen erfahren haben, die über dem Schwellwert liegen und somit werden die Statistiken vor der Erstellung des Plans aktualisiert.

Zusammenfassung

Statistiken sind bei Performance-Problemen immer ein Punkt, der überprüft werden sollte. Statistiken werden aber – entgegen der Aussage von Microsoft – nicht grundsätzlich aktualisiert, sobald der definierte Schwellwert überschritten ist. Statistiken werden auch nicht durch einen Background-Task aktualisiert. Die Aktualisierung von Statistiken beruht darauf, wie stabil / instabil ein gespeicherter Plan ist. Wird – auf Grund der Stabilität – bei der Ermittlung der Datensätze erkannt, dass sich die Datenmenge nicht verändern kann, kann es passieren, dass Statistiken so lange nicht aktualisiert werden, bis entweder ein bestehender Plan aus dem Cache gelöscht wird oder aber eine Abfrage mit RECOMPILE dazu gezwungen wird, einen neuen Plan zu verwenden.

Herzlichen Dank fürs Lesen!