SQL: VIEW ploetzlich ewige Laufzeit (mit angefuegten Order by funkionieren die Views)

Question

Hallo zusammen,

ich habe aktuell folgendes Problem.

Betroffen ist eine Datenbank auf einem MS SQL Server 2008 R2.

Einige View werden nicht fertig, wenn man Sie ausführt. Diese haben vorher funktioniert mit einer Laufzeit im Millisekunden Bereich. Fügt man beim Ausführen der View ein "Order by ID" an, geht die View wieder einwandfrei und so schnell wie vorher.

Neuanlegen hat leider nichts gebracht.

Es wurden keine Updates installiert die der Auslöser sein können.

Es wurden zudem keine größeren Änderungen außer editieren von Prozeduren auf der Datenbank vorgenommen.

Woran kann soetwas liegen?

Ich bin über jeden Hinweis dankbar.

Viele Grüße

Daniel Kramer

Answer 1

Hallo Daniel,

dafür kann es verschiedenste Gründe geben. Evtl. fehlen Indizes auf der Tabelle bzw. den Tabellen bzw. sind nicht mehr sinnvoll nutzbar. Oder die Statistiken, die der SQL Server für die Analyse der Optimierungsmöglichkeiten der Abfragen nutzt, sind nicht mehr aktuell. Oder es hat sich doch was geändert.

Poste bitte das komplette DDL Statement (CREATE VIEW sowie alle CREATE TABLE ... Statements) inkl. aller Indizes und Schlüssel.

Dazu dann bitte dein SQL Statement für die Abfrage.

Zudem schau dir bitte mal den Ausführungsplan deiner Abfrage an (geht im SSMS bspw. über ein Icon bei einem Abfragefenster, siehe Screenshot)

Lass dir auch mal die Indexfragmentierung anzeigen.

SELECT   s.name                            AS SchemaName,
         t.name                            AS TableName,
         i.name                            AS IndexName,
         stat.avg_fragmentation_in_percent AS FragmentationInPercent,
         stat.page_count                   AS PageCount
FROM     sys.dm_db_index_physical_stats( DB_ID(), NULL, NULL, NULL, NULL ) stat
         INNER JOIN sys.tables  t on t.object_id = stat.object_id
         INNER JOIN sys.schemas s on t.schema_id = s.schema_id
         INNER JOIN sys.indexes i ON i.object_id = stat.object_id AND stat.index_id    = i.index_id
WHERE    stat.database_id = DB_ID()
AND      t.name           LIKE '%<Filter>%'
ORDER BY stat.avg_fragmentation_in_percent desc

