Indices inutilizados sql server 2008

[cancha]

Iniciado por iislas

¿Que indices NO se usan?

Código SQL:

DECLARE @db VARCHAR(25)
SET @db = '<database>'
SELECT object_name(i.object_id), i.name 
FROM sys.indexes i, sys.objects o 
WHERE  i.index_id NOT IN (SELECT s.index_id 
       FROM sys.dm_db_index_usage_stats s 
    WHERE s.object_id=i.object_id AND 
    i.index_id=s.index_id AND 
   database_id = db_id(@db) )
AND o.TYPE = 'U'
AND o.object_id = i.object_id
ORDER BY object_name(i.object_id) ASC
GO
 

¿Que indices me hacen falta?

Código SQL:

SELECT 
  mig.index_group_handle, mid.index_handle, 
  CONVERT (DECIMAL (28,1), 
    migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)
  ) AS improvement_measure, 
  'CREATE INDEX IDX_' + REPLACE(REPLACE(mid.statement, '[', ''), ']', '')+ CONVERT (VARCHAR, mig.index_group_handle) + '_' + CONVERT (VARCHAR, mid.index_handle) 
  + ' ON ' + mid.statement 
  + ' (' + ISNULL (mid.equality_columns,'') 
    + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END 
    + ISNULL (mid.inequality_columns, '')
  + ')' 
  + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement, 
  migs.*, mid.database_id, mid.[object_id]
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE CONVERT (DECIMAL (28,1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) > 10
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC
 

¿Consejos?

Crear un PLAN DE MANTENIMIENTO que se ejecute al menos 1 vez por semana que incluya la REGENERACION DE INDICES

Gracias iislas util estos scripts, ahora tengo otra duda corri esos escript normal
en el caso de los indices que no se usan con el script que compartes me da un total de 101 indices y cuando corro con otro escript que saco de la pagina de sql server me sale 104 aca esta el script

Código SQL:

SELECT OBJECT_NAME(ind.object_id) [Tabla], ind.name [Indice]
FROM Sys.Indexes ind
INNER JOIN Sys.Objects obj ON obj.object_id = ind.object_id
WHERE OBJECTPROPERTY(obj.object_id,'IsUserTable') = 1
AND NOT EXISTS
(SELECT 1 FROM Sys.dm_db_index_usage_stats usg
WHERE usg.object_id = ind.object_id 
AND usg.index_id = ind.index_id
AND ind.index_id = usg.index_id
) AND ind.name IS NOT NULL 
ORDER BY
[Tabla],
[Indice]