Logo principale
PROBLEMA DI HELLEN: Anche se ordini il piatto meno caro, il gruppo deciderà comunque di dividere il conto in parti uguali.
Torna alla pagina iniziale Aggiungi ai Preferiti Area Riservata Contatto
::: Testo | A- | A+ | A0 :::
   Pagina iniziale // SQL Server // Ottimizzazione degli indici
::: Cambia visualizzazione ::: who am i? :::   

Ottimizzazione degli indici

L'utilizzo degli indici ci permette di aumentare in maniera sostanziale le performance delle nostre query. Purtroppo, così come accade alle tabelle ed a tanti altri oggetti, la continua attività di insert, update e delete tende a frammentare la struttura degli indici, facendo perdere loro gran parte dell'efficacia.

Per impedire che gli indici diventino inefficaci è sufficiente che un amministratore della base di dati esegua periodicamente una manutenzione degli indici. La tecnica generalmente utilizzata per reimpostare gli indici è quella di eliminarli (in gergo "dropparli") e ricrearli. SQL Server, come gran parte delle basi di dati attualmente in circolazione, permette di deframmentare gli indici senza bisogno di eliminare e ricreare: semplicemente riorganizza le informazioni annullando così la frammentazione. La riorganizzazione è molto più veloce della ricostruzione fisica e non blocca la tabella in questione, ma ha lo svantaggio di non compattare l'indice al 100%. In SQL Server i due comandi sono:

  • DBCC INDEXDEFRAG – per la riorganizzazione
  • DBCC DBREINDEX – per la ricostruzione

Con questi due metodi ed un po' di logica possiamo creare uno script che, per ogni indice e in base al suo livello di frammentazione, applichi il metodo più pratico ed effettivo per ricompattarlo. Possiamo quindi decidere di riorganizzare un indice con un livello di frammentazione compreso tra il 10 ed il 50%, mentre per quelli meno contigui procederemo ad una ricreazione completa.
Ovviamente i parametri di soglia non saranno codificati (hard coded), ma saranno definiti tramite due variabili all'inizio dello script. Il codice si avvale di tue tabelle temporanee: una per contenere tutti gli indici da analizzare ed una seconda per contenere i risultati delle analisi. La seconda tabella sarà quindi utilizzata per decidere che tipo di deframmentazione va applicata per ogni indice.

 /**
 ** Deframentazione calcolata degli indici
 **/
 SET NOCOUNT ON

 /**
 ** Variabili
 **/
 DECLARE @table_name VARCHAR(255),
        @index_name VARCHAR(255),
        @sql VARCHAR(4000),
        @minp NUMERIC(3),
        @maxp NUMERIC(3)

 set @minp = 10
 set @maxp = 50

  /**
 ** Tabella temporaria per contenere valori sugli indici
 ** da ricostruire:
 **/
 CREATE TABLE #table_index (
  table_index_id INT IDENTITY(1, 1) NOT NULL,
  table_name VARCHAR(255) NULL,
  index_name VARCHAR(255) NULL,
  sql_statement VARCHAR(4000) NULL,
 )

 /*
 ** Inserisce nella tabella tutti i record ricavati da sysindexes.
 ** La query esclude gli indici di tipo testo, gli indici della
 ** tabella stessa e le tabelle dtproperties
 */
 INSERT #table_index (table_name, index_name)
  SELECT c.name + '.' + a.name AS table_name, b.name AS index_name
    FROM sysobjects a
    INNER JOIN sysindexes b
      ON a.id = b.id AND b.indid <> 0
         AND b.indid <> 255 -- indice tipo testo
          AND a.name <> 'dtproperties'
         AND a.type = 'u'
    INNER JOIN sysusers c
      ON c.uid = a.uid
    ORDER BY 1

  IF @@ERROR <> 0
  BEGIN
  RAISERROR('Errore nel popolamento della tabella temporanea', 16, 1)
 RETURN
 END

  /**
 ** Tabella temporaria contenente le statistiche sulle analisi
 ** degli indici
 **/
 CREATE TABLE #showcontig_results (
  ObjectName VARCHAR(255) ,
  ObjectID BIGINT ,
  IndexName VARCHAR(255) ,
  IndexID TINYINT ,
  [LEVEL] TINYINT ,
  Pages BIGINT ,
  [Rows] BIGINT ,
  MinimumRecordSize INT,
  MaximumRecordSize INT,
  AverageRecordSize INT,
  ForwardedRecords INT,
  Extents INT,
  ExtentSwitches INT,
  AverageFreeBytes NUMERIC,
  AveragePageDensity NUMERIC,
  ScanDensity INT,
  BestCount INT,
  ActualCount INT,
  LogicalFragmentation NUMERIC,
  ExtentFragmentation NUMERIC)

 
  /**
 ** Esegue DBCC SHOWCONTIG su tutti gli indici per ottenere
 ** le informazioni sugli indici
 **/
 DECLARE table_index_cursor CURSOR FOR
  SELECT table_name, index_name
    FROM #table_index
    ORDER BY 1

  OPEN table_index_cursor

  FETCH NEXT FROM table_index_cursor INTO @table_name, @index_name
 WHILE @@FETCH_STATUS = 0
  BEGIN
    SELECT @sql = 'DBCC SHOWCONTIG(''' + @table_name + ''', '''
                   + @index_name + ''') WITH TABLERESULTS'
    INSERT #showcontig_results
    EXEC(@sql)

     IF @@ERROR <> 0
      BEGIN
      EXEC master..xp_logevent 'Errore durante l''esecuzione
                                 di DBCC SHOWCONTIG', ERROR
       RETURN
    END

   FETCH NEXT FROM table_index_cursor INTO @table_name, @index_name
 END
 
  CLOSE table_index_cursor
 DEALLOCATE table_index_cursor
 
 
  UPDATE #table_index
   SET sql_statement = 'DBCC INDEXDEFRAG(' + db_name() + ', '''
                      + table_name + ''',''' + index_name + ''')'
   FROM #table_index a
    INNER JOIN #showcontig_results b ON a.index_name = b.IndexName
     WHERE (ScanDensity BETWEEN (100-@maxp) AND (100-@minp))
           OR LogicalFragmentation BETWEEN @minp AND @maxp
 
 
  UPDATE #table_index
   SET sql_statement = 'DBCC DBREINDEX(''' + db_name() + '.'
                      + table_name + ''',''' + index_name + ''')'
   FROM #table_index a
    INNER JOIN #showcontig_results b ON a.index_name = b.IndexName
     WHERE ScanDensity < (100 - @maxp)
          OR LogicalFragmentation > @maxp
 
 
  IF @@ERROR <> 0
   BEGIN
  EXEC master..xp_logevent 'errore durante l''analisi dell''indice', ERROR
  RETURN
 END

 
  /**
  ** Esegue il codice sql estimato dal precedente comando
 **/
 DECLARE sql_statement CURSOR FOR
  SELECT sql_statement
    FROM #table_index
    WHERE sql_statement IS NOT NULL

  OPEN sql_statement
 
  FETCH NEXT FROM sql_statement INTO @sql
  WHILE @@FETCH_STATUS = 0
  BEGIN
    EXEC(@sql)
    IF @@ERROR <> 0
      BEGIN
      EXEC master..xp_logevent 'errore durante l''esecuzione
                                 di DBCC DBREINDEX o INDEXDEFRAG', ERROR
       RETURN
    END
  FETCH NEXT FROM sql_statement INTO @sql
 END

  CLOSE sql_statement
  DEALLOCATE sql_statement

 
  /** ** Eliminazione tabelle temporanee **/
  DROP TABLE #table_index
 DROP TABLE #showcontig_results

Articolo visitato 2261 volte e votato 14 volte (media: 4.3571 su 5)

Voto:
Un commento:
(facoltativo)
::: Pagina iniziale ::: Torna all'inizio della pagina ::: Stampa la pagina ::: 
Valid HTML 4.01 Transitional Valid CSS!