none
IMPLICAZIONI NELL'USO DI SNAPSHOT ISOLATION ED RCSI RRS feed

  • Domanda

  • Ciao a tutti,

    in un'ottica di migrazione di SQL Server ad una concorrenza"ottimistica" utilizzando READ COMMITTED SNAPSHOT ISOLATION, volevo iniziare ad implementarla impostando il database con ALLOW_SNAPSHOT_ISOLATION LEVEL ed inserire nella singola transazione se usarla.

    Ho letto però che quest'ultima soluzione rispetto alla prima è vulnerabile a conflitti di update: https://technet.microsoft.com/en-us/library/ms188277%28v=sql.105%29.aspx

    In realtà però se ho ben capito il conflitto si verifica solo se una delle due transazioni utilizza il  metodo pessimisitico di SQL Server che è il classico "read commited", mentre se entrambe le transazioni utilizzassero lo snapshot isolation non dovrebbe verificarsi il problema , giusto?

    Ho già letto anche le implicazioni sopratutto a livello di prestazioni che si possono avere adottanto questo tipo di soluzioni ma mi piacerebbe sapere da chi le ha già implementate se ci sono particolari consigli o controindicazioni, mi sembra di capire che è molto importante valutare le dimensioni della TEMPDB che dovrà contenere il versioning delle tabelle soggette allo snapshot ma non ho ben capito quanta RAM è veramente necessaria per poter contenere il tutto, tempdb+database?

    grazie mille per il vostro aiuto!

    ciao

    venerdì 1 gennaio 2016 15:45

