How to Find Last Usage of Objects in SQL 2008 or higher?

When working with legacy database you will find lots of tables and other objects that you don’t use but have no clue if these are being used by any applications.  As we are moving our database to SQL Azure the need for having clustered index in all tables came up as requirement and we currently have many tables without clustered index!

So the plan is to figure out the tables not being used and then started to see how to add clustered index to remaining tables.

I have used the following SQL query to find out the last usage (seek, scan, update) for the tables in a heavily used database.

select OBJECT_NAME([object_id])
,last_user_seek, last_user_scan,last_user_update
from sys.dm_db_index_usage_stats
where database_id = DB_ID()

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s