Rebuild Index on Primary Key Column
-
Hey,
I am trying to rebuild an index on a table using DBCC DBREINDEX. The column is a primary key and is non clustered in ascending order. The table has just over 51k rows. The column is of type numeric. The fill factor is 0%.
When I run DBCC SHOWCONTIG, I get Extent Scan Fragmentation of nearly 60%. I have also tried updating all of the statistics before running DBREINDEX again.
I'm a bit confused by this. We have other sites using the same database and I do not have the same issues with this index.
This is the only index that is showing any degree of fragmentation after running a DBREINDEX.
As this is a production box, I can't drop the index without creating a change control document etc, so I am looking for any other suggestions before I restore the database to a test environment and experiment with this.
Thanks,
Clive
-
What is the definition of the table and the index? Maybe the record size is affecting the fragmentation.
I had one instance way back on SQL 6.5 where we had one table with a fill factor of 40% due to the record size. Anything higher, or lower than this caused excessive fragmentation. Thank god for 8k pages
--------------------
Colt 45 - the original point and click interface
-
Fragmentation is also determined by the distribution of key values over the entire possible range for a given index. Take a 6 digit integer as an example (999,999 rows).
If you have 200,000 rows in the table and each integer key is 3 greater than the previous fragmentation mught not be too bad. However change that to the first 100,000 rows are sequentially keyed, then the next key starts at 500,000 for a duration of 50,000 rows separated by one and then finish with the final 50,000 rows any way you'd like. My guess is that fragmentation will be greater.
Then factor in 'volitility' (the number of inserts/updates/deletes) over time and even the differences between identical tables with different data and usage will again alter the fragmentation look of things.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
How many files do the file group that the table (PK) resides have? Extent Scan Fragmentation value will be high if the index spans multiple files and you should not evaluate the fragmentation by this values.
In this case, check the scan desity is more helpful.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply