SQL server DMV (Dynamic Management View) not showing last user update info on table with 2 indexes

285 views Asked by At

I am using the following SQL server DMV to find out when a table was last updated.

USE DB_NAME 
GO

SELECT
[database_name] = DB_NAME(DB_ID())
, [table_name] = tbl.[name]
, ius.last_user_update
, ius.user_updates
, ius.last_user_seek
, ius.last_user_scan
, ius.last_user_lookup
, ius.user_seeks
, ius.user_scans
, ius.user_lookups
FROM sys.dm_db_index_usage_stats ius
INNER JOIN sys.tables tbl
    ON (tbl.OBJECT_ID = ius.OBJECT_ID)
WHERE ius.database_id = DB_ID() -- current database
ORDER BY tbl.[name]

There is 1 table that has 2 indexes on it, and the table has been refreshed by our daily ETL. The DMV above shows 2 rows for that particular table, but both of them contains NULL in the last_user_update column. We have not done any server restart/clearing the DMV cache. The same table is in our Dev/Prod environment. Curiously, the day before in Dev, the DMV showed last_user_update value, but not anymore. The DMV in Prod has been showing nulls for this table. The same DMV works for other tables correctly.

Does anyone know how we can get the last_user_update date for this particular table? Thanks!

Edit: Adding DMV query result below:

database_name   table_name  last_user_update    user_updates    last_user_seek              last_user_scan          last_user_lookup    user_seeks  user_scans  user_lookups
DB_NAME         Emp         NULL                0               2021-04-27 07:10:46.820     NULL                    NULL                1           0           0
DB_NAME         Emp         NULL                0               2021-04-27 07:33:54.480     2021-04-27 07:10:38.063 NULL                4           3           0

Edit 2: ETL for these tables:

  1. Drop index for the table
  2. Extract data from application database, insert those data into the table in question
  3. Truncate whitespace of the table in question.
  4. Add the index back to the table in question.

We are dropping the index because this is a data warehouse, we are re-capturing the data daily.

Edit 3: Found out what the issue is. Adding a primary key constraint actually deletes the DMV data for last_update_date. Dropping an index / recreating the index will not.

Thank you everyone for looking into this issue :)

0

There are 0 answers