December 15, 2011 at 2:37 am
I am database owner for a database around 800 GB. we have auditing tables created for each of the table and triggers populate data in those tables. Currently we are using data purging on daily basis. As the data volume is getting increased day by day, the time of purging job is increasing as well. Is there a way I can do my purging faster ? The columns on the basis of purging is done and which are included in where condition of delete statement are covered by non clustered index. Could you please give me some suggestions on the purging strategy ?
December 15, 2011 at 6:31 am
sqlnaive (12/15/2011)
I am database owner for a database around 800 GB. we have auditing tables created for each of the table and triggers populate data in those tables. Currently we are using data purging on daily basis. As the data volume is getting increased day by day, the time of purging job is increasing as well. Is there a way I can do my purging faster ? The columns on the basis of purging is done and which are included in where condition of delete statement are covered by non clustered index. Could you please give me some suggestions on the purging strategy ?
I would partition the auditing tables in a way that fits the purging strategy meaning, if you want to preserve the last month of auditing data I would partition by date/range in one month slices.
Once you do that purging becomes really easy, you just have to either truncate or drop the unwanted ... better saying purgeable partitions and you are done. This particular strategy makes puging easy and inexpensive in terms of I/O.
Hope this helps.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.December 16, 2011 at 8:21 am
sqlnaive (12/15/2011)
I am database owner for a database around 800 GB. we have auditing tables created for each of the table and triggers populate data in those tables. Currently we are using data purging on daily basis. As the data volume is getting increased day by day, the time of purging job is increasing as well. Is there a way I can do my purging faster ? The columns on the basis of purging is done and which are included in where condition of delete statement are covered by non clustered index. Could you please give me some suggestions on the purging strategy ?
I'm assuming that the issue with the job taking longer is either that it blocks or otherwise interferes with other processing/data access?
A technique that I've used in the past is to break the purge activities down into smaller oeprations, either by running the job more often, or by looping the delete with a rowcount restriction (or both).
I've used the template below many times in the past, especially when initially implementing archiving or cleanup on massive tables. It breaks the deletions into smaller implicit transactions, doesn't bloat the TXLOG as quickly, doesn't generally create locking problems. It is generally production-friendly, increasing the window when these deletions can occur (although I would recommend only running this in working hours as a last resort).
Hope this helps.
Andy
-- create some very quick test data
CREATE TABLE #Test(Number INT)
INSERT #Test
SELECT Number
FROM master..spt_values (NOLOCK)
CREATE NONCLUSTERED INDEX IX_Number ON #Test(Number)
DECLARE
@rcINT,
@PurgeLimitINT,
@BatchSizeINT
SELECT@rc = 1,
@PurgeLimit = 2000,
@BatchSize = 10
SELECT COUNT(*) AS 'Iniital Rowcount' FROM #Test
WHILE @rc > 0
BEGIN
-- SET ROWCOUNT @BatchSize (for earlier versions of SQL Server as I cant remember when TOP() was introduced)
DELETE TOP (@BatchSize) --as SET ROWCOUNT soon to be deprecated
FROM #Test
WHERE Number < @PurgeLimit
SELECT @rc = @@ROWCOUNT
END
SELECT COUNT(*) AS 'Final Rowcount' FROM #Test
DROP TABLE #Test
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply