Warum hat SQL Server plötzlich entscheiden, zu verwenden, wie eine schreckliche Ausführungsplan?
Hintergrund
Wir hatten vor kurzem ein Problem mit der Abfrage-Pläne für sql server auf eines unserer größeren Tabellen (um 175,000,000 Zeilen). Die Spalten-und index-Struktur der Tabelle hat sich nicht geändert, die für 5+ Jahre.
Die Tabelle und die Indizes sieht wie folgt aus:
create table responses (
response_uuid uniqueidentifier not null,
session_uuid uniqueidentifier not null,
create_datetime datetime not null,
create_user_uuid uniqueidentifier not null,
update_datetime datetime not null,
update_user_uuid uniqueidentifier not null,
question_id int not null,
response_data varchar(4096) null,
question_type_id varchar(3) not null,
question_length tinyint null,
constraint pk_responses primary key clustered (response_uuid),
constraint idx_responses__session_uuid__question_id unique nonclustered (session_uuid asc, question_id asc) with (fillfactor=80),
constraint fk_responses_sessions__session_uuid foreign key(session_uuid) references dbo.sessions (session_uuid),
constraint fk_responses_users__create_user_uuid foreign key(create_user_uuid) references dbo.users (user_uuid),
constraint fk_responses_users__update_user_uuid foreign key(update_user_uuid) references dbo.users (user_uuid)
)
create nonclustered index idx_responses__session_uuid_fk on responses(session_uuid) with (fillfactor=80)
Die Abfrage, die war schlecht (~2,5 Minuten anstatt der normalen <1 Sekunde-Leistung) sieht wie folgt aus:
SELECT
[Extent1].[response_uuid] AS [response_uuid],
[Extent1].[session_uuid] AS [session_uuid],
[Extent1].[create_datetime] AS [create_datetime],
[Extent1].[create_user_uuid] AS [create_user_uuid],
[Extent1].[update_datetime] AS [update_datetime],
[Extent1].[update_user_uuid] AS [update_user_uuid],
[Extent1].[question_id] AS [question_id],
[Extent1].[response_data] AS [response_data],
[Extent1].[question_type_id] AS [question_type_id],
[Extent1].[question_length] AS [question_length]
FROM [dbo].[responses] AS [Extent1]
WHERE [Extent1].[session_uuid] = @f6_p__linq__0;
(Die Abfrage generiert wird, die von entity framework und ausgeführt, unter Verwendung von sp_executesql)
Den Ausführungsplan während die Armen performance-Periode sah so aus:
Einige Hintergrundinformationen über die Daten - ausführen der Abfrage oben würde nie wieder mehr als 400 Zeilen. In anderen Worten, Filter auf session_uuid wirklich beschränkt sich die Ergebnismenge.
Einige Hintergrundinformationen über die geplante Instandhaltung - ein zeitgesteuerter Auftrag ausgeführt wird auf einer wöchentlichen basis, um die Datenbank neu erstellen Statistiken und rebuild Indizes der Tabelle. Der job läuft ein Skript, das wie folgt aussieht:
alter index all on responses rebuild with (fillfactor=80)
Die Lösung für das performance-problem wurde führen Sie den index neu erstellen-Skript (oben) auf dieser Tabelle.
Andere möglicherweise relevante Einzelheiten... Die Verteilung der Daten hat sich nicht geändert, seit der letzten index neu erstellen. Es gibt keine joins in der Abfrage. Wir sind ein SAAS-shop, wir haben bei 50 - 100 live-Produktion von Datenbanken mit genau dem gleichen schema, einige mit mehr Daten, einige mit weniger, alle mit den gleichen Abfragen ausführen, gegen Sie sich über ein paar sql-Server.
Frage:
Was passiert sein könnte, würde der sql-server starten, mit dieser schrecklichen Ausführungsplan in dieser bestimmten Datenbank?
Beachten Sie, das problem wurde gelöst, indem einfach Neuerstellen der Indizes auf der Tabelle.
Vielleicht eine bessere Frage ist "was sind die Umstände, unter denen der sql-server würde aufhören, ein index?"
Anderen Weg, es zu betrachten ist: "warum würde der optimizer keinen index, der umgebaut wurde, vor ein paar Tagen und dann starten Sie es wieder nach einem Notfall neu erstellen des index einmal bemerkten wir, dass die schlechte Abfrage-plan?"
- Vielleicht diese ähnliche Frage auf dba.stackexchange wird Ihnen einige Einblicke: dba.stackexchange.com/questions/4283/...
Du musst angemeldet sein, um einen Kommentar abzugeben.
Dies ist zu lang für einen Kommentar.
Der Grund ist einfach: der Optimierer ändert seinen Geist auf, was der beste plan ist. Dies kann durch subtile Veränderungen in der Verteilung der Daten (oder andere Gründe, wie eine Art Inkompatibilität in einem
join
- Taste). Ich wünschte, es gäbe ein tool, das gab nicht nur den Ausführungsplan für eine Abfrage, aber auch gezeigt, schwellen für, wie nah Sie an einen anderen Ausführungsplan. Oder ein tool, das würde Ihnen ermöglichen, stash ein Ausführungsplan und geben eine Warnung, wenn die gleiche Abfrage beginnt mit einem anderen plan.Habe ich mich gefragt, dieses genau die gleiche Frage mehr als einmal. Sie haben ein system, das läuft, jede Nacht, für Monate am Ende. Er verarbeitet eine Vielzahl von Daten mit wirklich komplizierten Abfragen. Dann, eines Tages, Sie kommen in den morgen-und den job, dass normalerweise abgeschlossen um 11:00 Uhr noch läuft. Arrrggg!
Die Lösung, die wir gekommen sind, war der Einsatz von expliziten
join
Hinweise für den fehlerhaften Beitritt. (option (merge join, hash join)
). Auch begannen wir speichern die Ausführungspläne für alle unsere komplexe Abfragen, so könnten wir vergleichen die Veränderungen von einer Nacht zur nächsten. Am Ende, das war mehr von akademischem Interesse als von praktischem Interesse-wenn die Pläne geändert wurden, waren wir schon leiden, eine schlechte Ausführung zu planen.Dies ist einer meiner am meisten gehasst Probleme mit SQL - ich habe mehr als ein Ausfall aufgrund von diesem Problem - sobald eine Abfrage, die hatte seit Monaten der Arbeit ging von ~250ms bis über die timeout-Schwelle verursacht ein manufacturing system zum Absturz bringen um 3 Uhr morgens natürlich. Dauerte eine Weile, um zu isolieren, die Abfrage und kleben Sie es in SSMS und dann starten bricht es in Stücke - aber alles, was ich Tat gerade "gearbeitet". Am Ende habe ich nur noch die Aussage "1=1" an die Abfrage, die Dinge wieder zu arbeiten für ein paar Wochen - der Letzte patch war, um "blind" die Optimierer - im Grunde kopieren Sie alle übergebenen Parameter in den lokalen Parametern. Wenn die Abfrage funktioniert, von der Fledermaus, wie es scheint, wird es weiterhin zu arbeiten.
Mir eine halbwegs einfache Lösung von MS wäre: wenn diese Abfrage wurde bereits profilierten und lief einfach gut die Letzte Zeit, und die entsprechenden Statistiken haben sich nicht wesentlich verändert (z.B. ein Faktor von verschiedenen änderungen in Tabellen oder neue Indizes, etc), und der "optimizer" beschließt, etwas aufzupeppen mit einem neuen Ausführungsplan, wie wäre es, wenn das neue und verbesserte plan nimmt mehr als X-Vielfaches von dem alten plan, den ich Abbrechen, und wechseln Sie wieder zurück. Ich kann verstehen, wenn eine Tabelle geht von 100 bis zu 100,000,000 Zeilen oder, wenn ein Schlüssel-index gelöscht wird, aber für eine stabile Produktionsumgebung eine Abfrage Sprung in der Dauer, zwischen 100x und 1000x langsamer, es konnte nicht so schwer sein, dies zu erkennen, markieren Sie den plan und gehen Sie zurück zu der vorherigen.
Neuere SQL Server-Versionen haben eine tolle neue Funktion namens "Abfrage Speichern", wo Sie analysieren aktuelle Abfragen performance.
Wenn Sie sehen eine Abfrage, die manchmal verwendet eine "schnelle" planen und manchmal ist ein "slow" - Sie können erzwingen, dass der schnelle plan. Siehe auch den screenshot. Der "gelbe Kreis" - plan ist der schnelle, sondern der "blue square" - plan ist nicht (es ist höher auf die "Dauer" Diagramm")