1

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 :)

gwoo
  • 31
  • 7
  • Are you executing as `sa`? What data *does* it have, can you [edit] and paste the full result? Is there anything we need to know about this index: memory-optimized, columnstore, partitioned? – Charlieface Apr 24 '21 at 21:49
  • As @Charlieface asks above, what indexes are they; clustered, non-clustered, etc. – Niels Berglund Apr 25 '21 at 08:23
  • @Charlieface I'm in the group with admin priviledge on the server. 1 index is regular clustered (primary key constraint), the other is regular nonclustered index on 1 column. Data wise: 101,300843,'Smith','John', '', 5000.1,1/1/2021 0:00,10,0,'F', 1000, 4/4/2021 6:46,100, NULL, NULL, NULL – gwoo Apr 26 '21 at 17:38
  • @Niels Berglund Index: ALTER TABLE hr.Emp ADD CONSTRAINT Emp_PX PRIMARY KEY CLUSTERED ( E_EmpID ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON PRIMARY GO CREATE NONCLUSTERED INDEX Emp2_IX ON hr.Emp ( E_EmpNbr ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON PRIMARY GO – gwoo Apr 26 '21 at 17:40
  • I meant what data is returned by the DMV query? Please [edit] your question and add it in – Charlieface Apr 26 '21 at 18:59
  • @Charlieface sorry misunderstood you. Updated the post :) Thank you for looking into this. – gwoo Apr 27 '21 at 19:41
  • You mention ETL. Is the table dropped and recreated such that its object id changes? Is there a Truncate or other command that is not a traditionally a logged operations. – Sql Surfer Apr 27 '21 at 19:45
  • The other index has 4 seeks, 3 scans. This one only has 1 seek, no scans. It does seem like it's being dropped and recreated. At a guess, without any further info on what your ETL job does, I'm guessing the seek refers to a clustered index insert or update, which perhaps for some reason doesn't put in the date. – Charlieface Apr 27 '21 at 20:31
  • @SqlSurfer Nope, but I have updated the post. – gwoo Apr 27 '21 at 22:45
  • @Charlieface Post updated. Right now, I'm thinking because we drop and recreate indexes, this could be the cause of these updates not showing through the DMV. Would you know if there's a way to drop the index and still keep the update data in DMV? – gwoo Apr 27 '21 at 22:50
  • Of course not: the DMV relates directly to the index, if you drop the index the data is gone. You might be able to disable it and still keep the DMV data, but if you disable a clustered index you lose access to the table. Perhaps you could use partition switching and still keep the DMV data, see https://stackoverflow.com/questions/67215051/how-to-rename-two-tables-in-one-atomic-operation-in-ms-sql-server/67216017#67216017 – Charlieface Apr 28 '21 at 00:08
  • @Charlieface I am using SQL server 2019 at home, I tried dropping index, truncating table, inserting data, re-create index, but I can still see DMV's last_user_update showing value. I'll see if that's different at my work database which is 2016. – gwoo Apr 28 '21 at 03:05

0 Answers0