none
SQL_BufferPool nutzt Speicher nicht. RRS feed

  • 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?

    SQL-Performance

    Freundliche Grüße

    Andreas Aichinger



    Freitag, 23. September 2016 09:54

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

    Dienstag, 27. September 2016 12:20
    Beantworter

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

    Freitag, 23. September 2016 10:45
  • 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
  • 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
  • @@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
  • 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
  • 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
  • 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
  • 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
  • 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
  • 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
  • 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
  • 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
  • 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
  • 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
  • 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
  • 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
  • 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
  • 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
  • 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
  • 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
  • Versionsunterschied im ERP System ist bzgl. Datenverwaltung/Abfragengestaltung minimal. 

    Andreas Aichinger

    Dienstag, 27. September 2016 13:04