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

Purpose: The purpose of this job is to collect and monitor the size of tables (specifically HEAP and CLUSTER types) in SQL Server databases.
Why: This job is essential as it helps in tracking growth trends, detecting potential space issues, and managing database capacity efficiently. If these metrics surpass certain thresholds, it may indicate performance degradation or a need for data maintenance activities, such as indexing or archiving.
Manual checking: You can check this manually in the database by issuing these SQL commands:

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

Job Details

“Table size collector” is designed to gather size statistics from tables in the SQL Server environments by querying the sys.dm_db_index_physical_stats dynamic management view. It further leverages performance insights into table storage and indexing.
Depending on the configuration, additional detailed information on record counts can be collected if specified by the parameter “collect record count stat.”

Dependencies and Cleanup

The job depends on several internal components for successful operation:

table_list: List of specific tables to monitor.
tab_size_collector_proc: Main procedure that gathers table sizes.
tab_size_histr and tab_size tables: These are used for holding historical and current data about table sizes.

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

Execution Schedule

The default schedule set for this task is configured to run daily at 06:05 AM.

Report Generation

This job produces a report titled “Objects size collector” which provides a detailed view of fragmentation statistics across databases.
The outputs include a visual representation of the data as well as tabular details such as database name, schema, table name, type of table, page count, number of rows, and the date when the statistics were gathered.

Technical Implementation

SQL procedures used to populate the database tables with the required data:

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

The job includes detailed error logging and task value updates to ensure any execution interruptions or anomalies are documented, enabling easier troubleshooting and maintenance.
In case of exceptions, these are captured, and diagnostic information such as error position, database, and error message are logged. This structure assists in pinpointing the exact failure point within the job’s execution.