I was working on a data migration project and needed a way to quantify the progress. Since 1 particular issue we were facing was that the whole process slowed down gradually as it went, I wanted a way to collect data points to know the current insertion rate. Kind of how you'd want to monitor the download speed when downloading a movie or something :).
In this thread, I found the following script that would return the total number of rows contained within a database using clustered indices (i.e. the primary keys), and the heap. So the only reason this would not work is if the database has design flaws.
I added the NOLOCK hint because I don't want this to add to the problem by locking the table. Besides it is just a monitoring tool and I can compromise on the accuracy.
So the logic is,
1. collect 2 data points with an interval in between. Higher the interval, the more accurate the rate is. But you don't want to wait for too long either. So I've found that a 10 second interval works best for me.
2. get the delta which tells us the number of rows added within that period
3. divide it by the time taken for the whole process to get the number of rows per second.
In this thread, I found the following script that would return the total number of rows contained within a database using clustered indices (i.e. the primary keys), and the heap. So the only reason this would not work is if the database has design flaws.
SELECT SUM(row_count)
FROM sys.dm_db_partition_stats
WHERE index_id IN (0,1)
AND OBJECTPROPERTY([object_id], 'IsMsShipped') = 0;I added the NOLOCK hint because I don't want this to add to the problem by locking the table. Besides it is just a monitoring tool and I can compromise on the accuracy.
So the logic is,
1. collect 2 data points with an interval in between. Higher the interval, the more accurate the rate is. But you don't want to wait for too long either. So I've found that a 10 second interval works best for me.
2. get the delta which tells us the number of rows added within that period
3. divide it by the time taken for the whole process to get the number of rows per second.
DECLARE @startcount INT, @endcount INT
DECLARE @startdate DATETIME, @enddate DATETIME
SELECT @startcount = SUM(row_count),
@startdate = GETDATE()
FROM sys.dm_db_partition_stats WITH(NOLOCK)
WHERE index_id IN (0,1)
AND OBJECTPROPERTY([object_id], 'IsMsShipped') = 0
WAITFOR DELAY '00:00:10'
SELECT @endcount = SUM(row_count),Please note that you need to run this query with a user that has access to sys objects.
@enddate = GETDATE()
FROM sys.dm_db_partition_stats WITH(NOLOCK)
WHERE index_id IN (0,1)
AND OBJECTPROPERTY([object_id], 'IsMsShipped') = 0
SELECT @startcount StartCount,
@endcount EndCount,
@startdate StartTime,
@enddate EndTime,
(@endcount - @startcount)/DATEDIFF(second, @startdate, @enddate) [Rec/s]
