Identificando índices duplicados en SQL Server

Un problema común que se puede encontrar en SQL Server son los índices duplicados parcial o totalmente. Los índices son por regla general buenos para mejorar el rendimiento de una base de datos, pero el exceso de índices en una base de datos puede causar que SQL Server gaste mucho tiempo actualizándolos cuando no necesariamente hará uso de ellos.

Este es el caso de los índices duplicados, estos nos pueden causar los siguientes problemas:

  • Degradación del rendimiento que proviene del overhead causado por el mantenimiento de updates, inserts y deletes
  • Incremento en las actividades del log de transacciones (llenando los logs, discos y haciendo que Log Shipping falle)
  • Incremento en el tamaño de los backups (los índices hacen los backups mas grandes, requiriendo mas espacio en disco, creando tiempos de mantenimiento mas altos y consumiendo más energía para poder realizarlos, además de impactar el rendimiento del servidor mientras este se realiza).

¿Pero que es un índice duplicado y como lo detecto?

Digamos que tenemos la tabla de empleados, la cual posee la siguiente estructura:

Nombre de columna Tipo de dato
Codigo_Empleado Integer
Nombre Varchar(128)
Fecha_Nacimiento SmallDateTime
Sexo_Masculino Bit
Numero_Identificacion_Personal Varchar(16)

Y ahora digamos que tenemos dos índices creados para esa tabla:

1. IX_General: Indexa las columnas Codigo_Empleado y Nombre
2. IX_General_Identificacion: Indexa las columnas Codigo_Empleado, Nombre y Numero_Identificacin_Personal

En este caso, ambos índices indexan las columnas Codigo_Empleado y Nombre, por lo que se podría decir que uno de ellos solo causa trabajo extra al motor de base de datos. Ahora bien, ¿Como determino cual de los índices borrar?

En este punto, se necesita recurrir a la experiencia que se tenga del sistema, en general, se necesita determinar como se realizan las búsquedas en esta tabla. Por ejemplo, si siempre se busca información por Nombre, entonces lo mejor será dejar el índice IX_General, porque es mas pequeño y eficiente, pero si por otro lado, la búsqueda más común fuera por Numero_Identificacion_Personal, lo mejor será entonces quedarse con el índice IX_General_Identificacion, porque este índice incluye la columna que mas se utiliza, volviendo al otro índice obsoleto.

Tenga en cuenta que antes de borrar un índice, debe también verificar que no se estén utilizando sentencias con un index hint que la utilice, de otra forma la eliminación del índice provocará que su sentencia falle.

El siguiente procedimiento le ayudará a identificar los índices duplicados en su base de datos:

create procedure [dbo].[usp_duplicateindexes] @tablename varchar(255) as

 

set nocount on

 

print @tablename

 

–dump sp_helpindex into temp table

 

if object_id(‘tempdb..#helpindex’) > 0 drop table #helpindex

create table #helpindex

(

      index_name varchar (900) not null primary key,

      index_description varchar (1000) null,

      index_keys varchar (1000) null

)

 

insert #helpindex exec sp_helpindex @tablename

 

alter table #helpindex add inccols varchar(1000) null

 

declare cr cursor for

      select si.name, sc.name

      from sysobjects so

      join sysindexes si on so.id = si.id

      join sys.index_columns ic on si.id = ic.object_id and si.indid = ic.index_id

      join sys.columns sc on ic.object_id = sc.object_id and ic.column_id = sc.column_id

where so.xtype = ‘U’

and so.name = @tablename

and ic.is_included_column = 1

order by si.name, ic.index_column_id

 

declare @siname varchar(1000), @scname varchar(1000)

open cr

fetch next from cr into @siname, @scname

while @@fetch_status = 0

begin

      update #helpindex set inccols = isnull(inccols , ) + @scname + ‘, ‘ where index_name = @siname

      fetch next from cr into @siname, @scname

end

 

update #helpindex set inccols = left(inccols, datalength(inccols) 2) where right(inccols, 2) = ‘, ‘

 

close cr

deallocate cr

 

if object_id(‘tempdb..#helpindex2’) > 0 drop table #helpindex2

 

create table #helpindex2

(

      index_name varchar (900) not null primary key,

      index_description varchar (1000) null,

      index_keys varchar (1000) null,

      inccols varchar(1000) null

)

 

insert into #helpindex2

 

select hi.index_name, hi.index_description, hi.index_keys, hi.inccols

from #helpindex hi

join #helpindex h2 on hi.index_keys=h2.index_keys

                    and hi.index_description=h2.index_description

                    and hi.index_name<>h2.index_name

if @@rowcount > 0

      select @tablename as “tablename”, * from #helpindex2 order by index_name, index_keys, inccols

 

if object_id(‘tempdb..#helpindex2’) > 0 drop table #helpindex2

if object_id(‘tempdb..#helpindex’) > 0 drop table #helpindex 

Para correrlo, utilice el siguiente comando:

sp_MSForEachTable ‘usp_duplicateindexes”?”’

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s


A %d blogueros les gusta esto: