After being given a chance to have a look at the Database of a company for coming up with maintenance plan i had a peek into the DB. And to my astonishment i found over 60% of the table not being used. Regardless of the total number of tables existing in the DB the percentage of the unused table is too high to ignore. In this case total number of unused table was close to 1500. Now this is a classic example how badly people treat the SQL Server DB. You fatten it like a cow and then expect it to run like a horse?? Think again!!
Most of the tables were backup residuals. Clearly some one had backed up the data in table just in case if anything goes wrong and then forgot dropping the table. Or just created a table for one time reporting purposes and then never cleaned it up again. Further investigation also showed that there were few legacy tables with no rows. We will come back to this bit in a few.Lets get to the part where we find out how we get the unused tables.
Starting from SQL 2005 MS introduced something called as DMV or Dynamic Management Views. Every time we access the table either clustered or the Heaps it records the stats. The query is as below
Declare @DBName varchar(20)
Declare @SQL_Str nvarchar(1000)
set @SQL_Str='select o.name, i.type_desc FROM ['+@DBName+'].sys.indexes i INNER JOIN ['+@DBName+'].sys.objects o ON i.object_id = o.object_id INNER JOIN ['+@DBName+'].sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id INNER JOIN sys.databases sd ON sd.name ='''+ @DBName + ''''
set @SQL_Str =@SQL_Str +' LEFT OUTER JOIN '+@DBName+'.sys.dm_db_index_usage_stats ius ON i.object_id = ius.object_id AND i.index_id = ius.index_id AND ius.database_id = sd.database_id '
set @SQL_Str =@SQL_Str +' WHERE i.type_desc in (''HEAP'',''CLUSTERED'') '
set @SQL_Str =@SQL_Str +' AND COALESCE(ius.user_seeks, ius.user_scans, ius.user_lookups, ius.user_updates) IS NULL AND sd.name <> ''tempdb'''
set @SQL_Str =@SQL_Str +' AND o.is_ms_shipped = 0 AND o.type <> ''S'''
EXECUTE sp_executesql @SQL_Str
I use the script above just set the variable @DBName to the set the current DB name.
Now, we have to be careful when we use this script. Although the SQL Server records the tables where we have accessed the data it wipes out the records every time the server has restarted. So be mindful about that . The rule of thumb which i have devised for myself is that allow the system to run for month and half which will mean that month end process if any have been run. Also, if there were people who were not in office have come back and used the system for few days. Again, going back to the point which i left above. Some dev’s have nasty habit of leaving the tables in join even without using the tables actively in query. What will happen is when you drop the table, the testers / users will complain of not being able to use the system.
Again, when it comes to dropping the tables we should just rename them to start their name with ‘z’. What this will do is push the table to the bottom and give you clear view of the tables to be dropped. Also what this does is if you happen to mark the tables which out of the blue is started to be used again you can simply rename the table and bring the system to working state. And once you have confirmed that the tables are no longer used you can make a note of the date when you had dropped the table so you can resurrect the tables from backup if required. This step should not be necessary but as you must have experienced ‘Never say Never’. Another step you could take is to search your source code and stored procedures for the name of the tables. This is fairly cumbersome but sometimes necessary.