Elimina performance per i dati LOB in SQL Server

Questa domanda è legata a questo thread del forum .

Esegue SQL Server 2008 Developer Edition sulla mia workstation e un cluster di macchine virtuali a due nodes Enterprise Edition where faccio riferimento a "cluster di alfa".

  • Non è ansible aggiungere un membro a un gruppo locale perché il membro ha il tipo di account errato
  • Dovrebbero essere sempre utilizzati i ruoli per l'applicazione delle autorizzazioni di SQL Server?
  • Perché le pagine di SQL Server 8 KB?
  • Aiuto! Il file di log per il database 'tempdb' è pieno. Eseguire il backup del registro delle transactions per il database per liberare uno spazio di registro
  • il sistema locale e NT AUTHORITY \ SYSTEM lo stesso?
  • Ottenere dati da un database di Sospetto
  • Il tempo necessario per eliminare righe con una colonna varianale (max) è direttamente correlato alla lunghezza dei dati in quella colonna. Questo potrebbe sembrare intuitivo in un primo momento, ma dopo l'indagine, si scontra con la mia comprensione di come SQL Server effettivamente elimina le righe in generale e si occupa di questo tipo di dati.

    Il problema deriva da un problema di eliminazione del timeout (> 30 secondi) che vediamo nell'applicazione web .NET, ma lo ho semplificato per questa discussione.

    Quando un record viene eliminato, SQL Server lo contrassegna come fantasma per essere ripulito da un'attività di pulizia di Ghost in un momento successivo alla commessa (vedi blog di Paul Randal ). In un test che elimina tre righe con 16 KB, 4 MB e 50 MB di dati in una colonna varbinaria (max), rispondo rispettivamente, vedo questo accadere nella pagina con la porzione in row dei dati, nonché nella transazione log.

    Quello che mi sembra strano è che le serrature X siano poste su tutte le pagine dei dati LOB durante l'eliminazione e le pagine vengono deallocate nel PFS. Lo vedo nel registro delle transactions, così come con sp_lock ei risultati del dm_db_index_operational_stats page_lock_count ( page_lock_count ).

    Ciò crea un collo di bottiglia I / O sulla workstation e il nostro cluster alfa se queste pagine non sono già nella cache di buffer. Infatti, la page_io_latch_wait_in_ms dello stesso DMV è praticamente l'intera durata page_io_latch_wait_count e la page_io_latch_wait_count corrisponde al numero di pagine bloccate. Per il file di 50 MB sulla mia stazione di lavoro, questo si traduce in più di 3 secondi quando inizia con una cache buffer vuota ( checkpoint / dbcc dropcleanbuffers ) e non ho dubbi che sarebbe più lungo per la frammentazione pesante e sotto carico.

    Ho cercato di assicurarmi che non fosse solo l'allocazione dello spazio nella cache che prendeva in quel momento. Ho letto in 2 GB di dati da altre righe prima di eseguire l'eliminazione anziché il metodo del checkpoint , che è più che assegnato al process di SQL Server. Non è sicuro se questo è un test valido o no, poiché non so come SQL Server mescola i dati in giro. Ho pensato che avrebbe sempre spinto fuori il vecchio a favore del nuovo.

    Inoltre, non modifica nemless le pagine. Questo vedo con dm_os_buffer_descriptors . Le pagine sono pulite dopo l'eliminazione, mentre il numero di pagine modificate è inferiore a 20 per tutte e tre le cancellazioni piccole, medie e grandi. Ho anche confrontato l'output di DBCC PAGE per un campionamento delle pagine di ricerca, e non ci sono stati cambiamenti (solo il bit ALLOCATED stato rimosso da PFS). Li distrugge solo.

    Per dimostrare ulteriormente che le ricerche / deallocazioni di pagina stanno causando il problema, ho provato lo stesso test utilizzando una colonna filestream invece di vanilla varbinary (max). Le cancellazioni erano tempo costante, indipendentemente dalla dimensione LOB.

    Quindi, prima le mie domande accademiche:

    1. Perché SQL Server deve cercare tutte le pagine di dati LOB per bloccarle? È solo un dettaglio di come i blocchi sono rappresentati in memory (memorizzati in qualche modo con la pagina)? Ciò rende l'impatto I / O fortemente dipendente dalla dimensione dei dati se non completamente memorizzata nella cache.
    2. Perché X blocca affatto, solo per deallocarli? Non è sufficiente bloccare solo la foglia di indice con la porzione in row, poiché la deallocazione non ha bisogno di modificare le pagine stesse? C'è un altro modo per get i dati LOB che la protezione protegge contro?
    3. Perché deallocare le pagine di fronte a tutti, dato che c'è già un task di background dedicato a questo tipo di lavoro?

    E forse più importnte, la mia domanda pratica:

    • C'è un modo per far funzionare le cancellazioni in modo diverso? Il mio objective è il tempo costante eliminato a prescindere dalla dimensione, simile a filestream, where qualsiasi pulizia avviene in background dopo il fatto. È una cosa di configuration? Sto memorizzando cose stranamente?

    Ecco come riprodurre il test descritto (eseguito tramite la window di query di SSMS):

     CREATE TABLE [T] ( [ID] [uniqueidentifier] NOT NULL PRIMARY KEY, [Data] [varbinary](max) NULL ) DECLARE @SmallID uniqueidentifier DECLARE @MediumID uniqueidentifier DECLARE @LargeID uniqueidentifier SELECT @SmallID = NEWID(), @MediumID = NEWID(), @LargeID = NEWID() -- May want to keep these IDs somewhere so you can use them in the deletes without var declaration INSERT INTO [T] VALUES (@SmallID, CAST(REPLICATE(CAST('a' AS varchar(max)), 16 * 1024) AS varbinary(max))) INSERT INTO [T] VALUES (@MediumID, CAST(REPLICATE(CAST('a' AS varchar(max)), 4 * 1024 * 1024) AS varbinary(max))) INSERT INTO [T] VALUES (@LargeID, CAST(REPLICATE(CAST('a' AS varchar(max)), 50 * 1024 * 1024) AS varbinary(max))) -- Do this before test CHECKPOINT DBCC DROPCLEANBUFFERS BEGIN TRAN -- Do one of these deletes to measure results or profile DELETE FROM [T] WHERE ID = @SmallID DELETE FROM [T] WHERE ID = @MediumID DELETE FROM [T] WHERE ID = @LargeID -- Do this after test ROLLBACK 

    Di seguito sono elencati alcuni risultati della modifica dei cancelli sulla mia workstation:

     |  Tipo di colonna  Elimina dimensione |  Durata (ms) |  Leggi |  Scrive |  CPU |
     -------------------------------------------------- ------------------
     |  VarBinary |  16 KB |  40 |  13 |  2 |  0 |
     |  VarBinary |  4 MB |  952 |  2318 |  2 |  0 |
     |  VarBinary |  50 MB |  2976 |  28594 |  1 |  62 |
     -------------------------------------------------- ------------------
     |  FileStream |  16 KB |  1 |  12 |  1 |  0 |
     |  FileStream |  4 MB |  0 |  9 |  0 |  0 |
     |  FileStream |  50 MB |  1 |  9 |  0 |  0 |
    

    Non possiamo necessariamente utilizzare filestream invece perché:

    1. La nostra distribuzione dei dati non lo garantisce.
    2. In pratica, aggiungiamo dati in molti blocchi e filestream non support aggiornamenti parziali. Ci sarebbe bisogno di progettare intorno a questo.

    Aggiorna 1

    Testato una teoria che i dati vengono scritti nel registro delle transactions come parte dell'eliminazione, e questo non sembra essere il caso. Sto testando per questo errato? Vedi sotto.

     SELECT MAX([Current LSN]) FROM fn_dblog(NULL, NULL) --0000002f:000001d9:0001 BEGIN TRAN DELETE FROM [T] WHERE ID = @ID SELECT SUM( DATALENGTH([RowLog Contents 0]) + DATALENGTH([RowLog Contents 1]) + DATALENGTH([RowLog Contents 3]) + DATALENGTH([RowLog Contents 4]) ) [RowLog Contents Total], SUM( DATALENGTH([Log Record]) ) [Log Record Total] FROM fn_dblog(NULL, NULL) WHERE [Current LSN] > '0000002f:000001d9:0001' 

    Per un file di size superiori a 5 MB, questo restituì 1651 | 171860 1651 | 171860 .

    Inoltre, mi aspetto che le pagine stesse siano sporche se i dati sono stati scritti nel registro. Soltanto le deallocazioni sembrano essere registrate, che corrispondono a ciò che è sporco dopo l'eliminazione.

    Aggiorna 2

    Ho ricevuto una risposta da Paul Randal. Ha affermato che deve leggere tutte le pagine per attraversare l'tree e trovare le pagine da deallocare e affermare che non esiste altro modo per cercare le pagine. Questa è una risposta a metà a 1 e 2 (anche se non spiega la necessità di bloccare i dati fuori-fila, ma che sono piccole patate).

    La domanda 3 è ancora aperta: perché deallocare le pagine in avanti se c'è già un'attività di background per eseguire la pulizia per le cancellazioni?

    E ovviamente, l'interrogazione importnte: C'è un modo per mitigare direttamente (ovvero non funzionare) questo comportmento di eliminazione dipendente dalla dimensione? Penserei che questo sarebbe un problema più comune, a less che non siamo veramente gli unici a memorizzare e cancellare righe da 50 MB in SQL Server? Tutti gli altri là fuori funzionano intorno a questo con qualche forma di lavoro di raccolta dei rifiuti?

  • Errore SQL Server 3415
  • Aggiorna manualmente le statistiche necessarie quando le statistiche di creazione / aggiornamento automatico sono triggerste per un database di SQL Server?
  • Come proteggere SQL Server dagli hacker
  • il sistema locale e NT AUTHORITY \ SYSTEM lo stesso?
  • Identificare gli errori "Timeout di block" in SQL Server 2000
  • Dovrebbero essere sempre utilizzati i ruoli per l'applicazione delle autorizzazioni di SQL Server?
  • One Solution collect form web for “Elimina performance per i dati LOB in SQL Server”

    Non posso dire perché esattamente sarebbe molto più inefficiente eliminare un VARBINARIO (MAX) rispetto al stream di file, ma un'idea che potresti considerare se stai solo cercando di evitare tempi fuori dall'applicazione web quando elimina questi LOBS. Potresti memorizzare i valori VARBINARIO (MAX) in una tabella separata (la lascia call tblLOB) indicata dalla tabella originale (consente di call questo tblParent).

    Da qui quando si elimina un record è ansible eliminarlo dal record padre e quindi avere un process occasionale di raccolta rifiuti per entrare e ripulire i record nella tabella LOB. Durante questo process di raccolta dei rifiuti, potrebbe esserci un'attività aggiuntiva sul disco rigido, ma sarà alless separata dalla networking frontale e può essere eseguita durante i periodi non di punta.

    Suggerimenti per Linux e Windows Server, quali Ubuntu, Centos, Apache, Nginx, Debian e argomenti di rete.