External fragmentation (all databases)

Job details

Parameters

Job Summary

• Purpose: The purpose of this job is to monitor and collect statistics on external fragmentation for all tables and indexes across databases managed by SQL Server. This is crucial for maintaining database performance and efficient query execution.
• Why: External fragmentation can lead to increased disk I/O and CPU usage as the physical order of the data on the disk does not match the logical order within the database index. This misalignment can cause SQL Server to perform extra work, leading to slower query performance and potentially impacting the overall system stability.
• Manual checking: External fragmentation can be manually checked by querying specific dynamic management views like sys.dm_db_index_physical_stats and sys.dm_db_index_usage_stats in SQL Server:

SELECT object_name(object_id) AS ObjectName, index_id, avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) WHERE avg_fragmentation_in_percent > 10;

Dependencies

This job has several dependencies:

• self dependency to continue the fragmented object collection,
• fragmentation statistics dependencies to gather comprehensive data,
• history statistics dependencies to track changes over time,
• execution history table to record task execution details.

Implementation Details

This section can be complex due to the involvement of dynamic SQL and procedures to create necessary tables:

• Tables creation:

CREATE TABLE all_db_ext_fragm_obj (database_name varchar(200), object_name varchar(150), index_name varchar(150), type varchar(150), index_depth bigint, avg_fragmentation_in_percent bigint, fragment_count bigint, page_count bigint, record_count bigint, histr_date datetime, last_access datetime)

CREATE TABLE all_db_ext_fragm_exec_histr (db_count bigint, min_size_threshold int, max_size_threshold int, objects_checked bigint, objects_checked_size_mb bigint, heap_count bigint, index_count bigint, start_date datetime, end_date datetime, elap_sec bigint, last_db_name varchar(512), db_checked bigint)

• Stored procedure to collect external fragmentation statistics:

The main procedure, all_db_ext_fragm_obj_collector, is involved in collecting data based on certain thresholds and parameters, querying index properties and usage statistics, then logging this data for later analysis.

Monitoring and Reporting

• The job automatically compiles reports on its execution history and individual fragmentation statistics for each database:
• Displays scrollable history and statistics such as database count, size thresholds, objects checked, and elapsed time.

• Key visualizations include:
• Elapsed time of job execution,

Overall, this job is crucial for optimizing SQL Server performance by ensuring that indexes and tables are maintained with minimal fragmentation, thereby supporting efficient data retrieval and management.