CODEWITHSUNDEEP
sqlsql-server-2008indexing
geographika
geographika

Reputation: 6528

When are SQL Server Index Usage Stats Updated?

I'm using SQL Server 2008 R2, and running the following query to try and see when an index was last updated in my database 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 correct, and contains NULL where no updates have been made to the table since the last restart of SQL Server.

When I run the following query to rebuild the index, I'd expect the last_system_update field to contain a date to indicate 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 the databases on the server. last_user_update does not change either.

I also tried: 

UPDATE STATISTICS dbo.MyTable

But no luck. So when is this field updated? And how can I force it to be updated?

Upvotes: 6

Views: 7083

Answers (2)

geographika
geographika

Reputation: 6528

I found some more details on these columns at:

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 the user query columns, but records when the index is used for a system operation, such as automatic statistics operations.

Upvotes: 0

bhamby
bhamby

Reputation: 15450

You can use the following query to determine when the Indexes were last updated, which uses STATS_DATE():

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.

Upvotes: 10

Related Questions