How do I calculate how fast a table is growing?

×

Sometimes it is important to know how fast a table is growing during an insert process or maybe just monitoring a growth table. Use the following process to easily calculate the rate at which a table is growing:

  • Get the current record count - select count(*) from {Table Name}
  • Record the count and the time
  • Wait a certain amount of time (the longer you wait the more accurate your calculation)
  • Get the current record count - select count(*) from {Table Name}
  • Record the count and the time
  • Get the time interval between the start and end time
    • Hours - select DATEDIFF(HOUR, StartTime, EndTime)
    • Minutes - select DATEDIFF(MINUTE, StartTime, EndTime)
    • Seconds - select DATEDIFF(SECOND, StartTime, EndTime)
  • Divide that time into the record count differece and you have your recrords per time interval

Example

  • select count(*) from LoggingTable
    • Count: 23,673
    • Time: 2021-06-10 8:23:13.263
  • select count(*) from LoggingTable
    • Count: 25,936
    • Time: 22021-06-10 9:37:32.345
  • Interval
    • select DATEDIFF(HOUR, '2021-06-10 8:23:13.263', '2021-06-10 9:37:32.345') - 1
    • select DATEDIFF(MINUTE, '2021-06-10 8:23:13.263', '2021-06-10 9:37:32.345') - 74
    • select DATEDIFF(SECOND, '2021-06-10 8:23:13.263', '2021-06-10 9:37:32.345') - 4459
  • Record count differnce: 25,936 - 23,673 = 2,263
  • Rate
    • Hours - 2,263 / 1 = 2,263 records per hour
    • Minutes - 2,263 / 74 = 30.58 records per minute
    • Seconds - 2,263 / 4,459 = .5 records per second