“user_scans” of table “sys.dm_db_index_usage_stats”

Question

I am trying to find answer to the below.

What exactly the scan means for the field "user_scans" being with the table "sys.dm_db_index_usage_stats" ?

Like, there are tables in our prod which aren't used for sure as the module is shutdown 4 years back but the DB holds all the tables related to that module. So, was trying to list out the untouched tables against the DB prior to upgrading our SQL. But found that user_scans for those untouched tables are getting updated on certain occasions. But unable to trace back the reasons.

So need some more info on what scenarios does these user_scans gets updated? Anyone? - Thanks in advance!

Answer 1

If there are no traces of updating these tables found in the program code and program logs, you can try looking for database agent jobs, triggers, and stored procedures that include this table. Agent jobs and triggers are usually the most likely suspects.

Answer 2

So need some more info on what scenarios does these user_scans gets updated

"user_scans" means a user run a query against the table without hitting an usefull index; otherwise it would be a "user_seek", see

https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-usage-stats-transact-sql?view=sql-server-ver16

Answer 3

As Olaf says, if user_scan is > 0, this means that there is someone/something running queries against the table. It could be something trivial like someone running a script that runs SELECT COUNT(*) on all tables. To find out what it may be, you need to set up some sort of auditing or tracing.

Or move the table to a different schema and see if someone starts screaming, because the table is actually in use.