HOTSPOTYou are analyzing the performance of a database environment.
You need to find all unused indexes in the current database.
How should you complete the Transact-SQL statement? To answer, select the appropriate Transact-SQL
segments in the answer area.
Hot Area:

Answer:

Explanation:
Example: Following query helps you to find all unused indexes within database using
sys.dm_db_index_usage_stats DMV.
— Ensure a USE statement has been executed first.
SELECT u.*
FROM [sys].[indexes] i
INNER 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() –returning the database ID of the current database
WHERE o.[type] <> ‘S’ –shouldn’t be a system base table
AND i.[type_desc] <> ‘HEAP’
AND i.[name] NOT LIKE ‘PK_%’
AND u.[user_seeks] + u.[user_scans] + u.[user_lookups] = 0
AND u.[last_system_scan] IS NOT NULL
ORDER BY 1 ASC
https://basitaalishan.com/2012/06/15/find-unused-indexes-using-sys-dm_db_index_usage_stats/