Reorganising and Rebuilding Indexes

Question

Reorganising and Rebuilding Indexes

Uncle Boris

SSC Enthusiast

Points: 191
More actions
May 19, 2008 at 7:45 pm

#122154

With regards to writing a script that will reorganise or reindex tables based on the output of sys.dm_db_index_physical_stats MS states:
If avg_fragmentation_in_percent value > 5% and < = 30%
use ALTER INDEX REORGANIZE
of if > 30% use ALTER INDEX REBUILD WITH (ONLINE = ON)*
http://msdn.microsoft.com/en-us/library/ms189858.aspx
Two questions that somebody may know.
Is there a minimum row count that makes it usless to reindex a table
i.e < 1000 rows or so.
This could filter out more tables that do not require a rebuild thus saving a bit of time and resources!
AND
My results show some tables with 70% or so average fragmention but an index type of "heap" and no index name (NULL).
Correct me if I am wrong but I think this means the table either has no indexes at all or as is the case of one table a non-clustered index but no clustered index.
Should these "heaps" be reindexed -- or can these be reindexed if they have no index!!!
Thanks for any help

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

A heap is a table without a clustered index. It may or may not have nonclustered indexes on it. You can rebuild nonclustered indexes that are on a heap, but you cannot rebuild the heap

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

UncleBoris SSCrazy Points: 2018 More actions · Answer 2

Thanks for the reply.

So on that note are you able to tell me if it is "Best Pratice" to have a clustered index on a table? (as opposed to none at all).

We have few tables that are quite large but only have non-clustered indexes on them.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

mark (5/20/2008)
Thanks for the reply.
So on that note are you able to tell me if it is "Best Pratice" to have a clustered index on a table? (as opposed to none at all).

It is.

Tests that someone did showed that all access to a table (not just reads) had improved performance when the table had a clustered index

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

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

Microsoft has as whitepaper on this.

"Generally, you should not be concerned with fragmentation levels of indexes with less than 1,000 pages. In the tests, indexes containing more than 10,000 pages realized performance gains, with the biggest gains on indexes with significantly more pages (greater than 50,000 pages)."

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx

Kalen Delaney has also written a blog post about this.

http://sqlblog.com/blogs/kalen_delaney/archive/2008/02/28/fragmentation-revisited.aspx

I think that you shouldn't rebuild / reorganize these small indexes. I have a stored procedure that you can use if you like.

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

Ola Hallengren

http://ola.hallengren.com

Uncle Boris SSC Enthusiast Points: 191 More actions · Answer 5

Uncle Boris

SSC Enthusiast

Points: 191

May 20, 2008 at 2:37 pm

#818228

Thanks very much for the replies and help