Objects size collector (all databases)


Job details

Name: Objects size collector (all databases)
Platform: Sqlserver
Category: Capacity
Description: Collects table and index size information for the largest objects for all databases.
Long description: Collects table and index size information for the largest objects for all databases.
Version: 1.63
Default schedule: 30 6 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 (size, rows count, fragmentation, etc.) will be kept in the history table.
max objects 10 The number of objects (indexes or tables) for which the statistics will be collected 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.
ignore databases tempdb The list of databases to be ignored (separated by comma).
max objects per graph 5 The maximum number of objects visualized in the report graphs.
ignore READ ONLY databases YES Ignores READ ONLY databases. Databases where DATABASEPROPERTYEX Updateability is set to READ_ONLY.
collect statistics for objects List of objects (separated by comma, in format [DATABASE].[SCHEMA].[OBJECT]) for which statistics will be collected. If no value specified, only statistic for the largest objects will be collected (number of objects is controlled by the parameter “max objects” with the default value 10).

Job Summary

SELECT OBJECT_NAME(object_id) AS ObjectName, SUM(used_page_count) * 8 AS UsedSpaceKB FROM sys.dm_db_partition_stats GROUP BY object_id ORDER BY UsedSpaceKB DESC

Detailed Description

The job operates by capturing data from:

Key aspects include:

Dependencies

The main dependencies of the job are:

Operations

During execution:

Reporting Capabilities

This job supports generating reports that show:

Implementation Notes

Upgrade Handling

This job setup assists with comprehensive monitoring and timely insights into large database objects, playing a crucial role in the database administration and optimization processes.