How can you track the progress of a SQL update?

Let's say I have an update such as:

  UPDATE [db1].[sc1].[tb1] 
  SET c1 = LEFT(c1, LEN(c1)-1) 
  WHERE c1 like开发者_Go百科 '%:'

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

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.

Not really... you can query with the nolock hint and same where, but this will take resources

It isn't an optimal query with a leading wildcard of course...)

Database queries, particularly Data Manipulation Language (DML), are atomic. That means that the INSERT/UPDATE/DELETE either successfully occurs, or it doesn't. There's no means to see what record is being processed -- to the database, they all had been changed once the COMMIT is issued after the UPDATE. Even if you were able to view the records in process, by the time you would see the value, the query will have progressed on to other records.

The only means to knowing where in the process is to script the query to occur within a loop, so you can use a counter to know how many are processed. It's common to do this so large data sets are periodically committed, to minimize the risk of failure requiring having to run the entire query over again.