Skip to content

LOB_DATA, scansioni lente delle tabelle e alcune domande sull'I/O

Abbiamo bisogno del tuo aiuto per diffondere i nostri articoli sull'informatica.

Soluzione:

La presenza del campo XML fa sì che la maggior parte dei dati della tabella si trovi nelle pagine LOB_DATA (in effetti ~90% delle pagine della tabella sono LOB_DATA).

La semplice presenza della colonna XML nella tabella non ha questo effetto. È la presenza di XML dati che, in determinate condizioni fa sì che una parte dei dati di una riga venga memorizzata fuori dalla riga, nelle pagine LOB_DATA. E mentre uno (o forse molti 😉 potrebbero obiettare che duh, il XML implica che ci saranno effettivamente dei dati XML, non è garantito che i dati XML debbano essere memorizzati fuori dalla riga: a meno che la riga non sia praticamente già piena di dati XML, piccoli documenti (fino a 8000 byte) potrebbero entrare nella riga e non andare mai in una pagina LOB_DATA.

Ho ragione di pensare che le pagine LOB_DATA possano causare scansioni lente non solo a causa delle loro dimensioni, ma anche perché SQL Server non può eseguire una scansione efficace dell'indice clusterizzato quando ci sono molte pagine LOB_DATA nella tabella?

La scansione si riferisce all'esame di tutte le righe. Naturalmente, quando viene letta una pagina di dati, tutte le righe di LOB_DATA vengono lette. riga anche se è stato selezionato un sottoinsieme di colonne. La differenza con i dati LOB è che se non si seleziona quella colonna, i dati fuori riga non vengono letti. Pertanto, non è corretto trarre conclusioni sull'efficienza con cui SQL Server può eseguire la scansione di questo indice cluster, poiché non è stato eseguito un test preciso (o ne è stato eseguito solo una metà). Sono state selezionate tutte le colonne, compresa la colonna XML e, come si è detto, è lì che si trova la maggior parte dei dati.

Quindi sappiamo già che la colonna SELECT TOP 1000 * non si limitava a leggere una serie di pagine di dati da 8k, tutte in fila, ma saltava ad altre posizioni per ogni riga. La struttura esatta dei dati LOB può variare in base alla loro dimensione. In base alla ricerca illustrata qui ( What is the Size of the LOB Pointer for (MAX) Types Like Varchar, Varbinary, Etc? ), esistono due tipi di allocazioni LOB fuori riga:

  1. Radice in linea -- per dati compresi tra 8001 e 40.000 (in realtà 42.000) byte, spazio permettendo, ci saranno da 1 a 5 puntatori (24-72 byte) IN FILA che puntano direttamente alla pagina o alle pagine LOB.
  2. TEXT_TREE -- per dati superiori a 42.000 byte, o se i puntatori da 1 a 5 non possono essere inseriti nelle righe, ci sarà solo un puntatore a 24 byte alla pagina iniziale di un elenco di puntatori alle pagine LOB (cioè la pagina "text_tree").

Una di queste due situazioni si verifica ogni volta che si recupera dati LOB che superano gli 8000 byte o che non si adattano alle righe. Ho pubblicato uno script di test su PasteBin.com (script T-SQL per testare le allocazioni e le letture LOB) che mostra i 3 tipi di allocazioni LOB (in base alla dimensione dei dati) e l'effetto di ciascuna di esse sulle letture logiche e fisiche. Nel vostro caso, se i dati XML sono davvero inferiori a 42.000 byte per riga, allora nessuno di essi (o molto poco) dovrebbe essere nella struttura TEXT_TREE meno efficiente.

Se si desidera verificare la velocità con cui SQL Server può eseguire la scansione dell'indice cluster, eseguire il comando SELECT TOP 1000 ma specificando una o più colonne non compresa la colonna XML. Come influisce sui risultati? Dovrebbe essere un po' più veloce.

è considerato ragionevole avere una tale struttura di tabelle/schema di dati?

Dato che abbiamo una descrizione incompleta dell'effettiva struttura delle tabelle e dello schema dei dati, qualsiasi risposta potrebbe non essere ottimale, a seconda di quali siano i dettagli mancanti. Tenendo presente questo, direi che non c'è nulla di evidentemente irragionevole nella struttura delle tabelle o nello schema dei dati.

Posso (in un'applicazione c#) comprimere l'XML da 20KB a ~2,5KB e memorizzarlo in una colonna VARBINARY, impedendo l'uso di pagine di dati LOB. Questo accelera le SELECT di 20 volte nei miei test.

Questo ha reso possibile la selezione di tutte le colonne, o anche solo dei dati XML (ora in VARBINARY) più veloce, ma in realtà danneggia le query che non selezionano i dati "XML". Supponendo di avere circa 50 byte nelle altre colonne e di avere un file FILLFACTOR di 100, allora:

  • Nessuna compressione: 15k di XML dovrebbero richiedere 2 pagine LOB_DATA, che quindi richiedono 2 puntatori per la radice in linea. Il primo puntatore è di 24 byte e il secondo di 12, per un totale di 36 byte memorizzati nella riga per i dati XML. La dimensione totale delle righe è di 86 byte e circa 93 di queste righe possono essere inserite in una pagina di dati da 8060 byte. Pertanto, 1 milione di righe richiede 10.753 pagine di dati.

  • Compressione personalizzata: 2,5k di VARBINARY dati si inseriscono nelle righe. La dimensione totale della riga è di 2610 (2,5 * 1024 = 2560) byte e si possono inserire solo 3 di queste righe in una pagina dati da 8060 byte. Pertanto, 1 milione di righe richiede 333.334 pagine di dati.

Ergo, l'implementazione di una compressione personalizzata si traduce in una riduzione del 30x aumento in pagine di dati per l'indice cluster. Ciò significa che tutte le query che utilizzano una scansione dell'indice cluster hanno ora circa 322.500 pagine di dati. di più pagine di dati da leggere. Si veda la sezione dettagliata di seguito per ulteriori ramificazioni di questo tipo di compressione.

Vi sconsiglio di fare qualsiasi refactoring basato sulle prestazioni di SELECT TOP 1000 *. È improbabile che l'applicazione esegua questa query e non dovrebbe essere usata come unica base per ottimizzazioni potenzialmente inutili.

Per informazioni più dettagliate e per altri test da provare, si veda la sezione sottostante.


Non è possibile dare una risposta definitiva a questa domanda, ma possiamo almeno fare qualche passo avanti e suggerire ulteriori ricerche per avvicinarci alla soluzione del problema (idealmente basate su prove).

Cosa sappiamo:

  1. La tabella ha circa 1 milione di righe
  2. La dimensione della tabella è di circa 15 GB
  3. La tabella contiene un XML e diverse altre colonne di tipo: INT, BIGINT, UNIQUEIDENTIFIER, "etc"
  4. XML la colonna "dimensione" è, in media circa 15k
  5. Dopo aver corso DBCC DROPCLEANBUFFERSsono necessari 20-25 secondi per completare la seguente query: SELECT TOP 1000 * FROM TABLE
  6. L'indice cluster è in fase di scansione
  7. La frammentazione dell'indice cluster è prossima allo 0%.

Cosa pensiamo di sapere:

  1. Nessun'altra attività su disco al di fuori di queste query. Ne siete sicuri? Anche se non ci sono altre query utente, sono in corso operazioni in background? Ci sono processi esterni a SQL Server in esecuzione sulla stessa macchina che potrebbero occupare parte dell'IO? Potrebbe non esserci, ma non è chiaro sulla base delle sole informazioni fornite.
  2. Vengono restituiti 15 MB di dati XML. Su cosa si basa questo numero? Una stima derivata dalle 1000 righe per la media di 15k di dati XML per riga? O un'aggregazione programmatica di ciò che è stato ricevuto per quella query? Se si tratta solo di una stima, non ci farei affidamento perché la distribuzione dei dati XML potrebbe non essere uniforme come implica una semplice media.
  3. La compressione XML potrebbe essere utile. Come si effettua esattamente la compressione in .NET? Tramite le classi GZipStream o DeflateStream? Non è un'opzione a costo zero. Sicuramente comprimerà alcuni dati di una percentuale elevata, ma richiederà anche più CPU, poiché sarà necessario un processo aggiuntivo per comprimere/decomprimere i dati ogni volta. Questo piano eliminerebbe inoltre completamente la possibilità di:

    • interrogare i dati XML tramite il metodo .nodes, .value, .querye .modify Funzioni XML.
    • indicizza i dati XML.

      Tenere presente (dato che si è detto che XML è "altamente ridondante") che la funzione XML è già ottimizzato in quanto memorizza i nomi degli elementi e degli attributi in un dizionario, assegnando un ID indice intero a ogni elemento e usando poi quell'ID intero in tutto il documento (quindi non ripete il nome completo per ogni utilizzo, né lo ripete come tag di chiusura per gli elementi). I dati effettivi vengono anche rimossi dagli spazi bianchi estranei. Questo è il motivo per cui i documenti XML estratti non mantengono la loro struttura originale e perché gli elementi vuoti vengono estratti come anche se sono stati inseriti come . Quindi qualsiasi guadagno derivante dalla compressione tramite GZip (o qualsiasi altra cosa) si otterrà solo comprimendo i valori degli elementi e/o degli attributi, il che rappresenta una superficie molto più piccola che potrebbe essere migliorata rispetto a quanto ci si aspetterebbe, e molto probabilmente non vale la perdita di capacità come notato direttamente sopra.

      Si tenga inoltre presente che la compressione dei dati XML e la memorizzazione dei valori di VARBINARY(MAX) non eliminerà l'accesso al LOB, ma lo ridurrà soltanto. A seconda della dimensione del resto dei dati sulla riga, il valore compresso potrebbe essere inserito nella riga o potrebbe comunque richiedere pagine LOB.

Queste informazioni, pur essendo utili, non sono sufficienti. Ci sono molti fattori che influenzano le prestazioni delle query, quindi abbiamo bisogno di un quadro molto più dettagliato di ciò che sta accadendo.

Quello che non sappiamo, ma che dobbiamo sapere:

  1. Perché le prestazioni di SELECT * è importante? È uno schema che utilizzate nel codice. Se sì, perché?
  2. Quali sono le prestazioni della selezione della sola colonna XML? Quali sono le statistiche e le tempistiche se lo fate: SELECT TOP 1000 XmlColumn FROM TABLE; ?
  3. Quanto dei 20-25 secondi necessari per restituire queste 1000 righe è legato a fattori di rete (far passare i dati attraverso il cavo) e quanto è legato a fattori client (rendere quei circa 15 MB più il resto dei dati non XML nella griglia in SSMS, o eventualmente salvare su disco)?

    A volte è possibile escludere questi due aspetti dell'operazione semplicemente non restituendo i dati. Ora, si potrebbe pensare di selezionare in una tabella temporanea o in una variabile di tabella, ma questo introdurrebbe solo alcune nuove variabili (ad esempio, l'I/O su disco per il salvataggio dei dati). tempdb, scritture del Transaction Log, possibile crescita automatica dei dati di tempdb e/o del file di log, necessità di spazio nel Buffer Pool, ecc.) Tutti questi nuovi fattori possono effettivamente aumentare il tempo di interrogazione. Invece, io tipicamente memorizzare le colonne in variabili (del tipo di dati appropriato).e; non SQL_VARIANT) che vengono sovrascritte a ogni nuova riga (per esempio, il metodo SELECT @Column1 = tab.Column1,...).

    COME come è stato sottolineato da @PaulWhite in questo DBA.StackExchange Q & A, le letture logiche differiscono quando si accede agli stessi dati LOB, con ulteriori ricerche da me effettuate su PasteBin (script T-SQL per testare vari scenari per le letture LOB), i LOB non vengono acceduti in modo coerente tra SELECT, SELECT INTO, SELECT @XmlVariable = XmlColumn, SELECT @XmlVariable = XmlColumn.query(N'/')e SELECT @NVarCharVariable = CONVERT(NVARCHAR(MAX), XmlColumn). Le opzioni sono quindi un po' più limitate, ma ecco cosa si può fare:

    1. Escludere i problemi di rete eseguendo la query sul server che esegue SQL Server, in SSMS o SQLCMD.EXE.
    2. Per escludere i problemi del client in SSMS, andare in Opzioni query -> Risultati -> Griglia e selezionare l'opzione "Scarta i risultati dopo l'esecuzione". Si noti che questa opzione impedirà l'esecuzione di TUTTO l'output, compresi i messaggi, ma può comunque essere utile per escludere il tempo necessario a SSMS per allocare la memoria per ogni riga e poi disegnarla nella griglia.
      In alternativa, si può eseguire la query tramite SQLCMD.EXE e indirizzare l'output verso il nulla tramite: -o NUL:.
  4. Esiste un tipo di attesa associato a questa query? Se sì, qual è il tipo di attesa?
  5. Qual è l'intervallo di tempo effettivo dimensione dei dati per il file XML colonne che viene restituito? La dimensione media di quella colonna nell'intera tabella non ha molta importanza se le righe "TOP 1000" contengono una porzione sproporzionatamente grande del totale. XML dei dati totali. Se si vogliono conoscere le TOP 1000 righe, allora si guardano quelle righe. Eseguire quanto segue:

    SELECT TOP 1000 tab.*,
           SUM(DATALENGTH(tab.XmlColumn)) / 1024.0 AS [TotalXmlKBytes],
           AVG(DATALENGTH(tab.XmlColumn)) / 1024.0 AS [AverageXmlKBytes]
           STDEV(DATALENGTH(tab.XmlColumn)) / 1024.0 AS [StandardDeviationForXmlKBytes]
    FROM   SchemaName.TableName tab;
    
  6. Il esatto schema della tabella. Fornire il completoCREATE TABLE comprensiva di tutti gli indici.
  7. Piano di query? È qualcosa che si può pubblicare? Probabilmente questa informazione non cambierà nulla, ma è meglio sapere che non cambierà piuttosto che indovinare e sbagliare 😉
  8. C'è frammentazione fisica/esterna sul file di dati? Anche se in questo caso potrebbe non essere un fattore importante, dal momento che si sta utilizzando un "SATA di livello consumer" e non un SSD o un SATA supereconomico, l'effetto di settori ordinati in modo non ottimale sarà più evidente, soprattutto con l'aumento del numero di settori da leggere.
  9. Cosa sono i esatto risultati della seguente query:

    SELECT * FROM sys.dm_db_index_physical_stats(DB_ID(),
                              OBJECT_ID(N'dbo.SchemaName.TableName'), 1, 0, N'LIMITED');
    

AGGIORNAMENTO

Mi è venuto in mente di provare a riprodurre questo scenario per vedere se si verifica un comportamento simile. Ho quindi creato una tabella con diverse colonne (simile alla descrizione vaga nella domanda) e poi l'ho popolata con 1 milione di righe, e la colonna XML ha circa 15k di dati per riga (vedere il codice qui sotto).

Ho scoperto che facendo un SELECT TOP 1000 * FROM TABLE è stata completata in 8 secondi la prima volta, e in 2 - 4 secondi ogni volta successiva (sì, l'esecuzione di DBCC DROPCLEANBUFFERS prima di ogni esecuzione del metodo SELECT * ). E il mio portatile di diversi anni è non veloce: SQL Server 2012 SP2 Developer Edition, 64 bit, 6 GB di RAM, doppio Core i5 da 2,5 Ghz e un'unità SATA da 5400 RPM. Sto anche eseguendo SSMS 2014, SQL Server Express 2014, Chrome e diverse altre cose.

In base al tempo di risposta del mio sistema, ripeto che abbiamo bisogno di ulteriori informazioni (ad esempio specifiche sulla tabella e sui dati, risultati dei test suggeriti e così via) per aiutare a restringere la causa del tempo di risposta di 20-25 secondi che state vedendo.

SET ANSI_NULLS, NOCOUNT ON;
GO

IF (OBJECT_ID(N'dbo.XmlReadTest') IS NOT NULL)
BEGIN
    PRINT N'Dropping table...';
    DROP TABLE dbo.XmlReadTest;
END;

PRINT N'Creating table...';
CREATE TABLE dbo.XmlReadTest 
(
    ID INT NOT NULL IDENTITY(1, 1),
    Col2 BIGINT,
    Col3 UNIQUEIDENTIFIER,
    Col4 DATETIME,
    Col5 XML,
    CONSTRAINT [PK_XmlReadTest] PRIMARY KEY CLUSTERED ([ID])
);
GO

DECLARE @MaxSets INT = 1000,
        @CurrentSet INT = 1;

WHILE (@CurrentSet <= @MaxSets)
BEGIN
    RAISERROR(N'Populating data (1000 sets of 1000 rows); Set # %d ...',
              10, 1, @CurrentSet) WITH NOWAIT;
    INSERT INTO dbo.XmlReadTest (Col2, Col3, Col4, Col5)
        SELECT  TOP 1000
                CONVERT(BIGINT, CRYPT_GEN_RANDOM(8)),
                NEWID(),
                GETDATE(),
                N''
                  + REPLICATE(CONVERT(NVARCHAR(MAX), CRYPT_GEN_RANDOM(1), 2), 3750)
                  + N''
        FROM        [master].[sys].all_columns sac1;

    IF ((@CurrentSet % 100) = 0)
    BEGIN
        RAISERROR(N'Executing CHECKPOINT ...', 10, 1) WITH NOWAIT;
        CHECKPOINT;
    END;

    SET @CurrentSet += 1;
END;

--

SELECT COUNT(*) FROM dbo.XmlReadTest; -- Verify that we have 1 million rows

-- O.P. states that the "clustered index fragmentation is close to 0%"
ALTER INDEX [PK_XmlReadTest] ON dbo.XmlReadTest REBUILD WITH (FILLFACTOR = 90);
CHECKPOINT;

--

DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;

SET STATISTICS IO, TIME ON;
SELECT TOP 1000 * FROM dbo.XmlReadTest;
SET STATISTICS IO, TIME OFF;

/*
Scan count 1, logical reads 21,       physical reads 1,     read-ahead reads 4436,
              lob logical reads 5676, lob physical reads 1, lob read-ahead reads 3967.

 SQL Server Execution Times:
   CPU time = 171 ms,  elapsed time = 8329 ms.
*/

Inoltre, poiché vogliamo escludere il tempo necessario per leggere le pagine non-LOB, ho eseguito la seguente query per selezionare tutte le colonne tranne quella XML (uno dei test suggeriti sopra). Questa restituisce in 1,5 secondi in modo abbastanza costante.

DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;

SET STATISTICS IO, TIME ON;
SELECT TOP 1000 ID, Col2, Col3, Col4 FROM dbo.XmlReadTest;
SET STATISTICS IO, TIME OFF;

/*
Scan count 1, logical reads 21,    physical reads 1,     read-ahead reads 4436,
              lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1666 ms.
*/

Conclusione (per il momento)
Sulla base del mio tentativo di ricreare il vostro scenario, non credo che si possa indicare l'unità SATA o l'I/O non sequenziale come la causa principale dei 20-25 secondi, soprattutto perché non sappiamo ancora a che velocità viene restituita la query quando non include la colonna XML. Inoltre, non sono stato in grado di riprodurre il numero elevato di letture logiche (non-LOB) che state mostrando, ma ho la sensazione di dover aggiungere più dati a ogni riga alla luce di questo fatto. e la dichiarazione di:

~90% delle pagine di tabella sono LOB_DATA

La mia tabella ha 1 milione di righe, ognuna delle quali contiene poco più di 15k di dati XML e sys.dm_db_index_physical_stats mostra che ci sono 2 milioni di pagine LOB_DATA. Il restante 10% sarebbe quindi costituito da 222k pagine di dati IN_ROW, ma ne ho solo 11.630. Quindi, ancora una volta, abbiamo bisogno di maggiori informazioni sullo schema della tabella e sui dati effettivi.

Ho ragione di pensare che le pagine LOB_DATA possano causare scansioni lente non solo per le loro dimensioni, ma anche perché SQL Server non può scansionare
non solo a causa delle loro dimensioni, ma anche perché SQL Server non può eseguire la scansione dell'indice
indice clusterizzato in modo efficace

Sì, la lettura di dati LOB non memorizzati nelle righe comporta un IO casuale invece di un IO sequenziale. La metrica delle prestazioni del disco da utilizzare per capire perché è veloce o lenta è Random Read IOPS.

I dati LOB sono memorizzati in una struttura ad albero in cui la pagina di dati nell'indice clusterizzato punta a una pagina di dati LOB con una struttura radice LOB che a sua volta punta ai dati LOB effettivi. Quando si attraversano i nodi radice dell'indice clusterizzato, SQL Server può ottenere i dati all'interno delle righe solo tramite letture sequenziali. Per ottenere i dati LOB, SQL Server deve andare da qualche altra parte sul disco.

Credo che se si passasse a un disco SSD non si soffrirebbe molto di questo problema, dato che gli IOPS casuali di un SSD sono molto più alti di quelli di un disco rotante.

è considerato ragionevole avere una tale struttura di tabelle/dati
dati?

Sì, potrebbe esserlo. Dipende da cosa fa questa tabella per voi.

Di solito i problemi di prestazioni con XML in SQL Server si verificano quando si desidera utilizzare T-SQL per eseguire query nell'XML e ancora di più quando si desidera utilizzare i valori dell'XML in un predicato in una clausola where o in un join. In questo caso, si potrebbe valutare la promozione delle proprietà o gli indici XML selettivi o una riprogettazione delle strutture delle tabelle, che invece riducono l'XML in tabelle.

Ho provato la compressione

L'ho fatto una volta in un prodotto un po' più di 10 anni fa e da allora me ne sono pentito. Mi è mancato molto non poter lavorare con i dati usando T-SQL, quindi non lo consiglierei a nessuno se si può evitare.

Se hai delle riserve e la possibilità di aumentare la nostra cronaca, ti invitiamo a lasciare una recensione e la esamineremo volentieri.



Utilizzate il nostro motore di ricerca

Ricerca
Generic filters

Lascia un commento

Il tuo indirizzo email non sarà pubblicato.