How do I estimate the execution time of a SQL Server Update Statement?

Question

I would like to perform an update statement impacting 10 million rows. It is a simple update like

UPDATE Table 
SET ColumnX = 0;

I did notice that a simple SELECT like

SELECT Column 
FROM Table

takes about 34 seconds.

As it is a table used in production and the table is rather big (2,8 GB data, index 1,6 GB) I would like to estimate the runtime before executing the statement.

The update is performed on SQL Server 2008 R2.

Is there a possibility to obtain the estimated runtime?

Answer 1

There is no way to estimate this.

You ~could~ backup the database, restore it on similar hardware...and try that. But since no one will probably be hitting the restored-backup, even that is not an exact measurement.

If you can deal with having the updates occur....but without a transaction (aka, its ok for some rows to have the correct value for a few seconds...while the others do not), then I would invite the goldie locks approach.

Goldie Locks would be........not all the rows in one hit, but not row by row either.

Here is a pointer...Update TOP N ...

Maybe try 1,000 or 10,000 or something like that.

https://granadacoder.wordpress.com/2009/07/06/update-top-n-order-by-example/

........

Below is a modified example. I have 1,000 rows. I update 100 rows at a time, so it loops 10 times (10 x 100 = 1000)...to update the column.

It's a "goldie locks" trick..........I think this would serve you well on a real production db under load.

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Television]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
      BEGIN
            DROP TABLE [dbo].[Television]
      END
GO


CREATE TABLE [dbo].[Television] (
      TelevisionUUID [uniqueidentifier] not null default NEWSEQUENTIALID() ,
      TelevisionName varchar(64) not null ,
      TelevisionKey int not null ,
      IsCheckedOut bit default 0
)    
GO


ALTER TABLE dbo.Television ADD CONSTRAINT PK_Television_TelevisionUUID
PRIMARY KEY CLUSTERED (TelevisionUUID)
GO


ALTER TABLE dbo.Television ADD CONSTRAINT CK_Television_TelevisionName_UNIQUE
UNIQUE (TelevisionName)
GO


set nocount on

declare @counter int
select @counter = 11000
declare @currentTVName varchar(24)
declare @TopSize int
select @TopSize = 100

while @counter > 10000  /*  this loop counter is ONLY here for fake data,….do not use this syntax for production code */
begin

      select @currentTVName = 'TV:' + convert(varchar(24) , @counter)

      INSERT into dbo.Television ( TelevisionName , TelevisionKey ) values ( @currentTVName , @counter)

      select @counter = @counter - 1     
end


select count(*) as TV_Total_COUNT from dbo.Television


print ''
print 'Checked Out Items, Should be Zero at this point'
select count(*) as PRE_UPDATE_COUNT from dbo.Television tv where tv.IsCheckedOut <> 0


/*
--Does not Work!
Update TOP (10) dbo.Television
      Set IsCheckedOut = 1
FROM
      dbo.Television tv
ORDER BY tv.TelevisionKey
*/
declare @AuditTrail table ( TelevisionUUID uniqueidentifier , OldIsCheckedOut bit , NewIsCheckedOut bit )

declare @LoopCounter int
select @LoopCounter = 0

while exists ( Select top 1 * from dbo.Television tv where tv.IsCheckedOut = 0  )
BEGIN
            select @LoopCounter = @LoopCounter + 1
            /*print '/@LoopCounter/'
            print @LoopCounter
            print ''*/

                ;
                WITH cte1 AS      
                 (  SELECT
                      TOP (@TopSize)

                   TelevisionUUID , /* <<Note, the columns here must be available to the output */
                   IsCheckedOut       
                      FROM  
                            dbo.Television tv     
                    WITH ( UPDLOCK, READPAST , ROWLOCK ) --<<Optional Hints, but helps with concurrency issues  
                      WHERE 
                            IsCheckedOut = 0             
                      ORDER BY tv.TelevisionKey DESC       /* This order by is optional */
                )
                UPDATE cte1
                      SET  IsCheckedOut = 1
                output inserted.TelevisionUUID , deleted.IsCheckedOut , inserted.IsCheckedOut into @AuditTrail ( TelevisionUUID , OldIsCheckedOut , NewIsCheckedOut )
                ;
END

print '/Number of Update Loops/'
print @LoopCounter
print ''


print ''
print 'Newly Checked Out Items'
select count(*) as POST_UPDATE_COUNT from dbo.Television tv where tv.IsCheckedOut <> 0

print 'Output AuditTrail'
select * from @AuditTrail
print 'Not checked out items'
select count(*) as TVCOUNTIsNOTCheckedOut from dbo.Television tv where tv.IsCheckedOut = 0

Answer 2

I think - no way. However, it must be faster than SELECT (if the column is not included to index).

Answer 3

As a rough estimate, you can try updating a fraction of the table, then use math to calculate the whole time. For example, update the top 100,000 rows (1/100 of your table) and then take that time and multiply it by 100 to get a rough estimate of how long the full table will take. The only problem is that blocking could make this inaccurate (if there are parts of the table that are seldom accessed they will update much quicker than those that are frequently accessed).

The best thing to do is use a loop to update the table X rows at a time.

Answer 4

i try to estimate one single update like that

   DECLARE @i datetime
set @i=getdate()
DECLARE @t datetime

UPDATE dbo.EmployeeCity
SET City = 'dudi' -- varchar
where srno=1
set @t=getdate()

SELECT DATEDIFF(microsecond ,@i,@t) micro_sec
,      @i 'start'
,      @t 'end'

and most of the time i get 3000 microsec

this is definitely not accurate only estimation