MS SQL Server: aumenta la memoria allocata

Question

Il mio problema è che SQL Server impiega molto tempo per aumentare il suo utilizzo della memoria per istanze con TB di RAM, per tutto il tempo otteniamo attese intermittenti di MEMORY_ALLOCATION_EXT che rallentano la nostra elaborazione fino a quando SQL Server non raggiunge la sua memoria massima.

Abbiamo istanze cluster di failover (FCI) di SQL Server 2019 Enterprise Edition con terabyte di memoria sui nodi. Nei normali casi d'uso consentiamo solo 1 istanza di SQL Server per nodo, quindi impostiamo la memoria massima del server vicino a ~ 85% della memoria sul nodo, ma impostiamo anche la memoria minima del server relativamente bassa nel caso in cui SQL Server fallisca a un altro nodo e deve essere portato in linea con un footprint di memoria ridotto per zoppicare.

• Sono abbastanza consapevole che se imposto la memoria minima su un valore più alto, SQL Server consumerà la memoria tutta in una volta. Risulta che SQL Server non alloca la memoria minima all'avvio.
• Sono consapevole che SQL Server consumerà dinamicamente più memoria dal sistema operativo quando ne ha bisogno e che alla fine raggiungerà la memoria massima del server.
• Sono consapevole del fatto che l'esecuzione di una query di grandi dimensioni o di un checkdb DBCC che richiama molti dati costringerà SQL Server a consumare più memoria dal sistema operativo.

Esistono altri modi per forzare SQL Server ad aumentare rapidamente l'utilizzo della memoria?

Answer 1

Sono abbastanza consapevole che se imposto la memoria minima su un valore più alto, SQL Server consumerà la memoria tutta in una volta.

No, non è corretto, la memoria min server non ha nulla a che fare con la velocità con cui SQL Server la consumerà se impostata. Min server memory indica la quantità minima di memoria, se consumata, dopo la quale SQL Server non rilascerà memoria al di sotto di questo valore in condizioni normali se richiesto. Una volta raggiunto il valore minimo del server, SQL Server non ridurrà le cache e rilascerà la memoria al di sotto di questo valore.

Sono consapevole che SQL Server consumerà dinamicamente più memoria dal sistema operativo quando ne ha bisogno e che alla fine raggiungerà la memoria massima del server.

Sì corretto, anche solo per aggiungere SQl Server in determinate condizioni è possibile consumare memoria maggiore del valore specificato in max server memory. Dovresti leggere la Guida alla gestione della memoria

Esistono altri modi per forzare SQL Server ad aumentare rapidamente l'utilizzo della memoria?

C'è un altro modo che impone a SQL Server di riservare più memoria durante l'avvio e questo è chiamato " pagine grandi ". Si tratta di funzionalità dell'edizione aziendale e consente a SQL Server di riservare rapidamente un po 'di memoria libera durante l'avvio, l'account del servizio SQL Server dovrebbe anche avere pagine bloccate in memoria privilegio perché l'allocazione di pagine di grandi dimensioni viene eseguita dalla funzione VirtualAlloc () del sistema operativo Windows e il sistema operativo dovrebbe avere più di 8 GB di RAM, se queste condizioni vengono soddisfatte automaticamente L'allocazione di pagine grandi è abilitata. Una volta abilitato, apparirà come di seguito nel log degli errori

2009-06-04 12:21:08.16 Server      Large Page Extensions enabled.
2009-06-04 12:21:08.16 Server      Large Page Granularity: 2097152
2009-06-04 12:21:08.21 Server      Large Page Allocated: 32MB

La dimensione della pagina normale della memoria è di 4 KB sul sistema X64, ma quando la pagina grande è abilitata la dimensione sarà di 2 MB. Notare inoltre che LargePageSupport è abilitato e utilizzato dal motore anche se non si abilita il flag di traccia 834. Ma non viene utilizzata molta memoria per questo e la memoria del pool di buffer non viene utilizzata a meno che il flag di traccia 834 non sia abilitato. Vedi il blog condiviso.

Answer 2

SQL Server non utilizzerà la memoria per il pool di buffer (il suo utilizzo più grande) fino a quando non sarà necessario caricare le pagine dall'archiviazione perché vengono referenziate. A meno che tu non stia facendo qualcosa di strano per rallentare l'allocazione della memoria ^[†], è improbabile che il tuo ritardo sia quello, ma leggendo i dati che vengono allocati per contenere. Sospetto che, contemporaneamente alle attese di allocazione della memoria, siano presenti attese corrispondenti a IO.

Per forzare il server SQL a caricare cose in memoria, e quindi ad allocare memoria se disponibile e necessaria, accedere alle cose in modo che debbano essere caricate. Dopo aver avviato l'istanza SQL, eseguire un carico di lavoro di sola lettura in modo che il normale working set comune dell'applicazione venga caricato nel pool di buffer.

Non solo SELECT * FROM HugeTableperché è probabile che questo carichi un cattivo equilibrio di dati (non il set di lavoro principale delle applicazioni), quindi il set di lavoro principale dovrà comunque essere caricato dal disco / dalla rete al prossimo accesso in ogni caso, quindi avrai lo stesso IO ritardi. Inoltre, se HugeTableè davvero enorme, SQL Server potrebbe vedere che non tutto si adatta e non si preoccuperà di mantenerne comunque una quantità significativa in memoria [‡].

^{[†] In esecuzione in una VM su un host sovraccarico, quindi quella che sembra una vera RAM sono davvero pagine su disco ?!}

^{[‡] In circostanze normali non si desidera che una scansione inattesa di una tabella / indice su un oggetto di grandi dimensioni rimuova tutto il resto dalla memoria. Un limite su quanto viene mantenuto da un oggetto per una query è un'ottimizzazione per cercare di impedire che ciò accada).}