Benutzer mit den meisten Antworten
SQL_BufferPool nutzt Speicher nicht.

Frage
-
Guten Tag,
wieso wird der Speicher meines SQL-Servers nur für Ausführungspläne hauptsächlich verwendet?
Sollte nicht der größte Teil für den Memoryclerk_SQLBufferPool verwendet werden?
Bei der Pufferseitenverteilung ist nur ein kleiner teil 1-5% dirty der rest ist Stolen oder Free?
Nach einem neustart des SQL-Servers verwendet der Memoryclerk_SQLBufferpool ebenfalls wieder genau 416 KB ist dies ein zufall?
Freundliche Grüße
Andreas Aichinger
- Bearbeitet Andreas Aichinger Freitag, 23. September 2016 10:26
Antworten
-
Hallo Andreas,
vermutlich ist es nicht das, was Du hören willst, aber Du suchst einen Fehler in einer Version, für die es danach noch 4 Service-Packs gab. Dort wurden unzählige Fehler behoben (und zwischendurch manche neu eingeführt).
Mein Vorschlag: Zuerst SP4 + Security Patches einspielen. Das Betriebssystem hast Du ja auch aktualisiert.
Danach kann man noch mal in die Details gehen, oder?
Einen schönen Tag noch, Christoph -- Data Platform MVP - http://www.insidesql.org/blogs/cmu
- Als Antwort markiert Andreas Aichinger Dienstag, 27. September 2016 12:48
Alle Antworten
-
Hallo Andreas
womit sollte der SQL Server den Buffer Pool denn füllen, wenn er gerade erst neu gestartet wurde?
~400MB ist eine Zahl die recht typisch für einen frisch gestarteten Server ist. Aber auf den MB kann man das nicht festlegen.
Also am besten mal warten, bis der Server eine Weile (Stunden oder Tage) verwendet wurde, und dann nochmal nachsehen.
Andreas Wolter (Blog | Twitter)
MCSM: Microsoft Certified Solutions Master Data Platform/SQL Server 2012
MCM SQL Server 2008
MVP Data Platform
www.SarpedonQualityLab.com | www.andreas-wolter.com -
Danke für die Antwort,
Der Screenshot wurde erstellt vor dem Neustart bei einer Laufzeit von 2 Wochen unter Arbeitslast.
als Anmerkung habe ich hinzugefügt das der SQL_BufferPool nach dem Neustart auch nicht mehr KB nutzt.
Freundliche Grüße
Andreas Aichinger
-
Dann ist das in der Tat eine seltsame "Auslastung".
Von welcher SQL Version reden wir überhaupt? Wie viel Memory ist konfiguriert? Steht etwas im Errorlog?
Über die Ring_Buffer kann man tiefer in die Auslastung (auch Historie) schauen. Zum Beispiel hiermit:
WITH MemoryBrokerCte AS ( SELECT ROW_NUMBER() OVER (ORDER BY Buffer.Record.value( '@time', 'BIGINT' ) , Buffer.Record.value( '@id', 'INT' ) ) AS [RowNumber] , Data.ring_buffer_type AS [Type] , Buffer.Record.value('(MemoryBroker/Pool)[1]', 'INT') AS [Pool] , Buffer.Record.value('(MemoryBroker/Broker)[1]', 'NVARCHAR(128)') AS [Broker] , Buffer.Record.value('(MemoryBroker/Notification)[1]', 'NVARCHAR(128)') AS [Notification] , Buffer.Record.value( '@time', 'BIGINT' ) AS [time] , Buffer.Record.value('@id', 'int') AS [Id] , Data.EventXML FROM (SELECT CAST(Record AS XML) AS EventXML , ring_buffer_type FROM sys.dm_os_ring_buffers WHERE ring_buffer_type = 'RING_BUFFER_MEMORY_BROKER') AS Data CROSS APPLY EventXML.nodes('//Record') AS Buffer(Record) ) SELECT first.[Type] , summary.[Pool] , summary.[Broker] , summary.[Notification] , summary.[count] , DATEADD( second , first.[Time] - info.ms_ticks / 1000 , CURRENT_TIMESTAMP ) AS [FirstTime] , DATEADD( second , last.[Time] - info.ms_ticks / 1000 , CURRENT_TIMESTAMP ) AS [LastTime] , first.EventXML AS [FirstRecord] , last.EventXML AS [LastRecord] FROM (SELECT [Pool] , [Broker] , [Notification] , COUNT(*) AS [count] , MIN(RowNumber) AS [FirstRow] , MAX(RowNumber) AS [LastRow] FROM MemoryBrokerCte GROUP BY [Type] , [Pool] , [Broker] , [Notification] ) AS summary JOIN MemoryBrokerCte AS first ON first.RowNumber = summary.[FirstRow] JOIN MemoryBrokerCte AS last ON last.RowNumber = summary.[LastRow] CROSS JOIN sys.dm_os_sys_info AS info ORDER BY [Type] , [Pool] , [Broker] , [Notification];
und hiermit:
WITH MemoryBrokerClerksCte AS ( SELECT ROW_NUMBER() OVER (ORDER BY Buffer.Record.value( '@time', 'BIGINT' ) , Buffer.Record.value( '@id', 'INT' ) ) AS [RowNumber] , Data.ring_buffer_type AS [Type] , Buffer.Record.value( '(MemoryBrokerClerk/Name)[1]', 'NVARCHAR(128)' ) AS Name , Buffer.Record.value( '(MemoryBrokerClerk/TotalPages)[1]', 'INT' ) AS TotalPages , Buffer.Record.value( '@time', 'BIGINT' ) AS [time] , Buffer.Record.value( '@id', 'INT' ) AS [Id] , Data.EventXML FROM (SELECT CAST(Record AS XML) AS EventXML , ring_buffer_type FROM sys.dm_os_ring_buffers WHERE ring_buffer_type = 'RING_BUFFER_MEMORY_BROKER_CLERKS' ) AS Data CROSS APPLY EventXML.nodes('//Record') AS Buffer(Record) ) SELECT first.[Type] , summary.[Name] , summary.[TotalPages] , summary.[count] , DATEADD( second , first.[Time] - info.ms_ticks / 1000 , CURRENT_TIMESTAMP ) AS [FirstTime] , DATEADD( second , last.[Time] - info.ms_ticks / 1000 , CURRENT_TIMESTAMP ) AS [LastTime] , first.EventXML AS [FirstRecord] , last.EventXML AS [LastRecord] FROM (SELECT [Name] , [TotalPages] , COUNT(*) AS [count] , MIN(RowNumber) AS [FirstRow] , MAX(RowNumber) AS [LastRow] FROM MemoryBrokerClerksCte GROUP BY [Type] , [Name] , [TotalPages] ) AS summary JOIN MemoryBrokerClerksCte AS first ON first.RowNumber = summary.[FirstRow] JOIN MemoryBrokerClerksCte AS last ON last.RowNumber = summary.[LastRow] CROSS JOIN sys.dm_os_sys_info AS info ORDER BY [Type], [Name];
Andreas Wolter (Blog | Twitter)
MCSM: Microsoft Certified Solutions Master Data Platform/SQL Server 2012
MCM SQL Server 2008
MVP Data Platform
www.SarpedonQualityLab.com | www.andreas-wolter.com -
@@Version:
Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (X64) Jul 9 2008 14:17:44 Copyright (c) 1988-2008 Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2)
So viel zur Version. Es ist ein physischer Server mit 32 GB Arbeitsspeicher. Der wie folgt konfiguriert ist.
Ring_Buffer müsste ich mir wieder unter Arbeitsauslastung ansehen sieht momentan so aus:
CPU laut wait stats:
Freundliche Grüße
Andreas Aichinger
-
Hallo Andreas,
die Daten hast Du aus dem "Memory Consumption Report", oder?
Die Attribute [Virtual Memory Reserved] und [Virtual Memory Committed] sind Werte, die durch VAS (Virtual Address Space) allokiert sind. VAS beschreibt den "total amount of virtual address space" für den PROZESS!
Buffer Pool und VAS sind zwei unterschiedliche Dinge - der Zugriff auf den physikalischen RAM erfolgt immer durch den dem Prozess zur Verfügung stehenden VAS.
Aus der Abbildung würde ich ableiten, dass für den Bufferpool eine Reservierung von 32 GB zur Verfügung steht und von diesem Memory für den Bufferpool 21 GB bereits "committed" sind.
Die 416 KB beschreibt den Wert von [multip_pages_KB] aus der Systemview sys.dm_os_memory_clerks.
Da Du eine Version von Microsoft SQL Server < 2012 verwendest, werden multi pages noch getrennt verwaltet. Hierbei handelt es sich um Zuordnungen, die mehr als 8 KB an "zusammenhängenden Speicher" benötigen.Weitere Informationen zu den einzelnen Werten aus der Systemview bekommst Du hier:
https://msdn.microsoft.com/de-de/library/ms175019.aspx
und einen tieferen Einblick in die Memorystrukturen gibt es z. B. hier:
https://blogs.msdn.microsoft.com/sqljourney/2012/12/16/an-in-depth-look-at-sql-server-memorypart-1/
Zeige doch bitte mal - wenn Du schon den Report verwendest - das Diagramm für die "Buffer Pool Distribution".
PS: Ein Update auf das aktuellste Servicepack würde die Instanz wie eine vertrocknete Blume begrüßen :)
https://sqlserverupdates.com/
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 Freitag, 23. September 2016 13:17
-
als Anmerkung habe ich hinzugefügt das der SQL_BufferPool nach dem Neustart auch nicht mehr KB nutzt.
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) -
PS: Ein Update auf das aktuellste Servicepack würde die Instanz wie eine vertrocknete Blume begrüßen :)
https://sqlserverupdates.com/
+1 für's SP.
Wie stellt sich denn die Speichernutzung für die Datenbank(en) dar, wenn der Server eine gewisse Zeit gelaufen ist?
Ggf. mal hier eine der Diagabfragen von Glenn Berry nutzen.
-- Get total buffer usage by database for current instance (Query 26) (Total Buffer Usage by Database) -- This make take some time to run on a busy instance WITH AggregateBufferPoolUsage AS (SELECT DB_NAME(database_id) AS [Database Name], CAST(COUNT(*) * 8/1024.0 AS DECIMAL (10,2)) AS [CachedSize] FROM sys.dm_os_buffer_descriptors WITH (NOLOCK) WHERE database_id <> 32767 -- ResourceDB GROUP BY DB_NAME(database_id)) SELECT ROW_NUMBER() OVER(ORDER BY CachedSize DESC) AS [Buffer Pool Rank], [Database Name], CachedSize AS [Cached Size (MB)], CAST(CachedSize / SUM(CachedSize) OVER() * 100.0 AS DECIMAL(5,2)) AS [Buffer Pool Percent] FROM AggregateBufferPoolUsage ORDER BY [Buffer Pool Rank] OPTION (RECOMPILE); -- Tells you how much memory (in the buffer pool) -- is being used by each database on the instance
May you never suffer the sentiment of spending a day without any purpose
-
Hallo Andreas,
ich hab gerade auf einem meiner 2008er Server (ok, ist ein R2....) ebenfalls den Report aufgerufen.
Ich habe ein ähnliches Bild, wie Du es auch schilderst.
Aber wenn ich die bereits vorhin gepostete Abfrage absetze, dann bekomme ich doch noch ein paar nützliche Infos dazu:
Es sind also durchaus Datenseiten im Cache gelandet. Nur über den Report nicht so wirklich ersichtlich.
May you never suffer the sentiment of spending a day without any purpose
- Bearbeitet Dirk Hondong Montag, 26. September 2016 06:42
-
Danke für's Gegentesten Dirk
Also, Andreas, wie auch Uwe schon angedeutet hat, hängt das an dem alten Speichermodell von SQL Server 2008.
Lang ist's her..
Und natürlich unbedingt auf SP4 und die letzten Security-Patches(!) danach gehen.
Und damit man auch in Zukunft weiter Unterstützung bekommt, ist eine Migration (im Jahr 2016, also 8 Jahre später) eigentlich nicht wegzureden..
Andreas Wolter (Blog | Twitter)
MCSM: Microsoft Certified Solutions Master Data Platform/SQL Server 2012
MCM SQL Server 2008
MVP Data Platform
www.SarpedonQualityLab.com | www.andreas-wolter.com -
Danke für die Antworten,
unabhängig von SP4 als auch Security Updates welche ohnehin gemacht werden sollten, lässt doch folgendes Bild auf ein Speicherproblem schließen oder fehlerhafte Einstellungen:
Die Abfrage habe ich natürlich auch ausgeführt und die Datenbanken welche im Arbeitsspeicher sein sollten sind auch rank 1-5
Wieso aber wird bei erste Komponente nach Arbeitsspeichernutzung Cachestore_SQLCP angezeigt?
Freundliche Grüße
Andreas Aichinger
-
Hi Andreas,
CacheStore_SQLCP sind die gepufferten Statemens / Batches, welche nicht in Prozeduren, Funktionen oder Triggern vorkommen.
Ich tippe mal, dass du sehr viel ad hoc Abfragen hast. Ich kenne das z.B. von Applikationen, welche immer zur Laufzeit sich Ihre statemens zusammenbauen. Weicht nur ein Parameter ab und sei es nur, dass mal ein Leerzeichen mehr oder weniger verwendet wird, erstellt der SQL Server einen neuen Abfrageplan.
Auch wenn man die Einstellung "Optimize for ad hoc workloads" aktiviert hat, so kann es zu massig gepufferten Statements kommen.
Wahrscheinlich sind in Deinem Cache jede Menge Abfragen mit einem Usecount kleiner 10
Setz doch mal die Abfrage ab und poste das Ergebnis:
SELECT usecounts, SUM(size_in_bytes) AS [size_of_N_use_adhoc_plans_in_bytes] FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_sql_text(plan_handle) WHERE cacheobjtype = N'Compiled Plan' AND objtype = N'Adhoc' AND usecounts BETWEEN 1 AND 10 GROUP BY usecounts;
May you never suffer the sentiment of spending a day without any purpose
-
Ja auf dem Server laufen viele Ad Hoc abfragen die von einem ERP - System abgesetzt werden.
Allerdings ist selbiges ERP-System auch auf anderen Servern installiert die ein anderes Bild zeigen bzgl. Arbeitsspeichernutzung. Unterschiede sind SQL-Server Version.
Weitere Einstellungen um den SQL Server für Ad-Hoc abfragen zu optimieren gibt es?
Minimaler Serverarbeitsspeicher erhöhen da dieser sich auf den BufferPool bezieht und sobald dieser einmal aufgebaut wurde auch nicht mehr freigegeben wird. Liege ich da richtig?
Freundliche Grüße
Andreas Aichinger
-
Hi Andreas,
der SQL Server hat ja noch Luft, wenn man der Grafik gerade Glauben schenken darf.Über 700k free pages. Das sind mehr als 5,5GB. Der SQL Server kam nur noch nicht in die Verlegenheit, mehr Datenseiten zu puffern.
Und die Servereinstellungen, die du gepostet hast, zeigen ja, dass das von mir erwähnte Setting schon aktiv ist ("Für Ad-Hoc Arbeitsauslastungen optimieren").
Was den minimalen Serverarbeitsspeicher angeht:
Das ist quasi die Latte, über die der SQL Server erst einmal springen muss. Oder anders gesagt: damit konfiguriere ich die Menge an RAM, die der SQL Server nicht wieder hergeben soll. Aber erst einmal muss der SQL Server überhaupt diese Menge an Arbeitsspeicher konsumieren.
May you never suffer the sentiment of spending a day without any purpose
- Bearbeitet Dirk Hondong Dienstag, 27. September 2016 11:44
-
Hallo Andreas,
schau Dir mal diesen Artikel von mir an:
Optimieren für Ad-hoc-Arbeitsauslastungen
Scheint sich erledigt zu haben, wie ich dem Posting von Dirk entnehme.
Einen schönen Tag noch, Christoph -- Data Platform MVP - http://www.insidesql.org/blogs/cmu
- Bearbeitet Christoph MuthmannEditor Dienstag, 27. September 2016 11:49 Ergänzung
-
Danke dennoch Christoph aber diese Einstellung war eine der ersten die ich gesetzt habe,
@Dirk free pages sind viele teilweise auch stolen (von anderen Programmen oder OS?) selbes Bild hatte ich allerdings bei 2 Wochen unter Arbeitslast auch.
Wieso werden nicht mehr Pages verwendet wenn Datenbanken mit 30 GB auf dem Server liegen. Werden diese Daten tatsächlich nicht abgerufen?
Dirty Pages sind ja beschriebene Seiten welche vom Lazy Writer Prozess wieder auf die Platten geschrieben werden wenn ich das richtig verstanden habe.
Hier ein vergleich wie dies auf einem aktuelleren stärkeren Server aussieht (dieser Server hat keine free Pages "Luft nach oben") aber trotzdem ist dieser schneller und hat keine Performance Probleme:
wird hier der SQLBufferPool wie zuvor oben bereits einmal geschrieben bzgl MultiPages anders gerechnet. Denn hier hätten wir 15 GB SQLBufferPool im vergleich zum Server auf dem die Probleme sind wo der SqlBufferPool nicht einmal angezeigt wird im Diagramm.- Bearbeitet Andreas Aichinger Dienstag, 27. September 2016 12:05
-
Wenn Daten benötigt werden, werden diese in den Buffer Cache geladen. Wenn also Platz genug ist und er nicht verwendet wird, wird er nicht benötigt. (Ja, das ist eine Milchmädchenrechnung, aber das darf mal sein..)
Jetzt geht es eher Richtung Performance-Analyse. Da kann man mal anfangen mit den Gesamt-Server-Waits, dann auf Datenbanken und auf Abfragen herunterbrechen. So als Einstieg. Aber vielleicht genügt das für eine grobe Richtung warum Arbeitsspeicher nicht das Thema ist.
Andreas Wolter (Blog | Twitter)
MCSM: Microsoft Certified Solutions Master Data Platform/SQL Server 2012
MCM SQL Server 2008
MVP Data Platform
www.SarpedonQualityLab.com | www.andreas-wolter.com- Bearbeitet Andreas.WolterMicrosoft employee Dienstag, 27. September 2016 12:19 Typo
-
Hallo Andreas,
vermutlich ist es nicht das, was Du hören willst, aber Du suchst einen Fehler in einer Version, für die es danach noch 4 Service-Packs gab. Dort wurden unzählige Fehler behoben (und zwischendurch manche neu eingeführt).
Mein Vorschlag: Zuerst SP4 + Security Patches einspielen. Das Betriebssystem hast Du ja auch aktualisiert.
Danach kann man noch mal in die Details gehen, oder?
Einen schönen Tag noch, Christoph -- Data Platform MVP - http://www.insidesql.org/blogs/cmu
- Als Antwort markiert Andreas Aichinger Dienstag, 27. September 2016 12:48
-
Hallo Andreas,
oder mal anders gefragt: gibt es denn mit dem System derzeit Performanceprobleme oder ist Dir diese unausgeglichene Speichernutzung nur so aufgefallen?
Generell scheint es ja , dass es kein wirkliches Speicherproblem gibt. Die Instanz kann 28GB nutzen, so wie es derzeit konfiguriert ist. Die Page Life Expectancy liegt jenseits der 20000. Auf dem ersten Blick würde ich sagen, dass es ganz ok ist, wenn Datenseiten mehr als 5 Stunden im Cache verbleiben. Aber der Wert ist ja nur ein Schnappschuss und keine Langzeitbetrachtung... von daher kann ich mit der Aussage auch falsch liegen.
Was das "Stolen" angeht: dies waren keine anderen Programme oder das OS. Vielmehr hat der SQL Server hier Datenseiten gebraucht, um Prozesse abzubilden, die mit dem Datencaching nichts zu tun haben. Also z.B. eine Sortieroperation durchführen oder eine Hashoperation usw.
Gruß Dirk
May you never suffer the sentiment of spending a day without any purpose
-
Danke für die vielen Antworten ich werde entweder auf einen aktuelleren SQL-Server Updaten oder die SPs + Security Update einspielen und danach noch einmal das Ergebnis Posten wie es danach bei selber Arbeitslast aussieht.
Freundliche Grüeß
Andreas Aichinger