SQL Q & A: Annullamento di un danno
Analisi della selezione di un piano query, di backup eccessivamente lenti e del processo di riparazione di un database di replica.
Paul S. Randal
Piano di query
D. SQL Server è scelta di un piano di query per eseguire il mio query, tiene in considerazione i dati che sono attualmente in memoria?
**R.**La semplice risposta è che query optimizer mai considera il buffer contenuto piscina quando si sceglie un piano di query. Query optimizer valuta i vari piani come si restringe verso il basso le possibili scelte. Sembra che per il miglior piano di che esso può determinare entro un ragionevole lasso di tempo. Query optimizer non individuare sempre il miglior piano di assoluto. Query optimizer non possono spendere una quantità eccessiva di tempo sulla compilazione del piano, ma sempre seleziona un piano "abbastanza buono".
Query optimizer ha bisogno di conoscere le varie tabelle coinvolte nella query. Trova queste informazioni nei metadati relazionali per le tabelle. I metadati relazionali descrivono le colonne della tabella, indici e vincoli, come pure le statistiche circa le distribuzioni di valore nelle varie colonne (se queste statistiche sono state create). I metadati di conservazione descrivono come il tavolo e gli indici sono in realtà memorizzati nei file di dati. Query optimizer non fa uso di queste informazioni nel determinare un piano.
SQL Server non tenere traccia di quali parti di una tabella e i relativi indici sono in memoria in qualsiasi momento. Il pool di buffer tiene traccia delle pagine del file di dati da un database in memoria. Tuttavia, nulla in SQL Server non qualsiasi tipo di aggregazione automatizzato. Ad esempio, sarà in grado di determinare che il 50 per cento dell'indice due della tabella X è in memoria, considerando che solo il 5 per cento dell'indice tre della tabella X è in memoria.
Query optimizer presuppone che niente è in memoria, quindi è probabile che scelga un piano di query che coinvolge la minima quantità dei / o fisici. Piani con un sacco dei / o fisici sono lunghi e costosi. Si consideri una tabella con due indici non cluster che potrebbe soddisfare una query di selezione. Il primo indice ha tutte le colonne necessarie. Il secondo indice ha tutte le colonne necessarie, oltre a diverse colonne aggiuntive.
Il primo indice avrà più piccolo record di indice, quindi avrà più righe di indice per pagina del file di dati. Query optimizer sceglierà questo indice. L'accesso ai record indice richiesto per soddisfare che la query sarà necessario leggere poche pagine del file di dati in memoria utilizzando i/o fisici, confrontato con utilizzando il secondo indice. Il secondo ha il record più grandi e meno per ogni pagina del file dati. Questa logica è chiamata ottimizzazione basata sui costi, ed è come il processore di query nel SQL Server è stato progettato.
Tuttavia, se una parte consistente dell'indice secondo, più ampio è già in memoria e nessuno del primo indice è in memoria? La query richiederà i/o fisici per leggere l'indice selezionato in memoria. Questo sarà molto più lento rispetto all'utilizzo dell'indice più ampio già in memoria. In tal caso, il piano di query è in realtà non ottimale. Tuttavia, query optimizer non ha modo di sapere cosa c'è in memoria, e questo è solo un esempio.
Anche con questo in mente, però, che cosa succede se query optimizer riconosciuto ciò che era in memoria e generato un piano che utilizzato l'indice meno efficiente perché era già in memoria? Tale piano sarebbe ottimo solo fintanto che la situazione ha persisto. Se l'indice più efficiente è stato letto in memoria per un'altra query, query avrebbe quindi un piano non ottimale. Come sarebbe il piano essere invalidato così che potrebbe essere ricompilata nuovamente?
Come un ex software engineer del team SQL Server , so che la complessità ingegneristica di mantenere una vista aggregata delle quali tabelle e gli indici sono in memoria per facilitare l'invalidazione e selezione piano di query è estremamente impegnativa. Questo sarebbe probabilmente aggiungere un'indesiderabile performance overhead per solo un vantaggio occasionale, e probabilmente non accadrà mai.
Se sei interessato a vedere il pool di buffer, check out os_buffer_descriptors la vista a gestione dinamica (DMV) e le varie query ho messo insieme sulla Buffer Pool sezione del mio blog SQLskills.
Lungo backup
D. Usiamo il log shipping per fornire un database secondario per scopi di segnalazione. Ogni tanto, ci imbattiamo in una questione dove applicando un backup del log per il database secondario prende molto più tempo del solito. Avete qualche idea di che cosa potrebbe causare questo?
**R.**Sì, ho visto questa situazione un paio di volte. Se si utilizza un database secondario spedizione log per la segnalazione, questo significa che stai utilizzando l'opzione WITH STANDBY quando si ripristina il backup del log per il database secondario. Questo funziona in tre passaggi:
- Scrivere tutti i record di log dal backup in file di log del database.
- Eseguire la parte REDO di recupero (assicurando che tutte le operazioni di commit delle transazioni sono presenti nel database).
- Eseguire la parte di annullamento di recupero (assicurando che tutte le operazioni di rollback delle transazioni non sono presenti nel database).
Passo 3 scrive tutti i record di log generati da operazioni Annulla in un file speciale chiamato il file undo. Questo significa che il database è in modalità di sola lettura. È anche coerenza così gli utenti possono avere accesso. La ragione record di log vengono scritti nel file di undo è così il log delle transazioni del database non è alterato in alcun modo. Questo consente di ripristinare i backup del log successivo.
Quando inizia il processo di ripristino sul database secondario, se esiste un file di undo, c'è un altro passo eseguito prima i primi tre passaggi. Questa fase iniziale prende tutti i record di log nel file undo e Annulla gli effetti su di loro. Questo essenzialmente rimette il database in stato che si trovava alla fine della fase 2. Questo stato del database è lo stesso come se il backup del log precedente era stato restaurato utilizzando WITH NORECOVERY, anziché con STANDBY.
Il problema che stai riscontrando è quando viene ripristinato un backup del log contiene una transazione a esecuzione prolungata che non commettere prima della fine del backup del log. Questo significa che deve essere completamente annullata come parte del ripristino del backup del log. Questo si traduce in un file grandi modifiche, che di per sé può fare il ripristino di un registro backup richiedono più tempo. Se il backup del log viene ristabilito anche ha una transazione senza commit, lunga, allora è la tempesta perfetta. Passo 3 avrà anche un lungo tempo.
Questo può accadere quando il database principale è in fase di manutenzione indice e un backup del log termina vicino alla fine di una lunga operazione di ricostruzione indice di un indice cluster di grandi dimensioni. Il ripristino di quel backup del log per il database secondario iniziale richiede molto più tempo del solito a completare a causa della fase 3 del processo di ripristino.
Il successivo backup del log sul database principale completa anche appena prima di ricostruzione di un indice. Quando viene ripristinato sul secondario, il file intero annullamento deve essere nuovamente annullata. Poi succede il ripristino del log, e viene generato un altro file di grandi modifiche per annullare la seconda ricostruzione indice non impegnati.
Se il database secondario deve accedere 24 x 7 per la segnalazione, è necessario essere consapevoli di questa possibilità. In tal caso, accuratamente aumentare le operazioni di manutenzione indice primario nel database con i backup del log. Ciò assicurerà che solo indice completo, impegna ricostruzioni sono presenti nel backup del log, il database secondario in fase di restauro.
Un'alternativa è di passare da log shipping al mirroring del database. Qui, record di log vengono continuamente inviati dal database principale al database mirror. Non ci sono eventuali passaggi aggiuntivi che comportano annullamento Registro operazioni più volte. C'è un trade-off complessità coinvolte, come l'inconveniente di questo approccio è che segnalazione avrebbe dovuto utilizzare gli snapshot di database.
Replicare a riparare
D. Ogni tanto si finisce con i file danneggiati. Nostro backup anche finire danneggiato, quindi dobbiamo eseguire un'operazione di ripristino. La scorsa settimana, uno dei database che ho dovuto riparare era un database di pubblicazione della replica. In Microsoft Online libri, si dice che tutti gli iscritti devono essere reinizializzati dopo la riparazione di un database di pubblicazione. Può spiegare perché?
**R.**Se state pensando di utilizzare l'opzione REPAIR_ALLOW_DATA_LOSS della coerenza del database controllo CHECKDB (DBCC) (mi limiterò a dire "riparare" da qui), dovete pensare due volte se stai andando a riparare il database è un database di pubblicazione della replica. Se possibile, utilizzare i backup invece di eseguire la riparazione.
Se si utilizza la replica di tipo merge, Data Manipulation Language (DML) innesca cattura le modifiche al database di pubblicazione e li converte in operazioni logiche. Se si utilizza la replica transazionale, analisi log delle transazioni di database acquisisce le modifiche del database di pubblicazione. Quindi le operazioni registrate, fisiche vengono convertite in operazioni logiche. In entrambi i casi, le operazioni logiche vengono poi applicate per i database di sottoscrizione di replica.
Né meccanismo permette l'acquisizione di operazioni di riparazione. Operazioni di riparazione sono sempre cambiamenti fisici diretti per le strutture del database. Queste sono necessarie per risolvere le incoerenze nelle strutture, come ad esempio una pagina di database, record di un tabella o un collegamento tra due pagine. Sono diverse dalle modifiche apportate al database fisico a causa della query eseguire inserimenti, aggiornamenti o eliminazioni su tabelle.
Queste operazioni non possono tradurre in operazioni logiche che possono aver applicato agli abbonati di replica di riparazione. Non ci sono operazioni logiche che possono essere espresse utilizzando Transact-SQL per l'equivalente delle modifiche strutturali dirette che sta eseguendo la riparazione. Immaginate che un'operazione di ripristino è costretto a rimuovere una pagina di dati da un indice cluster univoco (essenzialmente l'eliminazione di alcuni record della tabella). L'abbonamento non viene reinizializzato. Tali registrazioni esisterebbe ancora sulla copia replicata del tavolo.
Se un'operazione di inserimento successivo inserito record con valori chiave cluster corrispondente ai record eliminati dall'operazione di riparazione, l'agente di distribuzione fallirebbe quando si tenta di applicare gli inserti per la copia replicata. L'operazione di riparazione sarebbe non sono stata applicata al database di sottoscrizione, quindi un errore di violazione di chiave duplicata si verifica quando si tenta di applicare l'inserto per la tabella replicata. Questo significa che se un'operazione di riparazione modifica qualsiasi tabella che fa parte di una pubblicazione di replica, la sottoscrizione di replica non è più valida. Esso dovrà essere reinizializzato.
Se l'operazione di riparazione colpisce qualsiasi delle tabelle di metadati della replica, la pubblicazione di replica intera è in uno stato incoerente. La replica deve essere completamente rimosso e riconfigurata. Ovviamente, questo è un processo molto più invasivo rispetto basta reinizializzare una sottoscrizione. La linea di fondo è che, se possibile, si vuole evitare di dover riparare un database di pubblicazione della replica.
.jpg)
Paul S. Randal è l'amministratore delegato di SQLskills.com, Microsoft regional director e un SQL Server MVP. Ha lavorato su team SQL Server di Storage Engine di Microsoft dal 1999 al 2007. Egli ha scritto il comando DBCC CHECKDB/repair per SQL Server 2005 ed era responsabile di Core Storage Engine durante lo sviluppo di SQL Server 2008. Randal è un esperto di disaster recovery, alta disponibilità e manutenzione del database ed è un presentatore regolarmente a conferenze in tutto il mondo. Blog a SQLskills.com/blogs/paul e si possono trovare lui su Twitter a Twitter.com /PaulRandal..