Estimating Completion Times of a SQL Process

This is the sort of query I've written from scratch a hundred times, or done manually using my trusty old friend calc.exe. You have a running process, and you know it has to chew through so many rows, and you have a count (possibly with a nolock hint) query that can see how many it has processed, or many are remaining to process. Well, I finally wrote out an adaptable version of this query that I can use as a template when I'm trying to quickly gauge how long a process is going to take. Basically you adjust the two "count" queries for your specific scenario, set either a target count query or a target fixed value (can be zero if you're counting backwards), and set a delay time between the two count values.

DECLARE
        @t INT,                 --Target value
        @c1 INT,                --First count value
        @c2 INT,                --Second count value
        @tm1 DATETIME,          --First time value
        @tm2 DATETIME;          --Second time value

--Alter delay value depending how patient you are.
DECLARE @SampleDelay CHAR(8) = '00:01:00';

--Determine Target Count
--Alter the following based on the specifics of the situation, or use a hard coded value:
SELECT @t=
--COUNT(*) FROM someTable WHERE WITH (NOLOCK) ThisValue='this';

--Determine First Count
SELECT @tm1=GETDATE(), @c1=
--Alter the following based on the specifics of the situation:
--COUNT(*) FROM someOtherTable WITH (NOLOCK) WHERE ThisValue='that';

--Variable Delay
WAITFOR DELAY @SampleDelay;

--Determine Second Count
SELECT @tm2=GETDATE(), @c2=
--Alter the following based on the specifics of the situation...should be same as first count query barring variable names
--COUNT(*) FROM someOtherTable WITH (NOLOCK) WHERE ThisValue='that';

--Compute Estimates
PRINT 'Target Count:         ' + CAST(@t AS VARCHAR(100));
PRINT 'Initial Count:        ' + CAST(@c1 AS VARCHAR(100)) + ' at ' + CAST(@tm1 AS VARCHAR(100));
PRINT 'Second Count:         ' + CAST(@c2 AS VARCHAR(100)) + ' at ' + CAST(@tm2 AS VARCHAR(100));
PRINT 'Elapsed seconds:      ' + CAST(DATEDIFF(second,@tm1,@tm2) AS VARCHAR(100));
PRINT 'Records processed:    ' + CAST(ABS(@c2-@c1) AS VARCHAR(100));
PRINT 'Rate per second:      ' + CAST(((@c2-@c1)*1.0)/DATEDIFF(second,@tm1,@tm2) AS VARCHAR(100));
PRINT 'Records remaining:    ' + CAST(ABS(@t - @c2) AS VARCHAR(100));
PRINT 'Seconds remaining:    ' + CAST(CAST((@t - @c2)/(((@c2-@c1)*1.0)/DATEDIFF(second,@tm1,@tm2)) AS INT) AS VARCHAR(100));
PRINT 'Estimated completion: ' + CAST(DATEADD(second,(@t - @c2)/(((@c2-@c1)*1.0)/DATEDIFF(second,@tm1,@tm2)), GETDATE()) AS VARCHAR(100));

Gives you rate information, time and records remaining, and calculates an estimated completion time. Here's a sample of the output:

Target Count: 20000
Initial Count: 1346 at Aug 15 2016 2:05PM
Second Count: 1498 at Aug 15 2016 2:06PM
Elapsed seconds: 60
Records processed: 152
Rate per second: 2.533333333333
Records remaining: 18502
Seconds remaining: 7303
Estimated completion: Aug 15 2016 4:08PM