none
SQL_BufferPool nutzt Speicher nicht. RRS feed

 > 

  • Frage

  • Question
    Anmelden
    0
    Anmelden

    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?

    SQL-Performance

    Freundliche Grüße

    Andreas Aichinger



    Freitag, 23. September 2016 09:54
    |

Antworten

  • Question
    Anmelden
    1
    Anmelden

    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

    Dienstag, 27. September 2016 12:20
    |
    Beantworter

Alle Antworten

  • Question
    Anmelden
    0
    Anmelden

    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

    Freitag, 23. September 2016 10:45
    |
  • Question
    Anmelden
    0
    Anmelden

    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

    Freitag, 23. September 2016 10:57
    |
  • Question
    Anmelden
    2
    Anmelden

    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

    Freitag, 23. September 2016 11:29
    |
  • Question
    Anmelden
    0
    Anmelden

    @@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

    Freitag, 23. September 2016 11:52
    |
  • Question
    Anmelden
    3
    Anmelden

    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)


    Freitag, 23. September 2016 13:13
    |
  • Question
    Anmelden
    0
    Anmelden

    als Anmerkung habe ich hinzugefügt das der SQL_BufferPool nach dem Neustart auch nicht mehr KB nutzt.

    Was logisch ist, da SQL Server 2008 noch multi_pages vom Buffer Pool getrennt hatte. Die Einstellungen für den Buffer Pool betrafen nur den Single page allocator!

    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)

    Freitag, 23. September 2016 13:19
    |
  • Question
    Anmelden
    0
    Anmelden

    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

    Montag, 26. September 2016 06:36
    |
  • Question
    Anmelden
    1
    Anmelden

    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


    Montag, 26. September 2016 06:41
    |
  • Question
    Anmelden
    0
    Anmelden

    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

    Montag, 26. September 2016 08:37
    |
  • Question
    Anmelden
    0
    Anmelden

    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

    Dienstag, 27. September 2016 11:10
    |
  • Question
    Anmelden
    0
    Anmelden

    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

    Dienstag, 27. September 2016 11:26
    |
  • Question
    Anmelden
    0
    Anmelden

    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

    Dienstag, 27. September 2016 11:34
    |
  • Question
    Anmelden
    1
    Anmelden

    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
    Dienstag, 27. September 2016 11:39
    |
  • Question
    Anmelden
    2
    Anmelden

    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


    Dienstag, 27. September 2016 11:40
    |
    Beantworter
  • Question
    Anmelden
    0
    Anmelden

    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. 
    Dienstag, 27. September 2016 12:01
    |
  • Question
    Anmelden
    0
    Anmelden

    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


    Dienstag, 27. September 2016 12:17
    |
  • Question
    Anmelden
    1
    Anmelden

    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

    Dienstag, 27. September 2016 12:20
    |
    Beantworter
  • Question
    Anmelden
    0
    Anmelden

    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

    Dienstag, 27. September 2016 12:29
    |
  • Question
    Anmelden
    1
    Anmelden

    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

    Dienstag, 27. September 2016 12:51
    |
  • Question
    Anmelden
    0
    Anmelden
    Eine Frage ist mir gerade noch in den Sinn gekommen: Ist die ERP Version identisch?

    May you never suffer the sentiment of spending a day without any purpose

    Dienstag, 27. September 2016 12:52
    |
  • Question
    Anmelden
    0
    Anmelden
    Versionsunterschied im ERP System ist bzgl. Datenverwaltung/Abfragengestaltung minimal. 

    Andreas Aichinger

    Dienstag, 27. September 2016 13:04
    |