Fragmentation means storing data in a non-contiguous manner. Due to fragmentation SQL Server need to do extra IO to read the indexes which causes slower performance.
Causes of Fragmentation
Inserts and updates causing page splits – The most common cause of index fragmentation is because of “page splits”, a page split occurs when there is no room to accommodate newly inserting / updating records, As a result SQL Server need to move 50% of page data into newly allocated page which causes page splits
Delete operations – The DML operation delete causes fragmentation because random delete will leave pages in use but not completely full or rows will result internal fragmentation.
There are two types of fragmentations 1) Internal Fragmentation 2) External Fragmentation
Internal Fragmentation (Logical Fragmentation) – When data pages are partially used and if each page has empty space creates fragmentation, In other words records are not stored contiguously in the page leads to internal fragmentation hence SQL Server needs to scan more pages to fetch the same number of rows which causes poor performance.
External Fragmentation -- It occurs when pages and extents are not stored contiguously in the physical disk. When the extents or pages scattered across the disk hence switching of extents causes high disk rotations and this is called Extent Fragmentation.
High fragmentation causes poor performance, we can get optimized performance of select queries only if the data pages are stored contiguously in the database file.
To detect fragmentation we can use DBCC SHOWCONTIG (deprecated) and sys.dm_db_index_physical_stats DMF.
No comments: