We have a collection of very old, horribly designed databases and I have been given the green light to tear them out and restructure them. However, these databases have no normalization, numerous fields have been empty for years, or re-purposed without documentation, and we have a slew of legacy applications and public websites that use various pieces of this data, but no one in house has any idea what does what to what.
How could I determine which fields in these tables are being used? Is there a way in SQL server, or using a third party tool if need be, to see a history of usage? or to set up some additional logging to determine this usage? "Usage" ideally meaning when it was last updated or inserted, and when or how often it is included in a select statement.
In addition to this, we are migrating from SQL 2005 to 2008. So a solution using either server type would work as I should be able to bring the 05 dbs up to 08 compliance.
Best Answer
Personally I would use the DMVs as my initial investigation to look for major activity. This can help target your investigation so you can create the right profiler traces. (As Sam mentions, you want to be careful with these so you don't create a performance problem.)
Since you mention you have the option of using sql 2008, the new auditing features may be useful for you.
Here is what I recommend looking at:
Look for object access with index DMVs
Two DMVs will be the most useful:
sys.dm_db_index_usage_stats(BOL here) andsys.dm_db_missing_index_details(BOL here).Look for the most frequent and highest impact queries in the cache
Use
sys.dm_exec_query_statsqueries to find the most frequently run queries. BOL here.Good luck!