After we decide to which objects are used/unused in our production SQL server, then i think the fast way about this. Maybe you think that i want to read the tuning advice but we will kill two birds with one stone ( we do not throw any stone to lovely birds  ), at the end, we will have which table accessed how many times. This can be a great information to find some bottlenecks on your software project.

Our start up point is schema of sys. After SQL Server 2005, index access counts are available on this schema. Do not worry, if your table has no indexex or primary key, also there will be one row at least for this.

Thanks to Arnaud Aubert for this great query;

 

SELECT t.name AS 'Table', 
  SUM(i.user_seeks + i.user_scans + i.user_lookups) 
    AS 'Total accesses',
  SUM(i.user_seeks) AS 'Seeks',
  SUM(i.user_scans) AS 'Scans',
  SUM(i.user_lookups) AS 'Lookups'
FROM sys.dm_db_index_usage_stats i 
RIGHT OUTER JOIN sys.tables t ON (t.object_id = i.object_id)
GROUP BY 
  i.object_id, 
  t.name
ORDER BY [Total accesses] DESC

Now you can make your decide better on your system :)

If you like this, follow my RSS channel!