Search This Blog

SQL Server:How do you find your most accessed tables? When is the last time each table was accesseed?

How do you determine which tables are being used the most?  Or, which tables may not even be used at all?  In this tip I will show you how to use sys.dm_db_index_usage_stats to gather this information.  First we will return the number of times each table (and index) has been accessed since the last service restart.  Then I will show you how to return the last time each table has been accessed.  It takes time to administer all of your database objects.  If you're not using it -- why is it there?  In my book, knowing the data is very key.  The better I know it, the better I can manage it.  I hope that this will help you learn your data a little better, too.

     /* Most accessed tables. */
     SELECT 
        db_name(ius.database_id) [Database],
        t.NAME [Table],
       SUM(ius.user_seeks + ius.user_scans + ius.user_lookups) [#TimesAccessed]
     FROM
        sys.dm_db_index_usage_stats ius INNER JOIN sys.tables t 
          ON ius.OBJECT_ID = t.object_id
     WHERE 
        database_id = DB_ID('YourDatabaseName')  
     GROUP BY 
        database_id, 
       t.name
     ORDER BY 
        SUM(ius.user_seeks + ius.user_scans + ius.user_lookups) DESC
  
    /* Most accessed indexes. */
    SELECT
        db_name(ius.database_id) [Database],
        t.NAME [Table],
        i.NAME [Index],
        i.type_desc [IndexType],
        ius.user_seeks + ius.user_scans + ius.user_lookups [#TimesAccessed]
     FROM
        sys.dm_db_index_usage_stats ius INNER JOIN sys.indexes i
          ON ius.OBJECT_ID = i.OBJECT_ID
          AND ius.index_id = i.index_id INNER JOIN sys.tables t
            ON i.OBJECT_ID = t.object_id
    WHERE
        database_id = DB_ID('YourDatabaseName')
    ORDER BY
        ius.user_seeks + ius.user_scans + ius.user_lookups DESC
 

 
   /* Last time the table was accessed. */
   USE YourDatabaseName;
   WITH latest AS
    (
    SELECT SCHEMA_NAME(B.schema_id) +'.'+object_name(b.object_id) [Table],
   (   SELECT MAX(last_user_dt) 
    FROM (VALUES (last_user_seek),(last_user_scan),(last_user_lookup)) AS all_val(last_user_dt)) [Accessed]
    FROM sys.dm_db_index_usage_stats a RIGHT OUTER JOIN sys.tables b
      ON a.object_id = b.object_id
    )
    SELECT
       [Table],
       MAX([Accessed]) [LastAccessed]

   FROM
 
      latest
    GROUP BY
       [Table]
    ORDER BY
       [LastAccessed] DESC

 

Quick update.  The logic I've posted above for the last time your table was accessed, does not work with SQL Server v2005.  You can use this to collect the last read and write times for your tables, in v2005:
 
   USE YourDatabaseName;   
   WITH latest AS
   (   SELECT
      [object_id],
      last_user_seek,
      last_user_scan,
      last_user_lookup,
      last_user_update
   FROM
      sys.dm_db_index_usage_stats
   WHERE
      database_id = DB_ID()
   )  
  SELECT      [Schema] = OBJECT_SCHEMA_NAME([object_id]),
      [TableOrView] = OBJECT_NAME([object_id]),
      LastReadTime = MAX(last_read),
      LastWriteTime = MAX(last_write)
  FROM    (     SELECT [object_id], last_user_seek, NULL FROM latest
       UNION ALL
     SELECT [object_id], last_user_scan, NULL FROM latest
       UNION ALL
     SELECT [object_id], last_user_lookup, NULL FROM latest
       UNION ALL
     SELECT [object_id], NULL, last_user_update FROM latest
    ) AS x ([object_id], last_read, last_write)
   GROUP BY
      OBJECT_SCHEMA_NAME([object_id]),
      OBJECT_NAME([object_id])
   ORDER BY
      [Schema],
      [TableOrView];
 
 
Please review this for more information: