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

Purpose: The purpose of this job is to monitor and analyze internal fragmentation for tables and indexes across all databases within a specified SQL Server instance. It utilizes dynamic management views for obtaining internal statistics about the page density and usage patterns of database objects, adjusting its operability based on several user-defined thresholds and conditions.
Why: This job is crucial for maintaining optimal performance and efficient storage utilization. By identifying highly fragmented tables and indexes, preventive actions such as reindexing or restructuring can be undertaken to enhance query performance and storage management. Internal fragmentation, if left unchecked, can lead to increased I/O operations, slower query response times, and higher storage costs.
Manual Checking: You can check internal fragmentation manually in the database by executing the following SQL command:

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)

History Threshold
Max Objects
Size Threshold
Mode (Sampled or Detailed)
Max Elapsed Time
Warning Threshold
Alarm Threshold
Ignore Databases
Ignore HEAP Tables
Enable Warnings and Alarms
Max Size Threshold
Continue on Next Run

Core Implementation Details

The procedure primarily involves the creation of history and execution tracking tables named `all_db_int_fragm_tab`, `all_db_int_fragm_tab_histr`, and `all_db_int_fragm_exec_histr`.
The main operational logic is embedded within the `all_db_int_fragm_check_proc` stored procedure, which conducts a thorough scan of all database objects, applying designated modes for varying levels of detail in the assessment.
The job respects certain conditions for databases to be analyzed, such as ignoring databases marked explicitly, systemic databases like ‘tempdb’ and ‘model’, or adhering to specific user access and updateability conditions.

Execution and Historical Data Tracking

The job is designed to handle databases incrementally by storing the last evaluated database, thus optimizing performance by avoiding a complete reevaluation in subsequent runs.
It captures detailed logs of each execution, storing metrics such as the number of databases checked, object details, fragmentation levels, and processing time into history tables for trend analysis and audit purposes.

Result Presentation and Alerts

The accumulated data can be visualized through designated report templates included in the XML, offering insights into historical trends and current fragmentation statuses.
Depending on configuration, the tool can trigger warnings and alarms if fragmentation metrics fall below specified performance thresholds, aiding in proactive database maintenance.

Compatibility and Considerations

The job setup is intended for use with Microsoft SQL Server and Azure SQL Managed Instances, with specific exclusions applying based on SQL Server versions and configurations.
It requires certain prerequisites and operational allowances on the SQL Server instance for accurate and efficient execution, such as necessary privileges and appropriate database statuses.

Upgrade Path

Includes upgrade scripts to adapt and expand existing setups as newer aspects like ‘continue on next run’ are incorporated to enhance robustness and fault tolerance in ongoing operations.

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.