December 6, 2017 at 8:28 am
I'm analyzing two tables in a database. Table A has indexes Index1, Index2 and Index 3. Table B has indexes Index 1, Index2, Index3 and Index 4. I queried dm_db_index_usage_stats to get the usage of each table's indexes.
Here's the output for Table A:
IndexName user_seeks user_scans user_lookups user_updates
Index1 214 0 0 0
Index2 0 199 0 0
Index3 0 0 0 0
Here's the output for Table B:
IndexName user_seeks user_scans user_lookups user_updates
Index1 0 16 0 12
Index2 21 0 0 12
No rows were returned for Index3 and Index4
What is the difference between an index that is return with zero seeks, zero scans, zero lookups and zero updates versus an index that does not appear in dm_db_index_usage_stats at all?
December 6, 2017 at 9:48 am
Incomplete information, what is the query you are running to get these results?
December 6, 2017 at 9:53 am
Here's the query:
SELECT [T].[name] AS 'TableName',
.[name] AS 'IndexName',
.[is_primary_key] AS 'IsPrimaryKey',
.[is_unique_constraint] AS 'IsUniqueConstraint',
.[is_unique] AS 'IsUnique',
[DDIUS].[user_seeks] AS 'UserSeeks',
[DDIUS].[user_scans] AS 'UserScans',
[DDIUS].[user_lookups] AS 'UserLookups',
[DDIUS].[user_updates] AS 'UserUpdates'
FROM [sys].[dm_db_index_usage_stats] [DDIUS]
INNER JOIN [sys].[indexes]
ON [DDIUS].[object_id] = .[object_id]
AND [DDIUS].[index_id] = .[index_id]
INNER JOIN [sys].[tables] [T]
ON .[object_id] = [T].[object_id]
WHERE [DDIUS].[database_id] = DB_ID('DatabaseName')
AND [DDIUS].[object_id] = OBJECT_ID('SchemaName.TableName')
ORDER BY [T].[name], .[name];
December 6, 2017 at 10:00 am
Having just restarted my laptop and running your query in a couple of my databases after minor changes (database name) I get no results which is what I would expect as I have run no queries of any kind since booting up my laptop.
In your case it looks like the indexes you aren't seeing have not been accessed since the last restart of SQL Server.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply