Internal fragmentation check


Job details

Name: Internal fragmentation check
Platform: Sqlserver
Category: Maintenance
Premium package: Maintenance package
Description: Checks the internal fragmentation for tables and indexes in all databases. The information is extracted from the dynamic management function (view) sys.dm_db_index_physical_stats.
Long description: Internal fragmentation occurs when there is too much free space in the index or table pages. Typically, some free space is desirable, especially when the index is created or rebuilt. You can specify the Fill Factor setting when the index is created or rebuilt to indicate a percentage of how full the index pages are when created. If the pages are too fragmented, it will cause queries to take longer (because of the extra reads required to find the data set) and cause your indexes to grow larger than necessary. If no space is available in the index data pages, data changes (primarily inserts) will cause page splits, which also require additional system resources to perform.
Version: 2
Default schedule: 30 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 90 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 checked and inserted into the history table.
size threshold 1 The minimum size (in MB) of the objects (tables, indexes, etc.) to be analyzed for fragmentation.
mode SAMPLED Name of the mode which specifies the scan level that should be used to obtain statistics. Valid inputs are SAMPLED or DETAILED.
max elapsed time 60 The maximum time (in minutes) the procedure is allowed to run.
warning threshold 50 Warning threshold value in percent. If the average page density (in percent) in the index or table pages get below the the threshold value, the alert will return a warning.
alarm threshold 30 Alarm threshold value in percent. If the average page density (in percent) in the index or table pages get below the the threshold value, the alert will return an alarm.
ignore databases model List of databases (separated by comma) which will be ignored. You can use % (percent sign) to represent wild card characters.
ignore HEAP tables NO If set to YES, the procedure will ignore to check HEAP tables for fragmentation.
enable warnings and alarms NO If set to “NO” (default), the alert will only collect statistics without returning status warning or alarm.
max size threshold 5000 The maximum size (in MB) of the objects (tables, indexes, etc.) to be analyzed for fragmentation.
continue on next run YES If set to “YES“ (default) the procedure will continue where it left off.

Job Summary

SELECT object_name(ind.object_id) AS ObjectName, index_id AS IndexID, avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') WHERE avg_fragmentation_in_percent > 30 ORDER BY avg_fragmentation_in_percent DESC;

Key Parameters (Brief Overview, No Description)

Core Implementation Details

Execution and Historical Data Tracking

Result Presentation and Alerts

Compatibility and Considerations

Upgrade Path

Overall, the dbWatch job encapsulates a thorough approach towards monitoring and managing internal fragmentation across large-scale database environments, providing necessary tools and data points to maintain high levels of database performance and efficiency.