Post reply

sys.dm_db_index_usage_stats -- what about tables w/ NO INDEXES defined?

  • April 4, 2017 at 8:15 am

    #342099

    To identify "table usage" on a given SQL Server, I've read up on several articles mentioning the use of the DMV:  sys.dm_db_index_usage_stats ---- (see query below)

    My question is, will this work for a table with NO indexes on it?

    WITH LastActivity (ObjectID, LastAction) AS
    (
       SELECT object_id AS TableName, last_user_seek as LastAction
       FROM sys.dm_db_index_usage_stats u (NOLOCK)
        WHERE database_id = db_id(db_name())
       UNION
       SELECT object_id AS TableName, last_user_scan as LastAction
       FROM sys.dm_db_index_usage_stats u (NOLOCK)
        WHERE database_id = db_id(db_name())
       UNION
       SELECT object_id AS TableName, last_user_lookup as LastAction
       FROM sys.dm_db_index_usage_stats u (NOLOCK)
        WHERE database_id = db_id(db_name())
    )
    SELECT OBJECT_NAME(so.object_id) AS TableName, MAX(la.LastAction) as LastSelect
      FROM sys.objects so (NOLOCK)
      LEFT JOIN LastActivity la (NOLOCK) on so.object_id = la.ObjectID
      WHERE so.type = 'U'
       AND so.object_id > 100
         AND so.is_ms_shipped < 1
    GROUP BY OBJECT_NAME(so.object_id)
    ORDER BY OBJECT_NAME(so.object_id)

    BT

  • April 4, 2017 at 8:46 am

    #1936561

    Express12 - Tuesday, April 4, 2017 8:15 AM

    My question is, will this work for a table with NO indexes on it?

    Yup, it'll work fine.

    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

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply