R: domande e risposte su SQL riprodurre la scheda indice

Articolo
08/24/2016

Gli indici SQL possono risultare problematici ma, se si sottopongono a monitoraggio costante evitando eventuali problemi, la gestione dovrebbe rivelarsi più semplice.

S. Paul Randal

L'amministratore del database accidentali

D. Mi "DBA non ufficiale" alla propria azienda. Si utilizza SQL Server in più e dispone di istanze di SQL Server nascono dell'azienda e che dobbiamo un amministratore del database effettivo per aiutare. Mentre ci stiamo assunzione di un utente, è necessario essere in grado di capire che cosa sta apportando che alcune istanze più lenti. Si dispone di eventuali raccomandazioni generali per dove dovrei iniziare la ricerca?

**R.**Si tratta di un'ottima domanda. Può essere frustrante per un'istanza di SQL Server non viene eseguito correttamente e non sa dove inizia la ricerca della causa. Sono disponibili tutti i tipi di elementi che potrebbero contribuire a una riduzione delle prestazioni, pertanto sempre più semplice richiedere semplicemente a SQL Server stesso.

SQL Server registra due set di informazioni: le statistiche e le statistiche dei / O. Questi devono fornire una buona idea di dove si trova il problema.

La maggior parte delle installazioni di SQL Server i giorni selezionati sono associate ai / O-(vale a dire che le prestazioni sono vincolata da qualcosa a che fare con la lettura e scrittura dei dati). Il rallentamento potrebbe essere un sottosistema dei / O lento, una rete lenta di una rete SAN la connessione al server, memoria insufficiente nel server che sta forzando SQL Server per trasferire le pagine dentro e fuori di memoria, la strategia di indicizzazione scadente o una serie di altre cose.

È possibile utilizzare sys.dm_io_virtual_file_stats la vista a gestione dinamica (DMV) per vedere quali SQL Server sa sui / O volume, gabinetti e i ritardi dei / O ai file di dati e di log. È possibile trovare il sottosistema dei / O non è l'area sensibile, ma i/O potrebbe risultare ancora il problema. Il sottosistema dei / O può essere sufficientemente copia con il carico dei / O.

Si tratta in cui l'altra parte del puzzle è disponibile in: statistiche relative all'attesa. Tiene traccia di SQL Server ogni volta che un thread di esecuzione è in attesa di una risorsa disponibile e per quanto tempo il thread era in attesa. È anche possibile lavorare fuori per quanto tempo il thread ha dovuto attendere dopo la notifica della disponibilità della risorsa, ma prima di essere in grado di eseguire su una CPU. Tramite l'aggregazione di questi dati, è facile vedere le principali aree causando attese per SQL Server. In questo modo un puntatore in cui inizia la ricerca della causa.

Si tratta di una visualizzazione ad alto livello di questa metodologia. Per una trattazione più approfondita, incluso uno script che è possibile utilizzare, leggere il post del blog "Statistiche relative all'attesa.” Include inoltre i risultati di un sondaggio tra i lettori di più di 1800 istanze di SQL Server e i tipi di attesa più diffusi con una spiegazione. Conclusione: evitare di perdere tempo poking intorno in SQL Server fino a quando non è stato richiesto in modo che SQL Server riflette sul problema.

Analisi degli indici mancanti

D. Appena ho scoperto mancante DMV indice. A questo punto si informa ho centinaia di indici su una sola istanza di SQL Server mancanti. È sufficiente creare tutte le copie o che sta per causare problemi?

**R.**Non creare immediatamente tutti gli indici senza prima effettuare alcune analisi. Query processor di SQL Server 2005 e versioni successive consente di determinare se un indice trarrebbero vantaggio del piano di una query (o batch o stored procedure). Questa operazione viene eseguita durante la compilazione del piano di query.

Ogni volta che viene stabilito che un indice mancante, note di tale fatto. Garantisce anche il conteggio del numero di ogni indice mancante Impossibile è stato utilizzato, con il miglioramento previsto nel piano di query fosse tale indice esistente al momento che è stato compilato il piano di query.

È possibile accedere a tutte queste informazioni tramite tre viste a gestione dinamica (db_missing_index_groups, db_missing_index_group_stats e db_missing_index_details). È inoltre disponibile un DMV che comunica con le colonne in una tabella di indici mancanti (sys.dm_db_missing_index_columns). I primi tre viste a gestione dinamica sono più comuni. Query su di esse tra loro per il modo più semplice ottenere tali dati. Fernando caro ampiamente utilizzati script "Si sta utilizzando mancante indice DMV SQL?” può anche aiutare.

Si tratta di informazioni importanti, ma è necessario adottare con prestare particolare attenzione. In primo luogo, non vi è un potenziale bug nell'indice manca DMV. Utili non è presente un indice che effettivamente esiste. Questo errore verrà corretto nella prossima versione di SQL Server. Per ulteriori informazioni su di esso nel mio post di blog, "Bug di DMV indice mancante che potrebbe costare impazzire.”

In secondo luogo, il meccanismo che determina un indice mancante nella query processor considera solo se un indice è utile per la query in fase di compilazione. Essa non tiene in considerazione l'impatto sulle prestazioni possibili per le operazioni di inserimento/aggiornamento/eliminazione devono mantenere tale indice. Potrebbe essere notevole se la tabella include proporzionalmente molte altre modifiche di legge. Non considerare anche le dimensioni dell'indice creato. Che è un compromesso che solo è possibile apportare.

