Is it possible to reduce fragmentation by rebuilding index?
The first 8 page allocation would be from mixed extents and mixed extents could be anywhere in database files. Rebuilding indexes would not change this nature. If you have a small table, those mixed pages weight a lot during fragmentation calculation; therefore, rebuilding index may not reduce fragmentation.
What can cause index to be still fragmented after SQL Server?
Although its for SQL Server 2000 but gives very accurate definition )says that there is hardly any performance gain in rebuilding index with page_count <1000. Fragmentation affects disk I/O. Therefore, focus on the larger indexes because their pages are less likely to be cached by SQL Server.
How many fragments are in a table or index?
A new table or index is usually allocated it’s first 8 pages from a mixed, rather than uniform extent. So, it’s possible for each of the first 8 pages to be allocated from different mixed extents. A table or index consuming 8 pages could therefore have 8 fragments, 1 on each of 8 different mixed extents.
Is it possible to fragmentation a 19 page index?
Yes 19 pages is very low and that is the reason that you are seeing the fragmentation. If you try the same thing on a larger index you will see that this won’t happen because there are enough pages in the larger one for it to have its own extents that are allocated to just that object.
How does database engine defragment rowstore indexes?
For rowstore indexes, the Database Engine defragments only the leaf level of clustered and nonclustered indexes on tables and views by physically reordering the leaf-level pages to match the logical order of the leaf nodes (left to right). Reorganizing also compacts index pages to make page density equal to the fill factor of the index.
When to reorganize or rebuild indexes to improve performance?
If higher fragmentation or lower page density degrade performance unacceptably, reorganize or rebuild indexes. It is often sufficient to only reorganize or rebuild specific indexes used by queries with degraded performance. This avoids a higher resource cost of maintaining every index in the database.
How to calculate fragmentation in columnstore indexes?
For compressed row groups in columnstore indexes, fragmentation is defined as the ratio of deleted rows to total rows, expressed as a percentage. sys.dm_db_column_store_row_group_physical_stats lets you determine the number of total and deleted rows per row group in a specific index, all indexes on a table, or all indexes in a database.