Calculate Rows Inserted per Second for All Tables

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;
0saves
If you enjoyed this post, please consider leaving a comment or subscribing to the RSS feed to have future articles delivered to your feed reader.
Tagged , , , , . Bookmark the permalink.

10 Responses to Calculate Rows Inserted per Second for All Tables

  1. @TimMessar says:

    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.

  2. John says:

    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 [?];

  3. Pingback: Something for the Weekend – SQL Server Links 15/07/11

  4. 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

  5. Kishore says:

    Good Post..! But this script works for table indexes, if there are no indexes on a table, how to calculate it ?

  6. IJeb Reitsma says:

    Very cool script! I had to change ‘WHERE i.[type] = 1′ to ‘WHERE i.[type] = 1 OR i.[type] = 0′ to include heaps.

  7. Docker says:

    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.

  8. Docker says:

    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!

  9. mahesh says:

    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

  10. Pingback: BPOTW 2014-07-20 | SQL Notes From The Underground

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>