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
 General SQL Server Forums
 New to SQL Server Administration
 high index fragmentation

Author  Topic 

misken
Starting Member

8 Posts

Posted - 2014-10-30 : 07:36:54
Hello,

I have a question about index fragmentation.

I have implemented Ola Hallengrens index optimization script and it ran successfully tonight. A lot of databases indexes look much better today, but some still have a high fragmentation.

The worst case is:
average framentation in percent of over 60%
and
page_count over 830000

Can anyone explain this to me or know what the reason is?

Sys.developer/accidental DBA

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-30 : 09:12:38
Not familiar with the script you mentioned but have you tried Minion?

http://sqlmag.com/database-performance-tuning/new-free-sql-server-index-maintenance-solution-minion-reindex
Go to Top of Page

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2014-10-30 : 09:32:45
do they have a clustered index on table?

Javeed Ahmed
https://www.linkedin.com/pub/javeed-ahmed/25/5b/95
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-10-30 : 11:36:40
Olla Hallengren's scripts are arguably the best in the business, so I suspect it is something other than any deficiency with the script that is causing some indexes to be not rebuilt. Perhaps the indexes got fragmented after the rebuild/reorganization due to some overnight job that updates/inserts data into those tables? Or, it could be that you had some options selected which caused those tables to be omitted (e.g., if you chose online rebuild and the index did not support online rebuild). I assume you have looked at the various options available on his Index and Statistics Maintenance page
Go to Top of Page

misken
Starting Member

8 Posts

Posted - 2014-10-31 : 09:55:35
Ahmeds: does it make difference if it is clustered or nonclustered for the index optimization to get triggered?

James: I doubt that it would be any overnight job since i have online rebuild at 10% and offline rebuild at 20%, and the fragmentation is at 60%. We will check through the options more carefully though.

Sys.developer/accidental DBA
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-10-31 : 12:38:31
There is a @LogToTable option which lets you log the indexing operations; Details in the FAQ section.. https://ola.hallengren.com/frequently-asked-questions.html
Go to Top of Page

Shanky
Yak Posting Veteran

84 Posts

Posted - 2014-11-07 : 11:44:52
Are you specifying any fill factor during rebuild of index. Is index not getting defragmentaed at all or after some time(perhaps quickly) they are again getting fragmented. SQL Server must rebuild index with large page count like what you mentioned for small page count its likely fragmentation would not change much for reason please see
http://social.technet.microsoft.com/wiki/contents/articles/28182.sql-server-in-depth-what-can-cause-index-to-be-still-fragmented-after-rebuild.aspx

Hope this helps

Regards
Shanky
SQL Server MVP
http://social.technet.microsoft.com/wiki/contents/articles/24253.list-of-articles-by-shanky.aspx
Go to Top of Page
   

- Advertisement -