Home > Scripts > Indici doppi: un sovraccarico inutile

Indici doppi: un sovraccarico inutile

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

Chi è Sergio Govoni

Sergio Govoni è laureato in Scienze e Tecnologie Informatiche. Da oltre 16 anni lavora presso una software house che produce un noto sistema ERP, distribuito a livello nazionale ed internazionale, multi azienda client/server su piattaforma Win32. Attualmente si occupa di progettazione e analisi funzionale, coordina un team di sviluppo ed è responsabile tecnico di prodotto. Lavora con SQL Server dalla versione 7.0 e si è occupato d'implementazione e manutenzione di database relazionali in ambito gestionale, ottimizzazione delle prestazioni e problem solving. Nello staff di UGISS si dedica alla formazione e alla divulgazione in ambito SQL Server e tecnologie a esso collegate, scrivendo articoli e partecipando come speaker ai workshop e alle iniziative del primo e più importante User Group Italiano sulla tecnologia SQL Server. Ha conseguito la certificazione MCP, MCTS SQL Server. Per il suo contributo nelle comunità tecniche e per la condivisione della propria esperienza con altri, dal 2010 riceve il riconoscimento SQL Server MVP (Microsoft Most Valuable Professional). Nel corso dell'anno 2011 ha contribuito alla scrittura del libro SQL Server MVP Deep Dives Volume 2 (http://www.manning.com/delaney/).

Leggi Anche

sp_alter_column – La stored procedure per modificare una colonna!

Sarà successo anche a voi di dover modificare il tipo di dato di una colonna …