CREATE TABLE	tSample
		(
			i INT
		)

CREATE UNIQUE CLUSTERED INDEX IX_Sample ON tSample (i)

INSERT		tSample
		(
			i
		)
SELECT		Number
FROM		TallyNumbers
WHERE		Number < 10000
ORDER BY	NEWID()

DBCC SHOWCONTIG(tSample) WITH FAST

TRUNCATE TABLE	tSample

INSERT		tSample
		(
			i
		)
SELECT		Number
FROM		TallyNumbers
WHERE		Number < 10000
ORDER BY	Number

DBCC SHOWCONTIG(tSample) WITH FAST

ALTER INDEX IX_Sample ON tSample REORGANIZE

DBCC SHOWCONTIG(tSample) WITH FAST

ALTER INDEX IX_Sample ON tSample REBUILD

DBCC SHOWCONTIG(tSample) WITH FAST

DROP TABLE	tSample

DBCC SHOWCONTIG scanning 'tSample' table...
Table: 'tSample' (1500584434); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned................................: 17
- Extent Switches..............................: 3
- Scan Density [Best Count:Actual Count].......: 75.00% [3:4]
- Logical Scan Fragmentation ..................: 11.76%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC SHOWCONTIG scanning 'tSample' table...
Table: 'tSample' (1500584434); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned................................: 17
- Extent Switches..............................: 4
- Scan Density [Best Count:Actual Count].......: 60.00% [3:5]
- Logical Scan Fragmentation ..................: 23.53%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC SHOWCONTIG scanning 'tSample' table...
Table: 'tSample' (1500584434); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned................................: 17
- Extent Switches..............................: 4
- Scan Density [Best Count:Actual Count].......: 60.00% [3:5]
- Logical Scan Fragmentation ..................: 23.53%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC SHOWCONTIG scanning 'tSample' table...
Table: 'tSample' (1500584434); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned................................: 17
- Extent Switches..............................: 7
- Scan Density [Best Count:Actual Count].......: 37.50% [3:8]
- Logical Scan Fragmentation ..................: 35.29%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

quote:

---

Originally posted by sodeep

Taken from msdn

For small tables, usually performance impact on fragmentation is undectable. 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. (As matter of fact, I could easily construct a case that fragmentation increases after rebuild.) Those fragmentation would not be a pain for your query performance; so basically you can ingore them.

When page counts of an index reaches to certain big size (for example, 1000 pages), then fragmentation may start to impact performance. Rebuilding index should reduce fragmentation.

Another thing to consider is that how high the fragmentation is. If it is < 10%, it is hard for rebuilding to reduce more (we never could 100% guarantee that you can reach 0% fragmentation; with mixed page allocation, it is a hard goal to achieve).

If you don't care about space utilization, you can use undocumented trace flag 1118 to disable mixed page allocation, but I am not recommend it (this trace flag usually is used in tempdb for reducing SGAM contention).

---