500k in 10 minutes on production. Sqlplan attached

SELECT TOP (500000) X.ADDRESS_ID

FROM ( SELECT ADDRESS_ID FROM dbo.ADDRESS

EXCEPT SELECT ADDRESS_ID1 FROM EARLY_PAYOFF_INFO...

"nothing has examined the process well enough to know if any given Address_ID is about to be used in one of the other tables. There has to be either a...

Sergiy (2/14/2016)

---

Indianrock (2/14/2016)

---

You're correct that the Address table may not be worth bothering with as far as purging. There are ten other tables such as these which have more...

By "Except" were you referring to this alternative approach to all of the outer joins? ( sql code block below )? Right now the original query with...

You're correct that the Address table may not be worth bothering with as far as purging. There are ten other tables such as these which have more delete possibilities.

TableName______________________Rows____________Records...

Latest test in QA ( somewhat slow systems ). This uses the original criteria to find "delete-able" records, simplified temp table with just the single address_id column, top 100k records...

The application and database were developed by a .net development team ten years ago. A re-write project is about to start. We are an out-source...

Well I certainly hope sql server central.com doesn't lose any of this as it will take a while to go through. 🙂 We do have extensive index maintenance...

Thanks again Hugo. I will test all of those.

Attached plans for the alternative query. The "top 5 million" version is still running. To change this delete logic I'd have to run it through QA which could...

The identity column is used in the while loop as shown below.

SELECT @counter = MAX(ID)

FROM #tempdata;

SET @rowcount = 1;

WHILE @rowcount > 0

...

Thanks Hugo, I will thoroughly review your post.

I'll experiment with the number of rows gathered into the temp table up front -- this is only done once per day as...

Yes thanks, I added those 3 indexes in one QA environment but the initial select of 20k records into a temp table still ran over an hour before I killed...

We used to run these purge jobs on various tables on Sundays, deleting 50k records at a whack. Since our application tends to run 24/7 to one degree or...

Here's the sql plan.

<?xml version="1.0" encoding="utf-16"?>

<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.2" Build="11.0.5582.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">

<BatchSequence>

<Batch>

<Statements>

...