What exactly is Fragment_count ?

I started to think about this problem, when I saw this question asked by Sankar Reddy in SQL Server Quiz 2010. I have a fair bit of idea about Index fragmentation and defragmentation. I checked fragmentation of some of my table indexes previously and rebuilt the indexes when fragmentation percentage is too high. But I never thought about how exactly these will be calculated. But after looking at this question, I thought of finding how SQL server will calculate this. Let’s have a look at this example.

CREATE TABLE tblNumbers
(
   Id INT IDENTITY(1,1) PRIMARY KEY,
   Num INT
)

;with N AS
(
   SELECT 0 AS Num UNION all SELECT 0 UNION all SELECT 0 UNION all SELECT 0 UNION all SELECT 0 UNION all
   SELECT 0 UNION all SELECT 0 UNION all SELECT 0 UNION all SELECT 0 UNION all SELECT 0
),
Numbers AS
(
   SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS rn FROM N N1,N N2,N N3,N N4,N N5, N N6
)
INSERT INTO tblNumbers
SELECT rn FROM Numbers

SELECT page_count, fragment_count
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('tblnumbers'),NULL, NULL, 'detailed')
WHERE index_id = 1 and index_level = 0

In the above example we are inserting 1 Million records and checking the Physical stats of the Clustered index of the table. It shows 3832 pages were allocated to the clustered index in its Leaf Level and shows Fragment count as 17.

A Fragment is a collection of pages in sequence. Assume there is a page with ID 1000 is allocated to a table, and its sequences are 1001 and 495 instead of 1002, these will be considered as 2 fragments with one fragment having 1000-1001 and other fragment with 495.

Read more: LessThanDot

Posted via email from .NET Info