Table size collector


Job details

Name: Table size collector
Platform: Sqlserver
Category: Capacity
Description: Collects table (HEAP and CLUSTER type) size statistics.
Long description: Collects table (HEAP and CLUSTER type) size statistics..
Version: 1.1
Default schedule: 20 5 6 *
Requires engine install: Yes
Compatibility tag: .[type=‘instance’ & databasetype=‘sqlserver’]/instance[maj_version > ‘2000′ & hasengine=‘YES’ & eng_inst_priv = 0 & (engine_edition = ‘Microsoft SQL Server’ engine_edition = ‘Azure SQL Managed Instance’)]

Parameters

Name Default value Description
history threshold 365 The number of days the statistics will be kept in the history table.
collect record count stat NO If “YES“, the performance view sys.dm_db_index_physical_stats is queried in DETAILED mode (to collect information from the record_count column) for the largest object.
max objects per graph 5 The maximum number of objects visualized in the report graphs.
table list List of tables (separated by comma, in format [DATABASE].[SCHEMA].[TABLE]) for which size statistics will be collected.

Job Summary

SELECT * FROM sys.dm_db_index_physical_stats WHERE object_id = OBJECT_ID('YourTableName');

Job Details

Dependencies and Cleanup

The job depends on several internal components for successful operation:

Should any component fail during the execution, the job has mechanisms to clear potentially corrupted data to maintain system integrity.

Execution Schedule

Report Generation

Technical Implementation

create procedure dbw_tab_size_collector_proc @taskID INT
* The job includes dynamic SQL and cursor-based approaches to aggregate and record data accurately as per the predefined list of tables and based on input parameters received.

Key Output Tables

Table Name Usage
dbw_tab_size Stores current size data for monitored tables
dbw_tab_size_histr Retains historical data size snapshots for trend analysis and reporting
dbw_tab_size_list Maintains a record of user-defined table lists and comments regarding their monitoring status

Monitoring and Error Handling