Quanta memoria è necessaria per mantenere una tabella memorizzata nella cache?(SQL Server 2008)


5

Eseguo query lunghe e complesse che utilizzano principalmente un grande tavolo: 8 GB, 40 milioni di righe.AFAIK, tutte/la maggior parte delle righe vengono utilizzate in ogni query.Vedo un sacco di IO nel monitor delle attività - per la prima query e per ogni successiva.Il server utilizza attualmente 6,5 GB di memoria e voglio aggiornarlo.La domanda è, quanta memoria è necessaria per evitare tutte queste letture del disco?È nel campo da baseball delle dimensioni del tavolo o più?

Questo è l'output IO STATISTICS SET.BigTable è quello che sto chiedendo, SmallTable ha una relazione 1-a-molti con BigTable.#entrance contiene l'output della query (diverse centinaia di righe di output).

Table 'SmallTable'. Scan count 249005, logical reads 2829948, physical reads 2605, read-ahead reads 10395, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'BigTable'. Scan count 194004, logical reads 13482115, physical reads 33841, read-ahead reads 1181136, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#entrance__000000000023'. Scan count 0, logical reads 1568, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
+2

Questo server/database non fa altro che eseguire le query su una tabella di 8 GB di dati?Sto anche grattando la mia testa un po 'oltre la tabella 1 ma aspetto di query complesso di questa domanda.Qualche possibilità di DDL o un po 'di comprensione della natura di questa complessità? 14 set. 112011-09-14 22:20:40

  0

Solo un controllo di base, quale versione del server SQL stai usando?se non è un'azienda, non sarà possibile utilizzare tutta la memoria corrente per SQL Server. 16 set. 112011-09-16 15:30:00

  0

Alla fine ho aggiornato la RAM del server e ho fatto un test.Ho pubblicato i risultati come risposta sotto. 09 feb. 132013-02-09 08:50:11

3

qual è la natura delle tue domande?SELEZIONA solo?O una miscela di DUI (DELETE, UPDATE, INSERTs)?e qual è il tuo modello di recupero?stai usando il livello di isolamento della transazione predefinito?

Se vuoi mettere tutte quelle pagine in memoria, allora avrai bisogno di più memoria (ma lo sapevi già).Vuoi sapere 'quanto', ma la verità è che, a seconda della natura delle tue query, potresti ancora vedere alcuni problemi di I/O anche con abbastanza memoria da contenere l'intera tabella.

Considerare tutto quanto sopra ... sì, la dimensione del tavolo su disco è la dimensione della memoria.Non essere sorpreso se scopri di avere ancora l'I/O del disco in seguito.

  0

Ho aggiornato la RAM del server e "eseguo il ballpark sulla dimensione del tavolo su disco dato che la quantità di memoria" è assolutamente corretta.Vedi la mia risposta qui sotto. 08 feb. 132013-02-08 11:59:48


8

Questa è una risposta in due parti.

La prima parte è acquistare tutta la RAM che ti puoi permettere (a patto che si adatti al server).

La seconda parte è che vedrai sempre letture sui dati.Il trucco è vedere se quelle letture sono fisiche o logiche.Le letture fisiche stanno andando giù sul disco.Le letture logiche vengono lette dal buffer pool in memoria.Puoi vederlo mettendo "SET STATISTICS IO ON" nella parte superiore dello Script SQL e poi eseguendolo in SSMS.Nella scheda dei messaggi si ottengono informazioni I/O specifiche per ogni query che viene eseguita, in particolare si otterrà il numero di operazioni logiche e fisiche (letture nel tuo caso) che si verificano su una tabella per tabella.Se i numeri di lettura fisica sono piuttosto alti (questi numeri sono fondamentalmente il numero di blocchi che sono stati letti così per 8 per poi dividere per 1024 per ottenere un numero in Megs) e quindi serve più RAM.Se i numeri di lettura logica sono alti, non si sta effettivamente andando su disco ma la query potrebbe richiedere tuning, indici creati, ecc.

(Nota: ai fini di questa risposta, sto assumendo solo le istruzioni SELECT. Le operazioni INSERT/UPDATE/DELETE sono un po 'più complesse, ma si applicano le stesse nozioni di base.)

  0

Ci proverò: SET STATISTICS IO ON e rapporto indietro.Ulteriori informazioni: le query sono selezionate al 100%.Ogni query richiede diversi minuti.Il server esegue anche la piccola app che esegue i risultati di query e dump in Excel, ma dubito che ciò influenzi le prestazioni del server. 15 set. 112011-09-15 05:00:37

  0

Se hai bisogno di aiuto per leggere l'output, sentiti libero di pubblicare il piano di esecuzione e l'output di SET STATISTICS IO. 15 set. 112011-09-15 05:05:59


1

Non sono sicuro che più RAM ti aiuterà qui.Il calcolo rapido suggerisce che la query viene elaborata da qualche parte nella regione di 125 GB di dati, abbastanza impressionante per una tabella che contiene 8 GB.

(2829948 + 13482115 + 1568) x 8/1048576 = 125 GB

I 270 MB di letture fisiche rappresenteranno una parte del rallentamento, ma non tanto quanto i sovraccarichi in memoria.

Otterrete risultati migliori dal rielaborare la query piuttosto che aggiungere memoria aggiuntiva.Pubblica DDL/query/piano di esecuzione in una nuova domanda e qualcuno sarà sicuramente in grado di aiutarti.


3

Sono sempre in ritardo per la festa.:) Questa query molto probabilmente può essere ottimizzata.Ma, per quanto riguarda la quantità di RAM per memorizzare nella cache una tabella di 8 GB ... una domanda preliminare è quanti nodi di memoria SQL Server segnala per il server?Ogni nodo di memoria (equivalente logico ai nodi NUMA fisici) ha una cache del pool di buffer gestita in modo indipendente.Le attività eseguite su processori in quel nodo inseriscono blocchi nel pool buffer in tale nodo.

Pertanto, se esiste la possibilità che le attività che leggeranno la tabella da 8 GB nella sua interezza vengano eseguite su un singolo nodo NUMA, il pool di buffer di quel nodo deve essere in grado di contenere la tabella da 8 GB.Quindi inizia con 8 GB di memoria massima del server per nodo NUMA.Quindi iniziamo ad aumentarlo.La memoria delle query può consumare fino al 70% del pool buffer, lasciando solo il 30% per il contenuto del blocco del database.Quindi la tabella da 8 GB deve essere non più del 30% della memoria massima del server su un singolo nodo NUMA per il server.26 2/3 GB per la memoria massima del server.Per nodo NUMA.Se SQL Server 2012, il materiale utilizzato da SQL Server 2008 R2 e precedente per l'allocatore di pagine multiple (che era quindi al di fuori dell'ambito di memoria del server massimo) si trova ora nell'ambito di memoria del server massimo e deve essere tenuto in considerazione.Deve essere incluso nella memoria totale del server in un modo o nell'altro, indipendentemente dalla sua parte del limite massimo di memoria del server SQL Server.

Oh sì ... qualsiasi uso di tabelle temporanee o altro uso di tempdb dovrebbe essere sistemato nella memoria massima del server, in modo da non lasciare che la tabella venga sfrattata dalla cache.

Aggiungi memoria sufficiente per il sistema operativo Windows (almeno 4 GB).Aggiungi abbastanza per i thread di lavoro di SQL Server, il processo di base di SQL Server, i lavori di SQL Agent, qualsiasi altra esigenza di memoria dell'applicazione.

Su un singolo server di nodo NUMA gestisco circa 36 GB di RAM con una memoria massima del server di almeno 27 GB per memorizzare in modo affidabile la tabella da 8 GB.E se ci sono più nodi NUMA, aumenterà di almeno 26 2/3 GB di memoria massima del server per nodo NUMA, nonché per i thread di lavoro aggiuntivi che accompagneranno i core aggiuntivi nei nodi NUMA aggiuntivi.

Anche se ciò può sembrare eccessivo, è dovuto in parte al modo in cui SQL Server gestisce NUMA.Ma potresti essere fortunato!Se è sufficiente un solo writer pigro, è possibile utilizzare il flag di traccia di avvio 8015 per ignorare NUMA e gestire un singolo pool di buffer.In questo modo, circa 36 - 40 GB dovrebbero essere sufficienti per mantenere la tabella di 8 GB residente in memoria, indipendentemente dal numero di nodi NUMA presenti sul server.discuti di questo un po 'di più nel seguente post sul blog.Se si decide di utilizzare il flag di traccia 8015, si consiglia di valutare il flag di traccia 8048 insieme ad esso, per evitare l'escalation del conflitto spinlock e le attese CMEMTHREAD associate all'assegnazione della memoria.http://sqlsasquatch.blogspot.com/2013/02/sql-server-cache-database-working-set.html


2

Ho aggiornato la RAM del server da 8 GB a 64 GB e ho eseguito un test.La dimensione della tabella ora è 16,05 GB e lo spazio dell'indice è 187 MB (1 cluster e 1 indici non cluster univoci).Ho controllato l'utilizzo totale della RAM del server prima e dopo aver eseguito una query che calcola in media ciascuno dei 137 campi nella tabella.La memoria totale è passata da 4,22 GB a 20,4 GB = 16,18 GB, pari al 100,8% delle dimensioni della tabella.SQLRockstar era assolutamente corretto nel predire che la quantità di utilizzo della RAM è un ballpark delle dimensioni del tavolo.

Ulteriori informazioni:

  • La pienezza della pagina dell'indice cluster è del 98,1%
  • Ulteriori query sulla tabella non hanno causato alcun IO del disco.
  • Sviluppatore SQL Server 2008R2
  • Il piano di esecuzione mostra che è stato utilizzato l'indice cluster