Sintomi
Considerare gli scenari seguenti:
-
Si dispone di una colonna che include valori sequenziali, ad esempio una colonna Identity o una colonna DateTime, che viene inserita tramite la funzione GETDATE () .
-
Si ha un indice cluster che include la colonna sequenziale come colonna iniziale. Nota Lo scenario più comune è una chiave primaria raggruppata in una colonna Identity. Meno frequentemente, questo problema può essere osservato per gli indici non cluster.
-
L'applicazione esegue frequenti operazioni di inserimento o aggiornamento nella tabella.
-
Nel sistema sono presenti molte CPU. In genere, il server ha 16 CPU o più. Questo consente a più sessioni di eseguire contemporaneamente le operazioni di inserimento nella stessa tabella.
In questa situazione, potresti riscontrare un calo delle prestazioni dell'applicazione. Quando si esaminano i tipi di attesa in sys.dm_exec_requests, si osservano le attese per il tipo di attesa PAGELATCH_EX e per molte sessioni in attesa in questo tipo di attesa.
Un altro problema si verifica se si esegue la query di diagnostica seguente nel sistema:
select session_id, wait_type, wait_time, wait_resource from sys.dm_exec_requests
where session_id > 50 and wait_type = 'pagelatch_ex'
In questa situazione, potresti ottenere risultati simili ai seguenti.
session_id wait_type wait_time wait_resource
---------- ---------------------- --------------- ------------------------
60 PAGELATCH_EX 100 5:1:4144
75 PAGELATCH_EX 123 5:1:4144
79 PAGELATCH_EX 401 5:1:4144
80 PAGELATCH_EX 253 5:1:4144
81 PAGELATCH_EX 312 5:1:4144
82 PAGELATCH_EX 355 5:1:4144
84 PAGELATCH_EX 312 5:1:4144
85 PAGELATCH_EX 338 5:1:4144
87 PAGELATCH_EX 405 5:1:4144
88 PAGELATCH_EX 111 5:1:4144
90 PAGELATCH_EX 38 5:1:4144
92 PAGELATCH_EX 115 5:1:4144
94 PAGELATCH_EX 49 5:1:4144
101 PAGELATCH_EX 301 5:1:4144
102 PAGELATCH_EX 45 5:1:4144
103 PAGELATCH_EX 515 5:1:4144
105 PAGELATCH_EX 39 5:1:4144
Si noterà che più sessioni attendono la stessa risorsa simile alla seguente :
database_id = 5, file_id = 1, database page_id = 4144
Nota Il database_id deve essere un database utente (il numero di ID è maggiore o uguale a 5). Se il database_id è 2, è possibile che si verifichi il problema discusso nell'articolo del Blog di MSSQL Tiger Team seguente:
Causa
PAGELATCH (latch in una pagina dati o indice) è un meccanismo di sincronizzazione dei thread. Viene usato per sincronizzare l'accesso fisico a breve termine alle pagine di database che si trovano nella cache buffer.
PAGELATCH differisce da un PAGEIOLATCH. Quest'ultimo viene usato per sincronizzare l'accesso fisico alle pagine quando vengono lette o scritte su disco.
I latch di pagina sono comuni in tutti i sistemi perché garantiscono la protezione delle pagine fisiche. Un indice raggruppato Ordina i dati in base alla colonna chiave iniziale. Per questo motivo, quando crei l'indice in una colonna sequenziale, in questo modo tutti i nuovi inserimenti dati si verificano nella stessa pagina alla fine dell'indice fino a quando tale pagina non viene riempita. Tuttavia, in carico elevato, le operazioni di inserimento simultanee possono causare conflitti nell'ultima pagina dell'albero B. Questo conflitto può verificarsi in indici cluster e non cluster. Il motivo è che l'indice non cluster Ordina le pagine a livello di foglia in base alla chiave iniziale. Questo problema è noto anche come conflitto di inserimento dell'ultima pagina.
Per altre informazioni, vedere il libro bianco seguente:
Risoluzione
Per risolvere questo conflitto, la strategia complessiva consiste nel impedire che tutte le operazioni di inserimento simultaneo accedano alla stessa pagina di database. Fai invece in modo che ogni operazione di inserimento acceda a una pagina diversa e aumenti la concorrenza. Di conseguenza, uno dei metodi seguenti che organizzano i dati in base a una colonna diversa dalla colonna sequenziale raggiunge questo obiettivo.
In SQL Server 2019 è stata aggiunta una nuova opzione di indice (OPTIMIZE_FOR_SEQUENTIAL_KEY) che può aiutare a risolvere il problema senza usare uno dei metodi seguenti. Per altre informazioni, vedere dietro le quinte OPTIMIZE_FOR_SEQUENTIAL_KEY .
Metodo 1
Impostare la colonna che contiene valori sequenziali come indice non cluster e quindi trasferire l'indice cluster in un'altra colonna. Ad esempio, per una chiave primaria in una colonna Identity, Rimuovi la chiave primaria raggruppata e quindi ricreala come chiave primaria non in cluster. Questo è il metodo più semplice da eseguire e raggiunge direttamente l'obiettivo. Si supponga ad esempio di avere la tabella seguente definita usando una chiave primaria raggruppata in una colonna Identity.
CREATE TABLE Customers
(
CustomerID bigint identity(1,1) not null Primary Key CLUSTERED,
CustomerLastName varchar (32) not null,
CustomerFirstName varchar(32) not null
)
Per modificare questa operazione, è possibile rimuovere l'indice della chiave primaria e ridefinirlo.
ALTER TABLE Customers
DROP CONSTRAINT PK__Customer__A4AE64B98819CFF6
ALTER TABLE Customers
add constraint pk_Cust1
primary key NONCLUSTERED (CustomerID)
Metodo 2
Riordinare la definizione di indice raggruppato in modo che la colonna iniziale non sia la colonna sequenziale. Questo richiede che l'indice cluster sia un indice composto. In una tabella Customer, ad esempio, è possibile impostare una colonna CustomerLastName come colonna iniziale, seguita dal CustomerID. Ti consigliamo di testare a fondo questo metodo per verificare che soddisfi i requisiti di prestazioni.
ALTER TABLE Customers
add constraint pk_Cust1
primary key clustered (CustomerLastName, CustomerID)
Metodo 3
Aggiungere un valore hash non sequenziale come chiave di indice iniziale. Verranno inoltre distribuiti gli inserimenti. Un valore hash può essere generato come modulo che corrisponde al numero di CPU nel sistema. Ad esempio, in un sistema a 16 CPU puoi usare un modulo di 16. Questo metodo distribuisce le operazioni di inserimento in modo uniforme in più pagine di database.
CREATE TABLE Customers
(
CustomerID bigint identity(1,1) not null,
CustomerLastName varchar (32) not null,
CustomerFirstName varchar(32) not null
)
go
ALTER TABLE Customers
ADD [HashValue] AS (CONVERT([tinyint], abs([CustomerID])%16)) PERSISTED NOT NULL
go
ALTER TABLE Customers
ADD CONSTRAINT pk_table1
PRIMARY KEY CLUSTERED (HashValue, CustomerID)
Metodo 4
Usare un GUID come colonna chiave iniziale di un indice per garantire la distribuzione uniforme degli inserimenti.
Nota Anche se raggiunge l'obiettivo, non è consigliabile questo metodo perché presenta più sfide, tra cui una chiave di indice di grandi dimensioni, frequenti divisioni di pagina, bassa densità di pagina e così via.
Metodo 5
Usa il partizionamento delle tabelle e una colonna calcolata con un valore hash per diffondere le operazioni di inserimento. Poiché questo metodo usa il partizionamento delle tabelle, è utilizzabile solo nelle edizioni Enterprise di SQL Server. Nota è possibile usare tabelle partizionate in SQL Server 2016 SP1 Standard Edition. Per altre informazioni, vedere la descrizione di "Partizionamento delle tabelle e degli indici nell'articolo edizioni e funzionalità supportate di SQL Server 2016. Di seguito è riportato un esempio in un sistema con 16 CPU.
CREATE TABLE Customers
(
CustomerID bigint identity(1,1) not null,
CustomerLastName varchar (32) not null,
CustomerFirstName varchar(32) not null
)
go
ALTER TABLE Customers
ADD [HashID] AS CONVERT(tinyint, ABS(CustomerID % 16)) PERSISTED NOT NULL)
go
CREATE PARTITION FUNCTION pf_hash (tinyint) AS RANGE LEFT FOR VALUES (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15)
GO
CREATE PARTITION SCHEME ps_hash AS PARTITION pf_hash ALL TO ([PRIMARY])
GO
ALTER TABLE Customers
DROP CONSTRAINT PK__Customer__A4AE64B98819CFF6
CREATE UNIQUE CLUSTERED INDEX CIX_Hash
ON Customers (CustomerID, HashID) ON ps_hash(HashID)
GO
Metodo 6
In alternativa, usare un OLTP in memoria in particolare se il conflitto di latch è molto elevato. Questa tecnologia elimina il conflitto di latch in generale. È tuttavia necessario riprogettare e eseguire la migrazione della tabella o delle tabelle specifiche in cui il conflitto di latch di pagina viene osservato in una tabella OLTP in memoria. Per determinare se è possibile la migrazione e lo sforzo necessario per eseguire la migrazione, è possibile usare il report ottimizzazione della memoria e analisi delle prestazioni delle transazioni . Per altre informazioni su come in memoria OLTP Elimina il conflitto di latch, scaricare e rivedere il documento in OLTP in memoria: modelli di carico di lavoro comuni e considerazioni sulla migrazione.
