MASANASA

Me he basado en las siguientes consultas:

He utilizado las siguientes consultas añado las fuentes para que podáis obtener más información.
Esta primera indica aquellos indices que no se realizan consultas de lectura solo de escritura actualizaciones y inserciones, por lo que es una sobrecarga del sistema por lo que son candidatos a ser eliminados.

USE [tuBBDD] /* Replace with your Database Name */
GO
--TOTALLY UN-USED INDEXES
SELECT DB_NAME(s.database_id) as [DB Name], OBJECT_NAME(s.[object_id]) AS [Table Name], i.name AS [Index Name], i.index_id,
i.is_disabled, i.is_hypothetical, i.has_filter, i.fill_factor, i.is_unique,
s.user_updates AS [Total Writes],
(s.user_seeks + s.user_scans + s.user_lookups) AS [Total Reads],
s.user_updates - (s.user_seeks + s.user_scans + s.user_lookups) AS [Difference],
(partstats.used_page_count / 128.0) AS [IndexSizeinMB]
FROM sys.dm_db_index_usage_stats AS s WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK)
ON s.[object_id] = i.[object_id]
AND s.index_id = i.index_id
AND s.database_id = DB_ID()
INNER JOIN sys.dm_db_partition_stats AS partstats
ON i.object_id = partstats.object_id AND i.index_id = partstats.index_id
WHERE OBJECTPROPERTY(s.[object_id],'IsUserTable') = 1
AND user_updates > (user_seeks + user_scans + user_lookups)
AND (s.user_lookups=0 AND s.user_scans=0 AND s.user_seeks=0)
AND i.index_id > 1
ORDER BY [Difference] DESC, [Total Writes] DESC, [Total Reads] ASC OPTION (RECOMPILE);
GO

USE [tuBBDD] /* Replace with your Database Name */

--TOTALLY UN-USED INDEXES

SELECT DB_NAME(s.database_id) as [DB Name], OBJECT_NAME(s.[object_id]) AS [Table Name], i.name AS [Index Name], i.index_id,

i.is_disabled, i.is_hypothetical, i.has_filter, i.fill_factor, i.is_unique,

s.user_updates AS [Total Writes],

(s.user_seeks + s.user_scans + s.user_lookups) AS [Total Reads],

s.user_updates - (s.user_seeks + s.user_scans + s.user_lookups) AS [Difference],

(partstats.used_page_count / 128.0) AS [IndexSizeinMB]

FROM sys.dm_db_index_usage_stats AS s WITH (NOLOCK)

INNER JOIN sys.indexes AS i WITH (NOLOCK)

ON s.[object_id] = i.[object_id]

AND s.index_id = i.index_id

AND s.database_id = DB_ID()

INNER JOIN sys.dm_db_partition_stats AS partstats

ON i.object_id = partstats.object_id AND i.index_id = partstats.index_id

WHERE OBJECTPROPERTY(s.[object_id],'IsUserTable') = 1

AND user_updates > (user_seeks + user_scans + user_lookups)

AND (s.user_lookups=0 AND s.user_scans=0 AND s.user_seeks=0)

AND i.index_id > 1

ORDER BY [Difference] DESC, [Total Writes] DESC, [Total Reads] ASC OPTION (RECOMPILE);

La siguiente consulta, muestra aquellos indices que tengan mas consultas de escritura que de lectura.
Debes estudiar cada indice si es candidato a ser eliminado.

USE [tuBBDD] /* Replace with your Database Name */
GO
--INDEXES WITH WRITES > READS
SELECT DB_NAME(s.database_id) as [DB Name], OBJECT_NAME(s.[object_id]) AS [Table Name], i.name AS [Index Name], i.index_id,
i.is_disabled, i.is_hypothetical, i.has_filter, i.fill_factor, i.is_unique,
s.user_updates AS [Total Writes],
(s.user_seeks + s.user_scans + s.user_lookups) AS [Total Reads],
s.user_updates - (s.user_seeks + s.user_scans + s.user_lookups) AS [Difference],
(partstats.used_page_count / 128.0) AS [IndexSizeinMB]
FROM sys.dm_db_index_usage_stats AS s WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK)
ON s.[object_id] = i.[object_id]
AND s.index_id = i.index_id
AND s.database_id = DB_ID()
INNER JOIN sys.dm_db_partition_stats AS partstats
ON i.object_id = partstats.object_id AND i.index_id = partstats.index_id
WHERE OBJECTPROPERTY(s.[object_id],'IsUserTable') = 1
AND (s.user_lookups<>0 OR s.user_scans<>0 OR s.user_seeks<>0)
AND s.user_updates > (s.user_seeks + s.user_scans + s.user_lookups)
AND i.index_id > 1
ORDER BY [Difference] DESC, [Total Writes] DESC, [Total Reads] ASC OPTION (RECOMPILE);
GO

USE [tuBBDD] /* Replace with your Database Name */

--INDEXES WITH WRITES > READS

SELECT DB_NAME(s.database_id) as [DB Name], OBJECT_NAME(s.[object_id]) AS [Table Name], i.name AS [Index Name], i.index_id,

i.is_disabled, i.is_hypothetical, i.has_filter, i.fill_factor, i.is_unique,

s.user_updates AS [Total Writes],

(s.user_seeks + s.user_scans + s.user_lookups) AS [Total Reads],

s.user_updates - (s.user_seeks + s.user_scans + s.user_lookups) AS [Difference],

(partstats.used_page_count / 128.0) AS [IndexSizeinMB]

FROM sys.dm_db_index_usage_stats AS s WITH (NOLOCK)

INNER JOIN sys.indexes AS i WITH (NOLOCK)

ON s.[object_id] = i.[object_id]

AND s.index_id = i.index_id

AND s.database_id = DB_ID()

INNER JOIN sys.dm_db_partition_stats AS partstats

ON i.object_id = partstats.object_id AND i.index_id = partstats.index_id

WHERE OBJECTPROPERTY(s.[object_id],'IsUserTable') = 1

AND (s.user_lookups<>0 OR s.user_scans<>0 OR s.user_seeks<>0)

AND s.user_updates > (s.user_seeks + s.user_scans + s.user_lookups)

AND i.index_id > 1

ORDER BY [Difference] DESC, [Total Writes] DESC, [Total Reads] ASC OPTION (RECOMPILE);

Fuente: thesqldude.com

Esta consulta complementa la anterior para decir que indices podrías eliminar.
Se debe prestar atención User Scan, User Lookup y User Update antes de eliminar el Index.
Si el valor de User Scan, User Lookup y User Update es alto y de User Seek bajo necesitas revisar el Indice

-- Unused Index Script
-- Original Author: Pinal Dave 
SELECT TOP 25
o.name AS ObjectName
, i.name AS IndexName
, i.index_id AS IndexID
, dm_ius.user_seeks AS UserSeek
, dm_ius.user_scans AS UserScans
, dm_ius.user_lookups AS UserLookups
, dm_ius.user_updates AS UserUpdates
, p.TableRows
, 'DROP INDEX ' + QUOTENAME(i.name)
+ ' ON ' + QUOTENAME(s.name) + '.'
+ QUOTENAME(OBJECT_NAME(dm_ius.OBJECT_ID)) AS 'drop statement'
FROM sys.dm_db_index_usage_stats dm_ius
INNER JOIN sys.indexes i ON i.index_id = dm_ius.index_id 
AND dm_ius.OBJECT_ID = i.OBJECT_ID
INNER JOIN sys.objects o ON dm_ius.OBJECT_ID = o.OBJECT_ID
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
INNER JOIN (SELECT SUM(p.rows) TableRows, p.index_id, p.OBJECT_ID
FROM sys.partitions p GROUP BY p.index_id, p.OBJECT_ID) p
ON p.index_id = dm_ius.index_id AND dm_ius.OBJECT_ID = p.OBJECT_ID
WHERE OBJECTPROPERTY(dm_ius.OBJECT_ID,'IsUserTable') = 1
AND dm_ius.database_id = DB_ID()
AND i.type_desc = 'nonclustered'
AND i.is_primary_key = 0
AND i.is_unique_constraint = 0
ORDER BY (dm_ius.user_seeks + dm_ius.user_scans + dm_ius.user_lookups) ASC
GO

-- Unused Index Script

-- Original Author: Pinal Dave

SELECT TOP 25

o.name AS ObjectName

, i.name AS IndexName

, i.index_id AS IndexID

, dm_ius.user_seeks AS UserSeek

, dm_ius.user_scans AS UserScans

, dm_ius.user_lookups AS UserLookups

, dm_ius.user_updates AS UserUpdates

, p.TableRows

, 'DROP INDEX ' + QUOTENAME(i.name)

+ ' ON ' + QUOTENAME(s.name) + '.'

+ QUOTENAME(OBJECT_NAME(dm_ius.OBJECT_ID)) AS 'drop statement'

FROM sys.dm_db_index_usage_stats dm_ius

INNER JOIN sys.indexes i ON i.index_id = dm_ius.index_id

AND dm_ius.OBJECT_ID = i.OBJECT_ID

INNER JOIN sys.objects o ON dm_ius.OBJECT_ID = o.OBJECT_ID

INNER JOIN sys.schemas s ON o.schema_id = s.schema_id

INNER JOIN (SELECT SUM(p.rows) TableRows, p.index_id, p.OBJECT_ID

FROM sys.partitions p GROUP BY p.index_id, p.OBJECT_ID) p

ON p.index_id = dm_ius.index_id AND dm_ius.OBJECT_ID = p.OBJECT_ID

WHERE OBJECTPROPERTY(dm_ius.OBJECT_ID,'IsUserTable') = 1

AND dm_ius.database_id = DB_ID()

AND i.type_desc = 'nonclustered'

AND i.is_primary_key = 0

AND i.is_unique_constraint = 0

ORDER BY (dm_ius.user_seeks + dm_ius.user_scans + dm_ius.user_lookups) ASC

Fuente: blog.sqlauthority.com

Mis apuntes de programación

Detectar Indices no utilizados en SQLServer

Relacionado

Deja un comentario Cancelar respuesta