Index Fragmentation

Question

Hi All,

:: SQL Server 2008 ::

       I am working on index fragmentation task.. And these are the observations::

1. Most of the indexes even the Avg fragmentation is more than 60% , the index rebuild is NOT  reducing the fragmentation :(

2. I surprise to see, that before re-building the index the fragmentation was 14.286 .... And as part of the script, I rebuid (yes REBUILD) the index with ONLINE = OFF and it is increased to 42.857 .

3. Again I changed the REBUILD option ONLINE = ON, the fragmentation came down to 14

SO , after observing these, my idea on the index fragmentation is blurred ..

Please suggest ..

-- Reddy Balaji C.

Answer 1

1. Probably because  you have page count (of that tables) less than 1000

2. Looks odd, are  you sure  you did not run DBCC SHRINKFILE commad

3. Just did some testing and ONLINE --OFFLINE does not 'affect' fragmentation ..

See script to do rebuild/reorg indexes

http://ola.hallengren.com/

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 2

Agree with Uri. I feel the table has very less rows.

---

Pradeep Adiga
My blog: http://www.sqldbadiaries.com

Answer 3

Hi Uri,

 Thank you for the reply ..

1) Yes, the page count is less than 1000

2,3) Again I tried with sample database : Please find the below specified data::

Before
DatabaseName	TableName	IndexName	IndexType	AvgPage Fragmentation	Page Counts
AdventureWorks	Employee	PK_Employee_EmployeeID	CLUSTERED INDEX	14.28571429	7
After executing "ALTER INDEX [PK_Employee_EmployeeID] ON [AdventureWorks].[HumanResources].[Employee] REBUILD WITH (SORT_IN_TEMPDB = OFF, ONLINE = OFF)"
DatabaseName	TableName	IndexName	IndexType	AvgPage Fragmentation	Page Counts
AdventureWorks	Employee	PK_Employee_EmployeeID	CLUSTERED INDEX	42.85714286	7

-- Reddy Balaji C.

 

Answer 4

Source BOL: In general, fragmentation on small indexes is often not controllable. The pages of small indexes are stored on mixed extents. Mixed extents are shared by up to eight objects, so the fragmentation in a small index might not be reduced after reorganizing or rebuilding the index.

---

Sivaprasad S http://sivasql.blogspot.com Please click the Mark as Answer button if a post solves your problem!

Answer 5

You should ask yourself why you want to get rid of the fragmentation?

 

If you have a big table, then - depending on your I/O subsystem - heavy

fragmentation can reduce performance. Such reduced performance might be

the case if you run a query that has to return (or look up) a large

percentage of rows, and during that process, because of the

fragmentation, the disk head has to move large distances back and forth

to read each individual page.

 

This fragmentation might be caused by fragmentation within the database

file(s), or because the database files themselves are not contiguous on

the Windows file system. You can eliminate the first type of

fragmentation by rebuilding the clustered index of the table. The second

type of fragmentation requires an SQL Server shutdown and a

defragmentation at the Windows file system level.

 

If your table does not have (at least) hundreds of pages then

defragmenting the table will not help performance. If you have no

queries that select large amounts of data from the table, then

defragmenting the table will not help performance.

--

Gert-Jan

 

Answer 6

Until your tables cross the 8-page bondary, data will reside in mixed extents containg data from potential other objects as well therfore your "bad" fragmentation.

After your table uses 8 pages and more space will come from dedicated extents and will not go back to use mixed extents.

But fragmentation on a table that consist of only 7 pages should not be a consern to you.

 

- Raoul