Created
September 10, 2025 14:54
-
-
Save kevinmartintech/516b8e27c6670c06b2b8e82a7dec8d6c to your computer and use it in GitHub Desktop.
Script to monitor table counts. Good for when tuning batch looping DELETES, UPDATES scripts. Count the row differences every minute.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| DECLARE @StartTime datetime = GETDATE(); | |
| DECLARE @LastMinuteStart datetime = DATEADD(MINUTE, -1, @StartTime); | |
| DECLARE @CurrentMinuteStart datetime; | |
| DECLARE @CurrentMinuteStartMessage nvarchar(30); | |
| DECLARE @LastRowCount bigint = -1; | |
| WHILE 1 = 1 | |
| BEGIN | |
| SET @CurrentMinuteStart = DATEADD(MINUTE, DATEDIFF(MINUTE, @StartTime, GETDATE()), @StartTime); | |
| DECLARE @CurrentRowCount bigint; | |
| /* For Accurate Count */ | |
| /* SELECT @CurrentRowCount = COUNT_BIG(*) FROM $SchemaName$.$TableName$ WITH (READPAST); */ | |
| /* For Estimated Count */ | |
| SELECT | |
| @CurrentRowCount = SUM(row_count) | |
| FROM | |
| sys.dm_db_partition_stats | |
| WHERE | |
| object_id = OBJECT_ID('$SchemaName$.$TableName$') | |
| AND (index_id = 0 OR index_id = 1); | |
| IF @LastRowCount = -1 | |
| BEGIN | |
| SET @LastRowCount = @CurrentRowCount; | |
| END; | |
| DECLARE @RowCountDifference nvarchar(30) = FORMAT(ABS(@LastRowCount - @CurrentRowCount), '#,##0'); | |
| SELECT @CurrentMinuteStartMessage = CONVERT(varchar(30), @CurrentMinuteStart, 109); | |
| RAISERROR('%s row difference for date %s', 0, 1, @RowCountDifference, @CurrentMinuteStartMessage) WITH NOWAIT; | |
| SET @LastMinuteStart = @CurrentMinuteStart; | |
| SET @LastRowCount = @CurrentRowCount; | |
| WAITFOR DELAY '00:01:00'; | |
| END; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment