SQL SERVER – Comparison Index Fragmentation, Index De-Fragmentation, Index Rebuild – SQL SERVER 2000 and SQL SERVER 2005

Index Fragmentation:
When a page of data fills to 100 percent and more data must be added to it, a page split occurs. To make room for the new data, SQL Server must move half of the data from the full page to a new page. The new page that is created is created after all the pages in database. Therefore, instead of going right from one page to the next when looking for data, SQL Server has to go one page to another page around the database looking for the next page it needs. This is Index Fragmentation. Severity of the Index fragmentation can be determined by querying sys.DM_DB_INDEX_PHYSICAL_STATS.

SQL SERVER 2000:
DBCC SHOWCONTIG was used to find index fragmentation. In SQL SERVER 2005 it is deprecated and replaced by query to sys.DM_DB_INDEX_PHYSICAL_STATS.

SQL SERVER 2005:
SELECT query to sys.DM_DB_INDEX_PHYSICAL_STATS displays all the Index Fragmentation related information.

Examples to determine Index Fragmentation in SQL SERVER 2005:
To return the Index Information for only Sales.SalesOrderDetail Table:
USE AdventureWorks;
SELECT INDEX_ID, AVG_FRAGMENTATION_IN_PERCENT
FROM sys.dm_db_index_physical_stats
(DB_ID(),OBJECT_ID(N'Sales.SalesOrderDetail'), NULL, NULL, 'DETAILED')
To return all the information for all the Indexes in Database:
SELECT *
FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL);

Index De-Fragmentation and Index Rebuilding:
Heavily fragmented indexes can degrade query performance and cause your application to respond slowly. I prefer Index Rebuilding is required if Index is fragmented more than 10%. Reorganizing an index de-fragments the leaf level of clustered and non-clustered indexes on tables and views by physically reordering the leaf-level pages to match the logical order (left to right) of the leaf nodes. Having the pages in order improves index-scanning performance.

SQL SERVER 2000:
The DBCC INDEXDEFRAG and DBCC DBREINDEX statements are used to reduce table fragmentation.

SQL SERVER 2005:
ALTER INDEX with the REBUILD clause. This statement replaces the DBCC DBREINDEX statement of SQL SERVER 2000. (DBCC DBREINDEX can still be used in SQL SERVER 2005 but will be sure deprecated in future version of SQL SERVER. I do not recommend to continue use of this in SQL SERVER 2005)
CREATE INDEX with the DROP_EXISTING clause.
Both the above method perform the same function.

Examples to rebuild Index in SQL SERVER 2005:
To Rebuild only one Index:
USE AdventureWorks
GO
ALTER INDEX PK_ProductPhoto_ProductPhotoID
ON Production.ProductPhoto
REORGANIZE
GO

TO Rebuild all the Indexes on Table with Specifying options:
USE AdventureWorks
GO
ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 90, SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = ON)
GO

I have been asked following two questions many times:
1) What is difference between FILLFACTOR = 0 AND FILLFACTOR = 100?
They are SAME.
2) What FILLFACTOR my own Database Servers have for Index? Why?
90. It works great for me.

Reference : Pinal Dave (https://blog.sqlauthority.com) , BOL