"killed" exec sp_updatestats aktive Verbindungen?
Frage
Antworten
-
Hallo Andreas,
das eine hat DEFINITV nichts mit dem anderen zu tun. Das nach einem STATISTICS Update unter Umständen andere Ausführungspläne verwendet werden, ist normal. sp_updatestats unterbricht NIE die Ausführung von Abfragen; vom Prinzip wird nur ein
UPDATE STATISTICS <schema>.<table> <name> WITH <options>
durchgeführt. Ansonsten gibt es keine weiteren Aktivitäten. Die von Dir beobachteten CPU-Lasten decken sich mit den Vermutungen von "nicht optimalen" Ausführungsplänen. Das nachfolgende Beispiel (SQL <= 2012) zeigt das von Dir beschriebene Verhalten:
CREATE TABLE dbo.demo_table ( Id int not null identity (1, 1), C1 CHAR(3) not null, C2 CHAR(1000) not null ); GO -- ~ 30.000 records INSERT INTO dbo.demo_table WITH (TABLOCK) (C1, C2) SELECT 'DE', CAST(text AS CHAR(1000)) FROM sys.messages WHERE language_id = 1033 UNION ALL SELECT 'AU', CAST(text AS CHAR(1000)) FROM sys.messages WHERE language_id = 1033 UNION ALL SELECT 'CH', CAST(text AS CHAR(1000)) FROM sys.messages WHERE language_id = 1033; GO -- Create an index on C1 CREATE NONCLUSTERED INDEX nix_demo_table_C1 ON dbo.demo_table (C1); GO
Eine Abfrage auf DE, AU oder CH erzeugt den folgenden Ausführungsplan:
Wie Du erkennen kannst, ist ein TABLE SCAN für Microsoft SQL Server eine bessere Wahl als ein INDEX SEEK mit einem entsprechenden RID-Lookup (HEAP). Nun werden 5.000 weitere Datensätze in die Tabelle eingetragen. Da damit der Schwellenwert von 20% + 500 nicht überschritten wird, werden die Statistiken nicht aktualisiert:
INSERT INTO dbo.demo_table WITH (TABLOCK) (C1, C2) SELECT TOP (5000) 'ES', CAST(text AS CHAR(1000)) FROM sys.messages WHERE language_id = 1033;
Eine Abfrage auf das Prädikat "ES" führt zu folgendem Ausführungsplan:
Wie Du sehen kannst, geht Microsoft SQL Server von 1 (!!!) Datensatz aus. Tatsächlich sind jedoch 5.000 Datensätze vorhanden. Mit Hilfe des NESTED LOOP werden aus der Tabelle selbst fehlende Informationen, die nicht im Index vorhanden sind, ermittelt (RID-Lookup).
Schaut man sich die Statistiken an, wird schnell klar, was hier passiert:
DBCC SHOW_STATISTICS ('dbo.demo_table', 'nix_demo_table_c1') WITH HISTOGRAM;
Da der neue Wert "ES" noch nicht in den Statistiken vorhanden sind, geht Microsoft SQL Server von 1 Datensatz aus (<= 2012). Nachdem die Statistiken aktualisiert wurden, wird die Abfrage erneut ausgeführt. Mit der nächsten Abfragen mache ich sichtbar, welche Statiistiken Microsoft SQL Server für die Abfrage lädt und analysiert:
UPDATE STATISTICS dbo.demo_table nix_demo_table_c1 WITH FULLSCAN;
Sobald die Statistiken aktualisiert wurden, wird folgende Abfrage ausgeführt - achte auf den OUTPUT!
SELECT * FROM dbo.demo_table WHERE C1 = 'ES' OPTION ( QUERYTRACEON 3604, QUERYTRACEON 9204, QUERYTRACEON 9292 );
Wie Du erkennen kannst, hat Microsoft SQL Server nach der Aktualisierung der Statistiken den Plan als "invalide" gekennzeichnet und er musste neu kompiliert werden. Dazu gehört natürlich, die Statistiken neu zu laden und für eine optimale Abfragestrategie zu analysieren.
Das alles in eine einzige Antwort zu quetschen, ist schon recht schwierig - aber vielleicht sehen wir uns ja mal auf einem Usertreffen. Ich bereite gerade zum Thema Statistiken einen entsprechenden Vortrag vor :)
Uwe Ricken (Blog | Twitter)
Microsoft Certiied Master - SQL Server 2008
Microsoft Certified Solution Master - CHARTER Data Platform
Microsoft Certified Solution Expert - Data Platform
db Berater GmbH
Microsoft SQL Server Blog (german only)
- Bearbeitet Uwe RickenMVP Dienstag, 13. September 2016 09:00
- Als Antwort markiert Andreas Kreuzberg Dienstag, 13. September 2016 09:15
Alle Antworten
-
Hallo Andreas,
das eine hat DEFINITV nichts mit dem anderen zu tun. Das nach einem STATISTICS Update unter Umständen andere Ausführungspläne verwendet werden, ist normal. sp_updatestats unterbricht NIE die Ausführung von Abfragen; vom Prinzip wird nur ein
UPDATE STATISTICS <schema>.<table> <name> WITH <options>
durchgeführt. Ansonsten gibt es keine weiteren Aktivitäten. Die von Dir beobachteten CPU-Lasten decken sich mit den Vermutungen von "nicht optimalen" Ausführungsplänen. Das nachfolgende Beispiel (SQL <= 2012) zeigt das von Dir beschriebene Verhalten:
CREATE TABLE dbo.demo_table ( Id int not null identity (1, 1), C1 CHAR(3) not null, C2 CHAR(1000) not null ); GO -- ~ 30.000 records INSERT INTO dbo.demo_table WITH (TABLOCK) (C1, C2) SELECT 'DE', CAST(text AS CHAR(1000)) FROM sys.messages WHERE language_id = 1033 UNION ALL SELECT 'AU', CAST(text AS CHAR(1000)) FROM sys.messages WHERE language_id = 1033 UNION ALL SELECT 'CH', CAST(text AS CHAR(1000)) FROM sys.messages WHERE language_id = 1033; GO -- Create an index on C1 CREATE NONCLUSTERED INDEX nix_demo_table_C1 ON dbo.demo_table (C1); GO
Eine Abfrage auf DE, AU oder CH erzeugt den folgenden Ausführungsplan:
Wie Du erkennen kannst, ist ein TABLE SCAN für Microsoft SQL Server eine bessere Wahl als ein INDEX SEEK mit einem entsprechenden RID-Lookup (HEAP). Nun werden 5.000 weitere Datensätze in die Tabelle eingetragen. Da damit der Schwellenwert von 20% + 500 nicht überschritten wird, werden die Statistiken nicht aktualisiert:
INSERT INTO dbo.demo_table WITH (TABLOCK) (C1, C2) SELECT TOP (5000) 'ES', CAST(text AS CHAR(1000)) FROM sys.messages WHERE language_id = 1033;
Eine Abfrage auf das Prädikat "ES" führt zu folgendem Ausführungsplan:
Wie Du sehen kannst, geht Microsoft SQL Server von 1 (!!!) Datensatz aus. Tatsächlich sind jedoch 5.000 Datensätze vorhanden. Mit Hilfe des NESTED LOOP werden aus der Tabelle selbst fehlende Informationen, die nicht im Index vorhanden sind, ermittelt (RID-Lookup).
Schaut man sich die Statistiken an, wird schnell klar, was hier passiert:
DBCC SHOW_STATISTICS ('dbo.demo_table', 'nix_demo_table_c1') WITH HISTOGRAM;
Da der neue Wert "ES" noch nicht in den Statistiken vorhanden sind, geht Microsoft SQL Server von 1 Datensatz aus (<= 2012). Nachdem die Statistiken aktualisiert wurden, wird die Abfrage erneut ausgeführt. Mit der nächsten Abfragen mache ich sichtbar, welche Statiistiken Microsoft SQL Server für die Abfrage lädt und analysiert:
UPDATE STATISTICS dbo.demo_table nix_demo_table_c1 WITH FULLSCAN;
Sobald die Statistiken aktualisiert wurden, wird folgende Abfrage ausgeführt - achte auf den OUTPUT!
SELECT * FROM dbo.demo_table WHERE C1 = 'ES' OPTION ( QUERYTRACEON 3604, QUERYTRACEON 9204, QUERYTRACEON 9292 );
Wie Du erkennen kannst, hat Microsoft SQL Server nach der Aktualisierung der Statistiken den Plan als "invalide" gekennzeichnet und er musste neu kompiliert werden. Dazu gehört natürlich, die Statistiken neu zu laden und für eine optimale Abfragestrategie zu analysieren.
Das alles in eine einzige Antwort zu quetschen, ist schon recht schwierig - aber vielleicht sehen wir uns ja mal auf einem Usertreffen. Ich bereite gerade zum Thema Statistiken einen entsprechenden Vortrag vor :)
Uwe Ricken (Blog | Twitter)
Microsoft Certiied Master - SQL Server 2008
Microsoft Certified Solution Master - CHARTER Data Platform
Microsoft Certified Solution Expert - Data Platform
db Berater GmbH
Microsoft SQL Server Blog (german only)
- Bearbeitet Uwe RickenMVP Dienstag, 13. September 2016 09:00
- Als Antwort markiert Andreas Kreuzberg Dienstag, 13. September 2016 09:15
