Como identificar indices que sobran en SQL Server

Cuando se programa con GeneXus es comun tener bases de conocimiento que subsisten muchos años, con algunas migraciones tecnologicas, como por ejemplo cambios de plataformas.

Es comun tambien que en dichos procesos algunas tablas (generalmente las muy usadas) acumulen una cantidad de indices y algunos no siempre son utilizados por la aplicacion. El mantener dichos indices empeora la performance de los inserts y tambien hacen demorar mas al optimizador pues tiene mas opciones para analizar.


Leyendo el libro Relational Database Index Design and the Optimizers pude ver cuantificado las demoras que puede ocasionar el tener indices de mas en tablas con millones de registros.

Por suerte los muchachos de Microsoft, en SQL Server 2005, mejoraron muchisimo la metadata que se mantiene para ver que indices se utilizan y cuales no. Por ejemplo con la consulta

--Indices no usados ***
SELECT 'NOUsado',object_name(i.object_id) AS ObjectName
, i.name as IndexName
, i.index_id
FROM sys.indexes i
INNER JOIN sys.objects o ON o.object_id = i.object_id
LEFT JOIN sys.dm_db_index_usage_stats s
ON i.object_id=s.object_id AND i.index_id=s.index_id AND database_id = DB_ID()
WHERE objectproperty(o.object_id,'IsUserTable') = 1 AND s.index_id IS NULL
ORDER BY objectname,i.index_id,indexname ASC

Tiene como contra, que esta informacion se borra cada vez que se levanta el servicio de SQL Server 2005, pero igual esta espectacular para ver que indices se estan realmente usando y cuales no.

Para ver desde cuando corre SQL Server 2005 se puede usar

SELECT 'Corre desde hace ' + datediff(hh, login_time, getdate()) + ' horas ' FROM master..sysprocesses WHERE spid = 1

*** Con esto SQL Server permite hacer consultas que se podian resolver en el AS/400, hace unos cuantos años, que guardaba informacion sobre la ultima fecha y la cantidad de veces que habia sido utilizado cada uno de los objetos del sistema