Data Purge benifits

Question

Data Purge benifits

SQL!$@w$0ME

SSChampion

Points: 12349
More actions
November 14, 2014 at 5:56 am

#397641

I would like to do a purge for my 500 GIG database. Basically to remove data over 5 years.
What are the benifits in a general purge approach other than saving disk space? Like in terms of database performance...
Thanks in advance for your inputs..

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Summer90 SSC-Dedicated Points: 32891 More actions · Answer 1

It really depends. You have way to generic of a question without details. just because a database size is large it could be hundreds of medium sized tables or one large table.

If you have very poor indexing and you have one large table and remove over 60% of the data within that table and rebuild indexes you could see a big performance benefit.

It is always a good thing to remove rows from tables that are no longer needed if you can prove without a doubt the client base doesn't need the data. You will only know the performance gain when you make a copy of the db, restore it to another db name, purge the data and run some query testing before/after and compare the explain plan.

We have done data purging from some key tables and see some great benefits and some almost no one knows as well.

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 2

Heh... every time that I've deleted legacy data, even stuff over 5 years, someone finds a reason to "need it back NOW!". I don't delete. I "archive" on separate slower disks not on the SAN.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Summer90 SSC-Dedicated Points: 32891 More actions · Answer 3

Sometimes what we do is create an archive db and put the old data there for a year or so. If no one wants it then we get approval to drop it.

That was one way we upgraded a database froM SQL2000 to 2008R2. Made the backup/restore of the live data that people wanted a much smaller outage. That is sort of how I sold the upgrade taking a shorter time... if we delete this data you DON'T ever use the outage is smaller. They were a little nervous about deleting data so we came up with the archive db. Two years later we dropped the archive db since no one did request that data.