Page 84 - DLIS405_INFORMATION_STORAGE_AND_RETRIEVAL

Unit 8: Indexing

USE AdventureWorks; Notes
GO
— Display Index Usage Information
— Written By Gregory A. Larsen
SELECT o.name Object_Name,
SCHEMA_NAME(o.schema_id) Schema_name,
i.name Index_name,
i.Type_Desc,
CASE WHEN (s.user_seeks > 0
OR s.user_scans > 0
OR s.user_lookups > 0)
AND s.user_updates > 0
THEN ‘USED AND UPDATED’
WHEN (s.user_seeks > 0
OR s.user_scans > 0
OR s.user_lookups > 0)
AND s.user_updates = 0
THEN ‘USED AND NOT UPDATED’
WHEN s.user_seeks IS NULL
AND s.user_scans IS NULL
AND s.user_lookups IS NULL
AND s.user_updates IS NULL
THEN ‘NOT USED AND NOT UPDATED’
WHEN (s.user_seeks = 0
AND s.user_scans = 0
AND s.user_lookups = 0)
AND s.user_updates > 0
THEN ‘NOT USED AND UPDATED’
ELSE ‘NONE OF THE ABOVE’
END AS Usage_Info,
COALESCE(s.user_seeks,0) AS user_seeks,
COALESCE(s.user_scans,0) AS user_scans,
COALESCE(s.user_lookups,0) AS user_lookups,
COALESCE(s.user_updates,0) AS user_updates
FROM sys.objects AS o
JOIN sys.indexes AS i
ON o.object_id = i.object_id
LEFT OUTER JOIN

LOVELY PROFESSIONAL UNIVERSITY 79