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
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];
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: