I have a customer right now that needs a LOT of cleanup... BIG fragmentation! The tables have not been properly maintained, and the fragmentation really is tremendous. We backed up the db and restored it to a dev server so that I could test out my 'solution'. About 3 hours into it, I realized that I probably needed to revisit my approach. At the very minimum, I wanted to exclude the older, unused objects. No sense spending any time on the tables they are not using.
How do I know which objects are not being used anymore? This is a very quick way to show me all of the tables that have NOT been touched since the last service restart.
SELECT
ao.[name] [Table],
s.[name] [Schema],
[create_date] [Created],[modify_date] [LastModified]
FROM
sys.all_objects ao JOIN sys.schemas s
ON ao.schema_id = s.schema_id
WHERE
OBJECT_ID NOT IN (
SELECT OBJECT_ID
FROM sys.dm_db_index_usage_stats
)
AND [type] = 'U'
ORDER BY
[modify_date] DESC
Check this out for more detailed information on the sys.dm_db_index_usage_stats DMV:
http://technet.microsoft.com/en-us/library/ms188755.aspx
Another thing to look for would be the empty tables. Many times tables are created and developed in production, but never actually implemented. You can use this newer post of mine to bring back all tables that are empty.
http://www.sqlfingers.com/2014/10/list-all-empty-tables-in-your-sql.html
REMEMBER -- just being empty does not necessarily mean it isn't used. It could be a working table that is purged regularly as it is being used. Keep that in mind before you go and drop something just because it is empty.
Very helpful, thank you!
ReplyDeleteGlad you're using the site, and very glad to have helped!
Deleteit worked thanks
ReplyDeleteGood deal!!
DeleteI think this help to see if the strcuture of the table changed but no if it is used, you can not change a table inlong time but use it
ReplyDelete