When is SQL Server index usage statistics updated?

Question

I am using SQL Server 2008 R2 and running the following query to see when the index in my database was last updated using the following SQL:

SELECT last_system_update, last_user_update, OBJECT_NAME(object_id) AS tblName FROM sys.dm_db_index_usage_stats WHERE database_id = DB_ID('mydatabase') 

The last_user_update field is valid and contains NULL, in which updates have not been added to the table since the last restart of SQL Server.

When I ran the following query to rebuild the index, I would expect the last_system_update field to contain a date indicating that the index was rebuilt:

 ALTER INDEX ALL ON dbo.MyTable REBUILD 

However, this field remains NULL. In fact, the last_system_update field is empty (NULL) for all indexes in all databases on the server. last_user_update also does not change.

I also tried:

 UPDATE STATISTICS dbo.MyTable 

But no luck. So when is this field updated? And how can I make it update?

Answer 1

You can use the following query to determine when indexes that use STATS_DATE() were updated:

 USE your_db; SELECT t.name AS Table_Name ,i.name AS Index_Name ,i.type_desc AS Index_Type ,STATS_DATE(i.object_id,i.index_id) AS Date_Updated FROM sys.indexes i JOIN sys.tables t ON t.object_id = i.object_id WHERE i.type > 0 ORDER BY t.name ASC ,i.type_desc ASC ,i.name ASC; 

I believe this will only work with SQL Server 2005 or newer since sys.indexes was not in SQL Server 2000.

Answer 2

I found more detailed information about these columns:

http://sqlblog.com/blogs/louis_davidson/archive/2007/07/22/sys-dm-db-index-usage-stats.aspx

system_seeks, system_scans, system_lookups, system_updates, last_system_seek, last_system_scan, last_system_lookup, last_system_update

Same as custom query columns, but records when the index is used to operate the system, such as automatic statistics of an operation.