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?

[Aaron40]

Hi,

I know this is an old post but I wanted to catch up with you and see if this is still something you are wanting to do.

Half of what you are asking is really easy. Getting the row count of all the tables is a SNAP, and it's already been done for you. See http://community.servicenow.com/blog/mkaufman/table-row-counts . You can run a background script or create your own application to report against.

Counting the bytes in the table isn't so easy. But I wanted to post that link for you in case you were still looking for an answer.

[haldroid]

Thanks, but doing

count.addAggregate('COUNT');

as suggested at the link takes too long, or fails completely, on large tables, which is where it is needed most.

I was asking about access to what is normally only a db admin tool, something that reports on table stats from reading internal metadata, without querying the table in question.

[drjohnchun]

Hal - this is late in coming, but wanted to share what I posted in another thread just in case you may still be able to use the solution, which is fast (< 1 sec; I included the execution times below).

One way to get table / database size is to use gs.sql(), if you're on Fuji or earlier release (it no longer works from Geneva):

gs.sql("SELECT CONCAT('YOUR INSTANCE DATABASE IS CURRENTLY ', ROUND(SUM(data_length + index_length) / 1024 / 1024 / 1024, 1), ' GB') DB_SIZE_GB FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'");

gives (based on table sizes; took 180 ms)

DB_SIZE_GB

YOUR INSTANCE DATABASE IS CURRENTLY 2.8 GB

To get the size per table for all tables, with the largest first, use

gs.sql("SELECT TABLE_NAME, TABLE_ROWS, ROUND(((data_length + index_length) / 1024 / 1024), 2) TABLE_SIZE_MB FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' ORDER BY 3 DESC");

gives (took 308 ms)

TABLE_NAME, TABLE_ROWS, TABLE_SIZE_MB

sys_upgrade_history_log, 340131, 1458.94

sys_attachment_doc, 20109, 220.16

sys_user_token, 149572, 82.78

sys_audit, 55394, 61.13

syslog_transaction0006, 72771, 59.75

syslog_transaction0002, 65791, 56.73

syslog_transaction0003, 68679, 56.73

syslog_transaction0004, 57225, 55.67

sys_metadata, 72777, 53.97

syslog_transaction0005, 58621, 51.81

usageanalytics_count, 35119, 40.75

syslog_transaction0001, 42843, 38.75

syslog_transaction0007, 34261, 31.80

...

To use gs.sql(), you have to elevate privilege to security_admin and use "Scripts - Background" from the Navigator.

WARNING: misuse of gs.sql() may cause unrecoverable damage to your instance.

Please feel free to connect, follow, mark helpful / answer, like, endorse.

John Chun, PhD PMP

[PriyankaC]

Hello, I am running into a similar issue. Is there any suggestion how to get count # rows for a very large audit table efficiently ?