Dies ist eine SQL Server 2016 CU2-Instanz mit einer maximalen RAM-Nutzung von 15 GB. MDOP liegt bei 4. Die Datenbanken in dieser Instanz werden sehr sporadisch von Benutzern abgefragt und dies ist eine schreibgeschützte sekundäre Replik.
Das Problem: Wöchentlich wechseln alle Abfragen aufgrund von Speichererschöpfung in den Zustand RESOURCE_SEMAPHORE, selbst wenn auf diesem Server keine anderen Abfragen gleichzeitig ausgeführt werden. Nach einem Neustart der Instanz funktionieren die Abfragen wieder. Anhand der folgenden Bilder können Sie erkennen, dass uns der maximale Arbeitsspeicher fehlt und dies wirklich SEHR schlechte Abfragen sind (astronomische Abfragekosten).
Vor dem Neustart: nur 1,8 GB verfügbarer maximaler Arbeitsbereichsspeicher und ALLE fehlerhaften Abfragen wechseln in den Zustand RESOURCE_SEMAPHORE.
Nach dem Neustart der Instanz: reichlich Arbeitsbereichsspeicher (~11 GB) und kein RESOURCE_SEMAPHORE mehr, selbst bei schrecklichen Abfragen.
Wie Sie sehen, handelt es sich um eine abstoßende Abfrage, die ca. 2 GB RAM anfordert. In diesem Bild zeigt SSMS, dass der erforderliche Speicher tatsächlich gewährt wurde – weil die Instanz neu gestartet wurde. Vor dem Neustart bleibt die Speicherzuweisung auf NULL und die Abfrage verbleibt im Wartezustand RESOURCE_SEMAPHORE.
Nun möchten wir wissen: Warum verringert sich der maximale Arbeitsbereichsspeicher mit der Zeit und wird nicht freigegeben, wenn keine Abfragen ausgeführt werden?
Antwort1
Es scheint, als ob der gestohlene Serverspeicher von insgesamt 15.728.640.000 Bytes, der zugewiesen ist, 12.828.864.000 Bytes einnimmt, was offensichtlich nicht genug für den Normalbetrieb übrig lässt.
In der Dokumentation heißt es in SQL Server, Memory Manager-Objekt:
Gestohlener Serverspeicher (KB)
Gibt die Speichermenge an, die der Server für andere Zwecke als Datenbankseiten verwendet.
EinAntwortsagt mehr:
Gestohlener Speicher beschreibt Puffer, die zum Sortieren oder für Hash-Operationen (Abfragearbeitsbereichsspeicher) verwendet werden, oder Puffer, die als allgemeiner Speicher für Zuordnungen zum Speichern interner Datenstrukturen wie Sperren, Transaktionskontext und Verbindungsinformationen verwendet werden. Der LazyWriter-Prozess darf gestohlene Puffer nicht aus dem Pufferpool leeren.
Die verschwenderische Abfrage hat offenbar einen enormen Sortiervorgang ausgeführt und SQL Server gibt den zugewiesenen Speicher dann nicht frei. Der Speicher wird aus dem Pufferpool entnommen und kann für nichts anderes verwendet werden.
Sie haben nicht allzu viele Optionen:
- Verbessern Sie die Abfrage, damit sie nicht riesige Datenmengen sortiert
- Fügen Sie SQL Server mehr Speicher hinzu
- Starten Sie SQL Server regelmäßig neu, insbesondere nach dem Ausführen dieser Abfrage.