Rebuild Indexes not improving fragmentation

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.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Rebuild Indexes not improving fragmentation

Author

Topic

pkkalra
Starting Member

5 Posts

Posted - 2014-03-24 : 00:32:56

I am trying to reduce index fragmentation to below 30%. So I rebuild all indexes. It took 3 hrs and completed successfully.

There are 29 indexes which still have avg fragmentation above 30%. Some more than 90%.

How can I defragment these indexes? How come they are still fragmented after rebuild?

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2014-03-24 : 02:45:32

Could you post the commands you used?
It can sometimes happen fragmentation stays high , if there aren't enough rows.
To view the fragmentation levels use this script - http://www.sqlserver-dba.com/2012/01/sql-index-fragmentation-and-sysdm_db_index_physical_stats.html

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-03-24 : 12:52:32

How big are the tables that show this "issue"? If it's under a certain amount of pages, they cannot be defragmented. I believe scripts often will ignore indexes with less than 10,000 pages in them.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

pkkalra
Starting Member

5 Posts

Posted - 2014-03-25 : 02:22:29

Thanks for pushing me in right direction. The only fragmented tables are the ones having no primary key (can't believe it) or small setup tables. Its making sense now. I should be able to decide the primary keys and hopefully it should resolve my issue. Thanks a ton.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources