Do anyone knows 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!
While your application may not longer be running against the database, other people could be connecting to it via other means and querying or modifying data. Unless you audit your database server and user/login permissions, and lock down access, someone can be using it to run a report or some other side thing.
I'd suggest using an extended event, or a server/database audit, if you want to capture who and what is using that table. There's an audit example here:
That does pretty much exactly what you want, you might have to tweak it a bit.
There's an alternative method described here that might be useful, but it's intended to detect unused tables:
That uses extended events, and has some more flexibility with the XESmartTarget as far as reporting, collecting, etc. Server and database audits can only collect information, and you'd have to query them later to get the information.