Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AgaK
Yak Posting Veteran
86 Posts |
 Posted - 2009-09-02 : 12:29:44
|
| HiI am executing the following query to see the fragmentation of the indexes on my databases (SQL 2005):SELECT a.index_id, b.name index_name, a.avg_fragmentation_in_percentFROM sys.dm_db_index_physical_stats (66, NULL, NULL, NULL, NULL) AS aJOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_idJOIN sys.objects AS c ON c.object_id = a.object_idJOIN sys.schemas AS s ON s.schema_id = c.schema_id WHERE (b.name IS NOT NULL) AND (a.avg_fragmentation_in_percent > 30)ORDER BY a.avg_fragmentation_in_percent DESCI am executing the following statement to rebuild the index on one of the tables:ALTER INDEX ALL ON SchemaName.TableName REBUILDWhen I run the select statement again the avg_fragmentation_in_percent values for indexes on SchemaName.TableName remain unchanged.Any idea why? My understanding was that the value of avg_fragmentation_in_percent will get updated to 0 when the indexes are rebuilt.Thank you kindlyAgaK |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
AgaK
Yak Posting Veteran
86 Posts |
Posted - 2009-09-02 : 17:34:47
|
| 3510 rows in the table |
 |
|
|
AgaK
Yak Posting Veteran
86 Posts |
Posted - 2009-09-02 : 18:26:39
|
| 3510 rows and data space of 2.914 MB |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
AgaK
Yak Posting Veteran
86 Posts |
Posted - 2009-09-02 : 19:22:30
|
| Thank you very much for you reply.How big the table has to be to worry about defragmentation?thank you again |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
AgaK
Yak Posting Veteran
86 Posts |
Posted - 2009-09-03 : 11:12:26
|
| HiWould you have any articles or web sites that you could recommand to me? on the sujbect?Once again thank you |
|
|
|
|
|
|
avg_fragmentation_in_percent not updated