For very small tables, fragmentation is not only irrelevant, but nearly impossible to control. The first eight pages are allocated out of mixed extents, which are almost always going to be non-sequential. Only after an index has more than eight pages will it be allocated additional pages from uniform extents.
At fewer than 1,000 rows, your clustered index would be well under 64K and entirely allocated out of mixed extents (unless you start forcing the issue by adjusting the fill factor, as you have seen).
You can get an idea of how this clustered index's pages are allocated by issuing this command:
DBCC EXTENTINFO( DB_NAME, systemUserLangPreference, PK_systemUserLangPreference)
Once the index is large enough that it is allocated more than eight pages, you can ensure that they are all allocated out of uniform extents by dropping the index and then creating it again. Again, though, for such a small table, fragmentation is not going to have a performance impact, and you probably shouldn't try to solve it by fiddling with fill factor.
Lowering the fill factor might actually have detrimental results, as fewer records fit on each page, so more pages needed to be loaded into memory. Here's more on why to be careful with the fill factor.
For more details on pages and extents, see this article.
Is the required time for index rebuild depending on the level of
fragmentation?
I believe this will not be the major parameter on which SQL server will decide and takes time to rebuild\re-organize the index:
There are various other factors involved based on "DATA" via which it decides for how much time will it take: Parameters like
Factor 1: Table size
Factor 2: Availabililty concerns
Factor 3: Partitioning
Factor 4: Index columns and uniqueness
If you want to read more on these factors you can refer here.
Do does the rebuild of a 80% fragmented index approximately take 2
minutes if the rebuild of the same index fragmented 40% takes 1 minute
Again the answer can be it Depends! For the numbers you will need to test the scenario and see the outputs how it goes. Track such details like for FRAG level 80 , rebuild took X hrs\mins\secs and for Frag level 40, rebuild took Y hrs\mins\secs. Calculate and retain the history say over 15 days, (depends upon the maintenance activity scheduled) and you may to a conclusion on how much time its actually taking in comparing the both.
Additionally :
You can gather the data\calculation on the index rebuild progress:
either using DMV sys.dm_exec_requests OR
If you have Ola's Maintenance plans for Re-indexing-Re-organizing, there is an option to save the history of the actions performed during maintenance within table CommandLog as explained in SQL Server Index and Statistics Maintenance. Once the data is saved, you can query for command type `ALTER_INDEX--REBUILD' and difference for the same between columns START TIME and END TIME
Best Answer
What does Fragmentation Means in a Heap
The fragmentation value in Heap which you get from column
avg_fragmentation_in_percent
by queryingsys.dm_db_index_physical_stats
DMV states thatFurther the same BOL says that
So you can see it is not the free space present in pages allocated to Heap but the varying sequence of pages that creates the fragmentation.
This can be demonstrated by small test. Let us create a Heap Table and insert some records in it and then check the fragmentation.
So Heap table is created with 50 records in it. Below is what fragmentation looks like after query DMV sys.dm_db_index_physical stats
You can see
avg_fragmentation_in_percent
column value is 33 %. Now let us see how are pages arranged. This can be done by using undocumented query%%lockres%%
. The query would beAnd below is what output looks like. Attaching only relevant portion of it. The query produced 50 rows since we inserted 50 rows in our dbo.HeapTest table.
What it says is the first page has ID
197
the next page has ID242
subsequent pages has continuous ID till we reach page ID264
because after that we get page ID280
. So this jump in page ID numbers is what actually causing fragmentation.Now lest rebuild the heap and run The command again to see the fragmentation and how pages are arranged. We get fragmentation like
You can see fragmentation is now
14%
.Let us see page numbers allocated
We only have one jump rest all pages are allocated page ID serially. Since just one jump fragmentation decreased considerably.
I rebuild the Heap again and now when I checked fragmentation it was completely gone. And page ID allocation is like
Why Fragmentation Increased
Now regarding what could have caused fragmentation to rise we can corroborate it to fact that when pages were getting allocated to the heap they would not be continuous, as you saw above what caused fragmentation value to increase was jump in the PAGE ID's allocated to pages.
At the back of head you should also keep in mind that the word fragmentation for HEAP does not have any meaning, how would you define fragmentation for bunch of un-ordered pages.
Really Worried about Fragmentation
If you really face a scenario where heap table is fragmented and slowing queries it would be better creating a clustered index on table than rebuilding it. The reason is when you rebuild heap all underlying Non Clustered indexes are also rebuilt causing the rebuild process to take much longer time, utilizing lot of resources and bloating transaction log. On a production system one would always try to avoid this. Paul covered this in his Myth Section about heap.
PS: Please don't use undocumented command on production system. This was just for demonstration.