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.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
|
/\*\*
\*\* 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
|
