Reorg and Rebuilt Index fragmentation still over 80%

Question

Reorg and Rebuilt Index fragmentation still over 80%

jsheldon

SSCertifiable

Points: 5250
More actions
May 21, 2008 at 9:47 am

#187993

I have a table which is more or less a system table for the software product. The data is to provide a drop down list for various windows.
I realize this is not a performance hit but I can't for the life of me understand why if I try to defrag the table and its clustered index that the fragmentation doesn't go below 80%

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jeffrey Williams SSC Guru Points: 90304 More actions · Answer 1

How large is this table? How many rows/pages?

Small tables will always show a level of fragmentation, and any table with less than 1000 pages is not going to show any improvement in performance be rebuilding or reorganizing the index.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

jsheldon SSCertifiable Points: 5250 More actions · Answer 2

Jeff,

That's it, I was ignoring the page count...these are all small tables well under 1000, testing out the new SQL Admin tool from Idera on Quick Reindex and saw this...thanks

aspirant.dba SSC Eights! Points: 951 More actions · Answer 3

Can you please tell me where can you see page count.

jsheldon SSCertifiable Points: 5250 More actions · Answer 4

Sure run DBCC SHOWCONTIG

Pages Scanned tells you the page count

Ola Hallengren Hall of Fame Points: 3970 More actions · Answer 5

In SQL Server 2005 you can also see the page_count in the dynamic management view

sys.dm_db_index_physical_stats.

I have a stored procedure that is using sys.dm_db_index_physical_stats to dynamically rebuild / reorganize your indexes.

http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

Ola Hallengren

http://ola.hallengren.com

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 6

Ola Hallengren (5/22/2008)
In SQL Server 2005 you can also see the page_count in the dynamic management view
sys.dm_db_index_physical_stats.
I have a stored procedure that is using sys.dm_db_index_physical_stats to dynamically rebuild / reorganize your indexes.
http://blog.ola.hallengren.com/blog/_archives/2008/1/1/3440068.html
Ola Hallengren
http://ola.hallengren.com

There is also one in BOL.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service