Database fine tuning. Talking about duplicate statistics
Continuo, anche in questo post, i ragionamenti relativi al fine tuning dei nostri database.
Quindi a tutte quelle operazioni che possono apportare delle migliorie in termini di performance (sono solo operazionali, ma anche di manutenzione, di backup / ripristino, ...).
Un argomento molto importante e spesso non adeguatamente monitorato è quello delle statistiche.
Le statistiche sono lo strumento migliore per permettere al database engine di rispondere, nella maniera più intelligente, alla domanda che gli viene fatta.
Le statistiche sono quella serie di informazioni che vengono utilizzate per rappresentare la distribuzione dei valori di colonna delle tabelle o delle viste indicizzate.
In breve, il query optimizer utilizza queste informazioni per stimare la cardinalità dei dati e, di conseguenza, capire quale può essere l'indice migliore (= la via più rapida) da utilizzare per rispondere alla query ricevuta.
In molti casi avere delle statistiche accurate può fare la differenza tra poter sfruttare un'operazione di index seek rispetto ad una di index scan.
Facciamo una puntualizzazione:
- SQL Server, nel caso in cui sia abilitata l'opzione di auto-generazione automatica delle statistiche, si crea (nel caso non ci siano già) queste informazioni per agevolare il suo lavoro.
- SQL Server, allo stesso modo, genera, per ogni indice creato, una relativa statistica a supporto.
Cosa succede se esiste già una statistica identica a quella che verrebbe generata al momento di una operazione di CREATE INDEX?
Poichè, come abbiamo detto, la generazione di un indice la genera automaticamente, avremo due strutture identiche.
Ha senso averle? No.
Ha delle controindicazioni? Si.
Principalmente tre:
- lo storage utilizzato sarà maggiore (vengono memorizzate, in una tabella di sistema all'interno del database utente, due volte le stesse informazioni)
- il tempo di aggiornamento di una tabella (che sia INSERT / UPDATE / DELETE) sarà maggiore poichè le statistiche (anche se non vengono aggiornate ad ogni singola operazione ma di default, e estremizzando / banalizzando il concetto per brevità, solo alla modica del 10% del contenuto della tabella) dovranno essere a loro volta aggiornate
- il tempo di manutenzione del db (che ad esempio potrebbe avere delle istruzioni di UPDATE STATISTICS) sarà maggiore di quanto in realtà potrebbe essere
Facciamo un esempio.
USE MASTER
GO
CREATE DATABASE STATSTEST
GO
ALTER DATABASE [STATSTEST] SET RECOVERY SIMPLE WITH NO_WAIT
GO
USE STATSTEST
GO
/* Verify if auto create stats is on */
SELECT [is_auto_create_stats_on] FROM [sys].[databases] WHERE [name] = 'STATSTEST'
GO
/* Create table for test */
CREATE TABLE test
(
n int,
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 test
SELECT TOP (1000000) N,NEWID() AS Col1, NEWID() as Col2, NEWID() as Col3 FROM Nums
GO
La tabella appena generata non ha, evidentemente perché non ha ancora ricevuto delle richieste, alcuna statistica:
Infatti, provando a farne un aggiornamento non succede nulla (tempo = 0):
set statistics time on
UPDATE STATISTICS dbo.test WITH FULLSCAN
set statistics time off
/* 0 ms. NO Statistics */
Effettuo una generica interrogazione che genererà, implicitamente, una statistica a supporto:
/* Implicitly create statistics on "n" */
SELECT * FROM test WHERE n = 89000
GO
Eseguo, nuovamente, un aggiornamento:
set statistics time on
UPDATE STATISTICS dbo.test WITH FULLSCAN
set statistics time off
/* ~540 ms */
A questo punto costruisco un indice sulla colonna “n”, la stessa colonna utilizzata come predicato nella WHERE eseguita poco sopra:
/* Create index on "n" */
CREATE INDEX IDX_n on test (n)
GO
La sua esecuzione, come abbiamo detto, genera una statistica di appoggio:
Eseguo, ancora una volta, l’aggiornamento delle statistiche (i tempi, naturalmente, sono più alti della precedente UPDATE):
set statistics time on
UPDATE STATISTICS dbo.test WITH FULLSCAN
set statistics time off
/* ~720 ms */
In sostanza mi trovo, al momento, con due statistiche costruite sulla stessa colonna della stessa tabella:
/* List of statistics */
SELECT
OBJECT_NAME(s.object_id) AS objectName,
s.name AS statisticsName,
COL_NAME(sc.object_id, sc.column_id) AS columnName
FROM sys.stats s
INNER JOIN sys.stats_columns sc
ON s.stats_id = sc.stats_id
AND s.OBJECT_ID = sc.Object_id
INNER JOIN sys.tables t
ON t.OBJECT_ID = s.object_id
WHERE
t.is_ms_shipped = 0
ORDER BY
s.name;
- Ha senso mantenere “in vita” la prima statistica, generata automaticamente dal db engine con la prima SELECT ricevuta? No.
- Esiste un modo per effettuare una verifica di statistiche duplicate? Si, ad esempio con uno script come il seguente:
SET NOCOUNT ON;
WITH vStats(
object_id ,
table_column_id ,
index_name )
AS ( SELECT
o.object_id AS object_id ,
ic.column_id AS table_column_id ,
i.name
FROM sys.indexes AS i
JOIN sys.objects AS o ON i.object_id = o.object_id
JOIN sys.stats AS st ON i.object_id = st.object_id
AND i.name = st.name
JOIN sys.index_columns AS ic ON i.index_id = ic.index_id
AND i.object_id = ic.object_id
WHERE o.is_ms_shipped = 0
AND i.has_filter = 0
AND ic.index_column_id = 1 )
SELECT
QUOTENAME( SCHEMA_NAME( o.schema_id )) + '.' + QUOTENAME( o.name ) AS [Table] ,
s.name AS [Statistic To Remove] ,
vStats.index_name AS [Index on Table]
FROM sys.stats AS s
JOIN sys.stats_columns AS sc ON s.stats_id = sc.stats_id
AND s.object_id = sc.object_id
JOIN sys.objects AS o ON sc.object_id = o.object_id
JOIN sys.columns AS c ON sc.object_id = c.object_id
AND sc.column_id = c.column_id
JOIN vStats ON o.object_id = vStats.object_id
AND vStats.table_column_id = c.column_id
WHERE s.auto_created = 1
AND s.has_filter = 0;
Per approfondire il tema: statistics.