How can you track the progress of a SQL update?

Question

Let's say I have an update such as:

  UPDATE [db1].[sc1].[tb1] 
  SET c1 = LEFT(c1, LEN(c1)-1) 
  WHERE c1 like '%:'

This update is basically going to go through millions of rows and trim the colon if there is one in the c1 column.

How can I track how far along in the table this has progressed?

Thanks

This is sql server 2008

Answer 1

You can use the sysindexes table, which keeps track of how much an index has changed. Because this is done in an atomic update, it won't have a chance to recalc statistics, so rowmodctr will keep growing. This is sometimes not noticeable in small tables, but for millions, it will show.

-- create a test table
create table testtbl (id bigint identity primary key clustered, nv nvarchar(max))

-- fill it up with dummy data. 1/3 will have a trailing ':'
insert testtbl
select
    convert(nvarchar(max), right(a.number*b.number+c.number,30)) + 
    case when a.number %3=1 then ':' else '' end
from master..spt_values a
inner join master..spt_values b on b.type='P'
inner join master..spt_values c on c.type='P'
where a.type='P' and a.number between 1 and 5
-- (20971520 row(s) affected)

update testtbl
set nv = left(nv, len(nv)-1)
where nv like '%:'

Now in another query window, run the below continuously and watch the rowmodctr going up and up. rowmodctr vs rows gives you an idea where you are up to, if you know where rowmodctr needs to end up being. In our case, it is 67% of just over 2 million.

select rows, rowmodctr
from sysindexes with (nolock)
where id = object_id('testtbl')

Please don't run (nolock) counting queries on the table itself while it is being updated.