Situatie
Mai jos este o procedura sql care, rulata pe o baza de date MSSQL Server, returneaza toti indexii nefolositi de la ultimul restart al serverului de sql.
Solutie
CREATE PROCEDURE IndexiNefolositi
as
SELECT
o.name AS [object_name],
i.name AS index_name,
i.type_desc,
u.user_seeks, u.user_scans,
u.user_lookups, u.user_updates,
o.type
FROM
sys.indexes i
JOIN
sys.objects o ON i.[object_id] = o.[object_id]
LEFT JOIN
sys.dm_db_index_usage_stats u ON i.[object_id] = u.[object_id] AND
i.index_id = u.index_id AND
u.database_id = DB_ID()
WHERE
o.type IN (‘U’, ‘V’) AND
i.name IS NOT NULL AND
ISNULL(u.user_seeks,0) + ISNULL(u.user_scans,0) + ISNULL(u.user_lookups,0) = 0
Leave A Comment?