Di SQL Server, statistiche e piani di esecuzione

Article
06/09/2014

SQL Server utilizza le statistiche per reagire in maniera intelligente alle istruzioni che riceve.

Conoscendo il numero di righe, la densità delle pagine, gli indici disponibili, l’optimizer è in grado di ragionare in maniera più accurata circa la strada da intraprendere per rispondere a quanto gli viene chiesto.

E’ proprio grazie alle statistiche che SQL Server potrà decidere di evitare di utilizzare indici che “a prima vista” potrebbero sembrare perfetti per una certa istruzione.

Di certo, conoscere come le statistiche funzionano è un ottimo aiuto per comprendere come funziona l’ottimizzatore del database engine.

Proviamo a fare un esempio.

Costruiamo un database, semplice a piacere, con una tabella che andremo a popolare con 1.000.000 di righe.

NOTA: il database non avrà né la creazione delle statistiche, né il loro aggiornamento, abilitata. Non voglio statistiche.

 USE master;
GO

CREATE DATABASE stats CONTAINMENT = NONE 
ON PRIMARY
( 
    NAME = N'stats' , 
    FILENAME = N'C:\temp\stats.mdf' , 
    SIZE = 51200 KB , 
    FILEGROWTH = 10%
)
LOG ON
( 
    NAME = N'stats_log' , 
    FILENAME = N'C:\temp\stats_log.ldf' , 
    SIZE = 10240 KB , 
    FILEGROWTH = 10%
);
GO
ALTER DATABASE stats SET AUTO_SHRINK OFF; 
ALTER DATABASE stats SET AUTO_CREATE_STATISTICS OFF;
ALTER DATABASE stats SET AUTO_UPDATE_STATISTICS OFF; 
ALTER DATABASE stats SET RECOVERY SIMPLE; 
ALTER DATABASE stats SET MULTI_USER; 
ALTER DATABASE stats SET PAGE_VERIFY CHECKSUM;  
GO


USE [stats];
GO

/* Create table for test */

CREATE TABLE test
( 
    n int PRIMARY KEY CLUSTERED , 
    col1 varchar( 50 ) , 
    col2 varchar( 50 ) , 
    col3 varchar( 50 )
);
GO


/* Insert 1.000.000 dummy rows */
WITH L0
    AS ( SELECT 1 AS c
         UNION ALL
         SELECT 1
       ) , L1
    AS ( SELECT 1 AS c
           FROM L0 AS A CROSS JOIN L0 AS B
       ) , L2
    AS ( SELECT 1 AS c
           FROM L1 AS A CROSS JOIN L1 AS B
       ) , L3
    AS ( SELECT 1 AS c
           FROM L2 AS A CROSS JOIN L2 AS B
       ) , L4
    AS ( SELECT 1 AS c
           FROM L3 AS A CROSS JOIN L3 AS B
       ) , L5
    AS ( SELECT 1 AS c
           FROM L4 AS A CROSS JOIN L4 AS B
       ) , Nums
    AS ( SELECT ROW_NUMBER()OVER( ORDER BY( SELECT NULL ) )AS n
           FROM L5
       )
    INSERT INTO test
    SELECT TOP ( 1000000 )N , 
                NEWID()AS Col1 , 
                NEWID()AS Col2 , 
                NEWID()AS Col3
      FROM Nums;
GO

Sulla tabella appena creata e popolata, aggiungo una colonna sulla quale creo un indice (NOTA: l’indice lo sto creando prima di aggiornare il valore della colonna):

ALTER TABLE test

ADD colType char( 1 );

CREATE INDEX ixColType ON test( colType );

A questo punto valorizzo la colonna in modo tale che il 50% delle righe abbia lo stesso valore (in sostanza voglio fare in modo che l’indice su questa colonna non sia selettivo e, quindi, NON sia utile).

 UPDATE test
SET colType = 'Y'
  WHERE n % 2 = 0;
GO

Questa la situazione sulla tabella:

A questo punto, sulla mia colonna:

ho dei valori che non sono selettivi (500.000 righe sono valorizzate a “Y”)
non ho statistiche che possano informare l’optimizer su come sono distribuiti i dati al suo interno

Eseguo una query utilizzando, come predicato, proprio la colonna sulla quale stiamo lavorando.

L’optimizer, trovando disponibile un indice che lavora proprio su questa colonna, viene preso in inganno supponendo di poter sfruttare questa struttura per rispondere in maniera efficiente.

Come si può vedere dal piano di esecuzione, in effetti, lo usa (mi verrebbe da dire “in buona fede”):

 SELECT col1
  FROM test
  WHERE colType = 'Y';
GO

Con risultati devastanti in termini di attività I/O:

(500000 row(s) affected) Table 'test'. Scan count 1, logical reads 1531945

Le statistiche dell’indice, se dessimo un’occhiata alle loro proprietà, sono sì state generate alla sua creazione, ma non sono popolate (né aggiornate)…

Eseguiamo un secondo test:

elimino l’indice
lo ricreo (a questo punto, essendo la tabella popolata, le statistiche verranno generate sulla base dei dati presenti al momento)
rieseguo la stessa query lanciata prima

 DROP INDEX ixColType ON dbo.test;
GO
CREATE INDEX ixColType ON test
( colType );
GO

Rieseguo la query:

 SELECT col1
  FROM test
  WHERE colType = 'Y';
GO

In questa seconda esecuzione:

l’optimizer “vede” un indice che potrebbe fare al caso suo
controlla le statistiche di distribuzione dei dati
si rende conto che, se utilizzasse l’indice, non ne trarrebbe un vantaggio (tutt’altro…)
decide di andare “dritto” sulla tabella

Il piano di esecuzione parla chiaro ed è ben diverso da prima:

Così come le attività di I/O:

(500000 row(s) affected) Table 'test'. Scan count 1, logical reads 16191

Di SQL Server, statistiche e piani di esecuzione

Additional resources