brownph99 (3/10/2013)

---

I'm working with both a Vendor's DB and a purge script they've provided, and I need some outside opinions.

This purge runs incredibly long, especially on our SAN. Our first thought, was because the purge was querying tables using non-indexed columns, especially several large tables the big one being 60m rows and 160GB.

We added indexes and we definitely saw the performance improvements you'd expect when just purging other tables. Additionally, after about an hour of the purge, I can query the big table being purged and according to SQL Server receive the expected post-purge counts/values.

The catch here is that 5-6 hours after the table "looks" purged, the job is continuing to alternate in pagiolatch waits. Checking the resource information confirms that it's the same table requiring the wait.

It doesn't appear to be a script issue, per se. But, does this suggest anything that I can give back to my SAN admins or the vendor?

Portion of the script (implicit transactions is off by default in this environment):

BEGIN

SELECT@table_name=TABLE_NAME,@duration=PURGE_DURATION FROM OLTPDB_PURGE WHERE TABLE_NAME = 'CHECKPOINTDATA';

SET @purge_date = CURRENT_TIMESTAMP-@duration;

DECLARE m_cursor CURSOR FOR SELECT ID FROM CHECKPOINTDATA WHERE CREATEDATE < @purge_date

OPEN m_cursor

FETCH NEXT FROM m_cursor INTO @str_id;

WHILE @@FETCH_STATUS = 0

BEGIN

DELETE FROM CHECKPOINTDATA WHERE ID = @str_id;

SET @record_cnt = @record_cnt + 1;

IF (@record_cnt >=1000)

BEGIN

COMMIT;

SET @record_cnt = 0;

END

FETCH NEXT FROM m_cursor INTO @str_id;

END

CLOSE m_cursor

DEALLOCATE m_cursor

END