I was told that it could be possible to analyze the consumption of a given row. I already know I can use sys.dm_db_index_usage_stats to see the reads / writes on a given index (thus the table) but I need to know for the row level.
For example: The invoice table has 5 million rows, spread over 7 years. If I can determine that no rows are getting read for the first 5 years, this data will be archived. Now, if i have 100 tables that contain 7 years data, maybe archiving the first 5 years is good enough for the invoices but not for the other 99 tables.
Any way I can determine the data consumption / usage of rows for specific archiving?