1

Closed

sys.dm_db_index_usage_stats doesn't show never used indexes

description

I noticed that the sys.dm_db_index_usage_stats doesn't show never used indexes because the sys.index is INNER JOIN-ed to the sys.dm_db_index_usage_stats.
What about the following version?
 
 
SELECT
[schema_name] = s.[name],
[object_name] = o.[name],
[object_type] = o.[type],
[object_type_desc] = o.[type_desc],
[index_name] = i.[name],
[index_type] = i.[type],
[index_type_desc] = i.[type_desc],  
i.is_disabled,
i.is_primary_key,
i.is_unique,
i.is_unique_constraint,
total_seeks = ISNULL(user_seeks, 0) + ISNULL(system_seeks, 0),
total_scans = ISNULL(user_scans, 0) + ISNULL(system_scans, 0),
total_lookups = ISNULL(user_lookups, 0) + ISNULL(system_lookups, 0),
total_updates = ISNULL(user_updates, 0) + ISNULL(system_updates, 0),    
u.* 
FROM
sys.indexes i
INNER JOIN
sys.objects o ON i.[object_id] = o.[object_id]
INNER JOIN
sys.schemas s ON o.[schema_id] = s.[schema_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() 
AND
u.[database_id] > 4 
WHERE
i.[type] <> 0
AND
o.[type] IN ('U', 'V')
ORDER BY
u.[user_seeks]
Closed Apr 6, 2012 at 5:16 PM by manowar
Fixed and script released :)Thanks a lot!

comments

FrancescoQuaratin wrote Apr 5, 2012 at 4:47 PM

However I made a mistake in the JOIN condition. This the right part starting from the FROM clause.

FROM sys.indexes i
INNER JOIN sys.objects o ON i.[object_id] = o.[object_id]
INNER JOIN sys.schemas s ON o.[schema_id] = s.[schema_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
i.[type] <> 0 AND o.[type] IN ('U', 'V') AND db_id() > 4
ORDER BY
u.[user_seeks]

wrote Apr 6, 2012 at 5:16 PM

wrote Feb 13, 2013 at 4:06 AM

wrote May 16, 2013 at 4:04 AM