October 25, 2008 at 2:32 pm
I tried so many ways to defrag the index. I tried on Management Studio to rebuild a specific index. I tried the following script. it runs and didnot return any error message. I tried DBCC REINDEX. But no matter what i did, the AVG_FRAGMENTATION_IN_PERCENT didnot change at all.
USE ClarifyTest
GO
ALTER INDEX objindex ON ClarifyTest.dbo.table_qry_grp
REBUILD WITH (FILLFACTOR = 100, SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = ON)
GO
Can anyone tell me why?
October 25, 2008 at 2:43 pm
How big is the table, how many rows, how many pages? (sys.dm_db_index_physical_stats will tell you that)
If I had to guess, I'd say this is a really small table, less than 100 pages. In smaller tables, because of the allocation algorithms, the fragmentation will not reduce to 0. It's nothing to worry about on small tables (< 100 pages)
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
October 25, 2008 at 2:55 pm
It is a small table. but i am just wondering why the fragmentation didnot change to 0
October 25, 2008 at 3:27 pm
It has to do with how the first few pages of the table are allocated. How many pages are there in this table?
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
October 25, 2008 at 3:47 pm
it has 872 rows and 3-4 fields.
I created a reindex maintainess plan. It did help with some tables. but not all of them
October 26, 2008 at 3:18 am
How many pages?
As I said, smaller tables will not have fragmentation reduced to 0, and it's nothing to be concerned about
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply