In scenari di caricamenti massivi la best practice è sempre una, detta e ridetta:

• DROP indici
• caricamento massivo
• CREATE indici

Bene.

Ora la domanda è: in che ordine eseguo le singole operazioni?

Intendo dire:

Supponendo che la mia tabella abbia sia un indice cluster, che uno o più indici noncluster: in che ordine eseguo le azioni da fare?

Chi cancello per primo? Il cluster o il non cluster? E, una volta terminato il caricamento, chi creo per primo?

Ma ci sarà una differenza nell’eseguire un’azione prima dell’altra?

Le domande dovrebbero essere banali ma, da quanto vedo, non è così chiara la risposta.

Provo a ragionare su due possibili serie di azioni:

• Soluzione A
  • drop cluster
  • drop noncluster
  • attività…
  • create noncluster
  • create cluster
• Soluzione B
  • drop noncluster
  • drop cluster
  • attività…
  • create cluster
  • create noncluster

Prima di proseguire, provate a chiedervi quali delle due strade prendereste.

Nel ragionare, ricordiamoci semplicemente che:

• le foglie degli indici noncluster contengono, se esiste, la chiave cluster. Se non esiste il row-id della tabella HEAP.

Proviamo allora a rivedere con attenzione la Soluzione A che, se volessi raccontare nel dettaglio che cosa fa, diventerebbe:

1. io chiamo la drop cluster
2. il db engine aggiorna tutti gli indici noncluster eliminando, a livello foglia, il riferimento alla chiave cluster che non esiste più e portando un row-identifier in grado di identificare univocamente la riga a cui l’elemento indicizzato si riferisce
3. io chiamo la drop noncluster
4. faccio attività…
5. io chiamo la create noncluster
6. io chiamo la create cluster
7. il db engine aggiorna tutti i noncluster per portare, a livello foglia, la chiave cluster (eliminando il row-identifier scritto al momento della creazione degli indici e scrivendo la chiave cluster appena creata)

E’ evidente che, con questa soluzione, obblighiamo il db engine a fare del lavoro (assolutamente inutile) e tranquillamente evitabile.

Che senso ha, quando cancello o creo gli indici, far aggiornare i noncluster?

Giusto per essere precisi: i punti 2 e 7 sono attività NON necessarie.

Possiamo correggere la cosa semplicemente dando l’ordine corretto alle nostre istruzioni, utilizzando la Soluzione B:

• drop noncluster
• drop cluster
• attività…
• create cluster
• create noncluster

Facendo un test su una tabella da 2 milioni di righe, con questa struttura:

• indice cluster su chiave primaria
• 3 indici noncluster, uno per ogni colonna

Si ottengono questi numeri (msec):

Credo parlino da sè… :-)

Guardando le statistiche di I/O ecco che cosa avviene dietro le quinte:

Soluzione A

Soluzione B

Come si può verificare anche dall’immagine, con una corretta sequenza, l’attività di DROP non fa nulla (come è giusto che sia) e l’attività di CREATE lavora solo una volta su ciascun indice.

Il consiglio? Verificate l’ordine che date a queste attività nelle vostre procedure di caricamento massivo di dati…

Di seguito lo script per ricreare il test:

USE test
GO

 SET NOCOUNT ON;
 SET STATISTICS TIME OFF;
 DECLARE @t datetime;
 
 PRINT '-->  Test NVARCHAR(MAX) INSERT';
 SET @t = GETDATE( );
 SELECT TOP 2000000 
     CAST( ROW_NUMBER( )OVER( ORDER BY C.Object_ID )AS nvarchar( 35 ))AS  Col1,
     CAST( ROW_NUMBER( )OVER( ORDER BY C.Object_ID )AS nvarchar( 35 ))AS  Col2,
     CAST( ROW_NUMBER( )OVER( ORDER BY C.Object_ID )AS nvarchar( 35 ))AS  Col3 INTO testCluster
   FROM Master.sys.All_Columns C CROSS JOIN Master.sys.All_Columns C2;
 SELECT 'create table', DATEDIFF( MILLISECOND , @t , current_timestamp );


SET @t = GETDATE( );
ALTER TABLE testCluster
 ADD ColInt int NOT NULL IDENTITY( 1 , 1 );
ALTER TABLE testCluster ADD  CONSTRAINT [PK] PRIMARY KEY CLUSTERED ( colInt ) 
SELECT 'pk', DATEDIFF( MILLISECOND , @t , current_timestamp );


/* generate index */
 SET @t = GETDATE( );
create index idx1 on testCluster(col1)
create index idx2 on testCluster(col2)
create index idx3 on testCluster(col3)
SELECT 'create index', DATEDIFF( MILLISECOND , @t , current_timestamp );
GO


/* ************************** SOLUZIONE A ************************** */
/* drop index */
DECLARE @t datetime;
SET @t = GETDATE( );
    /* drop pk cluster, drop index */
    ALTER TABLE testCluster DROP CONSTRAINT [PK]    
    DROP INDEX idx1 ON testCluster;
    DROP INDEX idx2 ON testCluster;
    DROP INDEX idx3 ON testCluster;
    
SELECT '--- drop index', DATEDIFF( MILLISECOND , @t , current_timestamp );


SELECT 'some stuff...';


SET @t = GETDATE( );

    /* create index, create pk cluster */
    CREATE INDEX idx1 ON testCluster( col1 );
    CREATE INDEX idx2 ON testCluster( col2 );
    CREATE INDEX idx3 ON testCluster( col3 );
    ALTER TABLE testCluster ADD  CONSTRAINT [PK] PRIMARY KEY CLUSTERED ( colInt ) 

SELECT '--- recreate  index', DATEDIFF( MILLISECOND , @t , current_timestamp );
GO


/* ************************** SOLUZIONE B ************************** */
/* drop index */
DECLARE @t datetime;
SET @t = GETDATE( );
    /* drop index, drop pk cluster */
    DROP INDEX idx1 ON testCluster;
    DROP INDEX idx2 ON testCluster;
    DROP INDEX idx3 ON testCluster;
    ALTER TABLE testCluster DROP CONSTRAINT [PK]

SELECT '--- drop index', DATEDIFF( MILLISECOND , @t , current_timestamp );


SELECT 'some stuff...';


SET @t = GETDATE( );

    /* create pk cluster, create index */
    ALTER TABLE testCluster ADD  CONSTRAINT [PK] PRIMARY KEY CLUSTERED ( colInt ) 
    CREATE INDEX idx1 ON testCluster( col1 );
    CREATE INDEX idx2 ON testCluster( col2 );
    CREATE INDEX idx3 ON testCluster( col3 );

SELECT '--- recreate  index', DATEDIFF( MILLISECOND , @t , current_timestamp );
GO


/* pulizia */
DROP TABLE testCluster;
GO

Enjoy.