Indici doppi: un sovraccarico inutile

gennaio 24, 2009

Possiamo facilmente immaginare l’inefficienza introdotta dalla doppia indicizzazione (ugualmente ordinata) di un attributo: SQL Server dovrà mantenere aggiornati due indici, organizzati in strutture B-Tree letteralmente identiche, senza trarre alcun beneficio da una delle due.

La stored procedure dbo.usp_drop_double_more_index, definita di seguito, permette di individuare ed eliminare i casi di doppia (tripla, ecc…) indicizzazione dei medesimi attributi.

use [AdventureWorks] go

if object_id(‘usp_drop_double_more_index’) is not null drop procedure dbo.usp_drop_double_more_index go

create procedure dbo.usp_drop_double_more_index ( @mode tinyint, @object_name varchar(4000), @table_type varchar(10), @index_name_prefix_order varchar(8), @debug tinyint ) as begin /* Descrizione: Individua ed elimina i casi di doppia (tripla, ecc..) indicizzazione sui medesimi attributi

Parametri: - @mode Modalità di utilizzo Può assumere i valori: 1= Exec Mode, 0= Print Mode. In exec-mode esegue la cancellazione degli indici doppi, in print-mode stampa i comandi T-SQL

- @object_name Nome di una tabella o lista di nomi di tabelle nel formato <schema_name>.<table_name> separati da “,” per le quali di desidera verificare ed eventualmente cancellare gli indici doppi (esempio <schema_name_1>.<table_name_1>, <schema_name_2>.<table_name_2>, …,

<schema_name_N>.<table_name_N>

- @table_type Tipo di tabella Può assumere: “VIEW” o “BASE TABLE”

- @index_name_prefix_order Determina, attraverso ordinamento, quale indice mantenere tra quelli doppi. Qualora si desideri mantenere un particolare indice o una particolare famiglia di indici il cui nome inizia ad esempio con “IX_”, sarà sufficiente specificare il prefisso desiderato in questo parametro

- @debug Modalità di debug Può assumere i valori: 1= Debug ON, 0= Debug OFF */

declare @msg varchar(256), @cmd varchar(max), @index_name_prefix_len int

– Controllo parametri di input – @mode if (@mode not in (0, 1)) begin set @msg = object_name(@@procid) + ’ : Modalità di esecuzione non supportata, ’ + ‘@mode può assumere i seguenti valori: ’ + ‘1= Modalità Exec, 0= Modalità Print. ’ + ‘Il valore di @mode passato è: ’ + ltrim(str(@mode)) raiserror(@msg, 16, 1) return end

– @debug if (@debug not in (0, 1)) begin set @msg = object_name(@@procid) + ’ : Modalità di debug non supportata, ’ + ‘@debug può assumere i seguenti valori: ’ + ‘1= Debug ON, 0= Debug OFF. ’ + ‘Il valore di @debug passato è: ’ + ltrim(str(@debug)) raiserror(@msg, 16, 1) return end

set nocount on

– Setup tabelle – tmphelpindex if object_id(’tmphelpindex’) is not null drop table tmphelpindex

create table tmphelpindex (id int identity(1, 1) not null, index_name sysname, index_description varchar(210), index_keys varchar(4000))

– tmpindexes if object_id(’tmpindexes’) is not null drop table tmpindexes

create table tmpindexes (id int identity(1, 1) not null, table_schema varchar(128), table_name sysname, index_name sysname, index_description varchar(210), index_keys varchar(4000))

– tmpindexes1 if object_id(’tmpindexes1’) is not null drop table tmpindexes1

create table tmpindexes1 (id int identity(1, 1) not null, table_schema varchar(128), table_name sysname, index_name sysname, index_description varchar(210), index_keys varchar(4000))

set @cmd = ’’ set @index_name_prefix_len = len(ltrim(rtrim(@index_name_prefix_order)))

if (@debug = 1) begin print (’@index_name_prefix_order= “’ + ltrim(rtrim(@index_name_prefix_order)) + ‘”’) print (’@index_name_prefix_len= ’ + ltrim(str(@index_name_prefix_len))) end

select @cmd = @cmd + ‘insert into tmphelpindex ’ + ’exec sp_helpindex ’’’ + i.table_schema + ‘.’ + i.table_name + ‘’’; ’ +

‘delete ’ + ’t ’ + ‘from ’ + ’tmphelpindex t ’ + ‘join ’ +

‘(select ’ + ‘min(t1.id) as id ’ + ‘from ’ + ’tmphelpindex t1 ’ + ‘where ’ + ’exists (select ’ + ‘id ’ + ‘from ’ + ’tmphelpindex t2 ’ + ‘where ’ + ‘(t1.index_keys=t2.index_keys) and ’ + ‘(t2.index_description like ‘‘clustered%’’)) and ’ + ‘(t1.index_description like ‘’nonclustered%’’)) tx on tx.id=t.id; ’ +

‘insert into tmpindexes ’ + ‘(table_schema, ’ + ’table_name, ’ + ‘index_name, ’ + ‘index_description, ’ + ‘index_keys) ’ + ‘select ’ + ’’’’ + i.table_schema + ‘’’,’ + ’’’’ + i.table_name + ‘’’, ’ + ‘index_name, ’ + ‘index_description, ’ + ‘index_keys ’ + ‘from ’ + ’tmphelpindex; ’ +

‘delete from tmphelpindex; ’ from information_schema.tables as i where ((charindex((’,’ + i.table_schema + ‘.’ + i.table_name + ‘,’), (’,’ + replace(@object_name, ’ ‘, ‘’) + ‘,’)) > 0) OR (@object_name=’’)) and (i.table_type=@table_type) order by i.table_schema, i.table_name

if (@debug = 1) print(@cmd) exec(@cmd)

if (ltrim(rtrim(@index_name_prefix_order)) <> ‘’) begin insert into tmpindexes1 (index_name, index_description, index_keys, table_schema, table_name) select index_name, index_description, index_keys, table_schema, table_name from tmpindexes where (left(index_name, @index_name_prefix_len) <> @index_name_prefix_order)

insert into tmpindexes1 (index_name, index_description, index_keys, table_schema, table_name) select index_name, index_description, index_keys, table_schema, table_name from tmpindexes where (left(index_name, @index_name_prefix_len) = @index_name_prefix_order) end else insert into tmpindexes1 (index_name, index_description, index_keys, table_schema, table_name) select index_name, index_description, index_keys, table_schema, table_name from tmpindexes

– Select double index on the same columns set @cmd = '’

select @cmd = @cmd + ‘drop index [’ + t.table_schema + ‘].[’ + t.table_name + ‘].[’ + t.index_name + ‘]; ’ + char(13) + char(10) from tmpindexes1 t join (select max(id) as max_id, count(index_keys) as index_count, table_schema, table_name, index_keys, index_description from tmpindexes1 group by table_schema, table_name, index_keys, index_description having (count(index_keys) > 1)

) t1 on (t.table_schema = t1.table_schema) and (t.table_name = t1.table_name) and (t.index_keys = t1.index_keys) and (t.index_description = t1.index_description) and (t.id < t1.max_id) order by t.index_name

if (@cmd = ‘’) begin print (’’) print (’+-+-+-+-+-+-+-+-+-+-+-+-+’) print (object_name(@@procid) + ‘: No double or more index found!’) end else begin print (’’) print (’+-+-+-+-+-+-+-+-+-+-+-+-+’) if (@mode = 0) print (object_name(@@procid) + ‘: Scrip drop index…’) else print (object_name(@@procid) + ‘: Start drop index…’) print (’’)

if (@mode = 0) print (@cmd) else begin exec (@cmd)

if (@debug = 1) print (@cmd)

print (’’) if (@@error = 0) print (‘Commands: ’ + char(13) + char(10) + @cmd + ’ completed successfully.’) else print (‘Commands: ’ + char(13) + char(10) + @cmd + ’ ended with errors.’) print (’’) end end

print (’’) print (object_name(@@procid) + ‘: …Execution end.’) print (’+-+-+-+-+-+-+-+-+-+-+-+-+’)

– Pulizia tabelle – tmphelpindex if object_id(’tmphelpindex’) is not null drop table tmphelpindex

– tmpindexes if object_id(’tmpindexes’) is not null drop table tmpindexes

– tmpindexes1 if object_id(’tmpindexes1’) is not null drop table tmpindexes1

set nocount off end

Esempio:

Ipotizziamo la situazione illustrata nella figura seguente per la tabella Person.Contact del database di esempio AdventureWorks.

L’attributo FirstName è caratterizzato da una tripla (identica) indicizzazione, riproducibile con i seguenti comandi CREATE INDEX:

create nonclustered index [IX__Person_Contact_FirstName] on [Person].[Contact](FirstName); go

create nonclustered index [IX__Person_Contact_FirstName_1] on [Person].[Contact](FirstName); go

create nonclustered index [IX__Person_Contact_FirstName_2] on [Person].[Contact](FirstName)

Cosa ci aspettiamo dalla stored procedure dbo.usp_drop_double_more_index ?

Ci aspettiamo che rilevi la tripla indicizzazione della colonna FirstName nella tabella Person.Contact.

Eseguiamo la stored procedure in modalità Print (@mode = 0):

EXEC dbo.usp_drop_double_more_index @mode=0, @object_name=‘Person.Contact’, @table_type=‘base table’, @index_name_prefix_order = ‘’, @debug=1

L’output ottenuto propone la cancellazione di due, dei tre indici definiti per l’attributo FieldName della tabella Person.Contact:

+-+-+-+-+-+-+-+-+-+-+-+-+ usp_drop_double_more_index: Scrip drop index…

drop index [Person].[Contact].[IX__Person_Contact_FirstName]; drop index [Person].[Contact].[IX__Person_Contact_FirstName_1];

usp_drop_double_more_index: …Execution end. +-+-+-+-+-+-+-+-+-+-+-+-+

Il comando precedente invocato in modalità Exec (@mode = 1) eseguirà la cancellazione fisica dei due indici:

EXEC dbo.usp_drop_double_more_index @mode=1, @object_name=’’, @table_type=‘base table’, @index_name_prefix_order = ‘’, @debug=1

Risultato:

+-+-+-+-+-+-+-+-+-+-+-+-+ usp_drop_double_more_index: Start drop index…

drop index [Person].[Contact].[IX__Person_Contact_FirstName]; drop index [Person].[Contact].[IX__Person_Contact_FirstName_1];

Commands: drop index [Person].[Contact].[IX__Person_Contact_FirstName]; drop index [Person].[Contact].[IX__Person_Contact_FirstName_1]; completed successfully.

usp_drop_double_more_index: …Execution end. +-+-+-+-+-+-+-+-+-+-+-+-+

Un’altra soluzione ottimizzata per SS2005 e SS2008 la potete trovare qui: Verificare la presenza di indici identici / duplicati