sys.dm_db_index_usage_stats

• anjaliv

  Say Hey Kid

  Points: 689

  More actions

  July 1, 2011 at 8:38 am

  #252944

  Hi All,

  In what situations the last_user_update, last_user_seek field is NULL in sys.dm_db_index_usage_stats?

• Orlando Colamatteo

  SSC Guru

  Points: 182279

  More actions

  July 1, 2011 at 10:17 am

  #1347152

  When there have been no updates or seeks on the index of course 🙂

  Here is a quick proof that shows how to achieve that state:

  IF EXISTS ( SELECT *

  FROM sys.objects

  WHERE object_id = OBJECT_ID(N'dbo.idx')

  AND type IN (N'U') )

  DROP TABLE dbo.idx ;

  GO

  CREATE TABLE dbo.idx (a INT) ;

  GO

  CREATE INDEX idx_a ON dbo.idx (a)

  GO

  -- create a scan

  SELECT *

  FROM dbo.idx;

  GO

  -- no inserts so no last_user_update, no seek so no last_user_seek

  SELECT OBJECT_NAME(object_id),

  *

  FROM sys.dm_db_index_usage_stats

  WHERE database_id = DB_ID()

  AND OBJECT_ID('dbo.idx') = object_id ;

  There are no special teachers of virtue, because virtue is taught by the whole community.
  --Plato

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  July 1, 2011 at 10:46 am

  #1347179

  The DBV is cleared when SQL restarts or when the database is closed.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• Orlando Colamatteo

  SSC Guru

  Points: 182279

  More actions

  July 1, 2011 at 12:29 pm

  #1347259

  ...checking on something

  EDIT:

  OK, had to make sure:

  Restarting SQL makes the index disappear from the usage stats view altogether. It's not until the index is used again (in some way) that it appears in the results again.

  There are no special teachers of virtue, because virtue is taught by the whole community.
  --Plato