9

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

Matt
  • 25,943
  • 66
  • 198
  • 303

3 Answers3

22

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.

RichardTheKiwi
  • 105,798
  • 26
  • 196
  • 262
  • 2
    Curious about your last remark. What is the harm in running (nolock) count queries on a table that is being updated? – Dan Sep 04 '15 at 07:42
  • @RichardTheKiwi - Great answer. Is it possible to use this method on temp tables? I'd like to monitor the update progress of temp table created in another window, but of course can't see it (perhaps temporarily using a global temp table would suffice?) – SQLServerSteve Jan 12 '18 at 23:33
1

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...)

gbn
  • 422,506
  • 82
  • 585
  • 676
0

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.

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
  • 1
    Although I `do` agree with breaking it into chunks for sizeable datasets, although for a non-index friendly query, it may require each iteration to revisit covered ground (and go further in the table) – RichardTheKiwi Jan 21 '11 at 01:59
  • 2
    -1 for stating incorrectly that there is "no way" and providing incorrect explanation. `Within minutes, my answer gets voted down` (yes it's you). – RichardTheKiwi Jan 26 '11 at 22:12
  • 1
    Anyone is free to [read this question](http://stackoverflow.com/questions/4800611/query-where-two-columns-are-in-the-result-of-nested-query) to come to their own conclusions about someone who admits to downvoting first to test a response. Fact is, activity in a question makes it visible to others -- not just me. Elapsed time proves nothing. Maybe you should think about downvoting for the sake of getting a reaction? – OMG Ponies Jan 26 '11 at 22:20