list tables by usage ('select' usage)

Question

Hi experts,

 

I have a database with 300 tables and I need to migrate it…

 

The database is used by so many people, that asking who uses what, would be a nightmare…

 

I would love to list all the tables in the database ordered by usage of them; can I achieve that?

 

To move them I need to modify many ETLs and the source server will be taken down pretty soon; so my goal is to bring back live first; the most used tables…

 

Thanks in advance.

Answer 1

Hi there,

You can try this query:

SELECT 
	db_name(ius.database_id) AS DatabaseName,
	t.NAME AS TableName,
	SUM(ius.user_seeks + ius.user_scans + ius.user_lookups) AS NbrTimesAccessed
FROM sys.dm_db_index_usage_stats ius
INNER JOIN sys.tables t ON t.OBJECT_ID = ius.object_id
WHERE database_id = DB_ID('your database here')
GROUP BY database_id, t.name
ORDER BY SUM(ius.user_seeks + ius.user_scans + ius.user_lookups) DESC

Answer 2

Run this query. Just be sure to run it in the database you are going to migrate. It gives you TableName, Schema, RowCounts, TotalSpaceKb, UsedSpaceKB and UnusedSpaceKB. It is fundamental if you are going to partially migrate the database:

SELECT 
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.Name, p.Rows
ORDER BY 
     p.rows desc, SUM(a.total_pages) * 8

Please mark as answer if this post helped you

Answer 3

Regarding Enric's suggestion: This is relying on a DMV that keep stats on *index* usage. A query can access that data without using an index. Also, restarting SQL server will clear out the stats.

As for Diegoctn's suggestion: This doesn't tell you how often (etc) the table was used, only space usage stats.

In short: SQL Server doesn't keep track of this by itself. Possibly you can utilize the Query Store if you are on SQL 2016 and have turned it on.

---

Tibor Karaszi, SQL Server MVP (Web Blog)

Answer 4

I wrote a blog post on this topic. https://spaghettidba.com/2015/04/20/tracking-table-usage-and-identifying-unused-objects/

Unfortunately, it's a complex problem, with complex solutions.