As the title suggests, I seem to be getting unexpected readings from the last_user_update column in the index_usage_stats DMV. It seems to update the datetime value everytime I run a script inserting values to a table. The script however has a left join to the target table and excludes any values which exist. Whenever I run the script the rows affected for the tables in question always returns 0 and the row counts in the table do not change. Yet at anypoint at which I run the script, last_user_update for some reason seems to change despite no rows being modified. Is there a reason for this?
SELECT
DB_NAME(database_id) DBName
OBJECT_SCHEMA_NAME(object_id) + '.' + OBJECT_NAME(object_id)
,last_user_update
FROM
sys.dm_db_index_usage_stats stats
WHERE
database_id = DB_ID()
IF UPDATE(col)
in a trigger, for example, returnstrue
even in cases where the column isn't touched or changed. For index usage statistics in particular, it is arguably just as interesting, and maybe more so, how often an index is attempted to be updated, successful or not. Especially if that's the index being used to determine whether or not a row should be updated. – Aaron Bertrand May 9 '19 at 23:36