A & D SQL Rimozione di frammentazione dell'indice, sincronizzazione e sincronizzare e altro ancora
Paul S. Randal
QSto confuso sulla rimozione della frammentazione dell'indice influenza le statistiche. HO sentito che in alcuni casi È necessario ricreare mia statistiche dopo ricostruzione di un indice e in alcuni casi consiglia di non e che ricostruzione di un indice cluster avrà effetto su tutti gli altri indici troppo. Possibile è colmare le lacune su questo please che desidera verificare che sono apportare non inavvertitamente peggiorare delle prestazioni?
AQuesto è certamente un problema che causa molto confusione, ma sono a destra che una strategia di manutenzione database completo include rimozione della frammentazione dell'indice e l'aggiornamento delle statistiche. Non verrà per iniziare i dettagli di esattamente quando e perché rimuovere la frammentazione dell'indice, per, vedere l'articolo agosto 2008 " Superiore suggerimenti per la manutenzione dei database valide." Invece verrà presupporre che si è certi che gli indici per l'utilizzo con e si verrà solo concentrarsi funzionamento operazioni.
La prima parte della confusione è intorno a cui rimozione della frammentazione operazioni influenzano le statistiche. Ricostruzione di un indice (utilizzando ALTER INDEX … RICOSTRUIRE, DBCC DBREINDEX o CREATE INDEX … WITH DROP_EXISTING) aggiornare statistiche con l'equivalente di un'analisi completa, ma riorganizzazione di un indice (mediante l'istruzione ALTER INDEX … REORGANIZE o DBCC INDEXDEFRAG non aggiorna le statistiche, anche se entrambi rimuovere frammentazione dell'indice.
Il motivo che ricostruzione aggiorna le statistiche e riorganizzazione non è con algoritmi utilizzati per queste operazioni. Un'operazione di ricostruzione indice include una visualizzazione completa dell'indice e pertanto può aggiornare le statistiche correttamente. Un'operazione Riorganizza, tuttavia, solo opera su alcune pagine dell'indice in un momento e correttamente Impossibile aggiornare le statistiche per l'intero indice.
La seconda parte della confusione è sulle statistiche vengono aggiornate quando viene ricreato un indice. Esistono due tipi generali di statistiche per una tabella, ovvero quelli per colonne indicizzate la tabella e quelli per le colonne non indicizzate. Un'operazione di ricostruzione indice Aggiorna solo le statistiche per l'indice essere ricreato. Le statistiche per le colonne non indicizzate devono essere aggiornate manualmente il piano di manutenzione. Inoltre, come HO già detto nell'articolo "primi suggerimenti per valide manutenzione dei database", è necessario essere attenzione a non manualmente Aggiorna statistiche indice dopo la ricostruzione dell'indice, aggiornamento manuale può utilizzare una velocità di esempio di inferiore al 100 %, mentre la ricostruzione dell'indice utilizzerà l'equivalente di un campione di analisi completo (100 %). In altre parole, questo può comportare sovrascrivere una statistica Analisi completa con una statistica campionata.
L'ultima parte della confusione è attorno all'effetto ricostruzione di un indice dispone su altri indici. La risposta è sempre che ricostruzione di un indice influisce solo quel particolare indice e relativa statistiche. L'eccezione è per un indice non univoco cluster in SQL Server 2000, in cui ricostruzione tale indice anche causerà tutti gli indici non cluster nella tabella per essere ricostruito. Questo è stato risolto da SQL Server 2005 a partire da. È possibile trovare ulteriori informazioni su questo punto nel mio post di blog in" Gli indici dal ogni angolo."
Per riepilogare, la manutenzione indice e statistiche dovrebbe effettuare le seguenti operazioni:
- Ricreare o riorganizzare gli indici per rimuovere la frammentazione
- Aggiorna statistiche indice per tali indici non rigenerati
- Aggiorna statistiche indice non
QÈ stati sperimentare informazioni SQL Server 2008 ed È stato trovato un comportamento strana. Sembra che se si Abilita compressione dei dati nel database di produzione e quindi eseguire il backup del database e si tenta di ripristinare in un'istanza di server standard, il ripristino non riesce. È il comportamento previsto? In questo caso, è questo limitato per la compressione dei dati o altre caratteristiche interessati? E perché non il ripristino non immediatamente anziché seeming per eseguire il ripristino intero prima che si verifichi un errore?
AIl comportamento che in visualizzazione è in base alla progettazione. Più "aziendali solo" funzionalità solo limitare le edizioni di SQL Server a cui è possibile apportare utilizzare la funzionalità per Enterprise Edition, Enterprise Evaluation Edition e Developer Edition. Esistono alcune funzionalità, tuttavia, che anche il limite che edizioni di SQL Server può ripristinare un backup di un database contenente la funzione. È stato trovato una delle funzionalità SQL Server 2008 con questa limitazione, compressione dei dati.
Il comportamento è effettivamente non nuovo per SQL Server 2008. In SQL Server 2005, se un database contiene tabelle partizionate o indici, in modo esplicito utilizzando la funzione di partizionamento, quindi un backup del database che può essere ripristinato solo utilizzando una Edition nell'elenco appena detto. Esistono due problemi con questo, tuttavia, in SQL Server 2005. Firstly, può essere difficile stabilire se è qualsiasi partizione in un database e in secondo luogo, il ripristino non effettuate fino solo prima che si hanno completato.
Ciò significa che non si trova all'esterno che il ripristino verrà esito negativo finché non è attesa mediante l'intera operazione di ripristino (potenzialmente a esecuzione prolungata). Infatti, il database non è coerenza a livello di transazioni finché non viene completata la parte di ripristino di ripristino, e quindi un'operazione durante il ripristino può aggiungere o rimuovere la partizione. Presenza di questa situazione può essere davvero irritante in una situazione ripristino di emergenza in cui l'unico server disponibile per ottenere rapidamente il database ripristinato lezioni standard.
In SQL Server 2008, elenco di funzionalità "solo Enterprise" con questo comportamento ha raggiunto quattro: la compressione dei dati, modifica dati acquisizione crittografia trasparente dei dati e partizionamento. Ciò significa che altre persone intende si verifica questo problema. Per questo motivo, una nuova DMV è stato aggiunto, sys.dm_db_persisted_sku_features, che consente di amministratore di database Verificare facilmente se uno qualsiasi di queste funzionalità sono attivato in un database.
Ad esempio, in un database in cui una tabella è stato compressione dei dati attivata, in esecuzione il DMV fornirà i seguenti risultati:
SELECT * FROM sys.dm_db_persisted_sku_features;
GO
feature_name feature_id
-------------- -----------
Compression 100
Tuttavia, SQL Server 2008 risolvere non il problema con il ripristino operazione dover quasi completa prima che l'amministratore di database viene informato che il database effettivamente non può essere ripristinato. Infatti, questo problema è improbabile l'indirizzamento dato la natura del funzionamento del ripristino, vedere il Colonna di domande e risposte su SQL dal problema 2008 ottobre Per ulteriori informazioni.
Se è possibile che un database deve essere ripristinato su server standard o una versione inferiore, di imperativa che l'amministratore di database non consente di queste funzionalità oppure utilizza il DMV regolarmente per evitare di ricevere una sorpresa nasty durante il tentativo di ripristino in una situazione critica.
QÈ stato implementato il mirroring del database sincrono ed era sotto l'impressione che ciò significava che il database mirror è sempre sincronizzato con il database principale. Tuttavia, talvolta visto lo stato di mirroring diventano sincronizzazione anziché SYNCHRONIZED, nonché quando viene innanzitutto impostata in modo speculare. Come è possibile questo?
APrima di è possibile rispondere alla domanda, verrà definire SYNCHRONIZED e sincronizzazione mirroring del database degli stati. In istantaneamente il mirroring del database funziona continuamente inviando i record di registro delle transazioni fisiche compresi tra l'istanza di SQL Server che ospita il database principale e l'istanza che ospita il database mirror. Se non esistono Nessun record di registro delle transazioni in attesa l'entità di essere inviati al mirror, lo stato di mirroring è SYNCHRONIZED (in altre parole, i due database sono sincronizzati. Se non esistono transazioni i record di registro che non sono ancora stato inviati al server mirror (al database mirror è il principale dietro), lo stato di mirroring è sincronizzazione.
Il metodo per inizializzare un mirror del database è nel portare un backup completo del database, oltre ad almeno un registro delle transazioni backup e ripristino (utilizzando WITH NO_RECOVERY) sul mirror. Quando è attivato il mirroring, quindi è sincronizzazione iniziale lo stato di mirroring. Si tratta in quanto non è possibile che sia stata alcuni transazione attività sul principale dall'ultimo backup log ripristinato sul mirror. Questo è molto probabilmente per un database con un carico di lavoro costante.
Il trucco per ridurre al minimo il periodo in cui tenta di apportare al database mirror aggiornato possibile utilizzando il mirroring rimane dello stato sincronizzazione backup del log. Tuttavia, per un database con un carico di lavoro costante, questa potrebbe essere molto difficile e pertanto lo stato di mirroring inizialmente sarà sincronizzazione per alcuni minuti. Una volta il mirror rileva i, lo stato di mirroring verrà modificato in SYNCHRONIZED.
Dopo tale punto è possibile che il database mirror rientrano nuovamente dietro l'oggetto principal, in cui i casi lo stato di mirroring verranno rilascio nuovamente di sincronizzazione. Una causa di questo potrebbe essere se il server mirror e principale sono grado di comunicare per un periodo di tempo. O il problema può verificarsi se l'oggetto principal viene generato il log delle transazioni più veloce rispetto al registro può essere inviato al mirror. In entrambi i casi e a seconda del mirroring del database è configurazione, il database principale potrebbe continuerà a elaborare le transazioni e una coda di log delle transazioni record verranno creare (denominato coda SEND). Devono essere inviato al mirror in modo che rilevare i. Fino a quando il database mirror è rilevata alto, lo stato di mirroring rimarrà sincronizzazione.
Se il collegamento di rete è inaffidabile tra il principale e mirror, è possibile che venga visualizzato lo stato di mirroring passaggio back-end e a via tra sincronizzazione e SYNCHRONIZED. È possibile trovare una descrizione completa di questi stati nel white paper Database di mirroring di SQL Server 2005.
QAbbiamo di mirroring sincrono del database insieme i per il database dell'applicazione principale e il monitoraggio viene illustrato che la coda rollforward sul mirror è in genere prossima a zero. Si desidera utilizzare il database mirror per il reporting e coerenza controllo in modo è possibile migliorare utilizza dell'hardware ridondante. Sappiamo che questo è possibile, ma è presenti eventuali inconvenienti a ciò che è necessario conoscere di?
A Questa è una pratica piuttosto comune, ma esistono alcuni svantaggi che potrebbe essere necessario per il controllo all'esterno.
Il primo problema viene licenze. La licenza per l'istanza di SQL Server che ospita il database mirror è disponibile se per tale scopo viene utilizzato solo l'istanza. Appena si crea uno snapshot di database al database mirror o si fare nulla con tale istanza di SQL Server, è necessario acquistare una licenza.
Quanto si riferisce il controllo di coerenza, eseguirle nel database mirror (tramite uno snapshot di database non consentono le garanzie relative la coerenza del database principale. Solo i record di registro delle transazioni sono mirroring tra i database, pertanto se il sottosistema di I/O corrupts una pagina di database principale, tale danneggiamento verrà non sottoposti a essere mirroring al database mirror. Ciò significa che un controllo di coerenza del database mirror non sarebbe visibili pagina danneggiato al database principale.
Reporting è un buon Utilizzo di un mirror del database. Il problema primo che gli utenti eseguono in è come aggiornare lo snapshot di database che eseguono i report con. Impossibile aggiornare uno snapshot di database in modo che deve essere creato un nuovo snapshot e l'applicazione di reporting necessario connettersi al nuovo snapshot. Questa operazione richiede logica aggiuntiva per essere incorporate per l'applicazione di reporting. Il secondo problema consiste nel determinare comportamento applicazione reporting in caso di failover del mirroring, deve rimanere connesso al nuovo principale oppure deve spostare nuovo mirror? Corso in dettagli su questo esula dall'ambito di questo articolo.
Utilizzando qualsiasi aggiuntive al database mirror, il problema principale è la possibilità che causa problemi di prestazioni sul mirror. Creazione di uno snapshot di database aggiunge aggiuntivo carico di I/O nella prima volta una pagina di modifica nel database mirror, deve essere inserito nello snapshot di database. Inoltre, qualsiasi carico di lavoro è inserire su snapshot di database aumenta il carico / O nel database mirror, come numerose pagine lette lo snapshot di database verrà effettivamente da leggere dal mirror database, come verrà non hanno modificato poiché lo snapshot è stato creato.
Questo carico di I/O aggiuntivo al database mirror può rallentare la riesecuzione del record di registro delle transazioni e a sua volta lead per un backlog. Il backlog è denominata coda di ROLLFORWARD e indica la quantità di log delle transazioni deve essere riprodotto prima che il database mirror è connessa dopo un failover. Maggiore sarà il carico di I/O sul database mirror da snapshot database maggiore che nella coda di ROLLFORWARD potenzialmente aumenterà e il più il database non è disponibile dopo un failover.
È segnalare che la lunghezza della coda ROLLFORWARD è in genere prossima a zero in modo che questo potrebbe non essere un problema, ma è indubbiamente qualcosa per il controllo all'esterno in modo costretti compromettere la disponibilità del database di sua per eseguire i report la possibilità di aumentare.
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, disponibilità elevata e manutenzione dei database ed è in un normale relatore a conferenze di tutto il mondo. Blog ha in SQLskills.com/blogs/paul, e si trovino lui su Twitter in Twitter.com/PaulRandal.