How to find most used and least used tables in SQL Server.

In this article, we gonna solve most common problem how to find most and least used tables in SQL Server. This will help us in cleaning database and organize tables which we use and delete the unnecessary tables.

We have a default view sys.dm_db_index_usage_stats in SQL Server, which stores the table usage information. But, this will lose its data when SQL Server restarts. So to solve this problem, I came up with work around.

I created a SQL job runs every 3 hours to take backup of above view and incrementally load the temp table with new data. In this approach even the view data is lost, we still have the backup data in temp tables. After a month or so we have enough data to identify recently used tables and their last access and modified date.

First we will create a Temp table with similar columns like view sys.dm_db_index_usage_stats and we will load temp table.

Before loading we need to create a unique clustered index, this will stop inserting redundant data and avoid duplication.

Now we are ready to insert the data in our Temp table. We will join both tables using below code.

In the end we need to specify, which database we are focusing on so table information of those databases are only recorded. Now make this as Stored procedure and schedule to run as per assigned frequency.

Now we can take this temp table and create a view which make more meaningful by resolving DB_ID and OBJECT_ID to database name and table name with this functions respectively. DB_NAME(DB_ID),OBJECT_NAME(OBJECT_ID).

We can also join this table with sys.indexes, sys.partitions, sys.allocation_units, sys.schemas, sys.objects views to find out Space used in GB/MB.

I have used this query and created a dashboard in PowerBI to check the tables usage. Hope this helps and Please suggest me if any corrections.

Please feel to share or comment if there are any mistakes/queries. I hope you all enjoyed reading this article.

Thank You.