lunes, 19 de agosto de 2019

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

No hay comentarios.:

Publicar un comentario

Sinónimos de palabras similares en SQL SERVER

Buenas tardes. Hace un tiempo publique un post que hablaba sobre Sinónimos en SQL SERVER, sin embargo trataba de sinónimos en en objetos, e...