0

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()
Improve this question
  • Hi, could you add the query and what issue you are experiencing? Is the issue the last_user_update being modified after an insert on the table? – Randi Vertongen May 9 '19 at 22:05
  • There are multiple operations that register as an update more because they attempted to update something than they actually succeeded. IF UPDATE(col) in a trigger, for example, returns true 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

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.