While working on Index Analysis in SQL Server 2017, I observed that sys.dm_db_index_operational_stats not showing index is getting utilized(column name range_scan_count, however when i checked same in DMV sys.dm_db_index_usage_stats showing that non-clustered index is getting utilized under column user_seeks (For specific table on which i am trying to create non-clustered index to gain the performance of query). Not able to understand why there is difference ? Can somebody please help me to understand why it is so ?

Note :-

  1. There is no any system/SQL Server service restarted during this activity, as well as database is not attached/detached.
  2. Creating Non-clustered index on table which is getting utilized in one of the function, that table is not getting utilized in anywhere in calling procedure (Main Procedure which is calling that function, having underline table).
  • 1
    You might get a faster answer if you post code to reproduce the issue. – Erik Darling Feb 24 at 22:48
  • I was trying to replicate the same with code but unable to, however I have provided the exact explanation of what i observed in the prod environment. – Vikrant More Feb 25 at 4:20
  • 1
    There are many other ‚*_count` columns in the operational view, are you sure none of them covers your user_seeks, are they all 0? – eckes Feb 25 at 8:18
  • 1
    Also, the docs contains warnings that the data is not transactional consistent and might get reset if the metadata object is not in cache, it explicitly mentions not to use it for usage detection. docs.microsoft.com/de-de/sql/relational-databases/… – eckes Feb 25 at 8:26
  • @eckes - Yes, all other columns in dm_db_index_operational_stats are 0, however for the same index when i checked in dm_db_index_usage_stats at column user_seek showing me value 1. – Vikrant More Feb 25 at 8:40

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.