Depurar índices en una base de Datos

Buenas tardes queridos lectores. Ya tenía mucho sin escribir, abrumado por tantas cosas que hacer, hoy vamos a hablar de un tema de DBA's, y será Depurar índices en las bases de Datos.

Como sabemos los índices en una tabla de base de datos nos permiten agilizar las consultas, pues no tendrá que buscar en todos los datos de la tabla, sino que buscara por el índice que se haya definido haciendo la consulta más rápida, sin embargo, un índice ocupa espacio en disco, de manera que si abusamos del recurso y añadimos muchos índices a una tabla, índices que ni siquiera necesitamos, vamos a general el efecto contrario, es decir, en lugar de optimizar las consultas, vamos a bajar el rendimiento de las mismas, además, claro de gastar espacio en disco. Así que el día de hoy veremos como depurar los índices para tener únicamente los que necesitamos. Vamos a empezar realizando la siguiente consulta:

Use [TUBASE]

GO

SELECT

DISTINCT OBJECT_NAME(sis.OBJECT_ID) TableName,

si.name AS IndexName,

sc.is_identity,

sc.is_computed,

si.is_primary_key,

sis.user_seeks,

sis.last_user_seek,

sis.last_user_scan  ,

sis.last_user_update

FROM sys.dm_db_index_usage_stats sis

INNER JOIN sys.indexes si  on sis.object_id = si.object_id and sis.index_id = si.Index_id

INNER JOIN sys.index_columns sic on sis.object_id = sic.object_id and sic.Index_id = si.Index_id

INNER JOIN sys.columns sc on sis.object_id = sc.object_id and sic.Column_id = sc.Column_id

INNER JOIN  sys.objects o on si.object_id = o.object_id

WHERE sis.database_id = DB_ID('TUBASE') and o.type = 'U'

and si.is_primary_key = 0 and o.is_ms_shipped = 0

go

Únicamente a las tablas de tipo usuario, ignoraremos los índices de columnas que sean llaves primarias y también ignoraremos las tablas que se crean por las réplicas.

Obtendremos un resultado como éste:

Como vemos en las fechas de búsqueda, de scaneo y actualización, podemos ver que existen algunos índices que nunca se han utilizado, vamos a utilizar uno para corroborar esa información, y para ello utilizaremos éste índice: IDXDBA_CXCDEPOSITOS11073.

select top 10 * from CXCDEPOSITOS with (index (IDXDBA_CXCDEPOSITOS11073))

Si volvemos a ejecutar la primer consulta, que es la del estado de los índices veremos que ya cuenta ese índice con información.

Utilizando esta lógica, nos damos cuenta que podemos empezar eliminando los índices que nunca se han utilizado. Así que vamos a modificar un poco la consulta:

Use TUBASE

GO

SELECT

DISTINCT OBJECT_NAME(sis.OBJECT_ID) TableName,

si.name AS IndexName,

sc.is_identity,

sc.is_computed,

si.is_primary_key,

sis.user_seeks,

--isnull(ISNULL(sis.last_user_seek, isnull(sis.last_user_scan,sis.last_user_update)),DATEADD(year,-1, GETDATE())),

sis.last_user_seek,

sis.last_user_scan  ,

sis.last_user_update,

sh.name,

'DROP INDEX ' + si.name + ' ON ' + sh.name + '.' + o.name

FROM sys.dm_db_index_usage_stats sis

INNER JOIN sys.indexes si  on sis.object_id = si.object_id and sis.index_id = si.Index_id

INNER JOIN sys.index_columns sic on sis.object_id = sic.object_id and sic.Index_id = si.Index_id

INNER JOIN sys.columns sc on sis.object_id = sc.object_id and sic.Column_id = sc.Column_id

INNER JOIN  sys.objects o on si.object_id = o.object_id

inner join sys.schemas sh on sh.schema_id = o.schema_id

WHERE sis.database_id = DB_ID('TUBASE') and o.type = 'U'

and si.is_primary_key = 0 and o.is_ms_shipped = 0

--and isnull(ISNULL(sis.last_user_seek, isnull(sis.last_user_scan,sis.last_user_update)),DATEADD(year,-1, GETDATE())) < DATEADD (month, -6, GETDATE())

and sis.last_user_seek is null and sis.last_user_scan is null and sis.last_user_update is null

Mediante un query, agregamos el código para eliminar los indices mediante la sentencia DROP

DROP INDEX IDXDBA_CXCDEPOSITOSPAGOS160813 ON dbo.CXCDEPOSITOSPAGOS

DROP INDEX IX_no_deposito ON dbo.CXCDEPOSITOSPAGOS

DROP INDEX ix_CXCDEVOLUCIONES_ENC_tipo_documento_fecha_tipo_movimiento ON dbo.CXCDEVOLUCIONES_ENC

DROP INDEX IDXDBA_CXCDEVOLUCIONES_PAR315 ON dbo.CXCDEVOLUCIONES_PAR

DROP INDEX IDXDBA_CXCPARTIDAS150778 ON dbo.CXCPARTIDAS

DROP INDEX IDXDBA_CXCRECIBOSPAGOS265 ON dbo.CXCRECIBOSPAGOS

Hasta aquí el post queridos lectores, espero compartan, comenten o le den clic a cualquier anuncio que les interese. Quedo a la orden para cualquier duda o comentario.

Saludos