SQL Q & A: Compattazione, crescita e riprogettazione dei database

Articolo
08/26/2016

I database SQL sono disponibili in tutte le forme e le dimensioni e gli schemi. Questo mese, aiuta a nostro esperto SQL con riduzione, crescita e riprogettazione dei database.

Paul S. Randal

Il database di compattazione incredibile

D: È talvolta stiamo obbligati a eseguire la compattazione nel nostro database a causa della mancanza di spazio su disco, anche se è possibile stabilire tale operazione può causare problemi di prestazioni. Si occupano di successivamente qualsiasi frammentazione dell'indice. Può spiegare perché la compattazione sembra eseguita molto più lentamente di alcuni database di altri, anche quando sono di dimensioni simili?

R: Sono felici che sei cognizant di effetti collaterali di eseguire la compattazione di una database. È possibile realizzare anche che a volte è sufficiente inevitabile.

Attività simultanee database e lo schema delle tabelle del database può influire sul runtime di un'operazione di compattazione del database. Ciò significa che due database di dimensioni uguali ma con schemi diversi possono assumere notevolmente diverse quantità di tempo per la compattazione.

Accorciare funziona di spostamento delle pagine del file di dati da consolidare spazio alla fine del file, quindi restituisce il file System (riducendo la dimensione del file Data). Per spostare una pagina del file di dati, SQL Server deve acquisire un blocco esclusivo sulla pagina. Ciò significa che nessuno può avere qualsiasi blocco o tutti i record nella pagina. Se è presente l'attività simultanea del database comporta l'acquisizione di blocchi, la compattazione potrebbe essere bloccato e quindi di dover attendere il blocco deve. In questo modo riduzione richiedono più tempo per eseguire più se vi è un'altra attività nel database.

Un altro fattore durante la compattazione si sposta una pagina del file di dati è se dispongono di altre strutture di database fisici puntatori ai dati della pagina da spostare, è necessario aggiornare tali puntatori fisici con il nuovo percorso della pagina. Questo non è un problema, tranne quando una tabella è un heap (non con indice cluster) e/o quando una tabella ha una o più colonne LOB (line-of-business) archiviati fuori riga (separato dal record di dati di tabella).

Quando una tabella è un heap, tutti gli indici non cluster nell'heap contengono puntatori fisici per i record della tabella dati. Quando la compattazione si sposta una pagina di dati dalla tabella, gli indici non cluster devono essere aggiornati. SQL Server viene eseguita la chiamata in Query Processor per eseguire la manutenzione indice di indici non cluster 100 righe alla volta.

Quando una tabella include dati LOB off riga, i record di dati scegliere di disattivare la riga di dati LOB. Non c'è tuttavia nessun back-puntatore dati LOB per i record di dati. Ciò significa che quando la compattazione si sposta una pagina di testo (contenitore disattiva riga LOB dati) tutti i record di dati che fanno riferimento a dati LOB della pagina devono essere aggiornati. Sono presenti non puntatori di backup, eseguire una scansione della tabella per trovare i record di dati corretti per l'aggiornamento. Come è facile immaginare, questo processo può essere piuttosto lento per una tabella con una grande quantità di dati LOB.

Sebbene la compattazione può essere lenta, da SQL Server 2005 poi viene fornita attraverso la colonna percent_complete della vista di gestione dinamica sys.dm_exec_requests relazioni sull'avanzamento. È inoltre possibile monitorare il contatore delle prestazioni Riduci spostamento dati byte/sec dell'oggetto prestazioni database per visualizzare la velocità è in corso la compattazione.

IT consentono di aumento automatico

D: Sono un amministratore di database nuovi e ho state leggendo una grande quantità di informazioni in linea sulle procedure consigliate per le impostazioni del database. Sto confuso dalle viste in conflitto se aumento automatico delle dimensioni deve essere abilitato o meno. È possibile semplicemente disattivare tale senza causare problemi?

R: La semplice risposta è consigliabile attivare aumento automatico, ma non si basano su di esso. La procedura generale è monitoraggio delle transazioni e dati registro file di dimensioni/utilizzo e preventivamente crescere li (o analizzare aumento improvviso, imprevisto). Attivazione automatica aumento per i casi di emergenze quando chiunque ha il compito di monitorare l'utilizzo delle dimensioni file/non è immediatamente disponibile per gestire i file.

Se aumento automatico delle dimensioni non è attivata per il log delle transazioni, di file e i riempimenti di file, quindi tutti scrivere attività sul database saranno impedite finché non viene reso disponibile nel log delle transazioni più spazio. E se aumento automatico delle dimensioni non è attivata per i file di dati, le operazioni di inserimento o operazioni di manutenzione del database come la ricostruzione degli indici potrebbe non riuscire.

La parte difficile è capire l'impostazione di aumento automatico. Da poi SQL Server 2005, l'aumento automatico predefinito per i file di registro delle transazioni è 10% e 1 MB per i file di dati. Tuttavia, un aumento automatico basate su percentuale significa che come espandere i file, pertanto, è il livello di aumento automatico. Questo significa anche se possono ampliare i tempi si Don ’t hanno attivato l'inizializzazione file immediata. Entrambi i tipi di file dovrebbero pertanto essere un aumento automatico fisso crescere in modo automatico comportamento è prevedibile.

Un estremamente grande o basate su percentuale aumento automatico può essere particolarmente problematica per i file di registro delle transazioni. L'inizializzazione file immediata non è un'opzione file appena allocato spazio deve essere inizializzate su zero. Mentre viene zero inizializzazione eseguita, tutte scrivere attività nel log delle transazioni è bloccato. Pertanto è opportuno bilanciare la transazione log file aumento automatico è grande abbastanza tale operazioni possono continuare per un periodo di tempo, ma non troppo grande che il flusso delle operazioni viene interrotta per troppo tempo.

Per i file di dati, un aumento automatico di 1 MB è inutile piccola, ma è difficile determinare il valore corretto. Ciò dipende se si desidera aumento automatico a una misura di emergenza ripiego o sostituire la gestione manuale dei dati dei file di dimensioni. Si basa inoltre su spazio nuovo è necessario che ogni giorno per contenere i dati da inserire nel database. La conclusione è: Si deve attivare l'aumento automatico e impostarlo su un importo appropriato, percentuale non.

Schema di archiviazione

D: Sto riprogettare nostro schema di database in modo più efficiente le query. Alcune tabelle coinvolte hanno una grande quantità di dati di tipo carattere e si desidera assicurarsi che sta memorizzando nel modo più efficiente. Esistono istruzioni o procedure consigliate che è possibile condividere?

R: Il metodo scelto memorizzare i dati LOB può avere un notevole impatto sulle prestazioni di query, pertanto è essenziale scegliere la tecnica più appropriata. Un'analisi dettagliata di tutte le opzioni esula dall'ambito di questo articolo, ma di seguito sono riportate alcune indicazioni:

Innanzitutto, i dati sarà sempre inferiore a 8.000 byte? In tal caso, provare a un tipo di dati char (n) o (n) varchar, ma non uno dei tipi di dati LOB true come XML, (n)varchar(max), varbinary (max), testo (n) o immagine a meno che non sia assolutamente necessario. Se è necessario un tipo di dati LOB true a causa delle dimensioni dei dati, non utilizzare text (n) o image come dati di questi tipi sono obsolete in SQL Server 2005. Non sono funzionalità di altri, più recenti tipi di dati LOB.

In secondo luogo, se è necessario un tipo LOB true, considerare se memorizzare i dati nella riga (nella stessa tabella record di dati come colonne della tabella) o disattivare la riga (memorizzati nelle pagine del file di dati separato con un collegamento dal record di dati di tabella). Se si utilizzano frequentemente i dati LOB, sarebbe preferibile memorizzarlo nella riga come query possono recuperare in modo più efficiente. In caso contrario, è preferibile memorizzare disattivare la riga. Query occasionali pagare un costo leggermente superiore per recuperare i dati LOB, ma i record di dati saranno di dimensioni inferiori con denser archiviazione dati e in generale migliori le prestazioni delle query. Si noti che è possibile solo dati LOB nella riga superiore a 8.000 byte o qualsiasi quantità è possibile dato altre colonne nel record di dati, dopo la quale dispone automaticamente spostati fuori riga.

Terzo, se una tabella contiene una colonna LOB, operazioni sugli indici in linea non sono consentiti per gli indici che includono colonne LOB. Per definizione, questo influisce l'indice cluster della tabella. Per questo motivo, alcune persone memorizzano i dati LOB in una tabella separata completamente (partizionamento verticale da questa colonna LOB) e quindi eseguire un'operazione di JOIN tra la tabella principale e la tabella LOB quando i dati LOB sono richiesto da una query. Questo comporta un po' più memoria a causa della complessità del JOIN, ma consente più scelta della strategia di manutenzione indice.

Inoltre potrebbe essere preoccupati a larghezza fissa e tipi di dati a lunghezza variabile e possibilmente anche richiedono rapido accesso ai dati di flusso, nel qual caso è necessario considerare il tipo di dati FILESTREAM di SQL Server 2008. Per un'analisi più approfondita di tutti i tipi di dati LOB archiviazione, vedere il blog post, “ importanza della scelta di destra LOB archiviazione tecnica ”.

Controllo critici e saldi

D: Sto rielaborando le procedure di manutenzione del database alla nostra società e ho intenzione di avviare l'esecuzione di controlli DBCC nel nostro database critici. Frequenza è consigliabile eseguire un controllo su ciascun database?

R: Verifiche della coerenza proattiva sono una parte fondamentale di qualsiasi piano di manutenzione database completa, ovvero per i database utente e di sistema. È importante utilizzare un metodo di verifica della pagina. Per i database di SQL Server 2005 e versioni successive, è necessario attivare i checksum di pagina. Per i database di SQL Server 2000, utilizzare la rilevazione delle pagine incomplete.

Dal punto di vista a come sono interessate verifiche della coerenza, è difficile fornire una risposta assoluta per frequenza di eseguirli. In genere, è consigliabile eseguirle spesso possibile, almeno una volta alla settimana. La frequenza ottimale della verifica della coerenza è un classico “ dipende. ”

Ecco alcuni dei fattori da considerare:

Innanzitutto, qual è il periodo di manutenzione? Verifiche della coerenza richiedono grandi quantità di CPU, memoria e risorse di I/O, pertanto se la finestra di manutenzione di quando riserva queste risorse è inferiore al tempo necessario per eseguire tutte le verifiche della coerenza, potrebbe non essere in grado di controllare tutti i database contemporaneamente. Potrebbe essere necessario scaglionare le verifiche di coerenza su un'intera settimana o addirittura offload verifiche di coerenza su un sistema non di produzione (per ripristinare un backup e l'esecuzione di verifiche della coerenza sul database ripristinato).

In secondo luogo, come stabile è il sottosistema di I/O in cui sono archiviati i database? Se il sottosistema di I/O si verificano problemi, puoi eseguire verifiche di coerenza spesso possibile per ottenere l'indicazione possibile prima di danneggiamento. Nella mia esperienza più pervasiva, più tempo che passi inosservato danneggiamento Ottiene e la più difficile consiste nel ripristinare il database durante la riunione di Recovery Point Objective e Recovery Time Objective.

La conclusione è che è e il livello di comfort. In agosto 2009, condotto un sondaggio sul mio blog e di 276 intervistati, 37% settimanalmente eseguire verifiche di coerenza e un ulteriore 25 per cento di eseguirli ogni giorno. È possibile visualizzare i risultati completi della mia sondaggio con molte più informazioni su intuire la frequenza controllo www.sqlskills.com/BLOGS/PAUL/post/Importance-of-running-regular-consistency-checks.aspx .

Grazie a Kimberly l. Tripp di SQLskills.com per propria revisione tecnica di mese questo.

Paul Randal

Paul s. Randal è direttore gestione di SQLskills.com, un direttore regionale Microsoft e MVP per SQL Server. Ha lavorato nel team SQL Server Storage Engine in Microsoft dal 1999 al 2007. 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 ripristino di emergenza, disponibilità elevata e la manutenzione del database ed è un normale relatore a conferenze in tutto il mondo. Il suo blog all'indirizzo SQLskills.com/blogs/paul ed è possibile trovare lui Twitter in Twitter.com/PaulRandal.