Monday, January 27, 2014

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: