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:
SELECT INDEX_ID, AVG_FRAGMENTATION_IN_PERCENT
(DB_ID(),OBJECT_ID(N'Sales.SalesOrderDetail'), NULL, NULL, 'DETAILED')
To return all the information for all the Indexes in Database:
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:
ALTER INDEX PK_ProductPhoto_ProductPhotoID
TO Rebuild all the Indexes on Table with Specifying options:
ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 90, SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = ON)
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
Pinal ,one question
Is there any difference between Alter Index ..Rebuild and
DBCC DBREINDEX ?
I am using SQL Server 2005 and index frag is 66.67% all the time. I tired drop/create and reorganize and rebuild the indexes. but still the total fragmentation is 66.67%. How to reduce it.
i have read one your article What is the best value for the Fill Factor? – Index, Fill Factor and Performance – Part 2
where you have explained one approx fill factor criteria. I have a query regarding cluster index. I have a table where my cluster index is on a unique column but all other columns in this table are updated through out the process.(Appx 15-20 times or more a day) Should I keep fill factor to 100 as per the unique index or should i make it somewhere in between 70-90 as data is contineously updating in this table.
Basicly I want to know what we need to do with Fill factor if my unique queue where we have the clustered index is not changing but all the rest columns are updating contioneously.
Hello .. How can I check the progress rebuild of indexes?