Una delle variabili più critiche per le prestazioni del database è la frammentazione dell’indice SQL. Possiamo trovarci di fronte a blocchi, deadlock, problemi di IO, problemi di fuoriuscita del disco se il livello di frammentazione non è gestito in modo appropriato. Può anche portare a un piano di esecuzione improprio.
DMVs
In SQL Server, possiamo usare il DMV sys.dm_db_index_physical_stats per ottenere informazioni sul livello di frammentazione dell’indice, il numero di pagine e i tipi di indice.
SSMS
Possiamo anche usare i rapporti integrati di SQL Server ‘Index Physical Statistics’ in SSMS
Otteniamo le statistiche fisiche degli indici per il database nel seguente formato.
Siamo limitati a eseguire questo rapporto a livello di database, e se abbiamo più database nell’istanza e molte istanze da gestire, diventa un compito che richiede tempo e risorse per raccogliere i dettagli, analizzare lo stato basato sulla nostra soglia di stato e poi decidere sulla ricostruzione o riorganizzazione dell’indice SQL.
Script personalizzati
Possiamo usare anche script personalizzati per rilevare e risolvere i problemi di frammentazione, ma è anche un processo complicato da stabilire e analizzare manualmente i risultati. Potremmo volere tutte queste informazioni in un posto da centralizzare e poi decidere la politica di manutenzione dell’indice. Possiamo usare i comandi Alter Index per rimuovere il livello di frammentazione. Per i piccoli database, non ci preoccupiamo molto del conteggio delle pagine durante la ricostruzione dell’indice, tuttavia per i grandi database dobbiamo considerare anche questi.
Strumenti di terze parti
Possiamo facilmente monitorare, analizzare e rimuovere la frammentazione dell’indice SQL esistente utilizzando ApexSQL Defrag. Possiamo gestire più istanze e database in uno strumento grafico. Otteniamo i seguenti vantaggi da questo strumento.
- Fornisce un’interfaccia utente grafica per monitorare e analizzare la frammentazione dell’indice
- Possiamo fare la manutenzione dell’indice per tutte le istanze di SQL Server e i database
- Offre la possibilità di configurare i rapporti personalizzati in base alle nostre esigenze
- Possiamo configurare le politiche per controllare ed eseguire attività di manutenzione dell’indice secondo le metriche personalizzate
- Possiamo analizzare la frammentazione dell’indice (veloce o profonda), impostare il fattore di riempimento, la manutenzione dell’indice online
- Possiamo preparare i rapporti nei formati desiderati (CSV o HTML)
Una volta installato lo strumento ApexSQL Defrag e configurato un’istanza da monitorare, mostra rapidamente i dettagli dello stato di frammentazione dell’indice SQL.
Dovremmo evitare di ricostruire o riorganizzare gli indici SQL se non è necessario. Se abbiamo indici molto grandi, l’analisi potrebbe richiedere molto tempo e più risorse di sistema. ApexSQL Defrag offre l’opzione di controllare la frammentazione.
- Limited: In questa modalità di scansione, SQL Server raccoglie informazioni limitate sulla frammentazione. Non causa alcun problema di prestazioni al sistema. È la modalità predefinita in ApexSQL Defrag
- Sampled: In questa modalità, prende una porzione campione dell’indice SQL e raccoglie le informazioni sulla frammentazione
- Dettagliata: Otteniamo la conoscenza dettagliata di tutte le pagine dell’indice; potrebbe causare un po’ di overhead alle risorse del sistema, tuttavia, possiamo usarlo se osserviamo l’alta frammentazione con le modalità di scansione sopra
Allo stesso modo, possiamo impostare il livello di frammentazione nella categoria Alta, Media e Bassa.
Puoi anche impostare la tua soglia di frammentazione vinta per analizzare gli indici.
Una volta analizzato il set di risultati, cliccate con il tasto destro del mouse sul particolare indice SQL e potrete avere più opzioni da scegliere.
- Analizza (veloce)
- Analizza (profondo)
- Riorganizza\Ricostruisci\Ricostruisci online
- Imposta fattore di riempimento
- Possiamo mettere in pausa, fermare o annullare qualsiasi lavoro in corso per quell’indice. È utile se vediamo problemi di prestazioni o blocchi dovuti a un particolare indice, possiamo annullare quel lavoro stesso
ApexSQL Defrag permette di creare le politiche dai modelli predefiniti o dai parametri personalizzati. Il modello è anche adatto alla maggior parte dei requisiti di deframmentazione degli indici SQL.
Utilizzando questi modelli, possiamo decidere di deframmentare tutti gli indici o gli indici in base al livello di soglia in modalità offline o online. Per esempio, voglio creare la politica per ricostruire online il 50% degli indici più frammentati oltre il 30% o riorganizzare se frammentati oltre il 10%.
Possiamo anche configurare la soglia delle risorse per quanto riguarda la CPU, il carico di memoria, l’utilizzo del log delle transazioni, le transazioni attive. Non otteniamo questo livello di controllo sulla manutenzione dell’indice usando i lavori di manutenzione guidata o gli script personalizzati.
Se la risorsa è occupata, possiamo impostare anche il tempo di riprova. Ho visto le esigenze di alcuni utenti che non vogliono eseguire la manutenzione dopo che il loro periodo di utilizzo del database magro è finito. Se stiamo facendo la manutenzione, dobbiamo fermare il lavoro manualmente, o dobbiamo creare un altro lavoro dell’agente SQL per fermare il lavoro di manutenzione dell’indice in un momento particolare.
ApexSQL Defrag fornisce l’overhead per configurare e monitorare più lavori. Possiamo personalizzare l’annullamento del lavoro di manutenzione dell’indice dopo un numero ‘N’ di ore.
Nella schermata precedente, si può vedere che possiamo personalizzare ulteriormente l’operazione di manutenzione dell’indice SQL in base al livello di frammentazione, alle dimensioni dell’indice e al numero di pagine. È possibile definire il ritardo dell’oggetto bloccato per un tempo particolare. A volte vogliamo escludere l’indice dove abbiamo conteggi di pagina dell’indice più piccoli o più grandi dei conteggi specificati.
È possibile effettuare le modifiche e configurarle come un modello da riutilizzare. Una volta eseguita la manutenzione dell’indice, nella scheda Attività, si ottiene un’eccellente panoramica delle statistiche di frammentazione dell’indice prima e dopo la manutenzione. È essenziale sapere se il livello di frammentazione dell’indice è sotto controllo o meno dopo la manutenzione. Di solito abbiamo bisogno di catturare di nuovo il livello di frammentazione, ma questa informazione ci risparmia l’overhead.
Nella sezione Rapporti, possiamo visualizzare i rapporti dal livello del server, rapporto generale.
Possiamo anche ottenere una bella vista dei primi 10 indici clusterizzati e non clusterizzati.
Siamo anche interessati a conoscere il comportamento della CPU e della memoria durante la manutenzione degli indici. Possiamo ottenere queste informazioni utili e tenere d’occhio anche le risorse di sistema.
Possiamo esportare tutti i rapporti e configurare l’opzione di esportazione. Potremmo voler estrarre informazioni limitate durante l’esplorazione. ApexSQL Defrag ti dà la possibilità di scegliere quali informazioni visualizzare come grafici, statistiche, grafici statistici dell’indice SQL che vogliamo includere nel rapporto.
Conclusione:
Lo strumento ApexSQL Defrag rende facile per i DBA il compito amministrativo con un migliore monitoraggio e manutenzione degli indici SQL. Si ottengono tutte le informazioni in uno strumento grafico con immagini personalizzate, politiche insieme a vari rapporti informativi.
