Wednesday, September 17, 2008

[Indexes] And my unused index query.

Actually, not mine, but it's the one I use. All sorts of variations you can run

SELECT @@SERVERNAME AS server_name,
DB_NAME() AS database_name,
o.name AS object_name,
i.name AS index_name,
i.type_desc,
ISNULL(u.user_seeks, 0) user_seeks,
ISNULL(u.user_lookups, 0) user_lookups,
ISNULL(u.user_scans, 0) user_scans,
ISNULL(u.user_seeks, 0) + ISNULL(u.user_scans, 0) + ISNULL(u.user_lookups, 0) user_total,
ISNULL(u.user_updates, 0) user_updates,
last_user_seek,
GETDATE() AS date_inserted
-- fill_factor
-- ,'DROP INDEX ' + i.name + ' ON dbo.' + o.name
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_desc NOT IN ('SYSTEM_TABLE', 'INTERNAL_TABLE') -- No system tables!
AND (ISNULL(u.user_seeks, 0) + ISNULL(u.user_scans, 0) + ISNULL(u.user_lookups, 0)) < 100
AND i.type_desc NOT IN ('HEAP','CLUSTERED')
AND i.is_primary_key = 0
--AND i.is_unique_constraint = 0
ORDER BY (ISNULL(u.user_seeks, 0) + ISNULL(u.user_scans, 0) + ISNULL(u.user_lookups, 0)),ISNULL(u.user_updates, 0) DESC, o.name, i.name
--ORDER BY ISNULL(u.user_updates, 0) desc, o.name, i.name
--ORDER BY o.name,
-- i.name

No comments: