Last year, I was running cursors against massive datasets, and they seemed to run forever. It was like watching paint dry or grass grow, because, while SQL Server Management Studio shows the time elapsed and a running ticker of (1 row(s) affected) messages, there is no indication of what percentage of the dataset has been processed. So I ended up developing this progress bar in SQL to add to my scripts.

The first step is to turn off the NOCOUNT option, which silences the default SSMS output.

SET NOCOUNT OFF;
GO

Next, we declare and initialize a few additional variables.

DECLARE @PROGRESS_MAX NUMERIC, @PROGRESS_CURRENT NUMERIC, @START DATETIME;
SELECT @START = GETDATE();
SELECT @PROGRESS_MAX = COUNT(*) FROM TempTable
SELECT @PROGRESS_CURRENT = COUNT(*) FROM TempTable WHERE Processed = 1

The variable @START allows the script to calculate the amount of time elapsed, @PROGRESS_MAX is the total number of records to be processed, and @PROGRESS_CURRENT is the total number of records that have already been processed. This methods permits you to restart the progress bar if the script terminates prematurely.

Just before the end of the cursor or loop, include the following script.

SET @PROGRESS_CURRENT = @PROGRESS_CURRENT + 1
IF @PROGRESS_CURRENT % 1000 = 0 PRINT CONCAT('Progress: ',@PROGRESS_CURRENT, REPLICATE(' ',8-LEN(@PROGRESS_CURRENT)),'[',REPLICATE('#',CEILING((@PROGRESS_CURRENT/@PROGRESS_MAX)*100)),'>',REPLICATE('.',99-CEILING((@PROGRESS_CURRENT/@PROGRESS_MAX)*100)),'] Time: ',CONVERT(varchar,DATEADD(ms,DATEDIFF(ms,@START,GETDATE()),0),114))

This will increment the counter, and output the progress bar every thousand records.

The script uses CONCAT which was introduced in SQL Server 2012. If you are using an older version of SQL Server, then use the following instead:

SET @PROGRESS_CURRENT = @PROGRESS_CURRENT + 1
IF @PROGRESS_CURRENT % 1000 = 0 PRINT 'Progress: '+CONVERT(VARCHAR(10),@PROGRESS_CURRENT)+ REPLICATE(' ',8-LEN(@PROGRESS_CURRENT))+'['+REPLICATE('#',CEILING((@PROGRESS_CURRENT/@PROGRESS_MAX)*100))+'>'+REPLICATE('.',99-CEILING((@PROGRESS_CURRENT/@PROGRESS_MAX)*100))+'] Time: '+CONVERT(varchar,DATEADD(ms,DATEDIFF(ms,@START,GETDATE()),0),114)

This helps me maintain my sanity, and I hope it helps you as well.

Thanks for taking the time to read my post – I hope you found it helpful.
Leave feedback in the comments below.

If this post save you time, then consider sharing it with your friends and colleagues. If it saved you some money, then please consider buying me a coffee. If you want to know when I publish new content, then subscribe to my feed. Your support encourages me to write more. Thanks!