Delen via

Architectuurhandleiding voor geheugenbeheer

van toepassing op:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)

Windows Virtual Memory Manager

De vastgelegde regio's van adresruimte worden toegewezen aan het beschikbare fysieke geheugen door Windows Virtual Memory Manager (VMM).

Zie de Windows-documentatie over geheugenlimieten voor Windows-releases voor meer informatie over de hoeveelheid fysiek geheugen die wordt ondersteund door verschillende besturingssystemen.

Virtuele geheugensystemen maken de overtoezegging van fysiek geheugen mogelijk, zodat de verhouding tussen virtueel en fysiek geheugen groter kan zijn dan 1:1. Hierdoor kunnen grotere programma's worden uitgevoerd op computers met verschillende fysieke geheugenconfiguraties. Het gebruik van aanzienlijk meer virtueel geheugen dan de gecombineerde gemiddelde werksets van alle processen kan echter slechte prestaties veroorzaken.

Sql Server-geheugenarchitectuur

SQL Server verkrijgt dynamisch geheugen en maakt geheugen vrij, indien nodig. Normaal gesproken hoeft een beheerder niet op te geven hoeveel geheugen moet worden toegewezen aan SQL Server, hoewel de optie nog steeds bestaat en vereist is in sommige omgevingen.

Een van de belangrijkste ontwerpdoelen van alle databasesoftware is het minimaliseren van schijf-I/O, omdat schijflees- en schrijfbewerkingen een van de meest resource-intensieve bewerkingen zijn. SQL Server bouwt een buffergroep in het geheugen om pagina's op te slaan die uit de database worden gelezen. Veel van de code in SQL Server is bedoeld om het aantal fysieke lees- en schrijfbewerkingen tussen de schijf en de buffergroep te minimaliseren. SQL Server probeert een evenwicht te bereiken tussen twee doelen:

  • Zorg ervoor dat de buffergroep zo groot wordt dat het hele systeem weinig geheugen heeft.
  • Minimaliseer fysieke I/O naar de databasebestanden door de grootte van de buffergroep te maximaliseren.

In een zwaar geladen systeem kunnen sommige grote query's waarvoor een grote hoeveelheid geheugen moet worden uitgevoerd, de minimale hoeveelheid aangevraagd geheugen niet ophalen en een time-outfout ontvangen tijdens het wachten op geheugenbronnen. U kunt dit oplossen door de querywachtoptie te verhogen. Voor een parallelle query kunt u overwegen om de optie maximale mate van parallelisme te verminderen.

In een zwaar belast systeem onder geheugendruk kunnen query's met samenvoeging, sortering en bitmap in het queryplan de bitmap verwijderen wanneer de query's niet het minimaal vereiste geheugen voor de bitmap krijgen. Dit kan de prestaties van de query beïnvloeden en als het sorteerproces niet in het geheugen past, kan het gebruik van werktabellen in tempdb de database toenemen, wat tempdb kan doen toenemen. U kunt dit probleem oplossen door fysiek geheugen toe te voegen of de query's af te stemmen om een ander en sneller queryplan te gebruiken.

Conventioneel (virtueel) geheugen

Alle SQL Server-edities ondersteunen conventioneel geheugen op een 64-bits platform. Het SQL Server-proces heeft toegang tot de virtuele adresruimte tot het maximum van het besturingssysteem op x64-architectuur (SQL Server Standard Edition ondersteunt maximaal 128 GB). Met IA64-architectuur was de limiet 7 TB (IA64 wordt niet ondersteund in SQL Server 2012 (11.x) en latere versies. Zie Geheugenlimieten voor Windows voor meer informatie.

AWE-geheugen voor Windows-extensies

Met behulp van Address Windowing Extensions (AWE) en de vergrendelingspagina's in het geheugen (LPIM) die zijn vereist door AWE, kunt u het grootste deel van het SQL Server-procesgeheugen vergrendeld houden in fysieke RAM onder weinig virtuele geheugenomstandigheden. Dit gebeurt in zowel 32-bits als 64-bits AWE-toewijzingen. Het vergrendelen van het geheugen treedt op omdat AWE-geheugen niet via Virtual Memory Manager in Windows gaat, waarmee paging van geheugen wordt gecontroleerd. Voor de AWE-geheugentoewijzing-API is de bevoegdheid Vergrendel pagina's in geheugen (SeLockMemoryPrivilege) vereist; zie de opmerkingen over AllocateUserPhysicalPages. Daarom is het belangrijkste voordeel van het gebruik van de AWE-API om het grootste deel van het geheugen in ram-geheugen te houden als er geheugenbelasting op het systeem is. Zie De optie 'Pagina's in het geheugen vergrendelen inschakelen' (Windows) voor informatie over hoe SQL Server AWE kan gebruiken.

Als LPIM wordt verleend, raden we u ten zeerste aan om het maximale servergeheugen (MB) in te stellen op een specifieke waarde, in plaats van de standaardwaarde van 2.147.483.647 mb (MB) te laten staan. Zie Server Memory Server Configuration: Set options manually en Lock pages in memory (LPIM) (Engelstalig) voor meer informatie.

Als LPIM niet is ingeschakeld, schakelt SQL Server over naar het gebruik van conventioneel geheugen en in gevallen van uitputting van het besturingssysteemgeheugen en kan de fout [MSSQLSERVER_17890] (errors-events/mssqlserver-17890-database-engine-error.md) worden gerapporteerd in het foutenlogboek. De fout lijkt op het volgende voorbeeld:

A significant part of SQL Server process memory has been paged out. This may result in a performance degradation. Duration: #### seconds. Working set (KB): ####, committed (KB): ####, memory utilization: ##%.

Wijzigingen in geheugenbeheer vanaf SQL Server 2012

In oudere versies van SQL Server is geheugentoewijzing uitgevoerd met behulp van vijf verschillende mechanismen:

  • Single-Page Allocator (SPA), inclusief alleen geheugentoewijzingen die kleiner zijn dan of gelijk zijn aan 8 kB in het SQL Server-proces. De configuratieopties voor maximaal servergeheugen (MB) en minimaal servergeheugen (MB) bepalen de grenzen van het fysieke geheugen dat door de SPA wordt verbruikt. De Bufferpool was tegelijkertijd het mechanisme voor SPA, en de grootste gebruiker van eenpagina-toewijzingen.
  • Multi-Page Allocator (MPA) voor geheugentoewijzingen die meer dan 8 kB aanvragen.
  • CLR Allocator, inclusief de SQL CLR-heaps en de globale toewijzingen die gemaakt worden tijdens CLR-initialisatie.
  • Geheugentoewijzingen voor threadstacks in het SQL Server-proces.
  • Directe Windows-toewijzingen (DWA) voor aanvragen voor geheugentoewijzing rechtstreeks naar Windows. Dit zijn onder andere het gebruik van Windows-heap en directe virtuele toewijzingen die zijn gemaakt door modules die in het SQL Server-proces worden geladen. Voorbeelden van dergelijke aanvragen voor geheugentoewijzing zijn toewijzingen van uitgebreide opgeslagen procedure-DLL's, objecten die worden gemaakt met behulp van Automation-procedures (sp_OA aanroepen) en toewijzingen van gekoppelde serverproviders.

Vanaf SQL Server 2012 (11.x) worden Single-Page toewijzingen, toewijzingen met meerdere pagina's en CLR-toewijzingen samengevoegd in een paginatoewijzing 'Elke grootte' en opgenomen in geheugenlimieten die worden beheerd door de configuratieopties voor maximaal servergeheugen (MB) en minimale servergeheugen (MB). Deze wijziging biedt een nauwkeurigere grootte voor alle geheugenvereisten die het SQL Server-geheugenbeheer doorlopen.

Belangrijk

Controleer zorgvuldig uw huidige configuraties voor maximaal servergeheugen (MB) en minimale servergeheugen (MB) nadat u een upgrade hebt uitgevoerd naar SQL Server 2012 (11.x) en latere versies. Dit komt doordat dergelijke configuraties nu vanaf SQL Server 2012 (11.x) nu meer geheugentoewijzingen bevatten en er rekening mee houden in vergelijking met eerdere versies. Deze wijzigingen zijn van toepassing op zowel 32-bits als 64-bits versies van SQL Server 2012 (11.x) en SQL Server 2014 (12.x) en 64-bits versies van SQL Server 2016 (13.x) en latere versies.

De volgende tabel geeft aan of een specifiek type geheugentoewijzing wordt beheerd door de configuratieopties voor maximaal servergeheugen (MB) en minimale servergeheugen (MB ):

Type van geheugentoewijzing SQL Server 2005 (9.x), SQL Server 2008 (10.0.x) en SQL Server 2008 R2 (10.50.x) Vanaf SQL Server 2012 (11.x)
Toewijzingen van één pagina Ja Ja, samengevoegd in paginatoewijzingen met een willekeurig formaat
Toewijzingen met meerdere pagina's Nee. Ja, samengevoegd in paginatoewijzingen met een willekeurig formaat
CLR-toewijzingen Nee. Ja
Thread stacks-geheugen Nee. Nee.
Directe toewijzingen vanuit Windows Nee. Nee.

SQL Server kan geheugen doorvoeren via de maximale servergeheugeninstelling

Vanaf SQL Server 2012 (11.x) kan SQL Server meer geheugen toewijzen dan de waarde die is opgegeven in de instelling maximaal servergeheugen (MB). Dit gedrag kan optreden wanneer de waarde totaal servergeheugen (KB) al de instelling Geheugen van de doelserver (KB) heeft bereikt, zoals is opgegeven door het maximale servergeheugen (MB). Als er onvoldoende aaneengesloten geheugen is om te voldoen aan de vraag naar geheugenaanvragen met meerdere pagina's (meer dan 8 kB) vanwege geheugenfragmentatie, kan SQL Server overbezet zijn in plaats van de geheugenaanvraag te weigeren.

Zodra deze toewijzing is uitgevoerd, begint de achtergrondtaak Resource Monitor alle geheugengebruikers te signaleren om het toegewezen geheugen vrij te geven en probeert de waarde totaal servergeheugen (KB) onder de specificatie Doelservergeheugen (KB) te brengen. Daarom kan het geheugengebruik van SQL Server de instelling voor maximaal servergeheugen (MB) kort overschrijden. In deze situatie overschrijdt het prestatiemeteritem Total Server Memory (KB) de instellingen voor maximaal servergeheugen (MB) en doelservergeheugen (KB).

Dit gedrag wordt meestal waargenomen tijdens de volgende bewerkingen:

  • Grote columnstore-indexquery's
  • Grote batchmodus voor query's in rijopslag
  • Columnstore index (re)builds, die grote hoeveelheden geheugen gebruiken om Hash- en Sorteerbewerkingen uit te voeren
  • Back-upbewerkingen waarvoor grote geheugenbuffers zijn vereist
  • Traceringsbewerkingen die grote invoerparameters moeten opslaan
  • Aanvragen voor grote geheugentoekenning

Als u dit gedrag vaak opmerkt, kunt u overwegen traceringsvlag 8121 te gebruiken in SQL Server 2019 (15.x) zodat de Resourcemonitor sneller kan worden opgeschoond. Vanaf SQL Server 2022 (16.x) is deze functionaliteit standaard ingeschakeld en heeft de traceringsvlag geen effect.

Wijzigingen in memory_to_reserve vanaf SQL Server 2012

In oudere versies van SQL Server heeft sql Server-geheugenbeheer een deel van de virtuele adresruimte (VAS) voor het proces gereserveerd voor gebruik door de MULTI-Page Allocator (MPA), CLR Allocator, geheugentoewijzingen voor threadstacks in het SQL Server-proces en Directe Windows-toewijzingen (DWA). Dit deel van de virtuele adresruimte wordt ook wel 'Mem-To-Leave' of 'niet-buffergroep' genoemd.

De virtuele adresruimte die is gereserveerd voor deze toewijzingen, wordt bepaald door de configuratieoptie memory_to_reserve . De standaardwaarde die door SQL Server wordt gebruikt, is 256 MB.

Omdat de 'elke grootte' pagina-allocator ook toewijzingen verwerkt die groter zijn dan 8 kB, omvat de memory_to_reserve-waarde niet de pagina-toewijzingen van meerdere pagina's. Met uitzondering van deze wijziging blijft alles hetzelfde met deze configuratieoptie.

De volgende tabel geeft aan of een specifiek type geheugentoewijzing in de memory_to_reserve regio van de virtuele adresruimte voor het SQL Server-proces valt:

Type van geheugentoewijzing SQL Server 2005 (9.x), SQL Server 2008 (10.0.x) en SQL Server 2008 R2 (10.50.x) Vanaf SQL Server 2012 (11.x)
Toewijzingen van één pagina Nee. Nee, samengevoegd in paginatoewijzingen met een willekeurig formaat
Toewijzingen met meerdere pagina's Ja Nee, samengevoegd in paginatoewijzingen met een willekeurig formaat
CLR-toewijzingen Ja Ja
Thread stacks-geheugen Ja Ja
Directe toewijzingen vanuit Windows Ja Ja

Dynamisch geheugenbeheer

Het standaardgedrag voor geheugenbeheer van de SQL Server-database-engine is om zoveel geheugen te verkrijgen als nodig is zonder dat er een geheugentekort op het systeem ontstaat. De SQL Server-database-engine doet dit met behulp van de Geheugenmelding-API's in Microsoft Windows.

Wanneer SQL Server dynamisch geheugen gebruikt, wordt er periodiek een query uitgevoerd op het systeem om de hoeveelheid vrij geheugen te bepalen. Door dit vrij geheugen te onderhouden, voorkomt u dat het besturingssysteem pagina's verwisselt. Als er minder geheugen vrij is, geeft SQL Server geheugen vrij aan het besturingssysteem. Als er meer geheugen vrij is, kan SQL Server meer geheugen toewijzen. SQL Server voegt alleen geheugen toe wanneer de workload meer geheugen vereist; een server-at-rest vergroot niet de grootte van de virtuele adresruimte. Als u merkt dat Taakbeheer en Prestatiemeter een constante afname van het beschikbare geheugen tonen wanneer SQL Server dynamisch geheugenbeheer gebruikt, is dit het standaardgedrag en mag niet worden gezien als geheugenlek.

Opties voor servergeheugenconfiguratie bepalen de toewijzing van het SQL Server-geheugen, compileren van geheugen, alle caches (inclusief de buffergroep), het verlenen van geheugen voor query-uitvoering, vergrendelingsbeheergeheugen en CLR1-geheugen (in wezen alle geheugenbediende gevonden in sys.dm_os_memory_clerks).

1 CLR-geheugen wordt vanaf SQL Server 2012 (11.x) beheerd onder toewijzingen van max_server_memory.

De volgende query retourneert informatie over momenteel toegewezen geheugen:

SELECT physical_memory_in_use_kb / 1024 AS sql_physical_memory_in_use_MB,
       large_page_allocations_kb / 1024 AS sql_large_page_allocations_MB,
       locked_page_allocations_kb / 1024 AS sql_locked_page_allocations_MB,
       virtual_address_space_reserved_kb / 1024 AS sql_VAS_reserved_MB,
       virtual_address_space_committed_kb / 1024 AS sql_VAS_committed_MB,
       virtual_address_space_available_kb / 1024 AS sql_VAS_available_MB,
       page_fault_count AS sql_page_fault_count,
       memory_utilization_percentage AS sql_memory_utilization_percentage,
       process_physical_memory_low AS sql_process_physical_memory_low,
       process_virtual_memory_low AS sql_process_virtual_memory_low
FROM sys.dm_os_process_memory;

Stapelgrootten

Geheugen voor threadstacks 1, CLR 2, uitgebreide procedure .dll bestanden, de OLE DB-providers waarnaar wordt verwezen door gedistribueerde query's, automatiseringsobjecten waarnaar wordt verwezen in Transact-SQL instructies, en geheugen toegewezen door een niet-SQL Server DLL, worden niet beheerd door het maximale servergeheugen (MB).

1 Raadpleeg het artikel over het configureren van de maximale werkrolthreads (serverconfiguratieoptie) voor informatie over de berekende standaardwerkthreads voor een bepaald aantal geaffinaliseerde CPU's in de huidige host. SQL Server-stackgrootten zijn als volgt:

SQL Server-architectuur Architectuur van het besturingssysteem Stackgrootte
x86 (32-bits) x86 (32-bits) 512 KB
x86 (32-bits) x64 (64-bits) 768 KB
x64 (64-bits) x64 (64-bits) 2048 KB
IA64 (Itanium) IA64 (Itanium) 4.096 KB

Vanaf SQL Server 2012 (11.x) wordt 2 CLR-geheugen beheerd volgens max_server_memory-allocaties.

SQL Server maakt gebruik van de geheugenmeldings-API QueryMemoryResourceNotification om te bepalen wanneer sql Server-geheugenbeheer geheugen kan toewijzen en geheugen vrijgeven.

Wanneer SQL Server wordt gestart, wordt de grootte van de virtuele adresruimte voor de buffergroep berekend op basis van verschillende parameters, zoals de hoeveelheid fysiek geheugen op het systeem, het aantal serverthreads en verschillende opstartparameters. SQL Server reserveert de berekende hoeveelheid van de virtuele procesadresruimte voor de buffergroep, maar verkrijgt (doorvoeringen) alleen de vereiste hoeveelheid fysiek geheugen voor de huidige belasting.

Het exemplaar blijft vervolgens zo nodig geheugen verkrijgen om de workload te ondersteunen. Naarmate meer gebruikers verbinding maken en query's uitvoeren, krijgt SQL Server meer fysiek geheugen op aanvraag. Een SQL Server-exemplaar blijft fysiek geheugen verkrijgen totdat het de toewijzingsdoel voor het maximale servergeheugen (MB) bereikt of het besturingssysteem geeft aan dat er geen overtollig geheugen meer is; het maakt geheugen vrij wanneer het meer is dan de minimale servergeheugeninstelling en het besturingssysteem geeft aan dat er een tekort aan vrij geheugen is.

Wanneer andere toepassingen worden gestart op een computer waarop een exemplaar van SQL Server wordt uitgevoerd, verbruiken ze geheugen en de hoeveelheid vrij fysiek geheugen daalt onder het SQL Server-doel. Het exemplaar van SQL Server past het geheugenverbruik aan. Als een andere toepassing wordt gestopt en er meer geheugen beschikbaar komt, verhoogt het exemplaar van SQL Server de grootte van de geheugentoewijzing. SQL Server kan elke seconde verschillende megabytes aan geheugen vrij krijgen en verkrijgen, zodat deze snel kan worden aangepast aan wijzigingen in de geheugentoewijzing.

Effecten van minimale en maximale servergeheugen

Met de minimale servergeheugen - en maximale servergeheugenconfiguratieopties worden hogere en lagere limieten ingesteld voor de hoeveelheid geheugen die wordt gebruikt door de buffergroep en andere caches van de database-engine. De buffergroep verkrijgt niet onmiddellijk de hoeveelheid geheugen die is opgegeven in min servergeheugen. De buffergroep begint alleen met het geheugen dat nodig is om te initialiseren. Naarmate de werklast van de SQL Server Database Engine toeneemt, blijft deze het benodigde geheugen verwerven om de werklast te ondersteunen. De buffergroep maakt geen van de verkregen geheugen vrij totdat deze de hoeveelheid bereikt die is opgegeven in min servergeheugen. Zodra het minimale servergeheugen is bereikt, gebruikt de buffergroep vervolgens het standaard algoritme om zo nodig geheugen te verkrijgen en vrij te maken. Het enige verschil is dat de buffergroep nooit de geheugentoewijzing onder het niveau daalt dat is opgegeven in min servergeheugen en nooit meer geheugen krijgt dan het niveau dat is opgegeven in het maximale servergeheugen (MB).

Opmerking

SQL Server als proces krijgt meer geheugen dan is opgegeven door de optie max. servergeheugen (MB). Zowel interne als externe onderdelen kunnen geheugen buiten de buffergroep toewijzen, wat extra geheugen verbruikt, maar het geheugen dat aan de buffergroep is toegewezen, vertegenwoordigt meestal nog steeds het grootste deel van het geheugen dat wordt verbruikt door SQL Server.

De hoeveelheid geheugen die is verkregen door de SQL Server-database-engine, is volledig afhankelijk van de werkbelasting die op het exemplaar is geplaatst. Een SQL Server-exemplaar dat niet veel aanvragen verwerkt, bereikt mogelijk nooit de waarde die is opgegeven door minimaal servergeheugen.

Als dezelfde waarde is opgegeven voor zowel het minimale servergeheugen als het maximale servergeheugen (MB), stopt de SQL Server-database-engine met het dynamisch vrijmaken en toewijzen van geheugen voor de buffergroep zodra het geheugen dat aan de SQL Server Database Engine is toegewezen deze waarde bereikt.

Als een exemplaar van SQL Server wordt uitgevoerd op een computer waarop andere toepassingen regelmatig worden gestopt of gestart, kan de toewijzing en vrijgave van geheugen door SQL Server de opstarttijden van andere toepassingen vertragen. Als SQL Server een van de verschillende servertoepassingen is die op één computer worden uitgevoerd, moeten de systeembeheerders ook de hoeveelheid geheugen beheren die aan SQL Server is toegewezen. In deze gevallen kunt u de minimale servergeheugen- en maximale servergeheugenopties (MB) gebruiken om te bepalen hoeveel geheugen SQL Server kan gebruiken. De minimale servergeheugen en de maximale servergeheugenopties worden opgegeven in megabytes. Zie Opties voor servergeheugenconfiguratie voor meer informatie, waaronder aanbevelingen voor het instellen van deze geheugenconfiguraties.

Specificaties van het geheugengebruik door SQL Server-objecten

In de volgende lijst wordt de geschatte hoeveelheid geheugen beschreven die door verschillende objecten in SQL Server wordt gebruikt. De vermelde bedragen zijn schattingen en kunnen variëren, afhankelijk van de omgeving en hoe objecten worden gemaakt:

  • Vergrendelen (zoals onderhouden door Lock Manager): 64 bytes + 32 bytes per eigenaar
  • Gebruikersverbinding: ongeveer (3 * network_packet_size + 94 KB)

De grootte van het netwerkpakket is de grootte van de TDS-pakketten (tabellaire gegevensstroom) die worden gebruikt om te communiceren tussen toepassingen en de database-engine. De standaardpakketgrootte is 4 kB en wordt beheerd door de configuratieoptie voor netwerkpakketten.

Wanneer meerdere actieve resultatensets (MARS) zijn ingeschakeld, is de gebruikersverbinding ongeveer (3 + 3 * num_logical_connections) * network_packet_size + 94 kB.

Effecten van minimumgeheugen per query

Met de minimale hoeveelheid geheugen per queryconfiguratie wordt de minimale hoeveelheid geheugen (in kilobytes) vastgesteld die wordt toegewezen voor de uitvoering van een query. Dit wordt ook wel de minimale geheugentoekenning genoemd. Alle query's moeten wachten totdat het aangevraagde minimumgeheugen kan worden beveiligd, voordat de uitvoering kan worden gestart of totdat de waarde die is opgegeven in de configuratieoptie van de querywachtserver wordt overschreden. Het wachttype dat in dit scenario wordt verzameld, is RESOURCE_SEMAPHORE.

Belangrijk

Stel de minimale hoeveelheid geheugen per queryserverconfiguratieoptie niet te hoog in, met name op zeer drukke systemen, omdat dit kan leiden tot:

  • Meer concurrentie voor geheugenbronnen.
  • Verminderde gelijktijdigheid door de hoeveelheid geheugen voor elke query te verhogen, zelfs als het vereiste geheugen tijdens runtime lager is dan deze configuratie.

Zie Het minimale geheugen per query configureren Serverconfiguratieoptie voor aanbevelingen over het gebruik van deze configuratie.

Overwegingen bij geheugentoewijzing

Voor rijmodus-uitvoering kan de initiële geheugentoekenning onder geen enkele voorwaarde worden overschreden. Als er meer geheugen nodig is dan de eerste toekenning nodig is om hash - of sorteerbewerkingen uit te voeren, lopen de bewerkingen over op schijf. Een hash-bewerking die overloopt, wordt ondersteund door een Workfile in tempdb, terwijl een sorteerbewerking die overloopt wordt ondersteund door een Worktable.

Een overloop die optreedt tijdens een sorteerbewerking, wordt een gebeurtenisklasse voor sorteringswaarschuwingen genoemd. Sorteerwaarschuwingen geven aan dat sorteerbewerkingen niet in het geheugen passen. Dit omvat geen sorteerbewerkingen met betrekking tot het maken van indexen, maar alleen sorteerbewerkingen binnen een query (zoals een ORDER BY component die in een SELECT instructie wordt gebruikt).

Een overloop die optreedt tijdens een hash-bewerking wordt een hashwaarschuwingsklasse genoemd. Deze treden op wanneer een hash-recursie of beëindiging van hashing (hash-bailout) is opgetreden tijdens een hash-bewerking.

  • Hash-recursie treedt op wanneer de build-invoer niet in het beschikbare geheugen past, wat resulteert in het splitsen van invoer in meerdere partities die afzonderlijk worden verwerkt. Als een van deze partities nog steeds niet in het beschikbare geheugen past, wordt deze gesplitst in subpartities, die ook afzonderlijk worden verwerkt. Dit splitsingsproces gaat door totdat elke partitie in het beschikbare geheugen past of totdat het maximale recursieniveau is bereikt.
  • Hash-borging treedt op wanneer een hash-bewerking het maximale recursieniveau bereikt en naar een alternatief plan wordt verplaatst om de resterende gepartitioneerde gegevens te verwerken. Deze gebeurtenissen kunnen leiden tot verminderde prestaties op uw server.

Voor de uitvoering van de batchmodus kan de initiële geheugentoezening standaard tot een bepaalde interne drempelwaarde dynamisch toenemen. Dit mechanisme voor dynamische geheugentoekenningen is ontworpen om de uitvoering van hash- of sorteerbewerkingen in de batchmodus toe te staan. Als deze bewerkingen nog steeds niet in het geheugen passen, lopen de bewerkingen over op schijf.

Zie de architectuurhandleiding voor queryverwerking voor meer informatie over uitvoeringsmodi.

Bufferbeheer

Het primaire doel van een SQL Server-database is het opslaan en ophalen van gegevens, dus intensieve schijf-I/O is een kernkenmerk van de database-engine. En omdat schijf-I/O-bewerkingen veel resources kunnen verbruiken en relatief lang kunnen duren, is SQL Server gericht op het zeer efficiënt maken van I/O. Bufferbeheer is een belangrijk onderdeel bij het bereiken van deze efficiëntie. Het bufferbeheeronderdeel bestaat uit twee mechanismen: bufferbeheer voor toegang tot databasepagina's en het bijwerken van databasepagina's, en de buffercache (ook wel de buffergroep genoemd), om de I/O van het databasebestand te verminderen.

Zie de basisprincipes van SQL Server I/O voor een gedetailleerde uitleg van schijf-I/O in SQL Server.

Hoe bufferbeheer werkt

Een buffer is een pagina van 8 kB in het geheugen, dezelfde grootte als een gegevens- of indexpagina. De buffercache is dus onderverdeeld in pagina's van 8 kB. De bufferbeheerder beheert de functies voor het lezen van gegevens of indexpagina's van de databaseschijfbestanden in de buffercache en het terugschrijven van gewijzigde pagina's naar schijf. Een pagina blijft in de buffercache totdat het bufferbeheer het buffergebied nodig heeft om meer gegevens te lezen. Gegevens worden alleen teruggeschreven naar de schijf als deze worden gewijzigd. Gegevens in de buffercache kunnen meerdere keren worden gewijzigd voordat ze terug naar de schijf worden geschreven. Zie Leespagina's en Schrijfpagina's voor meer informatie.

Wanneer SQL Server wordt gestart, wordt de grootte van de virtuele adresruimte voor de buffercache berekend op basis van verschillende parameters, zoals de hoeveelheid fysiek geheugen op het systeem, het geconfigureerde aantal maximale serverthreads en verschillende opstartparameters. SQL Server reserveert deze berekende hoeveelheid van de virtuele procesadresruimte, ook wel het geheugendoel genoemd, voor de buffercache, maar gebruikt alleen de vereiste hoeveelheid fysiek geheugen voor de huidige belasting. U kunt een query uitvoeren op de kolommen committed_target_kb en committed_kbin de sys.dm_os_sys_info catalogusweergave om het aantal pagina's te retourneren dat is gereserveerd als geheugendoel en het aantal pagina's dat momenteel is vastgelegd in de buffercache.

Het interval tussen het opstarten van SQL Server en wanneer de buffercache het geheugendoel verkrijgt, wordt ramp-up genoemd. Gedurende deze tijd vullen leesaanvragen de buffers zo nodig in. Een leesaanvraag voor één pagina met 8 kB vult bijvoorbeeld één bufferpagina in. Dit betekent dat de opschaling afhankelijk is van het aantal en het type klantverzoeken. Versnelling van de opstartfase wordt bereikt door leesaanvragen van één pagina te transformeren in uitgelijnde aanvragen van acht pagina's (die samen één extent vormen). Hierdoor kan de ramp veel sneller worden voltooid, met name op machines met veel geheugen. Voor meer informatie over pagina's en gebieden, zie de architectuurgids voor pagina's en gebieden.

Omdat de bufferbeheerder het grootste deel van het geheugen in het SQL Server-proces gebruikt, werkt het samen met de geheugenbeheerder om andere onderdelen toe te staan de buffers te gebruiken. De bufferbeheerder communiceert voornamelijk met de volgende onderdelen:

  • Resource Manager voor het beheren van het totale geheugengebruik en, in 32-bits platforms, om het gebruik van adresruimte te beheren.
  • Databasebeheer en het SQL Server-besturingssysteem (SQLOS) voor I/O-bewerkingen op laag niveau.
  • Log Manager voor vooruit schrijvende logging.

Ondersteunde functies

De bufferbeheerder ondersteunt de volgende functies:

  • De bufferbeheerder is niet-uniform geheugentoegang (NUMA) bewust. Buffercachepagina's worden verdeeld over hardware-NUMA-knooppunten, waardoor een thread toegang heeft tot een bufferpagina die is toegewezen op het lokale NUMA-knooppunt in plaats van uit extern geheugen.

  • De bufferbeheerder ondersteunt Hot Add Memory, waardoor gebruikers fysiek geheugen kunnen toevoegen zonder de server opnieuw op te starten.

  • De bufferbeheerder ondersteunt grote pagina's op 64-bits platforms. Het paginaformaat is specifiek voor de versie van Windows.

    Opmerking

    Vóór SQL Server 2012 (11.x) is traceringsvlag 834 vereist voor het inschakelen van grote pagina's in SQL Server.

  • De bufferbeheerder biedt extra diagnostische gegevens die beschikbaar worden gesteld via dynamische beheerweergaven. U kunt deze weergaven gebruiken om verschillende besturingssysteembronnen te bewaken die specifiek zijn voor SQL Server. U kunt bijvoorbeeld de sys.dm_os_buffer_descriptors weergave gebruiken om de pagina's in de buffercache te bewaken.

Geheugendrukdetectie

Geheugendruk is een toestand die het gevolg is van geheugentekort en kan leiden tot:

  • Extra I/Os (zoals de zeer actieve lazy writer achtergrondtaak)
  • Hogere verhouding voor opnieuw compileren
  • Query's die langer worden uitgevoerd (als er wachttijden voor geheugentoekenningen bestaan)
  • Extra CPU-tijd

Deze situatie kan worden geactiveerd door externe of interne oorzaken. Externe oorzaken zijn onder andere:

  • Het beschikbare fysieke geheugen (RAM) is laag. Dit zorgt ervoor dat het systeem werksets van de huidige actieve processen inkromp, wat kan leiden tot een algehele vertraging van het systeem. SQL Server kan het doorvoerdoel van de buffergroep verminderen en interne caches vaker inkorten.
  • Algemeen beschikbaar systeemgeheugen (inclusief het systeempaginabestand) is laag. Dit kan ertoe leiden dat het systeem geheugentoewijzingen mislukt, omdat het momenteel toegewezen geheugen niet kan worden uitgepaginad.

Interne oorzaken zijn onder andere:

  • Reageren op de druk van het externe geheugen wanneer de SQL Server-database-engine lagere geheugengebruikslimieten instelt.
  • Geheugeninstellingen zijn handmatig verlaagd door de maximale servergeheugenconfiguratie te verminderen.
  • Wijzigingen in de geheugendistributie van interne onderdelen tussen de verschillende caches.

De SQL Server Database Engine implementeert een framework dat is toegewezen aan het detecteren en verwerken van geheugenbelasting als onderdeel van het dynamische geheugenbeheer. Dit framework bevat de achtergrondtaak genaamd Resource Monitor. De resourcemonitor-taak bewaakt de status van externe en interne geheugenindicatoren. Zodra een van deze indicatoren de status wijzigt, wordt de bijbehorende melding berekend en wordt deze uitgezonden. Deze meldingen zijn interne berichten van elk van de engineonderdelen en worden opgeslagen in ringbuffers.

Twee ringbuffers bevatten informatie die relevant is voor dynamisch geheugenbeheer:

  • De Resource Monitor-ringbuffer, waarmee resource-monitoractiviteit wordt bijgehouden, zoals of er geheugendruk is gesignaleerd of niet. Deze ringbuffer heeft statusinformatie, afhankelijk van de huidige toestand van RESOURCE_MEMPHYSICAL_HIGH, RESOURCE_MEMPHYSICAL_LOW, RESOURCE_MEMPHYSICAL_STEADY, of RESOURCE_MEMVIRTUAL_LOW.
  • De ringbuffer van de Memory Broker, die records met geheugenmeldingen voor elke bronpool van de Resource Governor bevat. Wanneer interne geheugenbelasting wordt gedetecteerd, wordt een melding met weinig geheugen ingeschakeld voor onderdelen die geheugen toewijzen, om acties te activeren die zijn bedoeld om het geheugen tussen caches te verdelen.

Geheugenbrokers controleren het geheugengebruik door de vraag van elk onderdeel en berekenen vervolgens op basis van de verzamelde informatie de optimale waarde van het geheugen voor elk van deze onderdelen. Er is een set brokers voor elke Resource Governor-resourcegroep. Deze informatie wordt vervolgens uitgezonden naar elk van de onderdelen, die hun gebruik naar behoefte vergroten of verkleinen.

Zie sys.dm_os_memory_brokers voor meer informatie over geheugenbrokers.

Foutdetectie

Databasepagina's kunnen een van de twee optionele mechanismen gebruiken om de integriteit van de pagina te garanderen, vanaf het moment dat deze naar de schijf wordt geschreven, totdat deze opnieuw wordt gelezen: bescherming tegen gescheurde pagina's en checksum bescherming. Deze mechanismen bieden een onafhankelijke methode voor het controleren van de juistheid van niet alleen de gegevensopslag, maar hardwareonderdelen zoals controllers, stuurprogramma's, kabels en zelfs het besturingssysteem. De beveiliging wordt net voordat deze naar de schijf wordt geschreven, toegevoegd aan de pagina en gecontroleerd nadat deze van schijf is gelezen.

SQL Server probeert eventuele leesbewerkingen die mislukken met een controlesom, gescheurde pagina of andere I/O-fout vier keer opnieuw. Als de leesbewerking is geslaagd in een van de nieuwe pogingen, wordt een bericht naar het foutenlogboek geschreven en wordt de opdracht die de leesbewerking heeft geactiveerd, voortgezet. Als de nieuwe pogingen mislukken, mislukt de opdracht met de MSSQLSERVER_824 fout.

Het type paginabeveiliging dat wordt gebruikt, is een kenmerk van de database met de pagina. Controlesombeveiliging is de standaardbeveiliging voor databases die zijn gemaakt in SQL Server 2005 (9.x) en latere versies. Het mechanisme voor paginabeveiliging wordt opgegeven tijdens het maken van de database en kan worden gewijzigd met behulp van ALTER DATABASE SET. U kunt de huidige instelling voor paginabeveiliging bepalen door een query uit te voeren op de page_verify_option kolom in de catalogusweergave sys.databases of de IsTornPageDetectionEnabled eigenschap van de functie DATABASEPROPERTYEX .

Opmerking

Als de instelling voor paginabeveiliging wordt gewijzigd, heeft de nieuwe instelling niet onmiddellijk invloed op de hele database. In plaats daarvan gebruiken pagina's het huidige beveiligingsniveau van de database wanneer ze hierna worden geschreven. Dit betekent dat de database kan bestaan uit pagina's met verschillende soorten beveiliging.

Beveiliging van gescheurde pagina's

Bescherming tegen gescheurde pagina's, geïntroduceerd in SQL Server 2000 (8.x), is voornamelijk een manier om paginabeschadigingen te detecteren ten gevolge van stroomuitval. Een onverwachte stroomstoring kan bijvoorbeeld ervoor zorgen dat slechts een deel van een pagina naar de schijf wordt geschreven. Wanneer paginabeveiliging wordt gebruikt, wordt een specifiek 2-bits handtekeningpatroon gebruikt voor elke sector van 512 bytes op de databasepagina van 8 kilobyte (KB) en opgeslagen in de koptekst van de databasepagina wanneer de pagina naar de schijf wordt geschreven.

Wanneer de pagina wordt gelezen van schijf, worden de gescheurde bits die zijn opgeslagen in de paginakoptekst vergeleken met de werkelijke informatie over de paginasector. Het kenmerkpatroon wisselt af tussen binair 01 en 10 bij elke schrijfbewerking, dus het is altijd mogelijk om te zien wanneer slechts een deel van de sectoren naar de schijf zijn geschreven: als een bit in de verkeerde staat is wanneer de pagina later wordt gelezen, is de pagina onjuist geschreven en wordt er een gescheurde pagina gedetecteerd. Detectie van gescheurde pagina's maakt gebruik van minimale resources; Er worden echter niet alle fouten gedetecteerd die worden veroorzaakt door schijfhardwarefouten. Zie ALTER DATABASE SET Options (Transact-SQL) voor informatie over het instellen van de paginadetectie van gescheurde pagina's.

Checksum bescherming

Controlesombeveiliging, geïntroduceerd in SQL Server 2005 (9.x), biedt een sterkere controle van gegevensintegriteit. Er wordt een controlesom berekend voor de gegevens op elke pagina die is geschreven en opgeslagen in de paginakoptekst. Wanneer een pagina met een opgeslagen controlesom van schijf wordt gelezen, berekent de database-engine de controlesom voor de gegevens op de pagina opnieuw en treedt fout 824 op als de nieuwe controlesom verschilt van de opgeslagen controlesom. Checksum-beveiliging kan meer fouten onderscheppen dan paginabeveiliging, omdat het wordt beïnvloed door elke byte van de pagina, maar het is matig veeleisend qua middelen.

Wanneer controlesom is ingeschakeld, kunnen fouten die worden veroorzaakt door stroomstoringen en defecte hardware of firmware worden gedetecteerd wanneer de bufferbeheerder een pagina van de schijf leest. Zie ALTER DATABASE SET Options (Transact-SQL)voor informatie over het instellen van controlesom.

Belangrijk

Wanneer een gebruiker of systeemdatabase wordt bijgewerkt naar SQL Server 2005 (9.x) of hoger, wordt de PAGE_VERIFY waarde (NONE of TORN_PAGE_DETECTION) behouden. We raden u ten zeerste aan om te gebruiken CHECKSUM. TORN_PAGE_DETECTION kan minder resources gebruiken, maar biedt een minimale subset van de CHECKSUM beveiliging.

Inzicht krijgen in niet-uniforme geheugentoegang

SQL Server is zich bewust van niet-uniforme geheugentoegang (NUMA) en presteert goed op hardware met NUMA zonder speciale configuratie. Naarmate de kloksnelheid en het aantal processors toeneemt, wordt het steeds moeilijker om de geheugenlatentie te verminderen die nodig is om deze extra verwerkingskracht te gebruiken. Om dit te omzeilen, bieden hardwareleveranciers grote L3-caches, maar dit is slechts een beperkte oplossing. NUMA-architectuur biedt een schaalbare oplossing voor dit probleem.

SQL Server is ontworpen om te profiteren van NUMA-computers zonder dat er toepassingswijzigingen nodig zijn. Zie Soft-NUMA (SQL Server) voor meer informatie.

Dynamische partitie van geheugenobjecten

Met Heap-toewijzingen, ook wel geheugenobjecten genoemd in SQL Server, kan de database-engine geheugen toewijzen vanuit de heap. Deze kunnen worden bijgehouden met behulp van de sys.dm_os_memory_objects DMV.

CMemThread is een thread-veilig geheugenobjecttype dat gelijktijdige geheugentoewijzingen van meerdere threads toestaat. Voor een correcte tracering CMemThread zijn objecten afhankelijk van synchronisatieconstructies (een mutex) om ervoor te zorgen dat slechts één thread kritieke stukjes informatie tegelijk bijwerkt.

Opmerking

Het CMemThread objecttype wordt gebruikt in de database-enginecodebasis voor veel verschillende toewijzingen en kan globaal worden gepartitioneerd, per knooppunt of door CPU.

Het gebruik van mutexes kan echter leiden tot conflicten als veel threads in hoge mate gelijktijdig geheugen toewijzen vanuit hetzelfde geheugenobject. Daarom heeft SQL Server het concept van gepartitioneerde geheugenobjecten (PMO) en elke partitie wordt vertegenwoordigd door één CMemThread object. De partitionering van een geheugenobject is statisch gedefinieerd en kan niet worden gewijzigd na het maken. Aangezien geheugentoewijzingspatronen sterk variëren op basis van aspecten zoals hardware en geheugengebruik, is het onmogelijk om vooraf het perfecte partitioneringspatroon op te stellen.

In de meeste gevallen volstaat het gebruik van één partitie, maar in sommige scenario's kan dit leiden tot conflicten, die alleen met een zeer gepartitioneerd geheugenobject kunnen worden voorkomen. Het is niet wenselijk om elk geheugenobject te partitioneren, omdat meer partities kunnen leiden tot andere inefficiënties en meer geheugenfragmentatie.

Opmerking

Vóór SQL Server 2016 (13.x) kan traceringsvlag 8048 worden gebruikt om een op knooppunten gebaseerde PMO te dwingen om een OP CPU gebaseerde PMO te worden. Vanaf SQL Server 2014 (12.x) SP2 en SQL Server 2016 (13.x) wordt dit gedrag dynamisch en beheerd door de engine.

Vanaf SQL Server 2014 (12.x) SP2 en SQL Server 2016 (13.x) kan de Database Engine dynamisch conflicten op een specifiek CMemThread object detecteren en het object promoveren naar een per knooppunt of een implementatie per CPU. Zodra de PMO is gepromoveerd, blijft de PMO gepromoveerd totdat het SQL Server-proces opnieuw wordt gestart. CMemThread conflicten kunnen worden gedetecteerd door de aanwezigheid van hoge CMEMTHREAD wachttijden in de sys.dm_os_wait_stats DMV en door de sys.dm_os_memory_objects DMV-kolommen contention_factor, partition_type, exclusive_allocations_counten waiting_tasks_count.

Verwante inhoud