Retrieving Update Request Execution Information

Question

On Sql Server 2008, I have a slow update request that works for 3 hours. Is there a way to get statistics (say, how many lines have changed so far or something else) about this execution (of course, at execution)? So that I can decide to cancel the request and optimize it or let it finish.

Or else, should I do something before execution? If so, what can be done before execution to obtain information about the update request during its operation. (of course, without affecting performance to a large extent)

update to clarify:
the expression mentioned in the update statement (for example):
UPDATE myTable SET col_A = 'value1' WHERE col_B = 'value2'
In other words, this is the only query that updates the entire table

Answer 1

What can you do now?

You can try to run a separate query using the WITH (NOLOCK) link of the table hint to find out how many rows have been updated. For example, if the update instruction:

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

You can run this:

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

What can you do in the future?

​​ . , 1000 ( ). , ( SSMS), .

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

RAISERROR, , , . PRINT , , .

Answer 2

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

" , update (, ).

"READ UNCOMMITTED", , , select, . ... .

Answer 3

, , . , , - SQL Profiler /.

Answer 4

Not knowing exactly what you are doing, I can’t say for sure, but if you put the update in a procedure, use the command PRINTto output status updates at certain stages, these messages are displayed during the run-time procedures in the message tab next to the results tab.