SQL Q & a: Velocità e dimensioni non trascurabili

La velocità relativa delle query di SQL Server e le dimensioni dei file di dati e dei log delle transazioni sembrano rappresentare fattori di primaria importanza per le prestazioni.

Paul S. Randal

Piano di Query

D. Recentemente abbiamo avuto un problema con una query che stava prendendo un po ' di tempo per eseguire. La query correva più veloce dopo abbiamo ricostruito l'indice cluster della tabella che è coinvolto. Non ho potuto vedere tutte le scansioni nel piano di query, quindi perché avrebbe frammentazione colpito il runtime query?

**R.**In questo caso, sembra che la frammentazione dell'indice non aveva nulla a che fare con il runtime query. Un piano di query non ottimale era probabilmente la fonte del problema.

Quando fai la ricostruzione di un indice, tutte le statistiche di colonna per tale indice vengono ricostruite automaticamente. È l'equivalente di una scansione completa. Tutti i valori di colonna sono considerati quando si creano le statistiche, quindi crea una rappresentazione completa della distribuzione del valore comune.

Tutti i piani di query creati utilizzando le statistiche sono essenzialmente invalidati e vengono ricompilati. Ricompilare un piano significa la prossima volta che si esegue la query, query optimizer passerà attraverso il processo di scelta di un modo nuovo, più ottimo di produrre i risultati della query. Questo è il piano di query.

In questo caso, la ricostruzione dell'indice ha attivato una ricompilazione del piano di query. Il nuovo piano era più ottimo rispetto al precedente. Ci sono un certo numero di possibili ragioni per questo. Il piano di query scarsamente performanti avrebbe potuto essere altamente ottimo e query veloce consentita quando fu compilato. Come la distribuzione dei valori di dati all'interno della tabella è cambiato nel tempo, il piano di query potrebbe diventare meno ottimo.

Il vecchio piano di query potrebbe utilizzano un indice non cluster basata sul fatto che una determinata colonna (parte dell'indice non cluster) era altamente selettiva. Pertanto, ha senso utilizzare l'indice non cluster per trovare i valori dei dati e quindi ulteriori colonne dalla tabella stessa. Che ha chiamato un'operazione di ricerca della chiave.

Se la distribuzione di dati cambiò drasticamente tale che la colonna non era altamente selettiva, questo potrebbe aver causato un gran numero di costose ricerche chiave. Considerando la nuova distribuzione di dati, un piano migliore sarebbe stata quella di utilizzare un indice non cluster diverso.

Quando è stato ricostruito l'indice cluster, sono state aggiornate le statistiche. Questo causò una ricompilazione del piano, che ha scelto l'indice non cluster più selettivo. Questo a sua volta ha prodotto un piano più efficiente.

Mentre io sto ipotizzando circa la causa dell'accelerazione di query, si vede quello che voglio dire circa la ricostruzione indice semplicemente essere l'innesco per la ricompilazione del piano. Potrebbe non avere fissati direttamente la causa principale del problema di prestazioni in primo luogo.

Più file, più spazio

D. Ho un filegroup con due file, ed entrambi sono molto completo. Voglio aggiungere qualche spazio in più per il filegroup, così ho intenzione di aggiungere due ulteriori file e quindi avere SQL Server riequilibrare i dati attraverso tutti e quattro i file. È possibile?

**R.**Purtroppo, non è un buon modo per riequilibrare i dati attraverso i file nel filegroup dopo l'aggiunta di nuovi file di spazio extra. Ho bloggato in passato su come avere più di un file per ogni filegroup può portare a miglioramenti delle prestazioni per alcuni carichi di lavoro. È risaputo che questo è il caso.

Che è una grande generalizzazione, però. Quanto guadagno potrai raggiungere dipende il sottosistema dei / o, il layout del file di dati e il carico di lavoro. Ci sarà un punto in cui il numero di file di dati diventa troppo ed è in realtà un danno prestazioni. Check out questi post su più file di dati di benchmarking e più file di dati su dischi a stato solido (SSD).

SQL Server semplicemente non ha un meccanismo di riequilibrio per i dati in un filegroup. Il file di dati dove l'assegnazione successiva verrà da è determinato dalla ripartizione di round robin e riempimento proporzionale. Round robin è dove le allocazioni capita a sua volta da ciascun file di dati. Ci sarà un'allocazione file uno, quindi un'allocazione dal file due, poi torna a file uno nuovo. Tuttavia, le assegnazioni sono fatte in maniera proporzionale. Dal file di dati sono proporzionalmente più spazio libero di altri file di dati nel filegroup vengono effettuate ulteriori allocazioni.

La premessa di base di riempimento proporzionale è che ogni file ha un peso, dove file con meno spazio avranno una maggiore ponderazione. I file con un sacco di spazio libero avrà un peso inferiore. I file con ponderazioni inferiori saranno assegnati da più frequentemente, significato di quei file con più spazio libero sarà assegnato dai più.

Tutto questo significa che quando si aggiungono nuovi file a un filegroup pieno, allocazioni successive verrà principalmente da nuovi file. Essi avranno molto ponderazioni proporzionale-riempimento inferiore rispetto ai vecchi file che sono intrinsecamente più dati. I nuovi file diventano punti caldi di allocazione, che porta a prestazioni globali potenzialmente inferiore con alcuni carichi di lavoro.

Non è possibile aggirare l'algoritmo di riempimento proporzionale. Non è possibile modificare le ponderazioni. Anche cercando qualcosa come ricostruire gli indici nel filegroup non funzionerà, come gli stanziamenti per i nuovi indici verrà dal nuovo file di dati.

Se si desidera aggiungere più file a un filegroup, il modo migliore è creare un nuovo filegroup con più file. Quindi spostare i dati di tabella e indice nuovo filegroup utilizzando l'indice di creare... CON (DROP_EXISTING = ON) comando, specificando il nuovo filegroup come percorso di destinazione. Dopo aver spostato tutti i dati, è possibile rilasciare il filegroup vecchio, vuoto. È anche possibile spostare dati line-of-business al nuovo filegroup, utilizzando qualche trucco da Kimberly Tripp.

Cancellare il registro

D. Recentemente ho avuto un problema con un file di log delle transazioni è cresciuto molto grande. Ho potuto per ridurla. Può suggerire alcune cose per me per controllare?

**R.**Ci sono due cause più comuni di una consistenza del database controllo SHRINKFILE (DBCC) non funziona correttamente sul log delle transazioni. Come nota laterale, compattazione di un file di log non introduce danneggiare prestazioni frammentazione dell'indice in modo che non una compattazione del file di dati. Tuttavia, dovrebbe essere ancora un'operazione rara.

Un file di log ridursi semplicemente rimuove qualsiasi parti inattive o attualmente inutilizzati della transazione registrare alla fine del file di log delle transazioni. Queste "parti" del log delle transazioni sono noti come file di log virtuali (VLF). Ci sono due problemi che possono impedire di essere in grado di compattare VLF: non eseguire l'operazione reale che consente VLF diventano inattivi e non avendo VLF inattivi alla fine del log delle transazioni.

VLF diventano inattivi attraverso un processo noto come "il Registro di compensazione". Si può fare con un checkpoint se si utilizza il modello di recupero semplice. È anche possibile farlo con un backup del log delle transazioni se utilizzando i modelli di recupero con registrazione completa o con registrazione di massa. Come i record del log delle transazioni nella VLF non sono richieste da SQL Server in qualche modo, è possibile rendere la VLF inattivo.

SQL Server può ancora richiedere il log registra per determinate situazioni, come se sono parte di una transazione lunga, se essi non sono analizzati mediante il processo dell'agente di lettura Log replica, o se sono in procinto di essere inviati a un database mirror o replica del gruppo di disponibilità. Potete chiedere a SQL Server perché un log delle transazioni specifico non "Cancella" utilizzando il seguente comando:

SELECT [log_reuse_wait_desc] FROM sys.databases WHERE [name] = N'MyDBName';

Utilizzare l'output di tale comando come indicatore di cosa fare dopo. Una volta che il log delle transazioni è in grado di cancellare, se DBCC SHRINKFILE non è ancora in grado di compattare il registro, questo significa che è stato solo in grado di ridurre fino a VLF attivo (o VLF). Questi potrebbe accadere di essere nel bel mezzo del file di log delle transazioni. In tal caso, eseguire nuovamente l'operazione di cancellazione del registro e poi un altro strizzacervelli.

Potrebbe essere necessario fare un paio di volte e, in ultima analisi, potrebbe essere difficile o impossibile compattare il log delle transazioni alla sua dimensione minima su un database di produzione occupato. Tuttavia, affrontare questi problemi comuni dovrebbe aiutare si compattano la transazione file di registro abbastanza soddisfatti. Potete leggere di più su questi argomenti nel mio febbraio 2009 TechNet Magazine articolo, "comprensione registrazione e il ripristino in SQL Server."

Integrità dei / o

D. Continuo a vedere i messaggi nel Registro di errore di uno dei miei istanze di SQL Server, i/o deve essere provato diverse volte prima di riuscire. Questo aspetto inquietante per me. Può spiegare che cosa significano i messaggi?

**R.**Questi messaggi sono istanze di messaggio 825. Questo messaggio è stato introdotto in SQL Server 2005. È un allarme che il sottosistema dei / o ha problemi di integrità.

Se SQL Server rilascia una lettura I/O e viene a mancare i I/O (o il sistema operativo indica a SQL Server non è riuscita il / o, o dati restituiti dal sistema operativo sono giudicati da SQL Server come corrotti), SQL Server ritenterà l'operazione di lettura altre quattro volte per vedere se uno di loro avrà esito positivo. La premessa per questo è che a volte sottosistemi I/O hanno difetti transitori, quindi riprovare un errore i/o potrebbe funzionare su un tentativo successivo. Questo evita la possibilità immediata di inattività.

Se nessuno il tentativo riesce, SQL Server genera un errore 823 o 824, e il collegamento è rotto (come questi errori sono gravità 24). Se uno il tentativo riesce, il carico di lavoro continua come normale, e SQL Server scrive il messaggio 825 il log degli errori.

Il messaggio 825 ha il seguente formato:

Msg 825, Level 10, State 2, Line 1.

Questo significa che una lettura del file "J:\SQLskills\MyDatabase_DF1. NDF"a offset 0 × 000004AA188000 è riuscito dopo aver fallito una volta con l'errore: checksum errato (previsto: 0 × 33d1d136; effettivo: 0 × 0a844ffd). Altri messaggi di log di SQL Server error log e sistema evento potrebbero fornire ulteriori dettagli.

Questa condizione di errore minaccia l'integrità del database e sarà necessario correggere la situazione. Completare un DBCC CHECKDB completo. Questo errore può essere causato da molti fattori. Per ulteriori informazioni, vedere SQL Server Books Online. Che cosa è veramente dire è che il sottosistema dei / o sta cominciando a fallire. Una struttura simile esiste in Exchange Server, in cui ha avuto origine l'idea per questo meccanismo.

Anche se questa funzionalità è utile, il messaggio 825 è solo gravità 10 (che significa informativo). A meno che non hai stai guardando attraverso il log di errore o un avviso di agente per messaggio 825, questi messaggi critici possono passare inosservati. Tuttavia, si dovrebbe avere un avviso per 825 messaggi e agire, non appena si verifica un messaggio di tentativi di lettura. Potete leggere di più su questo messaggio e sull'impostazione di un avviso di prenderlo in questo blog post.

Paul S. Randal

Paul S. Randal è l'amministratore delegato di SQLskills.com, Microsoft regional director e MVP per SQL Server. Il team di Microsoft SQL Server Storage Engine dal 1999 al 2007 ha lavorato. 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. Ha blog a SQLskills.com/blogs/paul, e lo si può trovare su Twitter a Twitter.com /PaulRandal..

Contenuti correlati