Risposte

  • In questo caso come suggerisci di procedere? dopo un lungo periodo di uptime del server vanno disabilitati indiscrimanatamente tutti gli indici mai utilizzati in seek e/o scan? In pratica con quale criterio decidi se mantenere o meno l'indice?

    Ciao,

    per determinare gli Indici non utilizzati, i cosiddetti "bad index", ovvero gli Indici Non-Cluster su cui si registrano molte più scritture (Update) che letture (Seek + Scan + Lookup), potresti utilizzare la seguente query di Glenn Berry:

    SELECT OBJECT_NAME(s.[object_id]) AS [Table Name], i.name AS [Index Name], i.index_id, 
    i.is_disabled, i.is_hypothetical, i.has_filter, i.fill_factor,
    user_updates AS [Total Writes], user_seeks + user_scans + user_lookups AS [Total Reads],
    user_updates - (user_seeks + user_scans + user_lookups) AS [Difference]
    FROM sys.dm_db_index_usage_stats AS s WITH (NOLOCK)
    INNER JOIN sys.indexes AS i WITH (NOLOCK)
    ON s.[object_id] = i.[object_id]
    AND i.index_id = s.index_id
    WHERE OBJECTPROPERTY(s.[object_id],'IsUserTable') = 1
    AND s.database_id = DB_ID()
    AND user_updates > (user_seeks + user_scans + user_lookups)
    AND i.index_id > 1
    ORDER BY [Difference] DESC, [Total Writes] DESC, [Total Reads] ASC OPTION (RECOMPILE);

    Ti consiglio di eseguirla con la cache "calda" quando sei ragionevolmente sicuro di avere in cache le query tipiche del tuo workload (se puoi, lontano la procedure di ricalcolo e attività speciali).

    La query restituirà l'elenco dei possibili bad index, potrai quindi individuare gli indici da eliminare, i candidati sono quelli che hanno un numero di Seek + Scan + Lookup prossimo allo zero e molti Update.

    Tieni in considerazione anche questo aspetto: spesso si sceglie di creare un indice a supporto di ogni Foreign Key, per migliorare le performance delle operazioni di Join. Questa è una best practices vera, però se l'indice Non-Cluster viene creato su una colonna che referenzia una tabella (collegata) avente 50 o anche 200 record, è possibile che l'indice non venga mai utilizzato neppure durante le operazioni di Join. Ti consiglio di controllare anche queste situazioni.. a mio avviso, questi sono i primi indici da eliminare.. ma li troverai anche estratti dalla query :)

    Ciao


    Sergio Govoni

    SQL Server MVP
    MVP Profile | English Blog | Twitter | LinkedIn

    venerdì 8 gennaio 2016 22:30
    Moderatore
  • non ho capito il perchè dei molti update .., perchè dovrei disabilitare degli indici che sono utilizzati tanto in update?

    Queste sono le prime righe che mi ha identificato, significa che dovrei disabilitare tutte le righe con READS a zero e poche unità?:

    Prime righe

    Ciao,

    dimentichiamoci un attimo del DB :) pensa all'elenco telefonico della provincia di Bologna (ad esempio). L'elenco telefonico è ordinato per Comune, che facilita la ricerca degli abbonati. All'interno del Comune, l'ordinamento secondario è per Cognome e Nome dell'abbonato. Supponiamo ora di avere un secondo elenco telefonico, sempre della provincia di Bologna (uguale al primo), ma con ordinamento diverso.. gli abbonati sono ordinati per Altezza in centimetri crescente :)

    Supponi ora di dover inserire un nuovo abbonato in elenco, conosci le sue generalità e l'altezza.. dovrai scrivere (= update) il nuovo abbonato sia nel primo elenco ordinato per Comune -> Cognome -> Nome, che nel secondo elenco ordinato per Altezza.

    Ricevi altre richieste di abbonamento e devi effettuare altre scritture e aggiornamenti dei due elenchi, potresti anche dover aggiungere o spostare pagine in funzione dello spazio disponibile e delle generalità degli abbonati.

    Ora supponi di avere anche un Call Center che fornisce i numeri di telefono degli abbonati a chi lo desidera.. supponi che le richieste (query) avvengano molto spesso per Comune, Cognome e Nome e quasi mai per Altezza.

    L'elenco telefonico (indice) ordinato per Altezza può essere eliminato perchè rappresenta solo un costo di manutenzione (= Total Writes) senza fornire benefici (= Total Reads).

    Tornando al risultato della query, gli indici con Total Writes elevati e Total Reads uguali a zero o poche unità sono molto probabilmente da eliminare perchè rappresentano solo un costo per l'istanza.

    mentre qui sotto ci sono le ultimissime righe trovate, anche in questo caso le letture (quindi Seek + Scan + Lookup) sono quasi tutte a zero, potrei disabilitare anche questi?:

    Ultime righe

    Questi indici non rappresentano un costo (con il carico di lavoro che ha prodotto questi risultati), ma non danno neppure benefici.. occupano solo spazio su disco :) se il carico di lavoro è reale potrebbero essere eliminati.

    Ciao


    Sergio Govoni

    SQL Server MVP
    MVP Profile | English Blog | Twitter | LinkedIn


    sabato 9 gennaio 2016 18:27
    Moderatore
  • In un'ottica di migrazione di SQL Server ad una concorrenza"ottimistica" utilizzando READ COMMITTED SNAPSHOT ISOLATION, volevo iniziare ad implementarla impostando il database con ALLOW_SNAPSHOT_ISOLATION LEVEL ed inserire nella singola transazione se usarla.

    Ho letto però che quest'ultima soluzione rispetto alla prima è vulnerabile a conflitti di update: https://technet.microsoft.com/en-us/library/ms188277%28v=sql.105%29.aspx

    In realtà però se ho ben capito il conflitto si verifica solo se una delle due transazioni utilizza il  metodo pessimisitico di SQL Server che è il classico "read commited", mentre se entrambe le transazioni utilizzassero lo snapshot isolation non dovrebbe verificarsi il problema , giusto?

    Il livello di isolamento "Read Committed Isolation" che utilizza l'opzione "row versioning" è raccomandato, rispetto al livello di isolamento "Snapshot" per le ragioni che hai letto in nel link tra cui c'è anche la miglior gestione dei conflitti nelle operazioni di UPDATE.

    Ho già letto anche le implicazioni sopratutto a livello di prestazioni che si possono avere adottanto questo tipo di soluzioni ma mi piacerebbe sapere da chi le ha già implementate se ci sono particolari consigli o controindicazioni, mi sembra di capire che è molto importante valutare le dimensioni della TEMPDB che dovrà contenere il versioning delle tabelle soggette allo snapshot ma non ho ben capito quanta RAM è veramente necessaria per poter contenere il tutto, tempdb+database?

    Non sono un fan di questi livelli di isolamento :) preferisco il default "Read Committed" :) Per quanto riguarda l'overhead sul database di sistema tempdb, ti consiglio di dimensionare i file del tempdb in modo da evitare che avvengano operazioni di auto-growth; ti consiglio inoltre di posizionarli nell'unità più performante (questo vale a prescindere dal livello di isolamento, ma utilizzando RCSI con row versioning è una best practices altamente consigliata).

    Qui trovi un po' di informazioni:

    Ciao


    Sergio Govoni

    SQL Server MVP
    MVP Profile | English Blog | Twitter | LinkedIn



    sabato 2 gennaio 2016 17:05
    Moderatore

