Listare indexi nefolositi pe o baza de date MSSQL Server

Configurare noua (How To)

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

Tip solutie

Permanent
Etichetare:

Voteaza

(56 din 105 persoane apreciaza acest articol)

Despre Autor

Leave A Comment?