Reporting on table sizes

[haldroid]

Here's a request for ServiceNow: Provide a report at regular intervals on table sizes. Every table: number of rows, number of bytes. I would love to see this in a list view that we could sort & search & report on.

It is argued in the posting Finding Size of Instance Database that we shouldn't have to care about the resources we consume. But we have been blind-sided a few times by tables growing to unmanageable size, some before we even realized those tables exist, e.g. cmdb_tcp_connection.

User efforts to fill the void, such as the one in Table Row Counts, fail when the need is greatest. Example: the sys_audit table in our production instance is so large that even attempts to get the record count (GlideAggregate or SELECT COUNT) time out.

The db admin tools exist for efficient collection of table stats. How about making this info available to users, so we can see if an approach is going to get us into trouble before misunderstandings and costly impairments occur?

What do you think?

[Aaron Holliman]

In general, you must consider two data storage areas when reporting against planning and budgeting data:

• The planning model tables, also known as the activity detail tables, where activity details are stored during the budgeting process.

  While the budgeting process is under way, the activity details are stored in a series of tables. These are the tables that get updated after a staging process has been performed, and when users are entering budget data during the budget process.

• The source/final tables, also known as the OWE (Operational Warehouse Enriched) tables, where both the source and final (master version) data are stored.

  Once the budget is finalized, the coordinator will want to run the export process for line item or position activity data, the master data is exported to the OWE tables.

[Gayle Lane]

List Table Sizes From a Single Database

1. SELECT TABLE_NAME AS `Table`, ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS `Size (MB)` FROM information_schema. ...
2. SELECT TABLE_NAME AS `Table`, ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS `Size (MB)` FROM information_schema.
3. One Cognizant