Deleting millions of rows from a MSSQL server table
The performance problem is due to the non-sargable expression YEAR(CREATEDATE).  Applying a function to a column in a WHERE clause prevents efficient use of the index on the column.  Below is an alternative technique, which uses an inclusive start and exclusive end for the datetime range.

Note the use of SET ROWCOUNT is deprecated for DELETE, INSERT and UPDATE statements.  Use TOP instead.

DELETE TOP(10000) table1
FROM dbo.table1
JOIN dbo.table2
    ON table2.DOC_NUMBER = table1.DOC_NUMBER
    AND table2.DOC_YEAR = table1.DOC_YEAR
WHERE 
    CREATEDDATE &gt;= '20070101' 
    AND CREATEDATE &lt; '20100101';


I'm not sure the purpose of @tmpcount in your script other than perhaps controlling the number of iterations for testing.