How to avoid Data Purging?

Question

How to avoid Data Purging?

sram24_mca

SSCrazy

Points: 2633
More actions
December 1, 2015 at 2:41 am

#305710

Frequently we are running the Data Purging in our production server. How to avoid this to improve the performance. Is there any better solution for resolving the problem
Regards,
Ram

Viewing 12 posts - 1 through 11 (of 11 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Help in resolving what problem? If you don't want to run the data purging scripts, then don't run them.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

Yes, or run them less frequently, or at a quieter time.

John

sram24_mca SSCrazy Points: 2633 More actions · Answer 3

If we are not running the data purge script, then the server is too slow and affecting the performance as well.

Regards,

Ram

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Ok, so either run the purging in quieter times, or in smaller chucks or fix the query and indexing problems that's causing queries to be slow.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sram24_mca SSCrazy Points: 2633 More actions · Answer 5

We are running in SQL SERVER 2008R2 , Is there any better solutions in SQL 2012 or SQL 2014 versions?

Regards,

Ram

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 6

Only you can see your database, so it's up to you to investigate what's causing the problems and hence whether the best solution would be batching, rescheduling, rewriting, upgrading or something else. If you need any help with that, please post something more specific. This article[/url] should help you.

John

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

It sounds like you are unfamiliar with debugging what could possibly be slowing down your system.

Are you familiar with SQL Profiler to capture long running queries and then see if any index tuning could speed them up?

Are you running any update stats on the database? sp_updatestats

Have you or anyone else identified some slow queries and looked at rewriting them or at index tuning opportunities?

Have you rebuild indexes on the tables that you have been purging data from?

Have you assessed memory, CPU, data file layout on the server?

Simply going from SQL 2008 to 2012 or 2014 isn't really going to help you.

Jeff Moden SSC Guru Points: 1003875 More actions · Answer 8

sram24_mca (12/1/2015)
Frequently we are running the Data Purging in our production server. How to avoid this to improve the performance. Is there any better solution for resolving the problem
Regards,
Ram

What are you calling "Data Purging"? Are you talking about some home-grown procs/scripts that delete older data from tables or what? And which Edition of SQL Server do you have? Standard or Enterprise or ??? It would also be helpful to know (if home grown) how big the affected tables are and what percentage of the table will be purged.

And, if home-grown, it would be nice to see some of your purge code so that we can figure out what the heck it is you're trying to do and maybe give you a way to improve the performance.

Otherwise, Gail is right... don't run them during busy times.

--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)

Smendle Hall of Fame Points: 3969 More actions · Answer 9

The ultimate solution would involve partitioning based on date, if your purging is date based, then all you would need to do is drop the partition to delete, much faster than running DELETE tsql statements, there are other issues related to partitioning but if performance is your #1 concern than partitioning, in my opinion, is the only way to go.

Sergiy SSC Guru Points: 110206 More actions · Answer 10

You must create a clustered index with definitions matching your purging criteria.

If during purging you delete all the records with DateInserted less than 90 days ago, then that must be the 1st column of you clustered index.

There is a chance that you won't need purging at all after sorting out the issue with the indexing.

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1003875 More actions · Answer 11

sram24_mca (12/2/2015)
If we are not running the data purge script, then the server is too slow and affecting the performance as well.
Regards,
Ram

The server runs fine. It's the code that's slow. As Sergiy points out, it may be soley because of a bad index choice, although I suspect there will be more to it.

--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)