Infine, viene cercato l'indice assoluto migliore aiutare il piano di query in fase di compilazione. Ad esempio, può essere una tabella con una query che richiede 25 delle colonne della tabella e un indice cluster e 30 colonne. Il meccanismo di determinazione indice mancante sarebbe consigliabile creare un indice non cluster per coprire la query di 25-colonna. Nella maggior parte dei casi, che non avrebbe senso.

Utilizzare script di Duncan per esaminare l'output di aggregati indici mancanti. Quindi osservare gli indici di 10 o 20 superiore ed effettuare alcune analisi per determinare se in realtà che vale la pena di creazione. La maggior parte dei casi, sono disponibili alcune che non sono che vale la pena di creazione, pertanto è sempre opportuno eseguire l'analisi.

Non possiamo tutto senza arriveremo?

D. È uno dei team di amministratori di database nella nostra azienda che si occupa di vari team di sviluppo dell'applicazione. Non c'è la costante animosity tra i team. È dannosa per l'ambiente di lavoro. Hai suggerimenti per come sfumare le relazioni tra i team?

**R.**Si tratta di un problema comune che può rendere spiacevole ad animosity e grudges a non considerare attendibile un ambiente di lavoro. Nessuno ne consente la produttività e ne risente della società. Fortunatamente, è una soluzione. È semplice da descrivere, ma più difficili da mettere in pratica:

È necessario istruire tra loro. Ogni team deve comprendere le motivazioni del team e le loro opinioni sono i limiti di responsabilità. Sarà sorprendente di ciò che ogni team ritiene che opportuno eseguire l'altro team.
Ogni team deve comprendere i punti deboli per gli altri team. Per farlo in modo anonimo, senza apportare cose personali.
Ogni team deve quindi educare gli altri team su come il lavoro che agli altri team si influisce sulla loro. Si supponga, ad esempio, il dev team scrive del codice, solo i test con un piccolo insieme di dati e quindi genera sulla parete nell'ambiente di produzione - e straordinaria non riesce. Se il team di sviluppo prevede che il team di DBA per risolvere i problemi e correggere il codice, che è chiaramente un processo interrotto.

Riconoscimento e la comprensione del problema è l'unico modo per motivare entrambi i lati fino al raggiungimento di una soluzione che renderà l'ambiente di lavoro pacifici e produttivi nuovamente.

Vexing di indicizzazione

D. Io sono un amministratore di SharePoint e so anche una quantità notevole su SQL Server. SQL Server 2008 che ospita i database di SharePoint ha una grande quantità di frammentazione degli indici. Questa opzione influisce sulle prestazioni di SharePoint. So che non è possibile modificare gli indici, ma cosa che posso fare oltre a dover costantemente ricostruirli?

**R.**Costantemente ricostruzione degli indici comporta un carico notevole sul Server SQL in termini dei / O CPU, generazione di log delle transazioni e risorse potenzialmente bloccano altri processi. Anche in esecuzione il db_index_physical_stats DMV per determinare gli indici frammentati può essere un numero elevato di risorse.

Numero degli indici frammentati in un ambiente SharePoint perché l'utilizzo dello schema di database SharePoint GUID in chiavi di indice cluster. Mia moglie, Kimberly relativi a questo nel suo post di blog "GUID come chiavi primarie e/o la chiave di clustering.”

Quando un indice è ciò che è essenzialmente una chiave casuale, inserimenti indice verificano casualmente e portare a un processo denominato suddivisioni di pagina. Una frammentazione cause divisione di pagina, che è un'attività costosa elaborare (vedere il blog di registrare "Costo è pagina viene divisa in termini di log delle transazioni?”). Divisione di pagina si verifica quando una pagina è piena, ma è necessario spazio in tale pagina (ad esempio, quando un'operazione di inserimento avviene in un indice con un valore di chiave casuale che deve essere memorizzato in tale pagina). Una nuova pagina viene allocata e circa la metà dei record nella pagina completa vengono spostata nella nuova pagina, creando così lo spazio disponibile. Questo è il processo di base.

Non è possibile modificare gli indici in un database di SharePoint, che comporta l'interruzione del contratto di assistenza. Tuttavia, è possibile modificare il fattore di riempimento predefinito. Quando si crea o ricostruzione un indice, è possibile impostare SQL Server per mantenere una certa quantità di spazio libero nelle pagine di indice per consentire inserimenti casuali. Ciò significa che è più probabile che le pagine di indice dispongano già di spazio su di essi per i nuovi record senza richiedere la divisione di pagina costosi. Imposta un fattore di riempimento pari a 80 indica le pagine verranno inserite 80 per cento della capacità quando l'indice viene ricostruito, lasciando al 20% di spazio libero.

La domanda diventa quindi, "Che cos'è il fattore di riempimento migliore?" Sfortunatamente, non esiste una risposta valida. Per un data warehouse in cui i dati non vengono modificati e non vi è alcuna attività di inserimento delle transazioni in linea (OLTP) di elaborazione, il fattore di riempimento migliore corrisponde in genere al Server SQL pari a 100 (ovvero spazio libero).

Per un ambiente OLTP, la risposta dipende dalla velocità con cui si verifica la frammentazione e la frequenza con cui Rigenera per rimuovere la frammentazione. È consigliabile iniziare con 70 (30% di spazio libero) e monitorare la frammentazione per verificare se è necessario deformare verso l'alto o verso il basso, o un indice analitico manutenzione più o meno frequentemente.

Paul S. Randal

S. Paul 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 l'istruzione 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, alta disponibilità e la manutenzione di database ed è un normale relatore a conferenze in tutto il mondo. Disponibile in /blogs/paul ed è possibile trovarlo nei movimenti in twitter.com/PaulRandal.