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
27 Comments. Leave new
Hi!
could you please describe why index fragmentation job fail to record info about a database at a particular point of time(every week on same day).Its a SQL serever 7.0. when I reran the transaction it successfully execute.Didn’t get ne clue?
please reply on my mail.
Thanks
Hi Pinal,
I think this is confusing please get it corrected from second line where it says rebuilding.. It conrtadicts
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.
Hi Bill,
If you have fragmentation to 0% it is wonderful. If that does not change it is totally fine.
Regards,
Pinal Dave (SQLAuthority.com)
After running
ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 90, SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = ON)
GO
checking the AVG_FRAGMENTATION_IN_PERCENT and find it didn’t change at all. If using DBCC and find that the FRAGMENTATION is 0%. Could you help on why? Thanks
Hi Dave
How r u doing?
I am Vijay from Hyderabad, working for satyam.
Thanks for all the service you provide to our DBA Team.
I need a help from you to resolve fragmentation issue on Full Text Index.
I have a table which is in size of 30GB and having Full Text Index.
Now I need to reindex the same. I need the steps how to perform the same.
Appreciate if you can help in making me understand and resolve the problem.
Thanks & Regards
Vijay
sir
i have a frgmantation of 42 % on one table and i have re created index for that table using LTER INDEX ALL command . but still after recreating index i am getting a freagmantation of 42 %. what should i do to make it to 0 %
MSSQL 2005
ALTER INDEX ALL ON dbo.Mail_Messages
REBUILD WITH (FILLFACTOR = 90, SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = ON)
I executed the above statement but my fragmentation is still over 85%. I have used the rebuild and reorg in the maintenance plan also and I cannot get my fragmentation to reduce.
Any suggestions?
Thanks
I’m a fan of your work.
Whats the difference between rebuild and reorganize? Do they just run at different speeds or is there some other difference between them.
Cheers,
Aaron
You mention index fragmentation… What about table fragmentation? If the index is rebuild, does the fragmentation of the actual data no longer matter?
@ TODD
Well it worked for me …
I created a table with a primary key, which create a clustered index by default and then I checked the fragmentation level using the command given by Dave, I saw the level was 0 (Zero)
Then I inserted 12000 rows in the table, then I saw the index fragmentation level was 20 % , and then I executed the second query,
Instantly my index fragmentation level became 0 %.
Worked for me … !
Pinal, stupid question:
The value returned for avg_fragmentation_in_percent from
sys.dm_db_index_physical_stats is Percent or .%
i.e. if .01 is returned is that .01% or 10% ?
Thanks,
Paul
it is in percentage
Hi Pinal,
i am working on a banking project for a financial bank.it deals with end of day and start of day…i have created some indexes in it. i m getting some error while end of day of any branch.
Error messages:
The replication agent has not logged a progress message in 10 minutes. This might indicate an unresponsive agent or high system activity. Verify that records are being replicated to the destination and that connections to the Subscriber, Publisher, and Distributor are still active. shud i try to defragment it, how can i defragment it.plz give me a long term solution bcoz branches are going to increase like anything… do u have any contact no. its urgent
I’m just wondering and frustrated about what is wrong with the code below. It executes perfectly on many DBs we have but fails on some throwing the error “Incorrect synatx near the keyword ‘with’.
DECLARE @Database VARCHAR(100)
DECLARE @Table VARCHAR(100)
DECLARE @cmd NVARCHAR(300)
DECLARE @fillfactor INT
SET @fillfactor = 70
SET @Database = ‘TestOnly’
SET @cmd = ‘DECLARE TableCursor CURSOR FOR SELECT table_catalog + ”.” + table_schema + ”.” + table_name as tableName
FROM ‘ + @Database + ‘.INFORMATION_SCHEMA.TABLES WHERE table_type = ”BASE TABLE”’
EXEC (@cmd)
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @Table
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd = ‘ALTER INDEX ALL ON ‘ + @Table + ‘ REBUILD WITH (FILLFACTOR = ‘ + CONVERT(VARCHAR(3),@fillfactor) + ‘)’
EXEC (@cmd)
FETCH NEXT FROM TableCursor INTO @Table
END
CLOSE TableCursor
DEALLOCATE TableCursor
Dave,
After reading your post I wanted to point something out. You said:
“I have been asked following two questions many times:
1) What is difference between FILLFACTOR = 0 AND FILLFACTOR = 100?
They are SAME.”
I cannot speak on behalf on SQL 2005 but with SQL 2k this is not an entirely a true statement as there is a difference between the two.
FILLFACTOR = 100 is 100% fill rate and is generally a bad idea.
FILLFACTOR = 0 is a wildcard for whatever the existing fill factor on the index was set to previously.
If the index previously had a FILLFACTOR of 100 then this statement would be true as the fill rate would be 100%.
However if the index had a FILLFACTOR of 90 and a FILLFACTOR = 0 was used, the fill rate of 90% would be retained.
I use a FILLFACTOR of 0 almost exclusively when reindexing a database, as FILLFACTOR’s on index should be individually tuned based on types of activity in that particular table. I would not want to change those values when doing a bulk reindex of a database.
Dear Pinal Dave
i heared alot abt u. impressive….
i am working in dubai. we are havinf problem related to Database Performance. Few days before, we upgraded the sql server. we moved our database from sql server 2000 to sql server 2005 by restoring the backup of SQL server 2000 on SQL Server 2005. we are having a web site which is using this Database. The database was working normally on sql server 200 but after we moved, its dead slow…i mean the over all performance of DB is very slow..we reindex the Database again in SQL Server 2005 but no use…its still same…
Just wanna know, is there any setting in SQL Server 2005 after restoring database from SQL Server 2000???
i mean can u help me which step i am missing ???
hope to have your reply soon
Regards
BASHARIT
Pinal Dave,
What are your thoughts on this post from Kalen Delaney?
Excerpts:
“Fragmentation is really only an issue if there are LOTS of pages. I usually say that if there are less than 100 pages, you should never worry about fragmentation. However, this this whitepaper suggests that a better cutoff would actually be 1000 pages: (link)” See orig post for actual link.
“REBUILD doesn’t seem to make a difference in the avg_fragmentation_in_percent value is when there are too few pages in the table.”
Thanks,
WCM
MODERATOR: HOW ARE YOU ABLE TO POST SCRIPT AND RETAIN COLORS AND INDENTATION?
This is a the latest version of this script as of 2008-11-12:
/*
Script originally authored by Microsoft but had an error - It was retrieving data without prefixing database schema.
-Added a @ViewOnly variable that will allow you to run this script as a test only and review proposed actions. Simply change the value to 0 to execute the proposed actions.
-Also enhanced to perform either a reorg or rebuild, based on range of fragmentation value.
-Also included pages value from DBCC SHOWCONTIG for analysis, to support theory that defragging indexes have few pages is not productive.
~Paul DeBrino : infinityrd.com : Nov 2008
USEFUL INFO -- INSPECT INDEXES' FILL FACTOR VALUES:
============================================
use YourDBName
select * from sys.indexes order by name
USEFUL INFO -- VIEW THE DEFAULT FILL FACTOR OF YOUR SERVER:
============================================
use YourDBname
exec sp_configure 'show advanced options', 1
go
reconfigure
go
exec sp_configure 'fill factor'
go
*/
-- Specify your Database Name:
USE YourDBName
-- Declare variables:
SET NOCOUNT ON
DECLARE @tablename VARCHAR(128)
DECLARE @execstr VARCHAR(255)
DECLARE @objectid INT
DECLARE @indexid INT
DECLARE @frag decimal
DECLARE @maxreorg decimal
DECLARE @maxrebuild decimal
DECLARE @IdxName varchar(128)
DECLARE @pages INT
DECLARE @maxpages INT
DECLARE @ViewOnly bit
DECLARE @ReorgOptions varchar(255)
DECLARE @RebuildOptions varchar(255)
-- Set to 1 to view proposed actions, set to 0 to Execute proposed actions:
SET @ViewOnly=1
-- Decide on the maximum number of pages to consider index for reorg or rebuild:
-- NOTE: Set to 0 if you wish not to consider this value for analysis.
-- Reference
SET @maxpages = 100
-- Decide on the maximum fragmentation to allow for a reorganize:
-- AVAILABLE OPTIONS: https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2005/ms188388(v=sql.90)
SET @maxreorg = 20.0
SET @ReorgOptions = 'LOB_COMPACTION=ON'
-- Decide on the maximum fragmentation to allow for a rebuild:
SET @maxrebuild = 30.0
-- NOTE: only specifiy FILLFACTOR=x if x is a value other than 0 (otherwise do not include the fillfactor option at all):
SET @RebuildOptions = 'PAD_INDEX=OFF, FILLFACTOR=90, SORT_IN_TEMPDB=ON, STATISTICS_NORECOMPUTE=OFF, ALLOW_ROW_LOCKS=ON, ALLOW_PAGE_LOCKS=ON'
-- Declare a cursor to process list of tables:
DECLARE tables CURSOR FOR
SELECT CAST(TABLE_SCHEMA AS VARCHAR(100))
+'.'+CAST(TABLE_NAME AS VARCHAR(100))
AS Table_Name
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
-- Create the temporary table:
if exists (select name from tempdb.dbo.sysobjects where name like '#fraglist%')
drop table #fraglist
CREATE TABLE #fraglist (
ObjectName CHAR(255),
ObjectId INT,
IndexName CHAR(255),
IndexId INT,
Lvl INT,
CountPages INT,
CountRows INT,
MinRecSize INT,
MaxRecSize INT,
AvgRecSize INT,
ForRecCount INT,
Extents INT,
ExtentSwitches INT,
AvgFreeBytes INT,
AvgPageDensity INT,
ScanDensity decimal,
BestCount INT,
ActualCount INT,
LogicalFrag decimal,
ExtentFrag decimal)
-- Open the cursor:
OPEN tables
-- Loop through all the tables in the database:
FETCH NEXT
FROM tables
INTO @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
-- Perform showcontig on all indexes for the current table:
INSERT INTO #fraglist
EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''')
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
FETCH NEXT
FROM tables
INTO @tablename
END
-- Close and deallocate the cursor:
CLOSE tables
DEALLOCATE tables
-- Declare the cursor for the list of indexes to be defragged:
DECLARE indexes CURSOR FOR
SELECT ObjectName, ObjectId, IndexId, LogicalFrag, IndexName, CountPages
FROM #fraglist
--WHERE ((LogicalFrag >= @maxreorg) OR (LogicalFrag >= @maxrebuild))
WHERE INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0
-- Open the cursor:
OPEN indexes
-- Loop through the indexes:
FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag, @IdxName, @pages
WHILE @@FETCH_STATUS = 0
BEGIN
-- Determine if index page count surpasses the defined threshold for needed maintenance:
IF (@pages >= @maxpages)
BEGIN
-- Determine if fragmentation surpasses the defined threshold for rebuilding:
IF (@frag >= @maxrebuild)
BEGIN
IF (@ViewOnly=1)
BEGIN
PRINT 'WOULD be executing ALTER INDEX ' + RTRIM(@IdxName) + ' ON ' + RTRIM(@tablename) + ' REBUILD WITH ( ' + @RebuildOptions + ' ) -- Fragmentation currently ' + RTRIM(CONVERT(VARCHAR(15),@frag)) + '% and Pages were ' + RTRIM(CONVERT(VARCHAR(15),@pages))
END
ELSE
BEGIN
PRINT 'Now executing ALTER INDEX ' + RTRIM(@IdxName) + ' ON ' + RTRIM(@tablename) + ' REBUILD WITH ( ' + @RebuildOptions + ' ) -- Fragmentation currently ' + RTRIM(CONVERT(VARCHAR(15),@frag)) + '% and Pages were ' + RTRIM(CONVERT(VARCHAR(15),@pages))
SELECT @execstr = 'ALTER INDEX ' + RTRIM(@IdxName) + ' ON ' + RTRIM(@tablename) + ' REBUILD WITH ( ' + @RebuildOptions + ' )'
EXEC (@execstr)
END
END
-- Determine if fragmentation surpasses the defined threshold for reorganizing:
ELSE IF (@frag >= @maxreorg)
BEGIN
IF (@ViewOnly=1)
BEGIN
PRINT 'WOULD be executing ALTER INDEX ' + RTRIM(@IdxName) + ' ON ' + RTRIM(@tablename) + ' REORGANIZE WITH ( ' + @ReorgOptions + ' ) -- Fragmentation currently ' + RTRIM(CONVERT(VARCHAR(15),@frag)) + '% and Pages were ' + RTRIM(CONVERT(VARCHAR(15),@pages))
END
ELSE
BEGIN
PRINT 'Now executing ALTER INDEX ' + RTRIM(@IdxName) + ' ON ' + RTRIM(@tablename) + ' REORGANIZE WITH ( ' + @ReorgOptions + ' ) -- Fragmentation currently ' + RTRIM(CONVERT(VARCHAR(15),@frag)) + '% and Pages were ' + RTRIM(CONVERT(VARCHAR(15),@pages))
SELECT @execstr = 'ALTER INDEX ' + RTRIM(@IdxName) + ' ON ' + RTRIM(@tablename) + ' REORGANIZE WITH ( ' + @ReorgOptions + ' )'
EXEC (@execstr)
END
END
ELSE
BEGIN
PRINT '--BYPASSING INDEX ' + RTRIM(@IdxName) + ' ON ' + RTRIM(@tablename) + ' since fragmentation is currently ' + RTRIM(CONVERT(VARCHAR(15),@frag)) + '% which is less than the Reorg and Rebuild thresholds.'
END
END
ELSE
BEGIN
PRINT '--BYPASSING INDEX ' + RTRIM(@IdxName) + ' ON ' + RTRIM(@tablename) + ' since Page Count was ' + RTRIM(CONVERT(VARCHAR(15),@pages)) + ' which is less than threshold. Fragmentation is currently ' + RTRIM(CONVERT(VARCHAR(15),@frag)) + '%'
END
FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag, @IdxName, @pages
END
-- Close and deallocate the cursor:
CLOSE indexes
DEALLOCATE indexes
-- Delete the temporary table:
DROP TABLE #fraglist
GO
Dear Pinal Dave,
I am facing a problem while rebuilding or reorganising a few of my DB inedexes. My Environment is SQL Server 2005 and I am using “ALTER INDEX ….” command.
These indexes remain “Fragmented” even aftre rebuilding or reorganising severl time.
Can you please tell me what could be the reasons?
With Regards,
M Azim
Nice article, just what I was looking for
Hi,
Could please provide me the archiecture of sqlserver2005.
I just want to know how it works
Dear Dave,
I have huge database around 2 tb all most of the tables are partioned.
1. Rebuilding an index at partition level is better or the whole table.
2. If I have to rebuild all partition
is it better by giving partition # like below
ALTER INDEX IX_TransactionHistory_TransactionDate
ON Production.TransactionHistory
REBUILD Partition = 5;
GO
or
ALTER INDEX IX_TransactionHistory_TransactionDate
ON Production.TransactionHistory
PARTITION = ALL
Is the second option is same as rebuild index on the whole table or it goes one partition at a time.
Hi ,wat i do is i rebuild cluster index 1st and then noncluster indexes which is good for me…….