Condividi tramite

Diagnosticare e risolvere una contesa di latch in SQL Server

Questa guida illustra come identificare e risolvere i problemi relativi alla contesa di latch osservati durante l'esecuzione di applicazioni SQL Server in sistemi con concorrenza elevata con determinati carichi di lavoro.

Poiché il numero di core CPU nei server continua ad aumentare, il conseguente aumento della concorrenza può introdurre punti di contesa nelle strutture dei dati a cui è necessario accedere in modo seriale all'interno del motore di database. Questo vale in particolare per i carichi di lavoro di elaborazione delle transazioni (OLTP) a velocità effettiva elevata/concorrenza elevata. Esistono diversi strumenti, tecniche e modi per affrontare queste sfide, nonché procedure che possono essere seguite nella progettazione di applicazioni che potrebbero contribuire a evitarle del tutto. Questo articolo illustra un particolare tipo di contesa sulle strutture di dati che usano spinlock per serializzare l'accesso a queste strutture di dati.

Nota

Il contenuto di questo articolo è stato scritto dal Team di consulenza clienti di Microsoft SQL Server (SQLCAT) sulla base del processo di identificazione e risoluzione dei problemi relativi alla contesa di latch di pagina nelle applicazioni SQL Server in sistemi a concorrenza elevata. Le raccomandazioni e le procedure consigliate illustrate in questo articolo si basano sull'esperienza reale di sviluppo e distribuzione di sistemi OLTP reali.

Che cos'è la contesa di latch di SQL Server?

I latch sono primitive di sincronizzazione leggere usate dal motore di SQL Server per garantire la coerenza delle strutture in memoria, inclusi indici, pagine di dati e strutture interne, ad esempio pagine non foglia in un albero B. SQL Server usa i latch di buffer per proteggere le pagine nel pool di buffer e i latch di I/O per proteggere le pagine non ancora caricate nel pool di buffer. Ogni volta che i dati vengono scritti o letti in una pagina nel pool di buffer di SQL Server, un thread di lavoro deve prima acquisire un latch di buffer per la pagina. Sono disponibili vari tipi di latch di buffer per l'accesso alle pagine nel pool di buffer, tra cui latch esclusivo (PAGELATCH_EX) e latch condiviso (PAGELATCH_SH). Quando SQL Server tenta di accedere a una pagina non già presente nel pool di buffer, viene pubblicato un I/O asincrono per caricare la pagina nel pool di buffer. Se SQL Server deve attendere che il sottosistema di I/O risponda, attende un latch di I/O esclusivo (PAGEIOLATCH_EX) o condiviso (PAGEIOLATCH_SH) a seconda del tipo di richiesta. Questa operazione viene eseguita per impedire a un altro thread di lavoro di caricare la stessa pagina nel pool di buffer con un latch incompatibile. I latch vengono usati anche per proteggere l'accesso alle strutture di memoria interne diverse dalle pagine del pool di buffer. Tali latch sono noti come latch non di buffer.

La contesa sui latch di pagina è lo scenario più comune riscontrato nei sistemi con più CPU e quindi la maggior parte di questo articolo è incentrata su questo argomento.

La contesa di latch si verifica quando più thread provano ad acquisire simultaneamente latch non compatibili nella stessa struttura in memoria. Poiché il latch è un meccanismo di controllo interno, il motore di SQL determina automaticamente quando usarli. Poiché il comportamento dei latch è deterministico, le decisioni correlate alle applicazioni, ad esempio la progettazione dello schema, possono influire su questo funzionamento. Questo articolo contiene le informazioni seguenti:

  • Informazioni generali su come i latch vengono usati da SQL Server.
  • Strumenti usati per individuare la causa della contesa di latch.
  • Come determinare se l'entità della contesa osservata è problematica.

Vengono illustrati alcuni scenari comuni e il modo migliore per gestirli per alleviare i conflitti.

In che modo SQL Server usa i latch?

Una pagina in SQL Server ha dimensioni pari a 8 KB e può archiviare più righe. Per aumentare la concorrenza e le prestazioni, i latch di buffer vengono mantenuti solo per la durata dell'operazione fisica sulla pagina, a differenza dei blocchi, che vengono mantenuti per la durata della transazione logica.

I latch sono interni al motore di SQL e vengono usati per garantire la coerenza della memoria, mentre i blocchi vengono usati da SQL Server per garantire la coerenza transazionale logica. Nella tabella seguente vengono messi a confronto latch e blocchi:

Struttura Scopo Controllato da Costo delle prestazioni Esposto da
Latch Garantire la coerenza delle strutture in memoria. Solo motore di SQL Server. Il costo delle prestazioni è basso. Per consentire la massima concorrenza e garantire prestazioni ottimali, i latch vengono mantenuti solo per la durata dell'operazione fisica sulla struttura in memoria, a differenza dei blocchi, che vengono mantenuti per la durata della transazione logica. sys.dm_os_wait_stats - fornisce informazioni sui tipi di attesa PAGELATCH, PAGEIOLATCH, e LATCH (LATCH_EX, LATCH_SH è usato per raggruppare tutte le attese di latch non buffer).
sys.dm_os_latch_stats : fornisce informazioni dettagliate sulle attese dei latch non buffer.
sys.dm_db_index_operational_stats - questa DMV fornisce attese aggregate per ogni indice, ed è utile per la risoluzione dei problemi di prestazioni correlati al latch.
Blocca Garantire la coerenza delle transazioni. Può essere controllato dall'utente. Il costo delle prestazioni è elevato rispetto ai latch perché i blocchi devono essere mantenuti per la durata della transazione. sys.dm_tran_locks.
sys.dm_exec_sessions.

Modalità e compatibilità dei latch di SQL Server

Si deve prevedere una contesa di latch come parte del normale funzionamento del motore di SQL Server. È inevitabile che più richieste di latch simultanee di compatibilità variabile si verifichino in un sistema di concorrenza elevata. SQL Server applica la compatibilità con latch imponendo alle richieste di latch non compatibili di attendere in una coda fino al completamento delle richieste di latch in attesa.

I latch possono essere acquisiti in cinque diverse modalità, che si distinguono per il livello di accesso. Le modalità di latch di SQL Server possono essere riepilogate come segue:

  • KP: Mantieni il meccanismo di chiusura. Assicura che la struttura a cui viene fatto riferimento non possa essere eliminata definitivamente. Viene usato quando un thread vuole esaminare una struttura di buffer. Poiché il latch KP è compatibile con tutti i latch ad eccezione del latch di eliminazione (DT), il latch KP viene considerato leggero, significa che l'effetto sulle prestazioni quando viene utilizzato è minimo. Poiché il latch KP non è compatibile con il latch DT, impedisce a qualsiasi altro thread di eliminare la struttura a cui si fa riferimento. Ad esempio, un latch KP impedisce che la struttura a cui fa riferimento venga distrutta dal processo di scrittura pigra. Per ulteriori informazioni su come il processo di "lazy writer" è utilizzato nella gestione delle pagine del buffer di SQL Server, vedere Scrivere pagine nel motore di database.

  • SH: latch condiviso. Obbligatorio per leggere la struttura a cui si fa riferimento( ad esempio, leggere una pagina di dati). Più thread possono accedere contemporaneamente a una risorsa per la lettura in un latch condiviso.

  • UP: aggiorna il latch. Compatibile con SH (latch condiviso) e KP, ma non con altri e pertanto non consente a un latch EX di scrivere nella struttura di riferimento.

  • EX: latch esclusivo. Impedisce ad altri thread di scrivere o leggere dalla struttura a cui si fa riferimento. Può essere usato, ad esempio, per modificare il contenuto di una pagina per la protezione delle pagine incomplete.

  • DT: distruggete il latch. Deve essere acquisito prima di eliminare il contenuto della struttura a cui si fa riferimento. Ad esempio, un DT latch deve essere acquisito dal lazy writer process per liberare una pagina prima di aggiungerla all'elenco di buffer liberi disponibili per l'uso da parte di altri thread.

Le modalità di latch hanno diversi livelli di compatibilità, ad esempio, un latch condiviso (SH) è compatibile con un latch di aggiornamento (UP) o di mantenimento (KP), ma è incompatibile con un latch di eliminazione (DT). È possibile acquisire contemporaneamente più latch nella stessa struttura purché i latch siano compatibili. Quando un thread tenta di acquisire un latch mantenuto in una modalità non compatibile, viene inserito in una coda per attendere un segnale che indica che la risorsa è disponibile. Uno spinlock di tipo SOS_Task viene usato per proteggere la coda di attesa applicando l'accesso serializzato alla coda. Questo spinlock deve essere acquisito per aggiungere elementi alla coda. Lo spinlock SOS_Task segnala anche ai thread della coda quando vengono rilasciati latch non compatibili, consentendo ai thread in attesa di acquisire un latch compatibile e di continuare a funzionare. La coda di attesa viene elaborata in base al criterio FIFO (First In, First Out) mentre vengono rilasciate le richieste di latch. I latch seguono questo sistema FIFO per garantire l'equità ed evitare la mancanza di thread.

La compatibilità della modalità latch è elencata nella tabella seguente (Sì indica la compatibilità e No indica incompatibilità):

Modalità latch KP ZITTO SU EX DT
KP Sì Sì Sì Sì NO
SH Sì Sì Sì NO NO
UP Sì Sì NO NO NO
EX Sì NO NO NO NO
DT NO NO NO NO NO

Superlaghe e sottolaghe di SQL Server

Con la crescente presenza di sistemi multiple socket/multi-core basati su NUMA, SQL Server 2005 ha introdotto Superlatches, note anche come sottolaghe, che sono efficaci solo nei sistemi con 32 o più processori logici. Superlatches migliorano l'efficienza del motore SQL per alcuni modelli di utilizzo in ambienti OLTP altamente concorrenti; ad esempio, quando alcune pagine presentano un modello di accesso condiviso prevalentemente in sola lettura (SH), ma vengono aggiornate raramente. Un esempio di pagina con un modello di accesso di questo tipo è una pagina radice dell'albero B (ovvero indice); Il motore SQL richiede che nella pagina radice venga mantenuto un latch condiviso quando si verifica una suddivisione di pagine a qualsiasi livello nell'albero B. In un carico di lavoro OLTP con molti inserimenti e concorrenza elevata, il numero di suddivisioni di pagine aumenta in modo proporzionale al flusso, il che può degradare le prestazioni. Le superlatches possono consentire un aumento delle prestazioni per l'accesso alle pagine condivise in cui più thread di lavoro in esecuzione simultanea richiedono SH latch. A tale scopo, il motore di SQL Server promuove dinamicamente un latch in una pagina di questo tipo in un superlatch. Un superlatch suddivide un singolo latch in una matrice di strutture di sublatch, una partizione per ogni core CPU, in cui il latch principale diventa un proxy di reindirizzamento e la sincronizzazione dello stato globale non è necessaria per i latch di sola lettura. In questo modo, il lavoratore, che viene sempre assegnato a una CPU specifica, deve solo acquisire il sottolatch condiviso (SH) assegnato allo scheduler locale.

Nota

Nella documentazione viene usato in modo generico il termine albero B in riferimento agli indici. Negli indici rowstore, il motore di database implementa un albero B+. Ciò non si applica a indici columnstore o a indici in tabelle ottimizzate per la memoria. Per altre informazioni, vedere Architettura e guida per la progettazione degli indici di SQL Server e Azure SQL.

L'acquisizione di latch compatibili, ad esempio un superlatch condiviso, usa meno risorse e dimensiona l'accesso alle pagine a cui si accede di frequente in modo più efficiente rispetto a un latch condiviso non partizionato perché, grazie alla rimozione del requisito di sincronizzazione dello stato globale e all'accesso alla sola memoria NUMA locale, le prestazioni migliorano considerevolmente. Viceversa, l'acquisizione di un superlatch esclusivo (EX) è più costosa rispetto all'acquisizione di un EX latch normale perché SQL deve segnalare tutti i sottolatti. Quando si osserva che un superlatch utilizza un modello di accesso intenso EX, il motore SQL può degradarlo dopo che la pagina è stata rimossa dal pool di buffer. Il diagramma seguente illustra un latch normale e un superlatch partizionato:

Diagramma del superlatch di SQL Server.

Usare l'oggetto SQL Server:Latches e i contatori associati in Performance Monitor per raccogliere informazioni sui Superlatch, incluso il numero di Superlatch, le promozioni di Superlatch al secondo e le demozioni di Superlatch al secondo. Per altre informazioni sull'oggetto SQL Server:Latches e sui contatori associati, vedere l'oggetto SQL Server, Latches.

Tipi di attesa latch

SQL Server tiene traccia delle informazioni cumulative sull'attesa, a cui è possibile accedere usando la DMW sys.dm_os_wait_stats. SQL Server usa tre tipi di attesa latch definiti dal corrispondente wait_type DMV sys.dm_os_wait_stats:

  • Latch di buffer (BUF): usato per garantire la coerenza delle pagine di indice e di dati per gli oggetti utente. Vengono usati anche per proteggere l'accesso alle pagine di dati usate da SQL Server per gli oggetti di sistema. Ad esempio, le pagine che gestiscono le allocazioni sono protette da latch di buffer, Sono incluse le pagine PFS (Page Free Space), Global Allocation Map (GAM), Shared Global Allocation Map (SGAM) e Index Allocation Map (IAM). I latch di buffer vengono segnalati in sys.dm_os_wait_stats con un wait_type di PAGELATCH_*.

  • Latch non di buffer (non-BUF): usato per garantire la coerenza di qualsiasi struttura in memoria diversa dalle pagine del pool di buffer. Qualsiasi attesa di latch non buffer viene segnalata come wait_type di LATCH_*.

  • Latch di I/O: un subset di latch di buffer che garantisce la coerenza delle stesse strutture protette dai latch di buffer quando queste strutture richiedono il caricamento nel pool di buffer con un'operazione di I/O. I latch di I/O impediscono a un altro thread di caricare la stessa pagina nel pool di buffer con un latch non compatibile. Associato a un wait_type di PAGEIOLATCH_*.

    Nota

    Se si riscontrano attese significative PAGEIOLATCH , significa che SQL Server è in attesa nel sottosistema di I/O. Mentre è prevista una certa quantità di PAGEIOLATCH attese e un comportamento normale, se i tempi di attesa medi PAGEIOLATCH sono costantemente superiori a 10 millisecondi (ms) è consigliabile esaminare il motivo per cui il sottosistema di I/O è sotto pressione.

Se quando si esamina la DMW sys.dm_os_wait_stats si rilevano latch non di buffer, è necessario esaminare sys.dm_os_latch_stats per ottenere il dettaglio delle informazioni cumulative sull'attesa per i latch non di buffer. Tutte le attese dei latch del buffer vengono classificate nella categoria BUFFER latch, mentre le rimanenti vengono utilizzate per classificare i latch non buffer.

Sintomi e cause della contesa di latch di SQL Server

In un sistema di concorrenza elevata occupato, è normale vedere contese attive sulle strutture a cui si accede di frequente e protette da latch e altri meccanismi di controllo in SQL Server. È considerato problematico quando il tempo di contesa e di attesa associato all'acquisizione del latch per una pagina è sufficiente per ridurre l'utilizzo della CPU, che ostacola il throughput.

Esempio di contesa di latch

Nel diagramma seguente la linea blu rappresenta la velocità effettiva in SQL Server, misurata in base alle transazioni al secondo. La linea nera rappresenta il tempo medio di attesa latch della pagina. In questo caso, ogni transazione esegue un inserimento INSERT in un indice clusterizzato con un valore principale che aumenta in sequenza, ad esempio quando si popola una colonna IDENTITY di tipo di dati bigint. Poiché il numero di CPU aumenta a 32, è evidente che la velocità effettiva complessiva è diminuita e il tempo di attesa del latch di pagina è aumentato a circa 48 millisecondi come evidenziato dalla linea nera. Questa relazione inversa tra velocità effettiva e tempo di attesa latch della pagina è uno scenario comune facilmente diagnosticabile.

Diagramma che mostra come la velocità effettiva diminuisce man mano che aumenta la concorrenza.

Prestazioni quando la contesa di latch viene risolta

Come illustra il diagramma seguente, in SQL Server non sono più presenti colli di bottiglia in corrispondenza delle attese latch della pagina e la velocità effettiva, misurata in transazioni al secondo, è aumentata del 300%. A questo scopo è stata usata la tecnica descritta più avanti in questo articolo in Usare il partizionamento hash con una colonna calcolata. Questo miglioramento delle prestazioni è diretto a sistemi con un numero elevato di core e un livello elevato di concorrenza.

Diagramma dei miglioramenti della velocità effettiva realizzati con il partizionamento hash.

Fattori che influiscono sulla contesa di latch

La contesa di latch che compromette le prestazioni negli ambienti OLTP è in genere causata da una concorrenza elevata correlata a uno o più dei fattori seguenti:

Fattore Dettagli
Numero elevato di CPU logiche usate da SQL Server La contesa di latch può verificarsi in qualsiasi sistema multicore. Nell'esperienza SQLCAT una contesa di latch eccessiva, che influisce sulle prestazioni dell'applicazione oltre i livelli accettabili, è stata osservata più comunemente nei sistemi con 16 core CPU e potrebbe aumentare man mano che vengono resi disponibili più core.
Progettazione di schemi e modelli di accesso La profondità dell'albero B, la progettazione di indici cluster e non cluster, le dimensioni e la densità delle righe per pagina e i modelli di accesso (attività di lettura/scrittura/eliminazione) sono fattori che possono contribuire a una contesa di latch di pagina eccessiva.
Grado di concorrenza elevato a livello di applicazione Una contesa di latch di pagina eccessiva si verifica in genere in concomitanza con un livello elevato di richieste simultanee dal livello applicazione. Esistono alcune procedure di programmazione che possono anche introdurre un numero elevato di richieste per una pagina specifica.
Layout di file logici usati dai database di SQL Server Il layout di file logico può influire sul livello di contesa di latch delle pagine causato da strutture di allocazione, ad esempio Page Free Space (PFS), Global Allocation Map (GAM), Shared Global Allocation Map (SGAM) e Index Allocation Map (IAM). Per altre informazioni, vedere Monitoraggio e risoluzione dei problemi di TempDB: collo di bottiglia di allocazione.
Prestazioni del sottosistema di I/O Attese significative PAGEIOLATCH indicano che SQL Server è in attesa nel sottosistema di I/O.

Diagnosticare la contesa di latch di SQL Server

Questa sezione fornisce informazioni per la diagnosi della contesa di latch di SQL Server per determinare se è problematica per l'ambiente in uso.

Strumenti e metodi per la diagnosi della contesa di latch

I principali strumenti usati per diagnosticare la contesa di latch sono:

  • Performance Monitor per monitorare l'utilizzo della CPU e i tempi di attesa all'interno di SQL Server e stabilire se esiste una relazione tra l'utilizzo della CPU e i tempi di attesa del latch.

  • Viste DMV di SQL Server, che possono essere usate per determinare il tipo specifico di latch che causa il problema e la risorsa interessata.

  • In alcuni casi, i dump della memoria del processo di SQL Server devono essere ottenuti e analizzati con gli strumenti di debug di Windows.

Nota

Questo livello di risoluzione dei problemi avanzata è in genere necessario solo per la risoluzione dei problemi relativi alla contesa di latch non di buffer. Per questo tipo di risoluzione dei problemi avanzati, potrebbe essere necessario contattare il Servizio Supporto Tecnico Clienti Microsoft.

I passaggi seguenti riepilogano il processo tecnico per la diagnosi della contesa di latch:

  1. Determinare che è presente una contesa che potrebbe essere correlata al latch.

  2. Utilizzare le viste DMV fornite nell'Appendice: Script per la contesa dei latch di SQL Server per determinare il tipo di latch e le risorse interessate.

  3. Ridurre la contesa usando una delle tecniche descritte in Gestione della contesa di latch per modelli di tabella diversi.

Indicatori di contesa di latch

Come indicato in precedenza, la contesa di latch costituisce un problema solo quando la contesa e il tempo di attesa associati all'acquisizione dei latch di pagina impediscono l'aumento della velocità effettiva quando sono disponibili risorse della CPU. Per stabilire un livello accettabile di contesa, è necessario un approccio olistico che consideri i requisiti di prestazioni e velocità effettiva, ma anche le risorse di I/O e CPU disponibili. Questa sezione illustra come determinare l'impatto della contesa di latch sul carico di lavoro, come segue:

  1. Misurare i tempi di attesa complessivi durante un test rappresentativo.
  2. Classificarli in ordine di priorità.
  3. Determinare la percentuale di tempi di attesa correlati ai latch.

Le informazioni cumulative sull'attesa sono disponibili nella DMV sys.dm_os_wait_stats. Il tipo più comune di contesa di latch è la contesa di latch del buffer, che si manifesta con un aumento dei tempi di attesa per i latch con un wait_type di PAGELATCH_*. I latch non-buffer sono raggruppati sotto il tipo di attesa LATCH*. Come illustra il diagramma seguente, è consigliabile innanzitutto esaminare le attese del sistema usando la DMW sys.dm_os_wait_stats per determinare la percentuale del tempo di attesa complessivo causato dai latch di buffer o non di buffer. Se si notano latch non di buffer, è necessario esaminare anche la DMV sys.dm_os_latch_stats.

Il diagramma seguente illustra la relazione tra le informazioni restituite dalle DMV sys.dm_os_wait_stats e sys.dm_os_latch_stats.

Diagramma delle attese di latch.

Per altre informazioni sulla sys.dm_os_wait_stats DMV, vedere sys.dm_os_wait_stats nella Guida di SQL Server.

Per altre informazioni sulla sys.dm_os_latch_stats DMV, vedere sys.dm_os_latch_stats nella Guida di SQL Server.

Le misure seguenti del tempo di attesa latch indicano che l'eccessiva contesa di latch sta compromettendo le prestazioni dell'applicazione:

  • I tempi medi di attesa latch di pagina aumentano costantemente con la produttività: se i tempi medi di attesa latch di pagina aumentano costantemente con la produttività e se i tempi medi di attesa latch di buffer superano i tempi di risposta del disco previsti, è consigliabile esaminare le attività in attesa correnti usando la DMV sys.dm_os_waiting_tasks. Le medie possono essere fuorvianti se analizzate in isolamento, quindi è importante esaminare il sistema in tempo reale quando possibile comprendere le caratteristiche del carico di lavoro. In particolare, verificare se in qualsiasi pagina sono presenti attese elevate sulle richieste di PAGELATCH_EX e/o PAGELATCH_SH. Seguire questa procedura per diagnosticare l'aumento dei tempi medi di attesa latch di pagina con la velocità effettiva:

    Nota

    Per calcolare il tempo medio di attesa per un particolare tipo di attesa (restituito da sys.dm_os_wait_stats come wt_:type), dividere il tempo di attesa totale (restituito come wait_time_ms) per il numero di attività in attesa (restituite come waiting_tasks_count).

  • Percentuale del tempo di attesa totale impiegato per i tipi di attesa latch durante il carico massimo: se il tempo medio di attesa latch aumenta in linea con il carico dell'applicazione, la contesa di latch potrebbe influire sulle prestazioni e dovrebbe essere indagata.

    Misurare attese di latch di pagina e attese di latch non di pagina con i contatori delle prestazioni dell'oggetto Wait Statistics di SQL Server. quindi confrontare i valori di questi contatori delle prestazioni con quelli dei contatori delle prestazioni associati a CPU, I/O, memoria e velocità effettiva della rete. Transazioni/sec e richieste batch/sec, ad esempio, sono due misure valide di utilizzo delle risorse.

    Nota

    Il tempo di attesa relativo per ogni tipo di attesa non è incluso nella sys.dm_os_wait_stats DMV perché questa DMW misura i tempi di attesa dall'ultima volta che l'istanza di SQL Server è stata avviata o le statistiche di attesa cumulative sono state reimpostate usando DBCC SQLPERF. Per calcolare il tempo di attesa relativo per ogni tipo di attesa, creare uno snapshot di sys.dm_os_wait_stats prima del picco di carico e dopo il picco di carico e quindi calcolare la differenza. A questo scopo, può essere usato lo script di esempio di Calcolare le attese in un periodo di tempo.

    Solo per un ambiente non di produzione, pulire la DMV sys.dm_os_wait_stats con il seguente comando:

    DBCC SQLPERF ('sys.dm_os_wait_stats', 'CLEAR');

    È possibile eseguire un comando simile per cancellare la DMV sys.dm_os_latch_stats:

    DBCC SQLPERF ('sys.dm_os_latch_stats', 'CLEAR');

  • La velocità effettiva non aumenta e in alcuni casi diminuisce, man mano che il carico dell'applicazione aumenta e il numero di CPU disponibili per SQL Server aumenta: questo è stato illustrato in Esempio di contesa latch.

  • L'utilizzo della CPU non aumenta man mano che aumenta il carico di lavoro dell'applicazione: se l'utilizzo della CPU nel sistema non aumenta man mano che aumenta la concorrenza basata sulla velocità effettiva dell'applicazione, si tratta di un indicatore che SQL Server è in attesa di qualcosa e sintomatico della contesa di latch.

Analizzare la causa radice. Anche se ognuna delle condizioni precedenti è vera, è comunque possibile che la causa radice dei problemi di prestazioni si trovi altrove. Nella maggior parte dei casi, infatti, l'utilizzo della CPU non ottimale è causato da altri tipi di attese, ad esempio il blocco sui blocchi, le attese correlate alle operazioni di I/O o o problemi correlati alla rete. Come regola generale, è sempre consigliabile risolvere l'attesa delle risorse che costituisce la maggiore proporzione del tempo di attesa totale prima di procedere con un'analisi più approfondita.

Analizzare i latch di buffer di attesa attuali

La contesa dei latch del buffer si manifesta come un aumento dei tempi di attesa per i latch con wait_type o PAGELATCH_* o PAGEIOLATCH_* come visualizzato nella DMV sys.dm_os_wait_stats. Per esaminare il sistema in tempo reale, eseguire la seguente query in un sistema per unire le DMV sys.dm_os_wait_stats, sys.dm_exec_sessions e sys.dm_exec_requests. I risultati possono essere usati per determinare il tipo di attesa corrente per le sessioni in esecuzione nel server.

SELECT wt.session_id,
       wt.wait_type,
       er.last_wait_type AS last_wait_type,
       wt.wait_duration_ms,
       wt.blocking_session_id,
       wt.blocking_exec_context_id,
       resource_description
FROM sys.dm_os_waiting_tasks AS wt
     INNER JOIN sys.dm_exec_sessions AS es
         ON wt.session_id = es.session_id
     INNER JOIN sys.dm_exec_requests AS er
         ON wt.session_id = er.session_id
WHERE es.is_user_process = 1
      AND wt.wait_type <> 'SLEEP_TASK'
ORDER BY wt.wait_duration_ms DESC;

Screenshot del tipo di attesa per l'esecuzione delle sessioni.

Le statistiche esposte da questa query sono descritte di seguito:

Statistica Descrizione
session_id ID della sessione associata all'attività.
wait_type Tipo di attesa registrato da SQL Server nel motore, che impedisce l'esecuzione di una richiesta corrente.
last_wait_type Se la richiesta è stata precedentemente bloccata, questa colonna restituisce il tipo dell'ultima attesa. Non è nullabile.
wait_duration_ms Tempo di attesa totale, espresso in millisecondi, trascorso nell'attesa di questo tipo di attesa dall'avvio dell'istanza di SQL Server o dalla reimpostazione delle statistiche cumulative relative all'attesa.
blocking_session_id ID della sessione che sta bloccando la richiesta.
blocking_exec_context_id ID del contesto di esecuzione associato all'attività.
resource_description La colonna resource_description elenca la pagina esatta di cui si è in attesa nel formato <database_id>:<file_id>:<page_id>

La query seguente restituisce informazioni per tutti i latch non buffer:

SELECT * FROM sys.dm_os_latch_stats
WHERE latch_class <> 'BUFFER'
ORDER BY wait_time_ms DESC;

Screenshot dell'output della query.

Le statistiche esposte da questa query sono descritte di seguito:

Statistica Descrizione
latch_class Tipo di latch registrato da SQL Server nel motore, che impedisce l'esecuzione di una richiesta corrente.
waiting_requests_count Numero di attese di latch in questa classe dal riavvio di SQL Server. Questo contatore viene incrementato all'inizio di un'attesa di latch.
wait_time_ms Tempo di attesa totale, espresso in millisecondi, trascorso nell'attesa di questo tipo di latch.
max_wait_time_ms Tempo massimo, espresso in millisecondi, trascorso da una richiesta nell'attesa di questo tipo di latch.

I valori restituiti da questa DMV sono cumulativi a partire dall'ultimo riavvio del motore di database o dall'ultima reimpostazione della DMV. Usare la colonna sqlserver_start_time in sys.dm_os_sys_info per trovare l'ora di avvio dell'ultimo motore di database. Perciò, in un sistema in esecuzione da molto tempo alcune statistiche, ad es. max_wait_time_ms, saranno raramente utili. Il comando seguente può essere usato per reimpostare le statistiche relative all'attesa per questa DMV:

DBCC SQLPERF ('sys.dm_os_latch_stats', CLEAR);

Scenari di contesa di latch di SQL Server

È stato osservato che gli scenari seguenti causano una contesa di latch eccessiva.

Contesa di inserimento dell'ultima pagina o della pagina finale

Una comune procedura OLTP consiste nel creare un indice cluster per una colonna Identity o Date. Ciò consente di mantenere una corretta organizzazione fisica dell'indice, che può migliorare considerevolmente le prestazioni delle operazioni sia di lettura che di scrittura nell'indice. Questa progettazione dello schema, tuttavia, può inavvertitamente causare una contesa di latch. Questo problema si verifica molto spesso nelle tabelle di grandi dimensioni con righe di piccole dimensioni e negli inserimenti in un indice contenente una colonna chiave iniziale con valori che aumentano in modo sequenziale, ad esempio numeri interi crescenti o chiavi datetime. In questo scenario, l'applicazione esegue raramente, o addirittura mai, aggiornamenti o eliminazioni, con l'eccezione delle operazioni di archiviazione.

Nell'esempio seguente, il thread 1 e il thread 2 vogliono entrambi eseguire un inserimento di un record che verrà archiviato a pagina 299. Dal punto di vista del blocco logico, non esiste alcun problema, poiché vengono usati blocchi a livello di riga e blocchi esclusivi su entrambi i record nella stessa pagina possono essere mantenuti contemporaneamente. Tuttavia, per garantire l'integrità della memoria fisica un solo thread alla volta può acquisire un latch esclusivo, quindi l'accesso alla pagina viene serializzato per impedire la perdita di aggiornamenti in memoria. In questo caso, il thread 1 ottiene il latch esclusivo, mentre il thread 2 attende, registrando un'attesa PAGELATCH_EX per questa risorsa nelle statistiche di attesa. Viene visualizzato tramite il valore wait_type nella DMV sys.dm_os_waiting_tasks.

Diagramma del latch di pagina esclusivo sull'ultima riga.

Questa contesa viene comunemente definita contesa di "inserimento dell'ultima pagina" perché si verifica all'estremità destra dell'albero B, come illustrato nel diagramma seguente:

Diagramma della contesa di inserimento dell'ultima pagina.

Questo tipo di contesa di latch può essere spiegato come segue. Quando una nuova riga viene inserita in un indice, SQL Server usa l'algoritmo seguente per eseguire la modifica:

  1. Attraversamento dell'albero B per individuare la pagina corretta in cui conservare il nuovo record.

  2. Aggancia la pagina con PAGELATCH_EX, impedendo ad altri utenti di modificarla e acquisisci latch condivisi (PAGELATCH_SH) su tutte le pagine non foglia.

    Nota

    In alcuni casi, il motore SQL richiede EX l'acquisizione di latch anche nelle pagine albero B non foglia. Ad esempio, quando si verifica una suddivisione di pagine, tutte le pagine direttamente interessate devono essere bloccate esclusivamente (PAGELATCH_EX).

  3. Registrazione di una voce di log indicante che la riga è stata modificata.

  4. Aggiunta della riga alla pagina e impostazione della pagina come dirty.

  5. Annullamento del latch per tutte le pagine.

Se l'indice della tabella si basa su una chiave che aumenta in sequenza, ogni nuovo inserimento passa alla stessa pagina alla fine dell'albero B, fino a quando tale pagina non è piena. In scenari di concorrenza elevata, questo potrebbe causare contenzione sull'estremità destra dell'albero B e può verificarsi su indici clusterizzati e non clusterizzati. Le tabelle interessate da questo tipo di contesa accettano principalmente INSERT query e le pagine per gli indici problematici sono in genere relativamente dense (ad esempio, una dimensione di riga di circa 165 byte (incluso l'overhead di riga) è uguale a circa 49 righe per pagina). In questo esempio caratterizzato da inserimenti frequenti, ci aspettiamo che si verifichino attese PAGELATCH_EX/PAGELATCH_SH, e questa è l'osservazione tipica. Per esaminare attese latch di pagina e le attese latch di pagina ad albero, usare la DMV sys.dm_db_index_operational_stats.

La tabella seguente riepiloga i principali fattori osservati con questo tipo di contesa di latch:

Fattore Osservazioni tipiche
CPU logiche usate da SQL Server Questo tipo di contesa di latch si verifica principalmente nei sistemi con più di 16 core CPU e ancora più comunemente nei sistemi con più di 32 core CPU.
Progettazione di schemi e modelli di accesso Usa un valore di identità che aumenta in modo sequenziale come colonna iniziale di un indice in una tabella per dati transazionali.

L'indice ha una chiave primaria crescente con una frequenza elevata di inserimenti.

L'indice ha almeno un valore di colonna che aumenta in modo sequenziale.

Le righe sono in genere di piccole dimensioni e sono presenti molte righe per pagina.
Tipo di attesa osservato Molti thread che lottano per la stessa risorsa con attese di latch esclusive (EX) o condivise (SH) associate allo stesso resource_description nella sys.dm_os_waiting_tasks DMV, restituita dalla query sys.dm_os_waiting_tasks, ordinata per durata dell'attesa.
Fattori relativi alla progettazione da considerare Considerare la possibilità di modificare l'ordine delle colonne di indice come descritto nella strategia di mitigazione dell'indice non sequenziale, se è possibile garantire che gli inserimenti vengano distribuiti in modo uniforme nell'albero B.

Se viene usata la strategia di mitigazione della partizione hash, rimuove la possibilità di usare il partizionamento per altri scopi, ad esempio l'archiviazione di finestre scorrevoli.

L'uso della strategia di mitigazione delle partizioni hash può causare problemi di eliminazione delle partizioni per SELECT le query usate dall'applicazione.

Contesa di latch in tabelle di piccole dimensioni con un indice non cluster e inserimenti casuali (tabella della coda)

Questo scenario viene in genere visualizzato quando una tabella SQL viene usata come coda temporanea, ad esempio in un sistema di messaggistica asincrona.

In questo scenario, possono verificarsi conflitti di latch in modalità esclusiva (EX) e condivisa (SH) nelle condizioni seguenti:

  • Le operazioni di inserimento, selezione, aggiornamento o eliminazione si verificano in concorrenza elevata.
  • Le dimensioni delle righe sono relativamente piccole e comportano quindi pagine dense.
  • Il numero di righe nella tabella è relativamente basso e comporta quindi un albero B superficiale, definito da un indice con una profondità di due o tre livelli.

Nota

Con questo tipo di modello di accesso anche per gli alberi B con una profondità maggiore di questa possono verificarsi contese, se la frequenza del linguaggio DML (Data Manipulation Language) e la concorrenza del sistema sono sufficientemente elevate. Quando sono disponibili 16 o più core CPU per il sistema, il livello di contesa di latch può diventare evidente man mano che aumenta la concorrenza.

La contesa di latch può verificarsi anche se l'accesso è casuale nell'albero B, ad esempio quando una colonna non sequenziale è la chiave iniziale in un indice cluster. Lo screenshot seguente è relativo a un sistema in cui si verifica questo tipo di contesa di latch. In questo esempio, la contesa è dovuta alla densità delle pagine causata dalle piccole dimensioni delle righe e da un albero B relativamente superficiale. Con l'aumentare della concorrenza, la contesa di latch nelle pagine si verifica anche se gli inserimenti sono casuali nell'albero B, perché la colonna iniziale nell'indice era un GUID.

Nello screenshot seguente, le attese si verificano sia nelle pagine di dati del buffer che nelle pagine PFS (Page Free Space). Anche quando è stato aumentato il numero di file di dati, la contesa di latch era prevalente per le pagine di dati del buffer.

Screenshot dei tipi di attesa.

La tabella seguente riepiloga i principali fattori osservati con questo tipo di contesa di latch:

Fattore Osservazioni tipiche
CPU logiche usate da SQL Server La contesa di latch si verifica principalmente nei computer con più di 16 core CPU.
Progettazione di schemi e modelli di accesso Frequenza elevata di modelli di accesso di inserimento/selezione/aggiornamento/eliminazione in tabelle di piccole dimensioni.

Albero B superficiale (indice con una profondità di due o tre livelli).

Righe di piccole dimensioni (molti record per pagina).
Livello di concorrenza La contesa di latch si verifica solo con livelli elevati di richieste simultanee dal livello applicativo.
Tipo di attesa osservato Osservare le attese sul buffer (PAGELATCH_EX e PAGELATCH_SH) e sul latch non buffer ACCESS_METHODS_HOBT_VIRTUAL_ROOT a causa di scissioni della radice. Inoltre, PAGELATCH_UP attende le pagine PFS. Per altre informazioni sulle attese di latch non buffer, vedere sys.dm_os_latch_stats nella Guida di SQL Server.

La combinazione di un albero B superficiale e di inserimenti casuali nell'indice può causare divisioni di pagina nell'albero B. Per eseguire una suddivisione di pagina, SQL Server deve acquisire latch condivisi (SH) a tutti i livelli e quindi acquisire latch esclusivi (EX) nelle pagine dell'albero B coinvolte nelle divisioni di pagina. Inoltre, quando la concorrenza è elevata e i dati vengono inseriti ed eliminati continuamente, potrebbero verificarsi split della radice dell'albero B. In questo caso, altri inserimenti potrebbero dover attendere eventuali latch non buffer acquisiti nell'albero B. Questo si manifesta come un elevato numero di attese sul tipo di latch ACCESS_METHODS_HOBT_VIRTUAL_ROOT osservate nella sys.dm_os_latch_stats DMV.

Lo script seguente può essere modificato per determinare la profondità dell'albero B per gli indici della tabella interessata.

SELECT
    o.name AS [table],
    i.name AS [index],
    indexProperty(object_id(o.name), i.name, 'indexDepth') + indexProperty(object_id(o.name), i.name, 'isClustered') AS depth, --clustered index depth reported doesn't count leaf level
    i.[rows] AS [rows],
    i.origFillFactor AS [fillFactor],
    CASE (indexProperty(object_id(o.name), i.name, 'isClustered'))
        WHEN 1 THEN 'clustered'
        WHEN 0 THEN 'nonclustered'
        ELSE 'statistic'
    END AS type
FROM sysIndexes AS i
     INNER JOIN sysObjects AS o
         ON o.id = i.id
WHERE o.type = 'u'
      AND indexProperty(object_id(o.name), i.name, 'isHypothetical') = 0 --filter out hypothetical indexes
      AND indexProperty(object_id(o.name), i.name, 'isStatistics') = 0 --filter out statistics
ORDER BY o.name;

Contesa di latch nelle pagine PFS (Page Free Space)

PFS è l'acronimo di Page Free Space, SQL Server alloca una pagina PFS per ogni 8088 pagine (a partire da PageID = 1) in ogni file di database. Ogni byte nella pagina PFS registra informazioni che includono la quantità di spazio disponibile nella pagina, se è allocata o meno e se la pagina archivia i record fantasma. La pagina PFS contiene informazioni sulle pagine disponibili per l'allocazione quando una nuova pagina viene richiesta da un'operazione di inserimento o di aggiornamento. La pagina PFS deve essere aggiornata in diversi scenari, tra cui quando si verificano allocazioni o deallocazione. Poiché è necessario usare un latch di aggiornamento (UP) per proteggere la pagina PFS, nelle pagine PFS può verificarsi una contesa di latch se sono presenti relativamente pochi file di dati in un filegroup e un numero elevato di core CPU. È possibile risolvere facilmente questo problema aumentando il numero di file per ogni filegroup.

Avviso

L'aumento del numero di file per filegroup potrebbe influire negativamente sulle prestazioni di determinati carichi, ad esempio i caricamenti con molte operazioni di ordinamento di grandi dimensioni che dis spillano memoria su disco.

Se vengono osservate molte PAGELATCH_UP attese per le pagine PFS o SGAM in tempdb, seguire questi passaggi per rimuovere questo collo di bottiglia:

  1. Aggiungere file di dati a tempdb in modo che il numero di file di dati tempdb sia uguale al numero di core del processore nel server.

  2. Abilitare il flag di traccia di SQL Server 1118.

Per altre informazioni sui colli di bottiglia di allocazione causati da conflitti nelle pagine di sistema, vedere il post di blog Che cos'è il collo di bottiglia dell'allocazione?

Funzioni con valori di tabella e contesa di latch in tempdb

Esistono altri fattori oltre alla contesa di allocazione che possono causare contese di latch in tempdb, ad esempio l'uso elevato di TVF all'interno delle query.

Gestire la contesa di latch per modelli di tabella diversi

Le sezioni seguenti descrivono le tecniche che possono essere usate per risolvere o ovviare ai problemi di prestazioni correlati a una contesa di latch eccessiva.

Usare una chiave di indice iniziale non sequenziale

Un metodo per gestire una contesa di latch consiste nel sostituire una chiave di indice sequenziale con una chiave non sequenziale per distribuire uniformemente inserimenti in un intervallo di indici.

Questa operazione viene in genere eseguita con una colonna iniziale nell'indice che distribuisce il carico di lavoro in modo proporzionale. In questo caso sono disponibili due opzioni:

Opzione: usare una colonna nella tabella per distribuire i valori nell'intervallo di chiavi dell'indice

Valutare il carico di lavoro per ottenere un valore naturale che possa essere usato per distribuire gli inserimenti nell'intervallo di chiavi. Si consideri, ad esempio, uno scenario bancario ATM in cui ATM_ID potrebbe essere un buon candidato per distribuire gli inserimenti in una tabella delle transazioni per i prelievi perché un cliente può usare un solo bancomat alla volta. Analogamente, in un sistema di vendita al dettaglio, forse Checkout_ID o un ID del negozio sarebbe un valore naturale che potrebbe essere usato per distribuire gli inserimenti in una gamma di chiavi. Questa tecnica richiede la creazione di una chiave di indice composita con la colonna chiave iniziale che rappresenta il valore della colonna identificata o un hash di tale valore combinato con una o più colonne aggiuntive per fornire l'univocità. Nella maggior parte dei casi, un hash del valore funziona meglio, perché troppi valori distinti generano un'organizzazione fisica scarsa. In un sistema di punti vendita, ad esempio, dal valore Store ID è possibile creare un hash, che è un modulo, che si allinea al numero di core CPU. Questa tecnica avrebbe come risultato un numero relativamente basso di intervalli all'interno della tabella. Tuttavia sarebbe sufficiente distribuire gli inserimenti in modo da evitare la contesa di latch. Questa tecnica è illustrata nell'immagine seguente.

Screenshot degli inserimenti dopo l'applicazione di un indice non sequenziale.

Importante

Questo modello è contrario alle tradizionali procedure consigliate di indicizzazione. Anche se questa tecnica consente di garantire una distribuzione uniforme degli inserimenti nell'albero B, potrebbe anche richiedere una modifica dello schema a livello di applicazione. Inoltre, questo modello potrebbe influire negativamente sulle prestazioni delle query che richiedono analisi di intervallo che utilizzano l'indice cluster. Per determinare se questo approccio di progettazione funziona correttamente, è necessaria un'analisi dei modelli di carico di lavoro. Questo modello deve essere implementato se è possibile sacrificare alcune prestazioni di analisi sequenziale per ottenere velocità effettiva e scalabilità.

Questo modello è stato implementato durante un engagement di lab per la misurazione delle prestazioni e ha risolto una contesa di latch in un sistema con 32 core CPU fisici. La tabella è stata usata per archiviare il saldo finale al termine di una transazione. Ogni transazione commerciale ha eseguito un singolo inserimento nella tabella.

Definizione di una tabella originale

Durante l'uso della definizione di una tabella originale, si è osservata una contesa di latch eccessiva nell'indice cluster pk_table1:

CREATE TABLE table1
(
    TransactionID BIGINT NOT NULL,
    UserID INT NOT NULL,
    SomeInt INT NOT NULL
);
GO

ALTER TABLE table1
    ADD CONSTRAINT pk_table1 PRIMARY KEY CLUSTERED (TransactionID, UserID);
GO

Nota

I nomi degli oggetti nella definizione della tabella sono stati modificati rispetto ai valori originali.

Definizione dell'indice riordinato

Riordinando le colonne chiave dell'indice con UserID come colonna iniziale nella chiave primaria è stata fornita una distribuzione quasi casuale degli inserimenti nelle pagine. La distribuzione risultante non era 100% casuale perché non tutti gli utenti sono online contemporaneamente, ma la distribuzione era sufficientemente casuale da ridurre la contenzione eccessiva dei latch. Un'avvertenza per riordinare la definizione dell'indice consiste nel fatto che qualsiasi query di selezione su questa tabella deve essere modificata in modo da usare sia UserID che TransactionID come predicati di uguaglianza.

Importante

Assicurarsi di testare accuratamente tutte le modifiche apportate in un ambiente di test prima dell'esecuzione in un ambiente di produzione.

CREATE TABLE table1
(
    TransactionID BIGINT NOT NULL,
    UserID INT NOT NULL,
    SomeInt INT NOT NULL
);
GO

ALTER TABLE table1
    ADD CONSTRAINT pk_table1 PRIMARY KEY CLUSTERED (UserID, TransactionID);
GO

Uso di un valore hash come colonna iniziale nella chiave primaria

La definizione di tabella seguente può essere usata per generare un modulo allineato al numero di CPU, HashValue viene generato usando il valore TransactionID che aumenta in sequenza per garantire una distribuzione uniforme nell'albero B:

CREATE TABLE table1
(
    TransactionID BIGINT NOT NULL,
    UserID INT NOT NULL,
    SomeInt INT NOT NULL
);
GO

-- Consider using bulk loading techniques to speed it up
ALTER TABLE table1
    ADD [HashValue] AS (CONVERT (TINYINT, ABS([TransactionID]) % (32))) PERSISTED NOT NULL;

ALTER TABLE table1
    ADD CONSTRAINT pk_table1 PRIMARY KEY CLUSTERED (HashValue, TransactionID, UserID);
GO

Opzione: usare un GUID come colonna chiave iniziale dell'indice

Se non è presente alcun separatore naturale, è possibile usare una colonna GUID come colonna chiave iniziale dell'indice per garantire una distribuzione uniforme degli inserimenti. Anche se l'uso del GUID come colonna iniziale nell'approccio basato sulla chiave di indice consente di usare il partizionamento per altre funzionalità, questa tecnica presenta anche alcuni svantaggi, tra cui un numero maggiore di divisioni di pagina, un'organizzazione fisica inadeguata e basse densità di pagina.

Nota

L'uso dei GUID come colonne chiave iniziali degli indici è un argomento molto dibattuto. Una discussione approfondita sui vantaggi e gli svantaggi di questo metodo esula dall'ambito di questo articolo.

Usare il partizionamento hash con una colonna calcolata

Il partizionamento delle tabelle in SQL Server può essere usato per attenuare una contesa di latch eccessiva. La creazione di uno schema di partizionamento hash con una colonna calcolata in una tabella partizionata è un approccio comune realizzabile con questi passaggi:

  1. Creare un nuovo filegroup o usare un filegroup esistente in cui conservare le partizioni.

  2. Se si usa un nuovo filegroup, bilanciare equamente i singoli file in base al numero di unità logica, facendo attenzione a usare un layout ottimale. Se il modello di accesso prevede una frequenza elevata di inserimenti, assicurarsi di creare un numero di file pari a quello dei core CPU fisici nel computer SQL Server.

  3. Usare il CREATE PARTITION FUNCTION comando per partizionare le tabelle in partizioni X , dove X è il numero di core CPU fisici nel computer SQL Server. (almeno 32 partizioni).

    Nota

    Non è sempre necessario un allineamento 1:1 del numero di partizioni al numero di core CPU. In molti casi, questo valore può essere inferiore al numero di core CPU. La presenza di più partizioni può comportare un sovraccarico maggiore per le query che devono eseguire ricerche in tutte le partizioni e in questi casi un minor numero di partizioni può risultare utile. Nel test di SQLCAT sui sistemi con 64 e 128 CPU logiche con carichi di lavoro dei clienti reali, sono state sufficienti 32 partizioni per risolvere una contesa di latch eccessiva e raggiungere gli obiettivi di scalabilità. In ultima analisi, il numero ideale di partizioni deve essere determinato tramite test.

  4. Usare il comando CREATE PARTITION SCHEME:

    • Associare la funzione di partizione ai filegroup.
    • Aggiungere alla tabella una colonna hash di tipo tinyint o smallint.
    • Calcolare una distribuzione hash valida. Ad esempio, usare HASHBYTES con modulo o BINARY_CHECKSUM.

Lo script di esempio seguente può essere personalizzato per le finalità dell'implementazione:

--Create the partition scheme and function, align this to the number of CPU cores 1:1 up to 32 core computer
-- so for below this is aligned to 16 core system
CREATE PARTITION FUNCTION [pf_hash16](TINYINT)
    AS RANGE LEFT
    FOR VALUES (0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15);

CREATE PARTITION SCHEME [ps_hash16]
    AS PARTITION [pf_hash16]
    ALL TO ([ALL_DATA]);
-- Add the computed column to the existing table (this is an OFFLINE operation)

-- Consider using bulk loading techniques to speed it up
ALTER TABLE [dbo].[latch_contention_table]
    ADD [HashValue] AS (CONVERT (TINYINT, ABS(BINARY_CHECKSUM([hash_col]) % (16)), (0))) PERSISTED NOT NULL;

--Create the index on the new partitioning scheme
CREATE UNIQUE CLUSTERED INDEX [IX_Transaction_ID]
    ON [dbo].[latch_contention_table]([T_ID] ASC, [HashValue])
    ON ps_hash16 (HashValue);

Questo script può essere usato per il partizionamento hash di una tabella in cui si verificano problemi causati da una contesa di inserimento dell'ultima pagina o della pagina finale. Questa tecnica sposta la contesa dall'ultima pagina partizionando la tabella e distribuendo gli inserimenti tra le partizioni della tabella con un'operazione di modulo del valore hash.

Effetti del partizionamento hash con una colonna calcolata

Come illustra il diagramma seguente, questa tecnica sposta la contesa dall'ultima pagina ricompilando l'indice nella funzione hash e creando un numero di partizioni pari a quello dei core CPU fisici nel computer SQL Server. Gli inserimenti continuano a essere posizionati alla fine dell'intervallo logico (un valore che aumenta in modo sequenziale), ma l'operazione di modulo del valore hash garantisce che gli inserimenti vengano divisi tra i diversi alberi B, riducendo così il collo di bottiglia, come è illustrato nei diagrammi seguenti:

Diagramma della contesa di latch di pagina da quando è stata inserita l'ultima pagina.

Diagramma della contesa sui latch di pagina risolta con il partizionamento.

Compromessi per l'uso del partizionamento hash

Anche se il partizionamento hash può eliminare la contesa negli inserimenti, è necessario prendere in considerazione diversi compromessi quando si decide se usare o meno questa tecnica:

  • Nella maggior parte dei casi è necessario modificare le query per includere la partizione hash nel predicato e generare un piano di query che non fornisce alcuna eliminazione della partizione quando vengono eseguite queste query. Lo screenshot seguente mostra un piano non valido senza eliminazioni di partizioni dopo l'implementazione del partizionamento hash.

    Screenshot del piano di query in assenza di eliminazione delle partizioni.

  • Esclude la possibilità di eliminazione di partizioni in alcune altre query, ad esempio i report basati su intervalli.

  • Quando si aggiunge una tabella con partizionamento hash a un'altra tabella, per ottenere l'eliminazione della partizione, è necessario eseguire il partizionamento hash della seconda tabella nella stessa chiave e la chiave hash deve far parte dei criteri di join.

  • Il partizionamento hash impedisce di usare il partizionamento per altre funzionalità di gestione, ad esempio l'archiviazione di finestre temporali scorrevoli e le funzionalità di cambio di partizione.

Il partizionamento hash è una strategia efficace per ridurre una contesa di latch eccessiva perché aumenta la velocità effettiva complessiva del sistema attenuando la contesa negli inserimenti. Poiché esistono alcuni compromessi, potrebbe non essere la soluzione ottimale per alcuni modelli di accesso.

Riepilogo delle tecniche usate per gestire la contesa di latch

Le due sezioni seguenti forniscono un riepilogo delle tecniche che possono essere usate per gestire una contesa di latch eccessiva:

Chiave/Indice non sequenziale

Vantaggi:

  • Consente di usare altre funzionalità di partizionamento, ad esempio l'archiviazione di dati tramite uno schema di finestre temporali scorrevoli e le funzionalità di cambio di partizione.

Svantaggi:

  • Possibili sfide nella scelta di una chiave o di un indice per garantire sempre una distribuzione "il più possibile" uniforme degli inserimenti.
  • È possibile usare un GUID come colonna iniziale per garantire una distribuzione uniforme, tenendo tuttavia presente che ciò può comportare un numero eccessivo di operazioni di divisione di pagina.
  • Gli inserimenti casuali nell'albero B possono comportare un numero eccessivo di operazioni di divisione di pagina e causare una contesa di latch nelle pagine non foglia.

Partizionamento hash con colonna calcolata

Vantaggi:

  • È trasparente per gli inserimenti.

Svantaggi:

  • Il partizionamento non può essere usato per le funzionalità di gestione desiderate, ad esempio l'archiviazione dei dati tramite opzioni di cambio di partizione.
  • Può causare problemi di eliminazione di partizioni per le query, tra cui selezioni/aggiornamenti singoli e basati su un intervallo e query che eseguono un join.
  • L'aggiunta di una colonna calcolata persistente è un'operazione offline.

Suggerimento

Per altre tecniche, vedere il post di blog PAGELATCH_EX attese e inserimenti pesanti.

Procedura dettagliata: diagnosticare una contesa di latch

La procedura dettagliata seguente illustra gli strumenti e le tecniche descritti in Diagnosi della contesa di latch di SQL Server e Gestione della contesa di latch per modelli di tabella diversi per risolvere un problema in uno scenario reale. Questo scenario descrive un coinvolgimento del cliente per eseguire test di carico di un sistema di punti vendita, per simulare circa 8.000 punti vendita nelle transazioni con un'applicazione SQL Server in esecuzione su un sistema con 8 socket, 32 core fisici e 256 GB di memoria.

Il diagramma seguente illustra in dettaglio l'hardware usato per testare il sistema di punti vendita:

Diagramma dell'ambiente di test del sistema di punto vendita.

Sintomo: latch ad accesso frequente

In questo caso, abbiamo osservato attese elevate per PAGELATCH_EX che in genere definiamo elevata come una media superiore a 1 ms. In questo caso sono state costantemente osservate attese superiori ai 20 ms.

Screenshot dei latch ad accesso frequente.

Dopo aver determinato che la contesa di latch costituiva un problema, si è passati a determinare la causa della contesa di latch.

Isolare l'oggetto che causa contesa di latch

Lo script seguente usa la colonna resource_description per isolare l'indice che causava la PAGELATCH_EX contesa:

Nota

La colonna resource_description restituita da questo script fornisce la descrizione della risorsa nel formato <DatabaseID,FileID,PageID>, dove il nome del database associato a DatabaseID può essere determinato passando il valore di DatabaseID alla DB_NAME() funzione .

SELECT wt.session_id,
       wt.wait_type,
       wt.wait_duration_ms,
       s.name AS schema_name,
       o.name AS object_name,
       i.name AS index_name
FROM sys.dm_os_buffer_descriptors AS bd
     INNER JOIN (SELECT *,
             --resource_description
             CHARINDEX(':', resource_description) AS file_index,
             CHARINDEX(':', resource_description, CHARINDEX(':', resource_description) + 1) AS page_index,
             resource_description AS rd
      FROM sys.dm_os_waiting_tasks AS wt
      WHERE wait_type LIKE 'PAGELATCH%') AS wt
     ON bd.database_id = SUBSTRING(wt.rd, 0, wt.file_index)
        AND bd.file_id = SUBSTRING(wt.rd, wt.file_index + 1, 1) --wt.page_index)
        AND bd.page_id = SUBSTRING(wt.rd, wt.page_index + 1, LEN(wt.rd))
     INNER JOIN sys.allocation_units AS au
         ON bd.allocation_unit_id = au.allocation_unit_id
     INNER JOIN sys.partitions AS p
         ON au.container_id = p.partition_id
     INNER JOIN sys.indexes AS i
         ON p.index_id = i.index_id
        AND p.object_id = i.object_id
     INNER JOIN sys.objects AS o
         ON i.object_id = o.object_id
     INNER JOIN sys.schemas AS s
         ON o.schema_id = s.schema_id
ORDER BY wt.wait_duration_ms DESC;

Come mostrato qui, la contesa è nella tabella LATCHTEST e nel nome dell'indice CIX_LATCHTEST. Tenere presente che i nomi sono stati modificati per rendere anonimo il carico di lavoro.

Screenshot della contesa di LATCHTEST.

Per uno script più avanzato che esegue ripetutamente il polling e usa una tabella temporanea per determinare il tempo di attesa totale in un periodo configurabile, vedere Eseguire una query dei descrittori di buffer per determinare quali oggetti causano la contesa di latch nell'appendice.

Tecnica alternativa per isolare l'oggetto che causa la contesa di latch

A volte può essere poco pratico eseguire query sys.dm_os_buffer_descriptors. Poiché la memoria nel sistema e quella disponibile per il pool di buffer aumentano, aumenta anche il tempo necessario per eseguire questa vista DMV. In un sistema da 256 GB, l'esecuzione della DMV potrebbe richiedere fino a 10 minuti. È disponibile una tecnica alternativa, che viene descritta in dettaglio di seguito, con un carico di lavoro diverso eseguito nel lab:

  1. Eseguire query sulle attività in attesa correnti, usando lo script della sezione Eseguire una query su sys.dm_os_waiting_tasks con i risultati ordinati in base alla durata dell'attesa nell'appendice.

  2. Identificare la pagina chiave in cui si osserva una serie di istruzioni, come si verifica quando più thread si contendono la stessa pagina. In questo esempio, i thread che eseguono l'inserimento si contendono per la pagina finale nella struttura ad albero B e attendono fino a quando non possono acquisire un EX latch. Ciò è indicato dal resource_description nella prima query, in questo caso 8:1:111305.

  3. Abilita il flag di traccia 3604, che espone ulteriori informazioni sulla pagina tramite DBCC PAGE con la seguente sintassi, sostituisci il valore ottenuto tramite resource_description per il valore tra parentesi:

    Abilitare il flag di traccia 3604 per abilitare l'output della console:

    DBCC TRACEON (3604);

    Esaminare i dettagli della pagina:

    DBCC PAGE (8, 1, 111305, -1);

  4. Esaminare l'output di DBCC. In questo caso 78623323deve essere presente un ObjectID metadati associato.

    Screenshot dell'OBJECTID dei metadati.

  5. È ora possibile eseguire il comando seguente per determinare il nome dell'oggetto che causa la contesa, che come previsto è LATCHTEST.

    Nota

    Assicurarsi di essere nel contesto del database corretto, in caso contrario la query restituisce NULL.

    --get object name
SELECT OBJECT_NAME(78623323);

    Screenshot del nome dell'oggetto.

Riepilogo e risultati

Usando la tecnica precedente è stato possibile verificare che la contesa si è verificata in un indice cluster con un valore chiave che aumenta in modo sequenziale nella tabella che ha di gran lunga ricevuto il maggior numero di inserimenti. Questo tipo di contesa non è insolito per gli indici con un valore di chiave che aumenta in sequenza, come datetime, identity o generato da un'applicazione TransactionID.

Per risolvere questo problema, è stato usato il partizionamento hash con una colonna calcolata ed è stato osservato un miglioramento delle prestazioni del 690%. La tabella seguente riepiloga le prestazioni dell'applicazione prima e dopo l'implementazione del partizionamento hash con una colonna calcolata. L'utilizzo della CPU aumenta considerevolmente e parallelamente alla velocità effettiva, come previsto dopo la rimozione del collo di bottiglia dovuto alla contesa di latch:

Misura Prima del partizionamento hash Dopo il partizionamento hash
Transazioni commerciali/sec 36 249
Tempo medio di attesa latch di pagina 36 millisecondi 0,6 millisecondi
Attese latch/sec 9.562 2.873
Tempo processore SQL 24% 78%
Richieste batch SQL/sec 12.368 47.045

Come si può vedere dalla tabella precedente, l'identificazione e la risoluzione corrette dei problemi di prestazioni causati da un'eccessiva contesa di latch delle pagine possono avere un effetto positivo sulle prestazioni complessive dell'applicazione.

Appendice: tecnica alternativa

Una possibile strategia per evitare un'eccessiva contesa sui latch delle pagine consiste nel padding delle righe con una colonna char, per garantire che ogni riga occupi una pagina completa. Questa strategia è un'opzione quando le dimensioni complessive dei dati sono piccole ed è necessario gestire EX la contesa sui latch delle pagine causata dalla seguente combinazione di fattori:

  • Righe di dimensioni ridotte
  • Albero B superficiale
  • Modello di accesso con una frequenza elevata di operazioni casuali di inserimento, selezione, aggiornamento ed eliminazione
  • Tabelle di piccole dimensioni, ad esempio tabelle di coda temporanea

Riempire le righe per occupare una pagina completa richiede che SQL allochi più pagine, aumentando quelle disponibili per gli inserimenti e riducendo la contesa sui latch di pagina.

Riempi le righe per garantire che ogni riga occupi una pagina completa

Per riempire le righe in modo che occupino un'intera pagina, è possibile usare uno script simile al seguente:

ALTER TABLE mytable ADD Padding CHAR(5000) NOT NULL DEFAULT ('X');

Nota

Usare il carattere più piccolo possibile che forza una riga per pagina per ridurre i requisiti aggiuntivi della CPU per il valore di riempimento e lo spazio aggiuntivo necessario per registrare la riga. In un sistema a prestazioni elevate viene conteggiato ogni byte.

Questa tecnica viene illustrata per completezza. Nella pratica, SQLCAT l'ha usata solo su una piccola tabella con 10.000 righe in un unico engagement relativo alle prestazioni. Questa tecnica ha un'applicazione limitata in quanto accresce il numero di richieste di memoria in SQL Server per le tabelle di grandi dimensioni e può comportare una contesa di latch non di buffer nelle pagine non foglia. La pressione di memoria aggiuntiva può essere un fattore di limitazione significativo per l'applicazione di questa tecnica. Con la quantità di memoria disponibile in un server moderno, gran parte del working set per i carichi di lavoro OLTP viene in genere conservata in memoria. Quando il set di dati aumenta a una dimensione non più adatta alla memoria, si verifica un calo significativo delle prestazioni. Questa tecnica è quindi applicabile solo alle tabelle di piccole dimensioni. Questa tecnica non viene usata da SQLCAT per scenari come la contesa di inserimento dell'ultima pagina o della pagina finale per tabelle di grandi dimensioni.

Importante

L'uso di questa strategia può causare un numero elevato di attese sul tipo di latch ACCESS_METHODS_HOBT_VIRTUAL_ROOT perché questa strategia può portare a un numero elevato di divisioni di pagina che si verificano nei livelli non fogliari dell'albero B. In questo caso, SQL Server deve acquisire i latch condivisi (SH) a tutti i livelli seguiti da latch esclusivi (EX) nelle pagine dell'albero B in cui è possibile suddividere una pagina. Controllare la sys.dm_os_latch_stats DMV per un numero elevato di attese sul tipo di interblocco ACCESS_METHODS_HOBT_VIRTUAL_ROOT dopo il riempimento delle righe.

Appendice: script per la contesa di latch di SQL Server

Questa sezione contiene script che possono essere utilizzati per aiutare a diagnosticare e risolvere i problemi di contesa sui latch.

Eseguire una query sys.dm_os_waiting_tasks con i risultati ordinati per ID sessione

Lo script di esempio seguente effettua query su sys.dm_os_waiting_tasks e restituisce le attese latch ordinate in base all'ID sessione:

-- WAITING TASKS ordered by session_id
SELECT wt.session_id,
       wt.wait_type,
       er.last_wait_type AS last_wait_type,
       wt.wait_duration_ms,
       wt.blocking_session_id,
       wt.blocking_exec_context_id,
       resource_description
FROM sys.dm_os_waiting_tasks AS wt
     INNER JOIN sys.dm_exec_sessions AS es
         ON wt.session_id = es.session_id
     INNER JOIN sys.dm_exec_requests AS er
         ON wt.session_id = er.session_id
WHERE es.is_user_process = 1
      AND wt.wait_type <> 'SLEEP_TASK'
ORDER BY session_id;

Eseguire una query sys.dm_os_waiting_tasks con i risultati ordinati in base alla durata dell'attesa

Lo script di esempio seguente esegue la query sys.dm_os_waiting_tasks e restituisce le attese ai latch, ordinate in base alla durata dell'attesa.

-- WAITING TASKS ordered by wait_duration_ms
SELECT wt.session_id,
       wt.wait_type,
       er.last_wait_type AS last_wait_type,
       wt.wait_duration_ms,
       wt.blocking_session_id,
       wt.blocking_exec_context_id,
       resource_description
FROM sys.dm_os_waiting_tasks AS wt
     INNER JOIN sys.dm_exec_sessions AS es
         ON wt.session_id = es.session_id
     INNER JOIN sys.dm_exec_requests AS er
         ON wt.session_id = er.session_id
WHERE es.is_user_process = 1
      AND wt.wait_type <> 'SLEEP_TASK'
ORDER BY wt.wait_duration_ms DESC;

Calcolare le attese in un periodo di tempo

Lo script seguente calcola e restituisce le attese latch in un periodo di tempo.

/* Snapshot the current wait stats and store so that this can be compared over a time period
   Return the statistics between this point in time and the last collection point in time.

   **This data is maintained in tempdb so the connection must persist between each execution**
   **alternatively this could be modified to use a persisted table in tempdb.  if that
   is changed code should be included to clean up the table at some point.**
*/
USE tempdb;
GO

DECLARE @current_snap_time AS DATETIME;
DECLARE @previous_snap_time AS DATETIME;

SET @current_snap_time = GETDATE();

IF NOT EXISTS (SELECT name
               FROM tempdb.sys.sysobjects
               WHERE name LIKE '#_wait_stats%')
    CREATE TABLE #_wait_stats
    (
        wait_type VARCHAR (128),
        waiting_tasks_count BIGINT,
        wait_time_ms BIGINT,
        avg_wait_time_ms INT,
        max_wait_time_ms BIGINT,
        signal_wait_time_ms BIGINT,
        avg_signal_wait_time INT,
        snap_time DATETIME
    );

INSERT INTO #_wait_stats (wait_type, waiting_tasks_count, wait_time_ms, max_wait_time_ms, signal_wait_time_ms, snap_time)
SELECT wait_type,
       waiting_tasks_count,
       wait_time_ms,
       max_wait_time_ms,
       signal_wait_time_ms,
       getdate()
FROM sys.dm_os_wait_stats;

--get the previous collection point
SELECT TOP 1 @previous_snap_time = snap_time
FROM #_wait_stats
WHERE snap_time < (SELECT MAX(snap_time)
                   FROM #_wait_stats)
ORDER BY snap_time DESC;

--get delta in the wait stats
SELECT TOP 10 s.wait_type,
              (e.waiting_tasks_count - s.waiting_tasks_count) AS [waiting_tasks_count],
              (e.wait_time_ms - s.wait_time_ms) AS [wait_time_ms],
              (e.wait_time_ms - s.wait_time_ms) / ((e.waiting_tasks_count - s.waiting_tasks_count)) AS [avg_wait_time_ms],
              (e.max_wait_time_ms) AS [max_wait_time_ms],
              (e.signal_wait_time_ms - s.signal_wait_time_ms) AS [signal_wait_time_ms],
              (e.signal_wait_time_ms - s.signal_wait_time_ms) / ((e.waiting_tasks_count - s.waiting_tasks_count)) AS [avg_signal_time_ms],
              s.snap_time AS [start_time],
              e.snap_time AS [end_time],
              DATEDIFF(ss, s.snap_time, e.snap_time) AS [seconds_in_sample]
FROM #_wait_stats AS e
     INNER JOIN (SELECT *
      FROM #_wait_stats
      WHERE snap_time = @previous_snap_time) AS s
     ON (s.wait_type = e.wait_type)
WHERE e.snap_time = @current_snap_time
      AND s.snap_time = @previous_snap_time
      AND e.wait_time_ms > 0
      AND (e.waiting_tasks_count - s.waiting_tasks_count) > 0
      AND e.wait_type NOT IN ('LAZYWRITER_SLEEP', 'SQLTRACE_BUFFER_FLUSH', 'SOS_SCHEDULER_YIELD',
                              'DBMIRRORING_CMD', 'BROKER_TASK_STOP', 'CLR_AUTO_EVENT',
                              'BROKER_RECEIVE_WAITFOR', 'WAITFOR', 'SLEEP_TASK',
                              'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT',
                              'FT_IFTS_SCHEDULER_IDLE_WAIT', 'BROKER_TO_FLUSH',
                              'XE_DISPATCHER_WAIT', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP')
ORDER BY (e.wait_time_ms - s.wait_time_ms) DESC;

--clean up table
DELETE FROM #_wait_stats
WHERE snap_time = @previous_snap_time;

Eseguire una query dei descrittori di buffer per determinare quali oggetti causano la contesa di latch

Lo script seguente esegue una query dei descrittori di buffer per determinare quali oggetti sono associati ai tempi di attesa latch più lunghi.

IF EXISTS (SELECT *
           FROM tempdb.sys.objects
           WHERE [name] LIKE '#WaitResources%')
    DROP TABLE #WaitResources;

CREATE TABLE #WaitResources
(
    session_id INT,
    wait_type NVARCHAR (1000),
    wait_duration_ms INT,
    resource_description sysname NULL,
    db_name NVARCHAR (1000),
    schema_name NVARCHAR (1000),
    object_name NVARCHAR (1000),
    index_name NVARCHAR (1000)
);
GO

DECLARE @WaitDelay AS VARCHAR (16), @Counter AS INT, @MaxCount AS INT, @Counter2 AS INT;
SELECT @Counter = 0, @MaxCount = 600, @WaitDelay = '00:00:00.100'; -- 600x.1=60 seconds

SET NOCOUNT ON;

WHILE @Counter < @MaxCount
BEGIN
   INSERT INTO #WaitResources (session_id, wait_type, wait_duration_ms, resource_description)--, db_name, schema_name, object_name, index_name)
   SELECT wt.session_id,
         wt.wait_type,
         wt.wait_duration_ms,
         wt.resource_description
   FROM sys.dm_os_waiting_tasks AS wt
   WHERE wt.wait_type LIKE 'PAGELATCH%'
         AND wt.session_id <> @@SPID;

   -- SELECT * FROM sys.dm_os_buffer_descriptors;

   SET @Counter = @Counter + 1;
   WAITFOR DELAY @WaitDelay;
END

--SELECT * FROM #WaitResources;

UPDATE #WaitResources
    SET db_name = DB_NAME(bd.database_id),
        schema_name = s.name,
        object_name = o.name,
        index_name = i.name
FROM #WaitResources AS wt
     INNER JOIN sys.dm_os_buffer_descriptors AS bd
         ON bd.database_id = SUBSTRING(wt.resource_description, 0, CHARINDEX(':', wt.resource_description))
        AND bd.file_id = SUBSTRING(wt.resource_description, CHARINDEX(':', wt.resource_description) + 1, CHARINDEX(':', wt.resource_description, CHARINDEX(':', wt.resource_description) + 1) - CHARINDEX(':', wt.resource_description) - 1)
        AND bd.page_id = SUBSTRING(wt.resource_description, CHARINDEX(':', wt.resource_description, CHARINDEX(':', wt.resource_description) + 1) + 1, LEN(wt.resource_description) + 1)
        -- AND wt.file_index > 0 AND wt.page_index > 0
     INNER JOIN sys.allocation_units AS au
         ON bd.allocation_unit_id = AU.allocation_unit_id
     INNER JOIN sys.partitions AS p
         ON au.container_id = p.partition_id
     INNER JOIN sys.indexes AS i
         ON p.index_id = i.index_id
        AND p.object_id = i.object_id
     INNER JOIN sys.objects AS o
         ON i.object_id = o.object_id
     INNER JOIN sys.schemas AS s
         ON o.schema_id = s.schema_id;

SELECT * FROM #WaitResources
ORDER BY wait_duration_ms DESC;
GO
/*
--Other views of the same information
SELECT wait_type, db_name, schema_name, object_name, index_name, SUM(wait_duration_ms) [total_wait_duration_ms] FROM #WaitResources
GROUP BY wait_type, db_name, schema_name, object_name, index_name;
SELECT session_id, wait_type, db_name, schema_name, object_name, index_name, SUM(wait_duration_ms) [total_wait_duration_ms] FROM #WaitResources
GROUP BY session_id, wait_type, db_name, schema_name, object_name, index_name;
*/

--SELECT * FROM #WaitResources
--DROP TABLE #WaitResources;

Script di partizionamento hash

L'uso di questo script è descritto in Usare il partizionamento hash con una colonna calcolata e deve essere personalizzato in base alla propria implementazione.

--Create the partition scheme and function, align this to the number of CPU cores 1:1 up to 32 core computer
-- so for below this is aligned to 16 core system
CREATE PARTITION FUNCTION [pf_hash16](TINYINT)
    AS RANGE LEFT
    FOR VALUES (0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15);

CREATE PARTITION SCHEME [ps_hash16]
    AS PARTITION [pf_hash16]
    ALL TO ([ALL_DATA]);
-- Add the computed column to the existing table (this is an OFFLINE operation)

-- Consider using bulk loading techniques to speed it up
ALTER TABLE [dbo].[latch_contention_table]
    ADD [HashValue] AS (CONVERT (TINYINT, ABS(BINARY_CHECKSUM([hash_col]) % (16)), (0))) PERSISTED NOT NULL;

--Create the index on the new partitioning scheme
CREATE UNIQUE CLUSTERED INDEX [IX_Transaction_ID]
    ON [dbo].[latch_contention_table]([T_ID] ASC, [HashValue])
    ON ps_hash16 (HashValue);

Contenuti correlati

  • Last updated on