(Originalskript kam von http://www.schneider-electric.com/en/faqs/FA234246/)

Mit <Filter> kannst Du hier mal auf deinen Tabellennamen festlegen, ansonsten stehen da alle Indizes aller Tabellen.

 

---

Gruß, Stefan
Microsoft MVP - Visual Developer ASP/ASP.NET
http://www.asp-solutions.de/ - Consulting, Development
http://www.aspnetzone.de/ - ASP.NET Zone, die ASP.NET Community

Answer 2

Danke Stefan für deine Nachricht.

Die beschriebenen Schritte haben wir bereits durchgeführt.

Wir vermuten das Problem nicht in unserem SQL (VIEW) sondern im MS SQL Server.

Diese Abfragen habe Ewigkeiten tagtäglich gelaufen ohne Probleme und plötzlich "funktionieren diese nicht mehr" bzw laufen in einer endlos-Schleifen.

Aktuell haben wir neue Erkenntnisse:

Ein Select auf die View funktioniert nicht wenn ich TOP 1000 verwende, jedoch funktioniert dieses select OHNE TOP zu verwenden.

Dieses ist für uns unverständlich.

Hat jemand so etwas schon einmal gehabt ?

Viele Grüße und vielen Dank.

Daniel Kramer

Answer 3

Hallo Daniel,

ohne irgendwelche Änderungen wird auch der SQL Server nicht von heute auf morgen einfach mal Abfragen, die sonst schnell liefen, nun anders ausführen, so dass diese nicht mehr funktionieren.

Leider hast Du zu den Vorschlägen keine Rückmeldung gegeben, so dass ich das oben gesagte nur nochmal wiederholen kann. Ohne die Ergebnisse der einzelnen Schritte zu kennen, kann dir hier niemand auch nur irgendetwas sagen.

---

Gruß, Stefan
Microsoft MVP - Visual Developer ASP/ASP.NET
http://www.asp-solutions.de/ - Consulting, Development
http://www.aspnetzone.de/ - ASP.NET Zone, die ASP.NET Community

Answer 4

Hallo Daniel,

ich kann mich Stefan nur anschließen.

"Wir vermuten das Problem nicht in unserem SQL (VIEW) sondern im MS SQL Server."

Natürlich hat es etwas mit dem SQL Server zu tun, nur - davon bin ich überzeugt - ist der Server selbst bestimmt nicht die Ursache! In der Regel sind es geänderte Prozesse, Veränderung der Datenmenge, Veränderung der Anzahl der Benutzer, ...

Stefan hat die wesentlichen Punkte bereits hervorgehoben. Zusätzlich noch ein paar andere Fragen:

1. Habt ihr regelmäßige Maintenance-Aufgaben, die Indexe und Statistiken aktualisieren?
2. Wird die View i. d. R. aus einer Stored Procedure oder mit Hilfe einer Variablen als Prädikat aufgerufen?

Insbesondere Statistiken sind immer wieder der Grund für eine langsam laufende Abfrage, da der SQL Server bei nicht vorhandenen Werte in der Statistik (ascending key problem) immer von 1 Datensatz ausgeht, der gefunden wird. Damit wird dann z. B. bei einem JOIN eher ein NESTED LOOP statt eines (Beispiel!) der Datenmenge angepassten HASH oder MERGE JOIN verwendet. Ebenfalls ein großes Problem kann bei falschen Statistiken die Berechnung des "Memory Grant" sein. Reicht das - auf Basis der Statistiken errechnete - Memory nicht aus, werden Operationen in TEMPDB verlagert!

Ein weiteres Problem kann das Cachen von Ausführungsplänen sein. Dieses Phänomen nennt sich "Parameter Sniffing". Zu diesem Problem habe ich einen ausführlichen Blogartikel geschrieben, den Du hier nachlesen kannst:

http://www.db-berater.de/2016/09/parameter-sniffing-loesungsansaetze/

Es wäre sehr hilfreich, wenn Du die gewöhnlich auszuführende Abfrage mal in SSMS eingibst und anschließend [STRG+L] drückst, damit Du den "estimated Execution Plan" erhältst. Dann kann man zumindest schon mal sehen, welche Strategie der SQL Server für die Durchführung der Abfrage verwendet.

Eine andere Möglichkeit besteht darin, während der Ausführung der Abfrage zu prüfen, worauf der SQL Server während der Ausführung wartet. Eine Möglichkeit, die mir "in den Sinn kommt", ist, dass die Abfrage auf Speicher wartet. Du kannst - während die Abfrage auf die View läuft - folgende Abfrage in einem parallelen Fenster ausführen:

SELECT	session_id,
		status,
		wait_type,
		wait_time,
		last_wait_type,
		wait_resource
FROM	sys.dm_exec_requests
		OUTER APPLY sys.dm_exec_sql_text(sql_handle)
WHERE	session_id = <Id der ausführenden Abfrage>;

Mit dieser Abfrage siehst Du die Session und mögliche Wartevorgänge. Siehst Du hier einen Wartevorgang [RESOURCE_SEMAPHORE], dann ist es ein Memoryproblem.

RESOURCE_SEMAPHORE-Wartevorgänge treten auf, wenn die Abfrage den Speicher anfordert, der für die Ausführung der Abfrage benötigt wird (auf Basis eines Ausführungsplans). Wenn kein Speicher zur Verfügung steht, kann die Abfrage noch nicht ausgeführt werden.

Ursache hierfür sind entweder zu wenig Speicher, zu viele Abfragen, falsche Statistiken, Parameter Sniffing!

Hast Du z. B. den Wartevorgang [THREADPOOL], dann habt ihr zu viele Benutzer auf dem System, die gleichzeitig Ressourcen (Threads) blockieren und Du kommst mit Deiner Abfrage erst später dran. Stelle Dir das Szenario so vor, als ob Du in einen Club möchtest, der eine Obergrenze von Gästen einlassen darf. Geht einer / eine Gruppe raus, bekommst Du die Eintrittskarte. Ist der Club gerade voll, stehst Du in der Schlange :)

Das sind nur "Einblicke" auf Basis Deiner Angaben! Es wäre also schön, wenn Du uns mit weiteren Details (siehe Stefan) versorgen könntest. Dann würde sich auch ein "Rate mal..." schnell in ein "Das ist es..." ändern :)

---

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)