“user_scans” of table “sys.dm_db_index_usage_stats”

Radhai Krish 191 Reputation points
2024-06-17T05:33:15.94+00:00

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!

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,343 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,599 questions
{count} votes

Accepted answer
  1. 博雄 胡 190 Reputation points
    2024-06-18T02:06:04.4133333+00:00

    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.

    1 person found this answer helpful.
    0 comments No comments

2 additional answers

Sort by: Most helpful
  1. Olaf Helper 43,166 Reputation points
    2024-06-17T05:41:27.23+00:00

    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

    1 person found this answer helpful.

  2. Erland Sommarskog 106.8K Reputation points
    2024-06-17T21:39:26.3033333+00:00

    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.

    1 person found this answer helpful.
    0 comments No comments