September 18, 2009 at 7:50 am
Hi,
I have a job that rebuilds the index on one of our production databases. The job runs succesfully however when I check the 'avg fragmentaion in percent' using the DMV sys.dm_db_index_physical_stats I see that there has been no change in the value of 'avg fragmentaion in percent' for all the indexes.
The rebuilding of indexes is being performed using sql statements but not through management studio :
SQL :
USE [DB]
GO
ALTER INDEX [ACCOUNTBILL] ON [dbo].[Account] REBUILD WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF )
GO
September 18, 2009 at 8:00 am
is that the only index on the table?
The probability of survival is inversely proportional to the angle of arrival.
September 18, 2009 at 8:03 am
Trooper-731533 (9/18/2009)
Hi,I have a job that rebuilds the index on one of our production databases. The job runs succesfully however when I check the 'avg fragmentaion in percent' using the DMV sys.dm_db_index_physical_stats I see that there has been no change in the value of 'avg fragmentaion in percent' for all the indexes.
The rebuilding of indexes is being performed using sql statements but not through management studio :
SQL :
USE [DB]
GO
ALTER INDEX [ACCOUNTBILL] ON [dbo].[Account] REBUILD WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF )
GO
How big/rows is the table and can you post the results from sys.dm_db_index_physical_stats, you have to take into other factors, like what is the fill factor and how many pages are involved.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 18, 2009 at 8:17 am
Ther are many index present in the db....
here is a fraction of result :
538968053601HEAPIN_ROW_DATA1095.88235294117651684.39880952380952739NULLNULLNULLNULLNULLNULLNULLNULL
542720062201HEAPIN_ROW_DATA1092.5925925925926275.85185185185185158NULLNULLNULLNULLNULLNULLNULLNULL
September 23, 2009 at 7:07 am
I have also tried the follwing code to rebuild all the indexes in the db ...
DECLARE @TableName varchar(255)
DECLARE @cmd NVARCHAR(500)
DECLARE @fillfactor INT
SET @fillfactor = 90
DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
EXEC (@cmd)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
But there seems to be no change in the fragmentation , few of the indexes have an avg_fragmentation_in_percent value mare than 90.....
September 23, 2009 at 7:15 am
Any of these indexes clustered? I see the word 'heap' in your result set which suggests not.
---------------------------------------------------------------------
September 23, 2009 at 7:17 am
There are few clustered indexes and heap present on the db....
September 23, 2009 at 9:41 pm
Is clustered index defined on the table in question?
MJ
September 24, 2009 at 10:37 am
Check how many pages the table has used. The table in question might have fewer data pages. If there are fewer pages SQL Server does not consider this to be a target for defragmentation as it is unnecessary.
Thanks,
Amol
Amol Naik
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply