Ever needed to calculate the number of rows inserted every second, for every table in every database on a server? Or, have you ever needed to validate that all processes have stopped writing to tables? These types of questions come up routinely for me. To help with this, I’ve written the following script, which examines metadata values using sys.partitions. This method isn’t as accurate as running SELECT COUNT(*) FROM, but it’s much faster. Keep in mind, since it’s just looking at row counts, it’s not much help on tables that have a lot of update/delete activity. But it does what I need it to do, and I use it pretty regularly, so I thought I’d share in case anyone else can benefit from it too. 
/* Declare Parameters */ DECLARE @newBaseline BIT = 1 -- change to 0 when you don't want to replace the baseline, i.e. after initial run , @delay CHAR(8) = '00:00:30'; -- change as needed IF @newBaseline = 1 BEGIN IF OBJECT_ID('tempdb..#baseline') IS NOT NULL DROP TABLE #baseline; CREATE TABLE #baseline ( database_name SYSNAME , table_name SYSNAME , table_rows BIGINT , captureTime DATETIME NULL ); END IF OBJECT_ID('tempdb..#current') IS NOT NULL DROP TABLE #current; CREATE TABLE #current ( database_name SYSNAME , table_name SYSNAME , table_rows BIGINT , captureTime DATETIME NULL ); IF @newBaseline = 1 BEGIN EXECUTE sp_MSforeachdb 'USE ?; INSERT INTO #baseline SELECT DB_NAME() , o.name As [tableName] , SUM(p.[rows]) As [rowCnt] , GETDATE() As [captureTime] FROM sys.indexes As i JOIN sys.partitions As p ON i.[object_id] = p.[object_id] AND i.index_id = p.index_id JOIN sys.objects As o ON i.[object_id] = o.[object_id] WHERE i.[type] = 1 GROUP BY o.name;' WAITFOR DELAY @delay; END EXECUTE sp_MSforeachdb 'USE ?; INSERT INTO #current SELECT DB_NAME() , o.name As [tableName] , SUM(p.[rows]) As [rowCnt] , GETDATE() As [captureTime] FROM sys.indexes As i JOIN sys.partitions As p ON i.[object_id] = p.[object_id] AND i.index_id = p.index_id JOIN sys.objects As o ON i.[object_id] = o.[object_id] WHERE i.[type] = 1 GROUP BY o.name;' SELECT c.* , c.table_rows - b.table_rows AS 'new_rows' , DATEDIFF(second, b.captureTime, c.captureTime) AS 'time_diff' , (c.table_rows - b.table_rows) / DATEDIFF(second, b.captureTime, c.captureTime) AS 'rows_per_sec' FROM #baseline AS b JOIN #current AS c ON b.table_name = c.table_name AND b.database_name = c.database_name ORDER BY new_rows DESC; |
Great post @sqlfool. I once had to write something similar, but needed to not count the logical deletes between two databases. I found that filtered indexes on those few rows did the trick to help subtract out those rows much faster than count(1) even with a stats update.
If u have a db with a ‘-’ in its name (e-xample) , you have to update the 2 lines that say:
EXECUTE sp_MSforeachdb ‘USE ?;
for
EXECUTE sp_MSforeachdb ‘USE [?];
Pingback: Something for the Weekend – SQL Server Links 15/07/11
Not that if the script takes a while or if there are many databases, sp_msforeachdb is quite likely to skip databases, so you might not know or notice whether “no news is goodness.” I wrote a replacement here: http://www.mssqltips.com/tip.asp?tip=2201
Good Post..! But this script works for table indexes, if there are no indexes on a table, how to calculate it ?
Very cool script! I had to change ‘WHERE i.[type] = 1′ to ‘WHERE i.[type] = 1 OR i.[type] = 0′ to include heaps.
If you run the stored procedure with @executeSQL = 0 for database A, then run it for another database (let’s say B) then the index statistic will not be retrieved for database B. The reason is IF NOT EXISTS(SELECT Top 1 * FROM dbo.dba_indexDefragStatus WHERE defragDate IS NULL) condition will always return true because there is still some data for DB A there from the first run and defragDate IS NULL because defragmentation wasn’t done according to @executeSQL = 0. You can use @forceRescan = 1 parameter to overcome this.
Also, IMHO, it would be convenient to have it as a plain script, so you don’t need a database to host it. But anyway this is just an awesome piece of code and the most universal script I found in the web! Thank you!
In similar fashion is it possible to see for Updates?
Like I would want to know which are the Hottest tables in the database in terms of all DML operations..
Thanks
Pingback: BPOTW 2014-07-20 | SQL Notes From The Underground