Getting info about running update query

Question

On Sql Server 2008, I have a slow-running update query that has been working for 3 hours. Is there any way to get any statistics (say, how many rows have changed so far or something else) about this execution (of course, while executing) ? So that I can make a decision between canceling query and optimize it or let it finish.

Or else, should I have done something before execution ? If so, what can be done before execution for getting info about an update query while it is running. (of course, without affecting the performance largely)

an update for clarifying:
the update statement mentioned in question is (e.g.):
UPDATE myTable SET col_A = 'value1' WHERE col_B = 'value2'
In other words, it is a single query that updates whole table

Answer 1

What can you do now?

You could try running a separate query using the WITH(NOLOCK) table hint to see how many rows have been updated. e.g. if the update statement is:

UPDATE MyTable
SET MyField = 'UPDATEDVALUE'
WHERE ID BETWEEN 1 AND 10000000

You could run this:

SELECT COUNT(*)
FROM MyTable WITH (NOLOCK)
WHERE ID BETWEEN 1 AND 10000000
    AND MyField = 'UPDATEDVALUE'

What can you do in future?

You could do the update in batches, and output progress as it goes. e.g. use a loop to update the records in chunks of say 1000 (arbitary value for point of explanation). After each update chunk completes, print out the progress (assuming you are running it from SSMS) e.g.

DECLARE @RowCount INTEGER
SET @RowCount = 1
DECLARE @Message VARCHAR(500)
DECLARE @TotalRowsUpdated INTEGER
SET @TotalRowsUpdated = 0

WHILE (@RowCount > 0)
    BEGIN
         UPDATE TOP (1000) MyTable
         SET MyField = 'UPDATEDVALUE'
         WHERE ID BETWEEN 1 AND 10000000
              AND MyField <> 'UPDATEDVALUE'

         SELECT @RowCount = @@ROWCOUNT
         SET @TotalRowsUpdated = @TotalRowsUpdated + @RowCount
         SELECT @Message = CAST(GETDATE() AS VARCHAR) + ' : ' + CAST(@TotalRowsUpdated AS VARCHAR) + ' records updated in total'
         RAISERROR (@Message, 0, 1) WITH NOWAIT
    END

Using RAISERROR like this ensures progress messages are printed out immediately. If you used PRINT instead, the messages are spooled up and not output immediately so you wouldn't get to see real-time progress.

Answer 2

It would really help us if you posted the query so that we can look at it and try to help you. One way to see what is going on is to use SQL Profiler to profile the updates / inserts.

Answer 3

From http://www.sqlnewsgroups.net/group/microsoft.public.sqlserver.server/topic19776.aspx

"You should be able to do a dirty read to produce a rowcount of rows satisfying the update clause (assuming that value wasn't already used)."

If you set your query to use "READ UNCOMMITTED" you should be able to see rows that have been updated by your statement with a select statement having the proper criteria. I'm no expert on this...so just my guess.

Answer 4

Without knowning exactly what you're doing I can't say for sure, but if you put the update in a procedure then use the PRINT command to output status updates at specific steps, these messages are output during the procedures run time in the messages tab that's next to the results tab.