0

I'm running the following query on an AZure SQL Read Only secondary to determine which indexes are being used. But some of the indexes are not being returned in the query. I must be missing something obvious but I can't see what part of the statement is filtering my results !

SELECT
  objects.name AS Table_name,
  indexes.name AS Index_name,
  dm_db_index_usage_stats.user_seeks,
  dm_db_index_usage_stats.user_scans,
  dm_db_index_usage_stats.user_updates
FROM
  sys.dm_db_index_usage_stats
INNER JOIN sys.objects ON dm_db_index_usage_stats.OBJECT_ID = 
  objects.OBJECT_ID
INNER JOIN sys.indexes ON indexes.index_id = 
  dm_db_index_usage_stats.index_id AND dm_db_index_usage_stats.OBJECT_ID = 
indexes.OBJECT_ID
WHERE objects.name = 'BookingDiary'
ORDER BY
  objects.name , dm_db_index_usage_stats.user_updates DESC

SSMS screenshot

  • 3
    An index would not exist in sys.dm_db_index_usage_stats until it is used at least once. – Denis Rubashkin Dec 6 '19 at 10:57
  • thanks Denis, when I was posting the question i started to take a step back and after a cup of tea, logically came to that conclusion.. I think leaving it here might help someone else at some point! – DamagedGoods Dec 6 '19 at 11:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.