DOMANDE E RISPOSTE SU SQL Overflow di riga, backup differenziale e altro ancora

Paul S. Randal

D È aggiornato di recente un'applicazione affinché venga eseguita in SQL Server 2005. Una delle operazioni che È stata eseguita sfruttare è la possibilità di avere righe maggiore 8,060 byte in modo È possibile consentire agli utenti di creare più campi di dati senza la visualizzazione di un messaggio di errore da SQL Server. Una volta che l'applicazione è in produzione, si verificano problemi di prestazioni per alcune query analisi che consente di eseguire fine prima della modifica dello schema. È verificato la frammentazione degli indici diversi e tutto ciò che è valido. In perché sono le query esecuzione lenta su SQL Server 2005?

A Si utilizza la funzione, overflow di riga, è utile per consentire la riga occasionale superare 8,060 byte, ma non è adatta per la maggior parte delle righe da oversized e può causare a discesa delle prestazioni di query, come si verificano.

Questo è che quando sta per diventare lunghi una riga, una delle colonne a lunghezza variabile nella riga viene inserita "Off-riga." Questo significa la colonna è ricavata alla riga pagina dati o indice di e spostata in una pagina di testo. In sostituzione di precedente valore di colonna, viene sostituito un puntatore che punta alla nuova posizione del valore della colonna del file di dati.

Si tratta esattamente del meccanismo stesso utilizzato per memorizzare regolare colonne LOB (Large Object), ad esempio XML, text, image o varchar (max). Tenere presente che se lo schema della tabella contiene più colonne di lunghezza variabile, esiste alcuna garanzia che la stessa colonna verrà essere inserita esterno riga quando più righe sono lunghi.

Questo meccanismo è possibile creare un problema di prestazioni. Improvvisamente una query per recuperare una colonna a lunghezza variabile da una singola riga in una tabella potrebbe essere necessario un I/O aggiuntive se la colonna è stata inserita esterno e riga per la lettura della pagina di testo contenente la posizione del valore esterno riga). Se sono oversized più righe, è possibile che una query per recuperare la stessa colonna a lunghezza variabile da più righe che le prestazioni imprevisti, a seconda del numero di valori è stato inserito esterno riga.

Nel caso, una query di esecuzione di un intervallo scansione o la tabella analisi per un elenco di selezione che include una colonna a lunghezza variabile è subire da prestazioni scadenti in termini a causa dell'overflow di riga e relativi effetti. Non importa se gli indici sono perfettamente frammentati, quando una colonna a lunghezza variabile è stata inserita esterno riga, l'analisi in precedenza efficiente essenzialmente viene interrotto poiché un I/O casuale è necessario leggere la pagina di testo contenente il valore esterno riga.

Overflow a riga ancora è molto utile per le righe di lunghi occasionale. Tuttavia, se le prestazioni delle query è importante, un componente molto exploited la struttura non deve essere.

D È appena stato introdotto il mirroring del database tra due cluster di failover come metodo di ottenere ridondanza geo per minore il costo di replica di rete SAN (area di archiviazione. I centri dati sono in stessa città, siamo in grado di utilizzare il mirroring sincrono. Il problema è che quando si verifica un failover sul cluster locale, il database mirror failover del cluster remoto, che non è il comportamento che si desidera. Come è possibile evitare questo da accada? Vogliamo che il failover a verificarsi solo se il cluster locale non è disponibile.

A per una maggiore disponibilità, il mirroring è impostato con un server di controllo del mirroring in modo failover avvenire automaticamente se il principale diventa non disponibile. L'idea è che dell'intero cluster locale viene interrotta, il mirroring del database verrà failover del cluster secondo e l'applicazione può continuare.

Il problema si verifica quando si verifica un failover del cluster. Il failover richiede più tempo di quello l'impostazione di timeout predefinito del mirroring del database. Il server di controllo del mirroring e il server mirror (ovvero l'istanza attiva di SQL Server nel cluster secondo) concordano che che non è visibile l'oggetto principal, e quindi il server mirror avvia un failover del mirroring al cluster secondo.

Il metodo più semplice per impedire questo consiste nel rimuovere il server di controllo in modo che il mirroring del database viene automaticamente failover se cluster locale. Ovviamente questo riduce disponibilità, durante un essere umano è quindi necessario per avviare un failover.

La seconda opzione consiste nel modificare l'impostazione di timeout predefinita di mirroring del database. Questo è il numero di una volta, per-secondo "ping" che l'oggetto principal necessario non riescono a rispondere prima sia dichiarata non disponibile. Questa impostazione viene chiamata il timeout di partner e ha un valore predefinito di 10. Il valore di timeout corrente per il database può trovare utilizzando il seguente codice:

SELECT [mirroring_connection_timeout]
  FROM master.sys.database_mirroring 
  WHERE [database_id] = DB_ID ('mydbname');
GO

Il valore di timeout può essere modificato utilizzando il seguente codice:

ALTER DATABASE mydbname 
  SET PARTNER TIMEOUT <timeoutvalue>;
GO

Per questo scenario, il timeout di partner deve essere impostato che superiore alla normale richiede un failover del cluster si verificano sul cluster locale. Questo può sia difficile stabilire indicato di variabilità nel tempo che necessario per eseguire ripristino del database con mirroring quando si verifica il failover del cluster e sarà possibile stabilire un limite superiore. Il problema con questo metodo è che il valore di timeout potrebbe avere in minuti, che possono essere accettabile per quando si verifica una reale situazione di emergenza.

D lo strategia di backup comporta completo e i backup del log, ma È stato sentito che È necessario aggiungere i backup differenziali per ridurre ripristino ora. È possibile eseguire un backup completo dopo una settimana e l'orario backup del log. È tentato di aggiungere backup differenziale giornaliero, ma un aspetto irregolare che HO notato che i backup differenziali alla fine della settimana sono vicini alla dimensione stessa come il backup completo settimana. È nell'impressione che siano incrementale, come i backup del log. È mancante qualcosa?

misunderstanding L'UNA di seguito è intorno alla natura del backup differenziale. A differenza dei backup del log, i backup differenziali non sono incrementali. Un backup differenziale contiene tutte le estensioni di file dei dati modificati dopo l'ultimo backup completo (e questo viene applicato a database, filegroup e i backup di livello di file).

Quando un extent (un gruppo logico di pagine del file di dati di contigui otto viene modificato in alcun modo, è contrassegnato come in una pagina speciale bitmap detta mappa differenziale, ovvero più comunemente noto come il mapping delle differenze. È una mappa delle differenze per ogni blocco di 4 GB di ogni file di dati. Quando viene eseguita una copia di backup differenziale, il sottosistema di backup analizza tutti i mapping delle differenze e copia tutti gli extent modificati, ma i mapping delle differenze non vengono reimpostati. Ciò significa che se tra i backup differenziali successivi vengono modificate più estensioni, i backup successivi sarà maggiori. Le mappe diff vengono reimpostate solo quando viene eseguito un backup completo.

Se il carico di lavoro dell'applicazione è tale che il contenuto di database è ampiamente modificato entro un breve periodo di tempo (ad esempio, all'interno di una settimana), un backup completo settimana sarà quasi le stesse dimensioni un backup differenziale è stata eseguita solo prima completo successivo backup. Ciò viene illustrato il comportamento che sta osservando.

Sono corrette in a pensare che i backup differenziali rappresentano un metodo per ridurre i tempi di ripristino in una situazione di ripristino di emergenza. Se la strategia di backup è settimanale backup completi e orario backup del log, un ripristino aggiornato richiederebbe le operazioni seguenti:

• Eseguire un backup di coda di log (tutti i registri generati dall'ultimo backup log più recente).
• Ripristinare il backup completo del database più recente.
• Ripristinare tutti i backup del log, in sequenza, dall'ultimo backup completo del database.
• Ripristinare il backup di coda di log.

Questo potrebbe richiedere numerosi backup del log da ripristinare, soprattutto caso l'emergenza prima il prossimo backup completo è dovuto. Uno scenario pessimistiche sarebbe significa 24 + 24 + 24 + 24 + 24 + 24 + backup del log 23 per il ripristino. Backup differenziale giornaliero viene aggiunto a questa strategia, la sequenza di ripristino modifica al seguente:

• Eseguire un backup di coda di log (tutti i registri generati dall'ultimo backup log più recente).
• Ripristinare il backup completo del database più recente.
• Ripristinare il backup differenziale più recente.
• Ripristinare tutti i backup del log, in sequenza, dall'ultimo backup differenziale più recente.
• Ripristinare il backup di coda di log.

Questo grado di rimuovere la necessità di ripristino di un lotto di backup del log, come ripristinare un backup differenziale è essenzialmente identica come ripristinare i backup di log nel periodo interessato dal backup differenziale.

Nel caso peggiore molto di uno scenario in cui viene eseguito un backup differenziale giornaliero sarebbe backup del log 23, anche sull'ultimo giorno della settimana. Lo svantaggio di uno dei backup differenziali non sia incrementale è che può richiedere ulteriore spazio, ma quasi sempre un compromesso utile per ridurre i tempi di ripristino.

D dispongo di un cluster di failover a due nodi. Ciascun nodo è in esecuzione una singola istanza di SQL Server 2005. È possibile il seguente l'avviso comune di impostazione di ogni istanza da utilizzare solo il 50 % della memoria disponibile. Ora si verificano problemi perché il carico di lavoro entrambi i casi è più memoria per mantenere lo stesso livello delle prestazioni. Se è possibile rimuovere il limite di memoria o renderlo più alto, RITENGO che verrà eseguo in problemi quando una delle istanze viene eseguito il failover e sono entrambi in esecuzione su un solo nodo. Ciò che è consigliabile?

A Sarà rispondere a questa domanda per il caso a due nodi, due istanza, ma tutti gli elementi seguenti vale anche per altre impostazioni a istanza multipla (N-1 ai cluster di failover, in cui sono presenti nodi N e istanze di SQL Server N-1).

Molti utenti esperienza un elevato carico di lavoro (utilizzo di più di 50 % della memoria del server) in entrambe le istanze e non hanno in considerazione l'effetto sui carichi di lavoro quando entrambe le istanze del termine in esecuzione su un nodo singolo dopo un failover. Senza qualsiasi configurazione speciali, è possibile per la distribuzione di memoria tra istanze per diventare elevato, in modo da un carico di lavoro viene eseguito correttamente e l'altro rallenta per una ricerca per indicizzazione.

Con SQL Server 2000, il consiglio consiste di ogni istanza a un massimo del 50 % della memoria del nodo di cluster dei limiti. Infatti, il gestore di memoria in SQL Server 2000 non risponde alla pressione della memoria, se SQL Server, ad esempio 80 % della memoria del nodo, verrà non assegnargli nuovamente. In una situazione di failover, questo significa che un'altra istanza appena avvio sarebbe sufficiente 20 % di memoria disponibile. Limitando entrambe le istanze a un massimo del 50 % della memoria di un nodo, un'istanza di failover è garantita al 50 % della memoria. Il problema con questo è il carico di lavoro in ogni istanza inoltre limitato a 50 % della memoria.

In SQL Server 2005 (e SQL Server 2008), il gestore di memoria può rispondere a un numero eccessivo di richieste di memoria al 50 % massimo non è appropriato. Ma senza un tipo di limitazione, se due istanze esegue su un nodo del cluster, potrebbe pressure loro fino a raggiunta una distribuzione di memoria sproporzionato.

La risposta è per impostare ogni istanza per una quantità minima di memoria in modo che non può essere pressured per rilasciare una quantità eccessiva di memoria. Un'impostazione comune per un'installazione di due nodi, due istanza è per ogni istanza configurato per un minimo di 40 % della memoria. Ciò significa che quando ogni istanza è in esecuzione su un nodo distinto, può utilizzare tutta la memoria desiderano. Quando si verifica un failover, ciascuna istanza è garantito che una certa quantità di memoria per mantenere un livello di insieme di prestazioni del carico di lavoro, con un po'sinistra su da condividere tra di esse. Anche se ciò significa che le prestazioni di entrambi i carichi di lavoro possono eliminare in una situazione di failover come previsto), sarà limitati a tutti per la maggior parte della fase quando viene eseguito ogni istanza su un nodo di cluster separato.

S Paul Randal è il responsabile gestione SQLskills.come un MVP di SQL Server. Ha lavorato nel team motore di archiviazione di SQL Server in Microsoft da 1999 a 2007. Paul scritto DBCC CHECKDB e ripristino per SQL Server 2005 ed era responsabile per il motore di archiviazione principale durante lo sviluppo di SQL Server 2008. Paul è un esperto di ripristino di emergenza, un'elevata disponibilità e manutenzione dei database ed è in un normale relatore a conferenze di tutto il mondo. Blog ha in SQLskills.com/blogs/paul.