Fragmentation Level - SQL Server Forums

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)

Fragmentation Level

Author

Topic

peter.mano
Starting Member

6 Posts

Posted - 2010-06-28 : 14:35:07

Hi,
In one of our productions server, in a particular database, few of the tables were fragmented whose values were above 50% for those tables. I ran optimization job on the database, but even after that, fragmentation levels were not decreased. What could be the possible cause for it?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-06-28 : 14:41:45

How many pages are there in each of the indexes? Indexes with less than ~1000 pages can't be defragmented.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

peter.mano
Starting Member

6 Posts

Posted - 2010-06-28 : 14:46:19

Y so?

quote:
Originally posted by tkizer

How many pages are there in each of the indexes? Indexes with less than ~1000 pages can't be defragmented.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-06-28 : 15:20:03

Because there isn't enough data.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

peter.mano
Starting Member

6 Posts

Posted - 2010-06-29 : 10:53:20

There are around 800 index pages for fue tables. So can you explain me in detail, whether sql server doesnt do rebuild for those tables. Is there any valid comment from microsoft on this?

quote:
Originally posted by tkizer

Because there isn't enough data.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-06-29 : 12:21:49

There is no detail to provide. Your indexes are too small to be defragmented, plain and simple. You can certainly rebuild them, however fragmentation is unlikely to decrease.

This information is directly from several Microsoft engineers as well as several online articles. I don't have any links to show you at the moment, feel free to google it.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources