January 3, 2008 at 10:57 am
I have just updated a maintainance script that runs once a week and either reorganises or rebuilds indexes depending on the logical fragmentation percentage.
My script compares the logical fragmentation beforehand and afterwards so that I can see if its made a difference.
When I run this script on the dev box however I see that in nearly all cases the logical fragmentation does not decrease.
I picked one table from the list in particular that has two indexes based on it.
The clustered index has a logical fragmentation of 66.6 %, the non-clustered is 0%.
The fill factor is set to 100% (at the moment it will be changed later)
However whenever I run any of the following statements
ALTER INDEX PK_SITE_JOBS_INDEX_7 ON dbo.SITE_JOBS_INDEX_7 REBUILD WITH (FILLFACTOR = 70)
ALTER INDEX PK_SITE_JOBS_INDEX_7 ON dbo.SITE_JOBS_INDEX_7 REBUILD WITH (FILLFACTOR = 100)
ALTER INDEX PK_SITE_JOBS_INDEX_7 ON dbo.SITE_JOBS_INDEX_7 REORGANIZE
ALTER INDEX ALL ON dbo.SITE_JOBS_INDEX_7 REORGANIZE
ALTER INDEX ALL ON dbo.SITE_JOBS_INDEX_7 REBUILD (FILLFACTOR = 70)
ALTER INDEX ALL ON dbo.SITE_JOBS_INDEX_7 REBUILD (FILLFACTOR = 100)
(OR the same code with the non-clustered index)
It makes no difference whatsoever to the fragmentation level and the details always stay the same
(from DBCC SHOWCONTIG / sys.dm_db_index_physical_stats shows the same figures)
ObjectNameObjectIdIndexNameIndexIdLevelPagesRowsMinimumRecordSizeMaximumRecordSizeAverageRecordSizeForwardedRecordsExtentsExtentSwitchesAverageFreeBytesAveragePageDensityScanDensityBestCountActualCountLogicalFragmentationExtentFragmentation
SITE_JOBS_INDEX_7875306328PK_SITE_JOBS_INDEX_7103NULLNULLNULLNULLNULLNULL2NULLNULL33.33333333333331366.6666666666667NULL
SITE_JOBS_INDEX_7875306328nclidx_SITE_JOBS_INDEX_7_SortKey201NULLNULLNULLNULLNULLNULL0NULLNULL100110NULL
No errors are reported when I run the commands and I have the correct privelleges to do so.
When I do a select from sys.indexes I can see that the fillfactor has changed from 100 to 70 (or vice versa) so the commands are running.
So the question is what is keeping the fragmentation at 66.6% and is there anything that can be done to reduce it. Is there some other
issue such as hard disk fragmentation that is causing the problem and preventing the desired solution from running?
Thanks in advance for any help.
January 3, 2008 at 12:25 pm
How many rows are in the tables? I've seen this when the number of rows is very low. Other than that, rebuilding the index usually changes the fragmentation percentage.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 3, 2008 at 12:51 pm
There are 2347 rows in this table.
Looking at some of the other tables that no change in the frag % it does seems that the majority are all
in the 50 to 4000 rows in table range.
The largest table reported had 14732 rows in it and the frag % was in a range where the script was trying to do a re-organise to de-frag it with no change at all. However when I manually did a rebuild on the index instead it did reduce the fragmentation. However trying to do the same (opposite method to what the script was trying to do) with the other tables reported doesn't seem to work.
Currently I am trying to do a reorganise when the frag % is > 20 and < 50
and a rebuild when its > 50.
January 3, 2008 at 12:59 pm
It doesn't surprise me to see it in the tables with 50 rows. I ran into the same issue with tables that had less than ~200 rows.
The others... I'm not sure. It should work. I double checked the behavior on a couple of tables just now. Any other information on the system? Have you run update statistics on it recently?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 3, 2008 at 1:13 pm
I am running sp_updatestats at the end of the proc.
I was thinking that as its on the dev box there might be an issue with the actual hard drive being fragmented but I don't know enough about the issue.
here is the proc code maybe you can see something I am doing wrong:
ALTER PROCEDURE usp_sql_jobboard_index_stats_maintainance
AS
BEGIN
SET NOCOUNT ON;
DECLARE @ReOrganiseLvl int,
@RebuildLvl int,
@DEBUG bit
SET @DEBUG = 1
--For small fragmentation we try a re-organise for larger fragmentation we do a rebuild
SET @ReOrganiseLvl = 10 --the frag percent level which a re-organise is carried out
SET @RebuildLvl= 25 --the frag pecent level which a rebuild is carried out - Must be higher than the level for re-organise
IF @RebuildLvl <= @ReOrganiseLvl
BEGIN
PRINT 'You must set the rebuild percent level to be higher than the reorganise level.'
RETURN
END
DECLARE @tableName varchar(255),
@Object_id int,
@Index_id int,
@IndexName varchar(255),
@Frag decimal,
@AfterDeFrag decimal,
@Method varchar(10),
@ErrorMsg varchar(500),
@SQL varchar(1000),
@ERROR bit
DECLARE @INDEXES TABLE(tableName varchar(255), Object_id int, Index_id int, IndexName varchar(255), frag decimal, AfterDeFrag decimal, Method varchar(10), ErrorMsg varchar(500))
INSERT INTO @INDEXES
(tableName, Object_id, Index_id, IndexName, Frag)
SELECTobject_name(d.object_id), d.object_id, d.index_id, i.name as IndexName, avg_fragmentation_in_percent
FROMsys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL, NULL) as d
JOINsys.indexes as i ON i.object_id = d.object_id and d.index_id = i.index_id
WHERE d.Index_Type_Desc <> 'HEAP' AND
i.Name is not null AND
object_name(d.object_id) LIKE 'tbl_%' AND
avg_fragmentation_in_percent >= @ReOrganiseLvl
ORDER BY object_name(d.object_id)
DECLARE INDEXER CURSOR LOCAL FAST_FORWARD FOR
SELECTtableName, Object_id, Index_id, IndexName, Frag
FROM@INDEXES
WHEREAfterDeFrag IS NULL
ORDER BY tableName
OPEN INDEXER
WHILE(0=0)
BEGIN
FETCH NEXT
FROM INDEXER
INTO @tableName, @Object_id, @Index_id, @IndexName, @Frag
IF @@FETCH_STATUS <> 0 BREAK
SELECT @ERROR = 0, @ErrorMsg = ''
IF @FRAG < @RebuildLvl
BEGIN --do a defrag/re-organise
SET @Method = 'REORGANIZE'
SET @SQL = 'ALTER INDEX ' + @IndexName + ' ON dbo.' + @TableName + ' REORGANIZE'
END
ELSE --do a rebuild/reindex
BEGIN
SET @Method = 'REBUILD'
SET @SQL = 'ALTER INDEX ' + @IndexName + ' ON dbo.' + @TableName + ' REBUILD'
END
IF @DEBUG = 1
BEGIN
PRINT @Method + ' Index: ' + @IndexName + ' on table: ' + @TableName
PRINT @SQL
END
--now run the reindex / reorganise in a try catch
BEGIN TRY
EXEC (@SQL)
END TRY
BEGIN CATCH
SELECT @ERROR = 1, @ErrorMsg = CAST(ERROR_NUMBER() as varchar) + ' - ' + ERROR_MESSAGE()
IF @DEBUG = 1
PRINT 'ERRORED ' + @ErrorMsg
END CATCH
--if it didn't error lets see if it made the slightest bit of difference
IF @ERROR = 0
BEGIN
SELECT@AfterDeFrag = avg_fragmentation_in_percent
FROMsys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL, NULL)
WHEREobject_id = @Object_id AND
index_id = @Index_id AND
alloc_unit_type_desc = 'IN_ROW_DATA' --ignore out of row blob data
END
ELSE
SELECT @AfterDeFrag = @Frag --as it errored no change in this
--update table
UPDATE@INDEXES
SETMethod = @Method,
AfterDeFrag = @AfterDeFrag,
ErrorMsg = @ErrorMsg
WHEREObject_ID = @Object_ID AND
Index_ID = @Index_id
END
CLOSE INDEXER
DEALLOCATE INDEXER
SELECT*
FROM@INDEXES
ORDER BY TableName
--now update statistics
EXEC sp_updatestats
END
GO
January 3, 2008 at 1:22 pm
How many pages do the tables have?
Re org doesn't touch the non-leaf levels of the index, whereas rebuild does. That could be the cause of some of your differences.
Oh, and btw, you don't need to update the stats of any index that's been rebuilt. Stats are updated during the rebuild. Re org doesn't update stats.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 3, 2008 at 1:30 pm
The page count ranges from 2 to 77.
It seems that those indexes that have pages > 20 (roughly) are having re-organises carried out against them whilst the indexes with smaller no of pages and higher logical frag are having rebuilds.
January 4, 2008 at 3:10 am
I have just asked someone to take a look at the phyisical hard drive that the dev server is on and they
say it reports 97% fragmentation which isn't good. We are going to defrag the box over the weekend and then on Monday I will re-run the script to see if it makes any difference.
January 7, 2008 at 10:08 am
The tech lab rats say they have defraged the hard disk and its down to 0%.
However I ran the script again and it made no difference. I picked one table in particular that had 83% fragmentation (just over 1000 rows of data) and manually ran ALTER INDEX statements for REBUILD and REORGANIZE which made no difference whatsoever.
However I then ran the older DBCC DBREINDEX command and it actually did decrease down to 63% which I don't understand why a) The DBCC worked when the ALTER index didnt. Its SQL 2005 with correct compatibility mode on. b) Also why the DBCC worked but only slightly.
I changed the script to use DEFRAG and DBREINDEX instead of ALTERS and on a small percentage of the tables it made a difference but on the majority they stayed the same like the previous code.
However this shows that on those few tables the DBCC commands did make a difference in changing the fragmentation in some cases from 50%+ down to 0 whereas the ALTER INDEX reorg/rebuild had no effect. Why would that be?
Also with the one table/index in particular I also tried dropping the index totally and rebuilding from scratch but it still reports high fragmentation straight away after re-creation.
I have tried changing the fill factor, padding etc
fill factor = 100% (page fullness 95%, frag=87%)
fill factor = 70% (page fullness 69%, frag=63%)
fill factor = 50% (page fullness 47%, frag=43%)
I have tried changing it from a clustered to a non-clustered also as the key is on a numeric that has a few gaps in the sequence I tried putting it on a newly created sequential numeric with no gaps but that didn't make a difference either.
Does anyone have any suggestions. Thanks
February 25, 2008 at 5:13 pm
Make sure you have enough contiguous extents to build the new index in and that you are attempting one index at a time rather than using the ALL option. If there is not enough space in the database, it will not be able to recreate the index entirely contiguous.
I assume we are talking extent/external fragmentation here since the page fullness seems to agree with the fill factors.
You might check out this technet article. It is somewhat dated but I think most of it still applies.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Amongst other things, it says:
“Generally, you should not be concerned with fragmentation levels of indexes with less than 1,000 pages. "
One suggestion I heard was to create a file group with lots of empty space in it and re-create the index in that file group to give it plenty of contiguous extents to work with. If that works, you can try moving it back to the original filegroup afterwords.
One question you need to ask is whether it really even matters: External fragmentation only affects range scans such as when you seek into a clustered index and then scan in all the records from the first value to the final value. And even then it has to do with IO Read-ahead buffering, so if the table were 100% in memory external fragmentation would essentially be a non-issue; the problem, of course is that that's rarely the case as you try to fit a 100GB database into 2GB of RAM.
Let me know if any of this is helpful!
February 25, 2008 at 5:26 pm
Oh. I should also mention that external fragmentation should be as high as 100% if your clustered table/index has less than 9 pages; SQL Server allocates the first 8 pages on mixed extents, which by definition will mean that you often have non-contiguous pages. I believe you can even have every page on a seperate non-contiguous extent which would result in 100% fragmentation as a normal situation. So, until you have enough rows to fill 8 pages, you can expect very high external fragmentation.
And if the table is not a clustered index (has no clustered index/is a heap), external fragmentation has no meaning since there is no logical order to the data and there is no B-Tree. So fragmentation is essentially always 100% for tables with no clustered indexes (although the non-clustered indexes on non-clustered tables(heaps) can have their own fragmentation issues).
So, to summarize: you are wasting your time if you try to defragment the logical page order or extent order of tables that are less than 9 pages in size. And also forget about trying to defragment tables that have no clustered index (although you can worry about any non-clustered indexes regardless of whether a clustered index exists on the table).
February 26, 2008 at 2:30 am
Very interesting information. I didn't know most of that and it explains a lot. Basically I am worrying
about nothing in the majority of these cases then.
Thanks for your input!
February 27, 2008 at 8:34 am
Not only is it futile to try and defrag such small tables, but the benefit you would get, even if you were able to do so, would be negligible.
I would suggest that in your scripts you restrict the tables/indexes slated for defragmenting to having over, say 10 pages of data - and ignore the rest.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
June 18, 2008 at 12:41 am
Hi,
I even face the same issue while not able to remove fragmantation after usning alter index rebuild option.
Moreover index pages are more than 10 in my case.
Can anyone give some more information about this?????
Thanks,
Amit Khanna
June 18, 2008 at 2:57 pm
Microsoft has as whitepaper on this.
"Generally, you should not be concerned with fragmentation levels of indexes with less than 1,000 pages. In the tests, indexes containing more than 10,000 pages realized performance gains, with the biggest gains on indexes with significantly more pages (greater than 50,000 pages)."
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
My recommendation would be to have a filter on a 1000 pages and report back if you have indexes of that size where you're not able to remove the fragmentation.
I have a stored procedure that could help you with this.
http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
You could also use the script in Books Online.
http://msdn.microsoft.com/en-us/library/ms188917.aspx
Ola Hallengren
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply