2.1 SYS.DM_DB_INDEX_PHYSICAL_STATS 2
3 What is index fragmentation? 2
5 How to measure fragmentation? 2
5.1 To Find out fragmentation level of a given database 2
6 How to address fragmentation issues with indexes? 3
6.1 OPTION 1: REACTIVE APPROACH 3
6.1.1 INDEX REBUILD - TO RECREATE ALL INDEX PAGES 3
6.1.2 INDEX REORGANIZE - TO REARRANGE THE FRAGMENTED INDEX PAGES 3
6.2 OPTION 2: PROACTIVE APPROACH 3
Index Overview
Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Analytics Platform System (PDW)
Optimize index maintenance to improve query performance and reduce resource consumption.
The Database Engine automatically modifies indexes whenever insert, update, or delete operations are made to the underlying data
heavily fragmented indexes can degrade query performance because additional I/O may be required to read the data required by the query
Terms
SYS.DM_DB_INDEX_PHYSICAL_STATS
What is index fragmentation?
A phenomena in which the data pages are not in continuation with relevant index pages.
Means, index pages and data pages are not in sync.
Impact:
Index scan (reads) and index seek (searches) takes more time to execute.
Adverse impact on query performance. Means, the queries run slow.
Types of Fragmentation
INTERNAL FRAGMENTATION
When records are stored non-contiguously inside the page, then it is called internal fragmentation. In other words, internal fragmentation is said to occur if there is unused space between records in a page.
This fragmentation occurs through the process of data modifications (INSERT, UPDATE, and DELETE statements) that are made against the table and therefore, to the indexes defined on the table.
External Fragmentation:
External Fragmentation: When on disk, the physical storage of pages and extents is not contiguous. When the extents of a table are not physically stored contiguously on disk, switching from one extent to another (EXTENT SWITCH) causes higher disk rotations, and this is called Extent Fragmentation.
How to measure fragmentation?
SELECT * FROM SYS.DM_DB_INDEX_PHYSICAL_STATS (null, null, null, null, null)
1ST PARAMETER: DATABASE ID. OR NULL : MEANS ALL DATABASES
2ND PARAMETER: OBJECT ID. OR NULL : MEANS ALL TABLES & VIEWS
3RD PARAMETER: INDEX ID. OR NULL : MEANS ALL INDEXES
4TH PARAMETER: PARTITION NUMBER. OR NULL : MEANS ALL PARTITIONS
5TH PARAMETER: NULL (SAMPLED ** / DETAILED)
select * from sys.dm_db_index_physical_stats(db_id(N'AdventureWorks2022'), NULL, NULL, NULL , 'DETAILED')
To Find out fragmentation level of a given database
--This query will give DETAILED information
--CAUTION : It may take very long time, depending on the number of tables in the DB
USE AdventureWorks
GO
SELECT object_name(IPS.object_id) AS [TableName],
SI.name AS [IndexName],
IPS.Index_type_desc,
IPS.avg_fragmentation_in_percent,
IPS.avg_fragment_size_in_pages,
IPS.avg_page_space_used_in_percent,
IPS.record_count,
IPS.ghost_record_count,
IPS.fragment_count,
IPS.avg_fragment_size_in_pages
FROM sys.dm_db_index_physical_stats(db_id(N'AdventureWorks'), NULL, NULL, NULL , 'DETAILED') IPS
JOIN sys.tables ST WITH (nolock) ON IPS.object_id = ST.object_id
JOIN sys.indexes SI WITH (nolock) ON IPS.object_id = SI.object_id AND IPS.index_id = SI.index_id
WHERE ST.is_ms_shipped = 0
ORDER BY 1,5
GO
How to address fragmentation issues with indexes?
OPTION 1: REACTIVE APPROACH
INDEX REBUILD - TO RECREATE ALL INDEX PAGES
APPLICABLE FOR HIGHER FRAGMENTATION LEVELS. ABOVE 30%
SYNTAX: ALTER INDEX <INDEXNAME> ON <TABLENAME> REBUILD
INDEX REORGANIZE - TO REARRANGE THE FRAGMENTED INDEX PAGES
APPLICABLE FOR LOWER FRAGMENTATION LEVELS. BETWEEN 15% AND 30%
SYNTAX: ALTER INDEX <INDEXNAME> ON <TABLENAME> REORGANIZE
OPTION 2: PROACTIVE APPROACH
ENSURE AUTOMATED, SCHEDULED INDEX REORGANIZATION @ "DATABASE INTENANCE PLANS". THIS PREVENTS FRAGMENTATION.
STEPS TO CONFIGURE DATABASE MAINTENANCE PLANS :
STEP 1: LAUNCH SSMS >> CONNECT TO SERVER
STEP 2: RIGHT CLICK AGENT > START
STEP 3: FROM SERVER >> MANAGEMENT >> MAINTENANCE PLANS >> MAINTENANCE PLAN WIZARD >>
SPECIFY A NAME > SPECIFY A SCHEDULE [EVERYDAY @ 12 AM SERVER TIME] >
SELECT BELOW ITEMS:
a. INDEX REORGANIZE
b. UPDATE STATISTICS
SPECIFY THE ORDER AS LISTED ABOVE.
STEP 4: INDEX REROGANIZE:: SELECT ALL DATABASES
COMPRESS LARGE OBJECTS
SCAN TYPE (FAST, SAMPLED, DETAILED *)
OPTIMIZATION OPTIONS:
FRAGMENATION CONDITION > 15%
PAGE COUNT CONDITION > 1000
USAGE CONDITION 7 DAYS
STEP 5: UPDATE STATISTICS::SELECT ALL DATABASES
FULL SCAN
STEP 6: WRITE REPORT TO A TXT FILE [LOG] > FINISH > CLOSE.
STEP 7: OPTIONAL.
RIGHT CLICK MAINTENANCE PLAN > EXECUTE
Index Management Example
SYS.DM_DB_INDEX_PHYSICAL_STATS -REBUILD / REORGANIZE
USE MASTER
GO
-- STEP 1: REPORT FRAGMENTATION FOR ALL INDEXES ACROSS ALL DATABASES?
SELECT * FROM SYS.DM_DB_INDEX_PHYSICAL_STATS(null, null, null, null, NULL)
-- STEP 2: REPORT HEAVILY FRAGMENTED INDEXES ACROSS ALL DATABASES?
SELECT * FROM SYS.DM_DB_INDEX_PHYSICAL_STATS(null, null, null, null, NULL)
WHERE avg_fragmentation_in_percent > 80
-- STEP 3: HOW TO FIND FRAGMENTAITON DETAILS FOR A GIVEN DATABASE & OBJECT?
SELECT * FROM SYS.DM_DB_INDEX_PHYSICAL_STATS(6, 309576141, 1, 1, NULL)
-- STEP 4: IDENTIFY DATABASE NAME
SELECT DB_NAME(6) -- SSASSBS
-- STEP 5: CONNECT TO ABOVE DATABASE, IDENTIFY THE TABLE NAME
USE SSASSBS
GO
SELECT OBJECT_NAME(309576141) -- FactInternetSales
-- STEP 7: IDENTIFY THE INDEX NAME FOR ABOVE TABLE
SELECT * FROM SYS.INDEXES WHERE OBJECT_ID = 309576141 -- PK_FactInternetSales_SalesOrderNumber_SalesOrderLineNumber
-- STEP 8: FOR HIGHER FRAGMENTATION VALUES, WE NEED TO REBUILD THE INDEX
ALTER INDEX PK_FactInternetSales_SalesOrderNumber_SalesOrderLineNumber
ON FactInternetSales
REBUILD
-- OR
-- STEP 8: FOR MODERATE FRAGMENTATION VALUES, WE NEED TO REORGANISE THE INDEX
ALTER INDEX PK_FactInternetSales_SalesOrderNumber_SalesOrderLineNumber
ON FactInternetSales
REORGANIZE
-- STEP 9: VERIFY THE FRAGMENTATION LEVELS AFTER INDEX REBUILD OR REORGANIZE
SELECT * FROM SYS.DM_DB_INDEX_PHYSICAL_STATS(6, 309576141, 1, 1, NULL)
Page Density
Each page in the database can contain a variable number of rows. If rows take all space on a page, page density is 100%. If a page is empty, page density is 0%.
When page density is low, more pages are required to store the same amount of data. This means that more I/O is necessary to read and write this data, and more memory is necessary to cache this data. When memory is limited, fewer pages required by a query are cached, causing even more disk I/O. Consequently, low page density negatively impacts performance.
To avoid lowering page density unnecessarily, Microsoft does not recommend setting fill factor to values other than 100 or 0,