Tutte le risposte

  • In un'ottica di migrazione di SQL Server ad una concorrenza"ottimistica" utilizzando READ COMMITTED SNAPSHOT ISOLATION, volevo iniziare ad implementarla impostando il database con ALLOW_SNAPSHOT_ISOLATION LEVEL ed inserire nella singola transazione se usarla.

    Ho letto però che quest'ultima soluzione rispetto alla prima è vulnerabile a conflitti di update: https://technet.microsoft.com/en-us/library/ms188277%28v=sql.105%29.aspx

    In realtà però se ho ben capito il conflitto si verifica solo se una delle due transazioni utilizza il  metodo pessimisitico di SQL Server che è il classico "read commited", mentre se entrambe le transazioni utilizzassero lo snapshot isolation non dovrebbe verificarsi il problema , giusto?

    Il livello di isolamento "Read Committed Isolation" che utilizza l'opzione "row versioning" è raccomandato, rispetto al livello di isolamento "Snapshot" per le ragioni che hai letto in nel link tra cui c'è anche la miglior gestione dei conflitti nelle operazioni di UPDATE.

    Ho già letto anche le implicazioni sopratutto a livello di prestazioni che si possono avere adottanto questo tipo di soluzioni ma mi piacerebbe sapere da chi le ha già implementate se ci sono particolari consigli o controindicazioni, mi sembra di capire che è molto importante valutare le dimensioni della TEMPDB che dovrà contenere il versioning delle tabelle soggette allo snapshot ma non ho ben capito quanta RAM è veramente necessaria per poter contenere il tutto, tempdb+database?

    Non sono un fan di questi livelli di isolamento :) preferisco il default "Read Committed" :) Per quanto riguarda l'overhead sul database di sistema tempdb, ti consiglio di dimensionare i file del tempdb in modo da evitare che avvengano operazioni di auto-growth; ti consiglio inoltre di posizionarli nell'unità più performante (questo vale a prescindere dal livello di isolamento, ma utilizzando RCSI con row versioning è una best practices altamente consigliata).

    Qui trovi un po' di informazioni:

    Ciao


    Sergio Govoni

    SQL Server MVP
    MVP Profile | English Blog | Twitter | LinkedIn



    sabato 2 gennaio 2016 17:05
    Moderatore
  • grazie per la risposta, non capisco però questa tendenza comune a rifiutare questi livelli di isolamento , alla fine l'RCSI mi sembra di capire sia lo stesso metodo applicato di default da Oracle, ovvero usando la concorrenza "ottimistica".


    Per quanto riguarda le prestazioni ho già in mente di montare dischi SSD su cui mettere la tempdb, ho qualche dubbio invece sulla RAM perchè non so lo snapshot isolation e l'RCSI quanta ram richieda.
    • Modificato mykernel domenica 3 gennaio 2016 17:18
    domenica 3 gennaio 2016 15:21
  • Ciao,

    il mio non è un rifiuto categorico, semplicemente non sono portato a modificare il livello di isolamento quando rilevo problemi di performance o eccessivi lock su un DB. In questi casi, per prima cosa verifico l'indicizzazione delle tabelle; uno dei principali problemi di performance (che rilevo) è dovuto ad una cattiva strategia di indicizzazione; spesso SQL Server deve manutenzionare indici che non verranno mai utilizzati per operazioni di seek (e talvolta neppure per operazioni di scan), allungano così il tempo necessario per completare una transazione. Da non sottovalutare, inoltre, quando si hanno problemi di performance, la presenza di Trigger non ottimizzati.. anch'essi fanno aumentare il tempo necessario per completare una transazione.

    Dopo aver controllato tutti questi fattori, valuto, eventualmente, la modifica del livello di isolamento.

    Considera, inoltre, che il livello di isolamento Read-Committed-Snapshot (RCSI) introduce un overhead dovuto alla gestione del row-versioning.

    Ottimo se puoi portare il tempdb su SSD! :) Ne beneficerà l'intera istanza!

    Per quanto riguarda il consumo di memoria RAM, purtroppo non ho applicazioni in produzione che utilizzano il livello di isolamento RCSI.. comunque se hai un carico di lavoro reale, potresti fare qualche simulazione.

    Ciao


    Sergio Govoni

    SQL Server MVP
    MVP Profile | English Blog | Twitter | LinkedIn


    lunedì 4 gennaio 2016 18:24
    Moderatore
  • Grazie per la risposta Sergio, molto interessante il discorso dell'indicizzazione, se fosse dovuto a quello dovrei verificare tutti gli indici non utilizzati.

    In questo caso come suggerisci di procedere? dopo un lungo periodo di uptime del server vanno disabilitati indiscrimanatamente tutti gli indici mai utilizzati in seek e/o scan? In pratica con quale criterio decidi se mantenere o meno l'indice?

    Penso comunque che il problema principale sia il codice ma quello lo sto verificando con il vendor del software.

    Grazie

    ciao

    venerdì 8 gennaio 2016 17:01
  • In questo caso come suggerisci di procedere? dopo un lungo periodo di uptime del server vanno disabilitati indiscrimanatamente tutti gli indici mai utilizzati in seek e/o scan? In pratica con quale criterio decidi se mantenere o meno l'indice?

    Ciao,

    per determinare gli Indici non utilizzati, i cosiddetti "bad index", ovvero gli Indici Non-Cluster su cui si registrano molte più scritture (Update) che letture (Seek + Scan + Lookup), potresti utilizzare la seguente query di Glenn Berry:

    SELECT OBJECT_NAME(s.[object_id]) AS [Table Name], i.name AS [Index Name], i.index_id, 
    i.is_disabled, i.is_hypothetical, i.has_filter, i.fill_factor,
    user_updates AS [Total Writes], user_seeks + user_scans + user_lookups AS [Total Reads],
    user_updates - (user_seeks + user_scans + user_lookups) AS [Difference]
    FROM sys.dm_db_index_usage_stats AS s WITH (NOLOCK)
    INNER JOIN sys.indexes AS i WITH (NOLOCK)
    ON s.[object_id] = i.[object_id]
    AND i.index_id = s.index_id
    WHERE OBJECTPROPERTY(s.[object_id],'IsUserTable') = 1
    AND s.database_id = DB_ID()
    AND user_updates > (user_seeks + user_scans + user_lookups)
    AND i.index_id > 1
    ORDER BY [Difference] DESC, [Total Writes] DESC, [Total Reads] ASC OPTION (RECOMPILE);

    Ti consiglio di eseguirla con la cache "calda" quando sei ragionevolmente sicuro di avere in cache le query tipiche del tuo workload (se puoi, lontano la procedure di ricalcolo e attività speciali).

    La query restituirà l'elenco dei possibili bad index, potrai quindi individuare gli indici da eliminare, i candidati sono quelli che hanno un numero di Seek + Scan + Lookup prossimo allo zero e molti Update.

    Tieni in considerazione anche questo aspetto: spesso si sceglie di creare un indice a supporto di ogni Foreign Key, per migliorare le performance delle operazioni di Join. Questa è una best practices vera, però se l'indice Non-Cluster viene creato su una colonna che referenzia una tabella (collegata) avente 50 o anche 200 record, è possibile che l'indice non venga mai utilizzato neppure durante le operazioni di Join. Ti consiglio di controllare anche queste situazioni.. a mio avviso, questi sono i primi indici da eliminare.. ma li troverai anche estratti dalla query :)

    Ciao


    Sergio Govoni

    SQL Server MVP
    MVP Profile | English Blog | Twitter | LinkedIn

    venerdì 8 gennaio 2016 22:30
    Moderatore
  • Grazie mille per l'informazione, ho già eseguito la query e da quello che dici tu i candidati che hanno il  numero di Seek + Scan + Lookup prossimo allo zero sono i TOTAL READS, anche se non ho capito il perchè dei molti update .., perchè dovrei disabilitare degli indici che sono utilizzati tanto in update?

    Queste sono le prime righe che mi ha identificato, significa che dovrei disabilitare tutte le righe con READS a zero e poche unità?:

    Prime righe

    mentre qui sotto ci sono le ultimissime righe trovate, anche in questo caso le letture (quindi Seek + Scan + Lookup) sono quasi tutte a zero, potrei disabilitare anche questi?:

    Ultime righe

    Grazie ancora Sergio!

    ciao



    • Modificato mykernel sabato 9 gennaio 2016 10:55
    sabato 9 gennaio 2016 10:50
  • non ho capito il perchè dei molti update .., perchè dovrei disabilitare degli indici che sono utilizzati tanto in update?

    Queste sono le prime righe che mi ha identificato, significa che dovrei disabilitare tutte le righe con READS a zero e poche unità?:

    Prime righe

    Ciao,

    dimentichiamoci un attimo del DB :) pensa all'elenco telefonico della provincia di Bologna (ad esempio). L'elenco telefonico è ordinato per Comune, che facilita la ricerca degli abbonati. All'interno del Comune, l'ordinamento secondario è per Cognome e Nome dell'abbonato. Supponiamo ora di avere un secondo elenco telefonico, sempre della provincia di Bologna (uguale al primo), ma con ordinamento diverso.. gli abbonati sono ordinati per Altezza in centimetri crescente :)

    Supponi ora di dover inserire un nuovo abbonato in elenco, conosci le sue generalità e l'altezza.. dovrai scrivere (= update) il nuovo abbonato sia nel primo elenco ordinato per Comune -> Cognome -> Nome, che nel secondo elenco ordinato per Altezza.

    Ricevi altre richieste di abbonamento e devi effettuare altre scritture e aggiornamenti dei due elenchi, potresti anche dover aggiungere o spostare pagine in funzione dello spazio disponibile e delle generalità degli abbonati.

    Ora supponi di avere anche un Call Center che fornisce i numeri di telefono degli abbonati a chi lo desidera.. supponi che le richieste (query) avvengano molto spesso per Comune, Cognome e Nome e quasi mai per Altezza.

    L'elenco telefonico (indice) ordinato per Altezza può essere eliminato perchè rappresenta solo un costo di manutenzione (= Total Writes) senza fornire benefici (= Total Reads).

    Tornando al risultato della query, gli indici con Total Writes elevati e Total Reads uguali a zero o poche unità sono molto probabilmente da eliminare perchè rappresentano solo un costo per l'istanza.

    mentre qui sotto ci sono le ultimissime righe trovate, anche in questo caso le letture (quindi Seek + Scan + Lookup) sono quasi tutte a zero, potrei disabilitare anche questi?:

    Ultime righe

    Questi indici non rappresentano un costo (con il carico di lavoro che ha prodotto questi risultati), ma non danno neppure benefici.. occupano solo spazio su disco :) se il carico di lavoro è reale potrebbero essere eliminati.

    Ciao


    Sergio Govoni

    SQL Server MVP
    MVP Profile | English Blog | Twitter | LinkedIn


    sabato 9 gennaio 2016 18:27
    Moderatore
  • Sei stato anche fin troppo chiaro ed esaustivo  :)

    Procedo con la pulizia ma preferisco prima 1) identificare il nome dell'indice da rimuovere 2) disabilitarli in modo che possa riabilitarli se dovessero servire e 3) per ogni indice disabilitato farò una verifica del piano di esecuzione per vedere se ci sono effettivi miglioramenti.

    Grazie mille!

    Andrea

    sabato 9 gennaio 2016 20:52
  • Sei stato anche fin troppo chiaro ed esaustivo  :)

    Ciao Andrea,

    gli esempi pratici sono sempre i più chiari :D

    Condivido l'approccio prudenziale che hai scelto, procederei anch'io così.. comunque il più delle volte controllando le colonne chiave dell'indice e conoscendo il DB ed il carico di lavoro si è già in grado di prendere la decisione di eliminare l'indice con sufficiente sicurezza.

    Se invece di eliminare, preferisci disabilitare l'indice non è un problema, attenzione però, perché l'indice disabilitato (se non ricordo male.. purtroppo non ho modo di provare in questo momento) può essere riattivato eseguendo il rebuild degli indici sull'intera tabella con DBCC DBREINDEX('nometabella').

    Ciao


    Sergio Govoni

    SQL Server MVP
    MVP Profile | English Blog | Twitter | LinkedIn


    domenica 10 gennaio 2016 21:44
    Moderatore