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.