How to show query percentage or progress in SQL Server 2012 (SP4)?

Question

How to show query percentage or progress in SQL Server 2012 (SP4)?

Mark Miller 0

How to show query percentage or progress in SQL Server 2012 (SP4)? I have a very large purge query running: Total Number Txns To Be Purged: 124549981. The db is 588GB. I know the Live View didn't come along until Sql Server 2014 or 2016. Thanks.

1 answer

Your answer

Answer 1

Erland Sommarskog 121.4K MVP Volunteer Moderator

How are you running the purge? Is it one single statement? For this large amount of data, it is often a good idea to split up the work in chunks of, say, five millions rows at time. If you do this, showing progress becomes a trivial matter. Important! If you do things in chunks, you need an index to support your chunk condition, so that you don't have to scan the big table for every chunk.

If you are running all as one statement, about the only option is to run a

SELECT COUNT(*) FROM tbl WITH (NOLOCK) WHERE ...

to get an idea of the progress. This is one of the few situations where NOLOCK actually is useful.

Mark Miller 0 Reputation points

2023-05-01T15:19:51.31+00:00

The query has errors when executing:

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near '.'.
Mark Miller 0 Reputation points

2023-05-01T15:33:55.4066667+00:00

Using multiple purge statements in the same script.
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2023-05-01T16:00:14.8466667+00:00

The query has errors when executing:

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near '.'.

Yes, the query I gave you was only an outline. The dots here stands for "fill in the blanks". I don't know your tables, I don't know the statements you are running. So you will need find out some good condition. For instance, it could be the same condition that you use for the purge, so that you see how many rows that remains to purge.

Share via

How to show query percentage or progress in SQL Server 2012 (SP4)?

1 answer

Your answer