Konfigurer SQL Server riktig: Minne, MAXDOP og tempdb
SQL Server kommer med standardinnstillinger som er laget for å virke overalt – fra en bærbar PC til en server med hundrevis av kjerner. Det betyr at de sjelden er optimale for din server. Noen få innstillinger utgjør mesteparten av forskjellen: hvor mye minne SQL Server får bruke, hvordan den parallelliserer spørringer, og hvordan tempdb og databasefilene er satt opp. Denne guiden går gjennom dem én for én, med anbefalte verdier og T-SQL for å sette dem.
- Sett max server memory, slik at operativsystemet får beholde minne.
- Sett MAXDOP til antall kjerner per NUMA-node, maks 8, og øk cost threshold for parallelism til rundt 50.
- Gi tempdb flere datafiler av lik størrelse (inntil 8).
- Bruk fast filvekst i MB, slå på instant file initialization, og la auto shrink være av.
Se hva du har i dag
SELECT name, value, value_in_use, description
FROM sys.configurations
WHERE name IN ('max server memory (MB)', 'min server memory (MB)',
'max degree of parallelism', 'cost threshold for parallelism',
'optimize for ad hoc workloads', 'backup compression default',
'remote admin connections')
ORDER BY name;
Mange av innstillingene er «avanserte» og krever at du slår på visning av dem først:
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
Minne: max server memory
SQL Server bruker minne til å holde datasider i bufferet, og gir det i utgangspunktet ikke fra seg. Standardverdien for max server memory var i mange år «alt» (2 147 483 647 MB), og det er grunnen til at mange ser at SQL Server «bruker alt minnet». Det er tilsiktet – men operativsystemet, antivirus, backupagenter og andre tjenester trenger også minne.
Et vanlig utgangspunkt er å la operativsystemet få 10 prosent av minnet, men minst 4 GB:
| Minne i serveren | Forslag til max server memory |
|---|---|
| 16 GB | 12 GB (12288 MB) |
| 32 GB | 28 GB (28672 MB) |
| 64 GB | 57 GB (58368 MB) |
| 128 GB | 115 GB (117760 MB) |
EXEC sp_configure 'max server memory (MB)', 28672;
RECONFIGURE;
Kjører det flere instanser eller andre tunge tjenester (SSIS, SSAS, applikasjonsserver) på samme maskin, må du trekke fra mer. Følg med på telleren Available MBytes i Windows – den bør holde seg godt over 1–2 GB.
Lock pages in memory
Rettigheten Lock pages in memory for tjenestekontoen hindrer Windows i å skyve SQL Servers minne ut til sidevekslingsfilen. Den er nyttig på dedikerte databaseservere, men gjør det enda viktigere at max server memory er satt riktig.
Parallellitet: MAXDOP og cost threshold
SQL Server kan dele en tung spørring på flere kjerner. Det er bra for store rapporter, men mange små spørringer som alle går parallelt, kan kvele en server – du ser det som mye CXPACKET- og CXCONSUMER-venting. Se ventetyper.
Max degree of parallelism (MAXDOP) styrer hvor mange kjerner én spørring kan bruke. Microsofts anbefaling:
- Én NUMA-node med inntil 8 logiske prosessorer: MAXDOP = antall prosessorer.
- Én NUMA-node med flere enn 8: MAXDOP = 8.
- Flere NUMA-noder: antall prosessorer per node, maks 8 (eller 16 med svært mange kjerner per node).
Cost threshold for parallelism bestemmer hvor «dyr» en spørring må være før parallellitet vurderes. Standardverdien 5 er fra 1990-tallet og alt for lav. Start på 50 og juster ut fra arbeidsmengden.
EXEC sp_configure 'max degree of parallelism', 8;
EXEC sp_configure 'cost threshold for parallelism', 50;
RECONFIGURE;
-- Hvor mange NUMA-noder og kjerner har serveren?
SELECT node_id, online_scheduler_count
FROM sys.dm_os_nodes
WHERE node_state_desc = 'ONLINE';
MAXDOP kan også settes per database (ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 4), noe som er nyttig når ulike databaser på samme server har ulik type last.
tempdb
tempdb brukes av midlertidige tabeller, tabellvariabler, sortering, hashing, versjonslager for snapshot-isolasjon og mye annet. Når mange økter oppretter midlertidige objekter samtidig, kan det bli kø på allokeringssidene i datafilen (PAGELATCH_* på tempdb).
- Antall datafiler: én per logisk prosessor, inntil 8. Legg til fire av gangen bare hvis du fortsatt ser køer.
- Lik størrelse og lik vekst på alle filene, ellers blir de brukt ujevnt.
- Rask disk, gjerne lokal SSD, og helst egen disk.
- Fra SQL Server 2019 kan memory-optimized tempdb metadata fjerne køer på systemtabellene.
-- Se filene i tempdb
SELECT name, physical_name, size / 128 AS StørrelseMB, growth / 128 AS VekstMB
FROM tempdb.sys.database_files;
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, SIZE = 8192MB, FILEGROWTH = 1024MB);
ALTER DATABASE tempdb ADD FILE (NAME = temp2, FILENAME = 'T:\TempDB\temp2.ndf',
SIZE = 8192MB, FILEGROWTH = 1024MB);
Filvekst og diskinnstillinger
Fast vekst i stedet for prosent
Prosentvis vekst gir små vekster tidlig og enorme vekster senere. Sett fast vekst i MB – for eksempel 256–1024 MB for datafiler og 512–1024 MB for loggfiler, avhengig av størrelsen. Se også transaksjonsloggen om VLF-er.
-- Finn filer med prosentvis vekst
SELECT DB_NAME(database_id) AS DatabaseNavn, name, growth, is_percent_growth
FROM sys.master_files
WHERE is_percent_growth = 1;
Instant file initialization
Når en datafil vokser, nullstiller Windows normalt det nye området først. Med instant file initialization slipper SQL Server det, og vekst og gjenoppretting går mye raskere. Gi tjenestekontoen rettigheten Perform volume maintenance tasks (det er et valg i installasjonen). Sjekk med:
SELECT servicename, instant_file_initialization_enabled
FROM sys.dm_server_services;
Auto shrink og auto close
Begge skal være av. Auto shrink fragmenterer indekser og fører til at filene krymper og vokser om igjen, og auto close gjør at databasen må åpnes på nytt ved hver tilkobling.
SELECT name, is_auto_shrink_on, is_auto_close_on
FROM sys.databases
WHERE is_auto_shrink_on = 1 OR is_auto_close_on = 1;
Andre nyttige innstillinger
| Innstilling | Anbefaling | Hvorfor |
|---|---|---|
| backup compression default | 1 (på) | Mindre og raskere backuper, nesten uten ulemper. |
| optimize for ad hoc workloads | 1 (på) | Sparer minne når applikasjoner sender mange unike spørringer. |
| remote admin connections | 1 (på) | Gir tilgang til Dedicated Admin Connection (DAC) over nettverket når serveren henger. |
| Database Mail og varsler | Sett opp | Du vil vite om feil før brukerne gjør det. Se SQL Server Agent. |
EXEC sp_configure 'backup compression default', 1;
EXEC sp_configure 'optimize for ad hoc workloads', 1;
EXEC sp_configure 'remote admin connections', 1;
RECONFIGURE;
Vanlige spørsmål
Må SQL Server startes på nytt etter endringene?
Nei, ikke for innstillingene med sp_configure over – de gjelder etter RECONFIGURE. Nye tempdb-filer tas i bruk med en gang, men endret størrelse på eksisterende filer kan kreve omstart. Lock pages in memory og instant file initialization krever omstart av tjenesten.
Hvorfor bruker SQL Server fortsatt alt minnet?
Fordi max server memory gjelder bufferet og de fleste andre minneområder, og SQL Server fyller opp til grensen og blir der. Det er normalt. Problemet er bare når grensen er satt for høyt.
Skal jeg sette MAXDOP til 1?
Bare hvis en leverandør krever det (SharePoint er det klassiske eksemplet). Ellers mister tunge spørringer muligheten til å bruke flere kjerner.