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?

  • 2
    Read/write stats are not kept for individual rows. You could create a filtered index (with a WHERE clause) that filters out the years in question, then see over time if the index is ever used; however this relies on the index being more efficient than whatever other indexes are present. It's not foolproof. – Max Vernon May 6 '14 at 21:42

Analyzing the usage pattern of the server via profiler and/or extended events is going to be the way to go. Or ask the business unit what data they want archived. If they say that they don't want any data archived, then the conversation is over.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.