How Important archiving old data?

Question

How Important archiving old data?

Sam 1,476

Hi All,

How important is it archiving old data ? In recent days, We saw some timeouts and blockings happening for app API calls to the database. Later, found that they are using timeout parameter in their connection strings, The database size is 5TB.This is kind of warehouse but used as OLTP mixed workload.

Again, when trying to understand why queries are taking time, we observed the tables involved in API calls queries have grown from 10GB to 70GB in past 4 months. Then the application started blaming the dba team it's the database issue as the application has no code changes from last 8-9 months. During our initial analysis, found some of the queries, doesn't have proper filters, a lot of implicit conversions, lookups and only JOINS are involved of 5-6 tables which causing row explosions. from db size, we ensured fragmentation is taken care and stats are up to date.

Questions:

Where do you start your conversations with the app team in such situations? What specific questions to be asked?
When we suggested app team to archive some of the old data, they're hestitant & afraid that some downstream app team's who consumes data from this database, might ask for data requests for old data so they aren't ready to archive. Side effect is, quiries are running slow due to high reads.
In an ideal database world for an OLTP applications, how many years of data will be retained? 1 year,2 years or 3years? or is it totally dependent on application and business users? how important is it for DBA's to get this documented or getting business approval for archiving process?
In this scenario, what are the options available for a dba to improve performance? Any advise on balancing data retention needs with perf optimization?

Regards,

Sam

Karen Lopez 81 Reputation points MVP Volunteer Moderator

2024-03-30T21:50:53.1533333+00:00

Legal and compliance reasons should drive data retention rules, then business needs. First, you need to start with those before you ask DBAs what to do.

Also, this is a fairly complex questions and I'd typically say for complex questions, you should consider retaining an outside expert if you don't have in-house ones to help you with this. I'm not a query tuner, so I'm not saying it for my benefit.
Greg Low 1,980 Reputation points Microsoft Regional Director

2024-04-02T09:36:12.24+00:00

As Karen said, no-one can determine retention, etc. for you. That's an organization question. You either need the data or you don't.

With databases, "large" is a relative concept. I see many databases smaller and many much larger than what you describe.

Large databases never worry me. The only time they're a problem is if you start reading all the data in them.

Imaging walking into a huge old library. If you have a good index to the books, it probably doesn't matter how big the library is. You can find things. But if you start looking for a book by wandering along each bookshelf, the size of the library becomes a big issue, very quickly.

As databases get larger, you also need to start thinking about whether you're going to store all the data in one big "chunk" (or filegroup). That will become undesirable fast as the DB size grows. For example, if you are doing backups, there's probably no point backing up all the data that's not changing any more. But if it's all in a single filegroup, you don't have a choice.

Another issue is whether or not the older data is immutable? Can it still change, or can you make it available as a read-only copy? If so, that brings additional options.

Depending upon where the DB lives, filegroups might also give you options around tiered storage (perhaps it doesn't all have to run at the same speed).

You might also decide that the really old data is needed but you'll push it out into storage outside the database.

There are many, many options here.

Your answer

Karen Lopez 81 Reputation points MVP Volunteer Moderator

2024-03-30T21:50:53.1533333+00:00

Legal and compliance reasons should drive data retention rules, then business needs. First, you need to start with those before you ask DBAs what to do.

Also, this is a fairly complex questions and I'd typically say for complex questions, you should consider retaining an outside expert if you don't have in-house ones to help you with this. I'm not a query tuner, so I'm not saying it for my benefit.
Greg Low 1,980 Reputation points Microsoft Regional Director

2024-04-02T09:36:12.24+00:00

As Karen said, no-one can determine retention, etc. for you. That's an organization question. You either need the data or you don't.

With databases, "large" is a relative concept. I see many databases smaller and many much larger than what you describe.

Large databases never worry me. The only time they're a problem is if you start reading all the data in them.

Imaging walking into a huge old library. If you have a good index to the books, it probably doesn't matter how big the library is. You can find things. But if you start looking for a book by wandering along each bookshelf, the size of the library becomes a big issue, very quickly.

As databases get larger, you also need to start thinking about whether you're going to store all the data in one big "chunk" (or filegroup). That will become undesirable fast as the DB size grows. For example, if you are doing backups, there's probably no point backing up all the data that's not changing any more. But if it's all in a single filegroup, you don't have a choice.

Another issue is whether or not the older data is immutable? Can it still change, or can you make it available as a read-only copy? If so, that brings additional options.

Depending upon where the DB lives, filegroups might also give you options around tiered storage (perhaps it doesn't all have to run at the same speed).

You might also decide that the really old data is needed but you'll push it out into storage outside the database.

There are many, many options here.

Share via

How Important archiving old data?

Your answer