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.