SQL domande e risposte: deframmentazioni e situazioni di emergenza
I log degli errori e i file di database temporanei possono crescere a dismisura in breve tempo. La seguente tattica di configurazione può rivelarsi utile.
Paul s Randal
Impostazione predefinita per la deframmentazione
D. Ho stato indirizzamento alcuni problemi di manutenzione database che abbiamo sul nostro server e sto considerando la frammentazione dell'indice. Anziché dedicare molto tempo, l'individuazione di un fattore di riempimento per ogni indice, non sarebbe più semplice è sufficiente impostare il fattore di riempimento predefinito per tale istanza? Sono presenti eventuali svantaggi di questa operazione?
**R.**In genere non sarebbe consigliabile impostare il fattore di riempimento a livello di istanza per qualsiasi valore tranne il valore predefinito, ovvero al 100%. La modifica di tale impostazione può causare lo spazio inutilizzato nel database.
Quando si creano indici su una tabella, in genere solo uno di essi corrisponderà il motivo dell'inserimento tabella. Tutti gli altri indici inevitabilmente vengono frammentati. Fino a quando il criterio di inserimento è di sola aggiunta (nessun inserimenti casuali da una chiave casuale come un identificatore univoco globale [GUID]), è opportuno evitare che l'indice più grande frammentato.
L'indice più grande è sempre l'indice cluster, l'indice cluster è la tabella. Questo file contiene tutti i record di dati con tutte le colonne della tabella. È utile per l'indice cluster essere quella che non ottenere frammentato. Rimozione della frammentazione dell'indice cluster saranno più costosa (in termini di registrazione delle transazioni, tempo e spazio su disco) rispetto a tutti gli altri indici.
Come l'indice cluster in genere non ottenere frammentato, è quindi consigliabile che l'opzione fillfactor è impostata su 100. Non sarà necessario alcun spazio aggiuntivo. Vi possono essere altri motivi che l'indice cluster ottiene frammentato (ad esempio gli aggiornamenti alle colonne di lunghezza variabile che rendono più righe di tabella), ma che in genere è true.
Se si imposta il fattore di riempimento del livello di istanza su qualsiasi valore ad eccezione del 100 percento, è necessario impostare in modo specifico il FILLFACTOR di tutti gli indici cluster che non ottenere frammentati. In caso contrario, essi verranno utilizzare il nuovo fattore di riempimento a livello di istanza e lasciare uno spazio quando essi si ricostruito. Questo essenzialmente uno spreco di spazio nel database. È possibile scegliere di eseguire questa operazione, ma in genere presenta non considerata ottimale.
Sono presenti una serie di indici in qualsiasi database, è insolito trovare un valore fillfactor singola che rappresenta la soluzione ideale per tutti gli indici. È in genere preferibile dal punto di vista della gestibilità per lasciare invariato il fattore di riempimento del livello di istanza. In particolare, è possibile impostare inferiore FILLFACTOR su tali gli indici che li richiedono.
Riavviare il sistema le risposte
D. Alcuni server nel nostro ambiente raramente vengono riavviati. Mentre questa è una cosa positiva in molti modi, significa però che il log degli errori di SQL Server può crescere impractically di grandi dimensioni. Sembra che riempiono con decine di migliaia di messaggi di completamento del backup per il quale non si dispone di alcuna utilità. È possibile fare per rendere i log degli errori più piccolo e gestibile?
**R.**Esistono due operazioni possibili: tagliare verso il basso sui messaggi di backup e configurare la gestione del Registro di errore. Ogni volta che viene completata una copia di backup, scriverà una voce nel log degli errori. Si tratta di scarsa utilità. Completamento del backup è la norma, non un errore.
Non vi è un flag di traccia documentata, flag di traccia 3226 — che impedirà a messaggi di riuscita dei backup. È consigliabile aggiungere che per l'elenco dei flag di traccia di avvio (utilizzando il gestore di configurazione SQL Server). Per attivare questa opzione senza prima arrestare e riavviare SQL Server, è possibile anche attivare tale traccia contrassegnare mediante il comando "DBCC TRACEON (3226, -1)." I mezzi di-1 per applicare il flag di traccia a livello globale. Il team di SQL Server discusso sui flag di traccia nel 2007.
È inoltre possibile configurare la gestione del Registro di errore all'interno di SQL Server Management Studio (SSMS). Aprire Esplora oggetti in SQL Server Management Studio e connettersi a SQL Server. Espandere la finestra di gestione, fare clic destro sul log di SQL Server e scegliere Configura. Quando viene visualizzata la finestra di dialogo di configurare i registri di errore di SQL Server, è necessario selezionare l'opzione "Limita il numero di log degli errori prima che vengano riciclati". Log degli errori selezionare 99. Si esegue l'override il numero predefinito di sei log errore mantenuta.
L'ultimo passaggio di configurazione è ciò che consente di limitare la dimensione di ogni log degli errori. Fornire istruzioni SQL Server per creare un nuovo log degli errori ogni giorno (denominato "ciclo" il log degli errori). Eseguire questa operazione mediante la creazione di un processo di agente di SQL Server giornaliero viene semplicemente "sp_cycle_errorlog EXEC". Al termine, il log degli errori deve essere gestibile.
Offre il database Tempdb
D. Il volume di dati è cresciuto enormemente negli ultimi due anni. I nostri tempdb sembra sempre per il riempimento a seconda di quale unità si trova su. Abbiamo eseguito alcune query complesse, in modo che noi due facciamo una grande quantità di utilizzo di tabelle temporanee. Potete fornire eventuali suggerimenti su come ridurre l'utilizzo di tempdb?
R: utilizzo di Tempdb è un problema perenne per gli utenti di SQL Server. È disponibile solo un singolo database tempdb per ogni istanza di SQL Server, pertanto è necessario prestare attenzione a come viene utilizzato.
Uno degli utilizzi più comuni di tempdb è da tabelle temporanee. Queste tabelle consentono i risultati intermedi dal complesse delle aggregazioni o join per rendere persistenti e diventano parte di una query di dimensioni maggiore. Questo spesso è possibile suddividere una query lunga e complessa in modo efficiente, ma non è sempre il caso. A volte gli sviluppatori di acquisire familiarità con le tabelle temporanee per impostazione predefinita, anziché eseguire i test delle prestazioni per visualizzare o meno l'utilizzo di una tabella temporanea è più efficienti.
Il problema con la creazione di un risultato intermedio, impostato in una tabella temporanea è il grado di interrompere la pipeline di dati efficiente tramite una query complessa. Essa impone a query optimizer per elaborare la creazione di tabelle temporanee o di una popolazione separatamente da operazioni successive, utilizzare la tabella temporanea. Talvolta è possibile che query optimizer produce un piano di query più efficiente se non viene utilizzata una tabella temporanea. Può anche essere eseguito in modo più efficiente con un costrutto di diverse query come una tabella derivata o un'espressione di tabella comune.
Se una tabella temporanea è il modo più efficiente per suddividere la query, non vi sono due operazioni per ridurre l'utilizzo di tempdb:
- Creazione di indici appropriati: assicurarsi che gli indici solo creati sulla tabella temp siano effettivamente utili per un'ulteriore elaborazione (controllo utilizzo di questo tramite l'analisi e verifica che indicizza il piano di query). Gli indici non cluster creati in ogni colonna della tabella temporanea sono improbabile che siano utili. Assicurarsi inoltre che gli indici vengono creati dopo la compilazione dei dati della tabella temporanea, in modo da statistiche per la Guida in linea di query optimizer utilizza l'indice.
- Ridurre al minimo le dimensioni della tabella temporanea: assicurarsi che le uniche colonne permanenti nella tabella temporanea sono quelli utilizzati per l'ulteriore elaborazione, in caso contrario sono un completo spreco di spazio. Tabelle temporanee vengono spesso create con un'istruzione SELECT * costrutto con no pensato per le colonne che sono effettivamente necessari. Se si sta elaborando un set di risultati di grandi dimensioni, questo spazio inutilizzato realmente possibile sommare.
Backup non validi
D. Arresto anomalo del nostro SAN ultima settimana. Abbiamo subito una perdita di dati nel nostro database di produzione. I backup più recenti sono stati memorizzati sulla rete SAN con i file di database, in modo che quelli danneggiati. Abbiamo riscontrato anche i backup leggermente meno recenti sono stati danneggiati anche, in alcuni casi il danneggiamento stesso. Come ci possiamo per evitare questa situazione in futuro?
**R.**Si tratta di una situazione comune, ovvero Nessun backup buona e un singolo punto di errore, che in definitiva comporta la perdita di dati.
Il primo problema è che i backup sono danneggiati. È necessario implementare un backup di testing strategia è quindi possibile determinare se il backup è danneggiato o contiene un database danneggiato. Sono disponibili più parti al seguente:
- Implementare una coerenza a intervalli regolari per il database di produzione. Ciò significa che esegue il comando DBCC CHECKDB sul database di produzione se stesso o una copia del database.
- Attivare i checksum di pagina del database di produzione (se non già attivato). Utilizzare l'opzione WITH CHECKSUM su tutte le copie di backup. Per il test verrà i checksum della pagina come la lettura delle pagine del file di dati da includere nel backup, che consente di evitare la creazione di una copia di backup con un database danneggiato.
- Implementare un controllo di validità dei backup dopo che sono state adottate. Ciò consiste nel prendere il backup a un'altra istanza di SQL Server e ripristino, utilizzando nuovamente l'opzione WITH CHECKSUM, o almeno eseguire RESTORE VERIFYONLY del backup tramite WITH CHECKSUM. L'obiettivo consiste nel ripristinare il database ed eseguire DBCC CHECKDB. Inoltre si tratta di un buon metodo per trasferire il carico di lavoro coerenza dal server di produzione.
- Implementare una pianificazione di testing regolare in cui si pratica il ripristino del database di produzione dalle copie di backup disponibili.
Un altro modo per verificare facilmente la validità dei backup del log delle transazioni consiste nel creare una distribuzione dei log secondari. Verranno ripristinate costantemente i backup del log delle transazioni dalla produzione. Essa offre inoltre una copia di riserva del database.
Il secondo problema è che i backup vengono memorizzati nel sottosistema dei / O stesso come il database stesso. Ciò significa che non si dispone di alcuna protezione contro un errore del sottosistema dei / O.
È necessario conservare una copia di tutti i backup su un sottosistema dei / O separato dal database di produzione, in teoria, ciò sarebbe in una posizione completamente separata. Guida in linea locale copie di backup con ripristino d'emergenza più rapido e copie remote assicurarsi che ripristino di emergenza è sempre possibile se l'archiviazione locale è danneggiato o distrutto.
Un altro fattore da considerare è incoraggiante esercitazioni di ripristino di emergenza regolari. Fingere qualsiasi identità che ha colpito una situazione di emergenza e di lavoro attraverso il piano di ripristino di emergenza per determinare l'efficacia. Mio articolo di aprile 2011, "SQL Server: proteggere i dati a tutti i costi," illustra questo dal punto di vista gestionale.
I casi di ripristino di emergenza sono allo stesso modo. Uno degli orientamenti spesso ripetuta è "difesa approfondita." Ulteriori opzioni che disponibili per il ripristino di emergenza e i più potenziali problemi di anticipare e di evitare in modo proattivo, più sarà in grado di recuperare da una situazione di emergenza entro i tempo di inattività e perdita di dati contratti di servizio.
.jpg)
**Paul s Randal**è managing director di SQLskills.com, un Microsoft regional director e MVP per SQL Server. Ha lavorato nel team SQL Server Storage Engine in Microsoft dal 1999 al 2007. Egli ha scritto il comando DBCC CHECKDB/repair per SQL Server 2005 e si era responsabile di Core Storage Engine durante lo sviluppo del SQL Server 2008. Randal è un esperto in ripristino di emergenza, disponibilità elevata e manutenzione del database e un regolare come relatore a conferenze in tutto il mondo. SQLskills.com /blogs/paul ed è possibile trovarlo nei movimenti in Twitter.com/PaulRandal.