Alter Index On Database Rebuild (SQL Server 2005)

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Joe K. - 30 Nov 2008 18:17 GMT

I have a SQL Server 2005 database that I am trying to rebuild the indexes.
I used the ALTER INDEX ON TABLE_NAME REBUILD on each table.

After completing this task I ran the DBCC SHOWCONTIG on each table.

I noticed the logical scan fragmentation is set between 25 - 80.

I thought ALTER INDEX ON TABLE_NAME REBUILD would change the logical scan
fragmentation from DBCC SHOWCONTIG.

Please help me with this issue.

Thanks,

Reply to this Message

JXStern - 30 Nov 2008 18:43 GMT

Two questions, what version of SQL Server, and what filegroup is your
table built on - and what files is the filegroup built on? OK, that's
three questions.

Thanks.

Josh

>I have a SQL Server 2005 database that I am trying to rebuild the indexes.
>I used the ALTER INDEX ON TABLE_NAME REBUILD on each table.
[quoted text clipped - 9 lines]
>
>Thanks,

Reply to this Message

Jeffrey Williams - 30 Nov 2008 19:17 GMT

> Two questions, what version of SQL Server, and what filegroup is your
> table built on - and what files is the filegroup built on? OK, that's
[quoted text clipped - 17 lines]
>>
>> Thanks,

One more question :)

How large are the tables (number of rows/number of pages). If the
tables have less than 100 pages - you probably will see a lot of those
tables still fragmented.

Oops - one additional question:

How much free space do you have in the data file?

Jeff

Reply to this Message

Tibor Karaszi - 01 Dec 2008 07:48 GMT

> How large are the tables (number of rows/number of pages).

I agree, This is perhaps the most common reason ion these group where
a rebuild doesn't change things (much) - too few pages to be relevant.
The other would be that the index isn't really an index but a heap.
Posting the actual result will clarify these things.

Signature

Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

>> Two questions, what version of SQL Server, and what filegroup is
>> your
[quoted text clipped - 33 lines]
>
> Jeff

Reply to this Message

Joe K. - 30 Nov 2008 22:56 GMT

The version is version 9 (3042 - SQL Server 2005), with Primary filegroup
with a single data file.

Thanks,

> Two questions, what version of SQL Server, and what filegroup is your
> table built on - and what files is the filegroup built on? OK, that's
[quoted text clipped - 17 lines]
> >
> >Thanks,

Reply to this Message

jrstern - 01 Dec 2008 03:49 GMT

OK, then my latest "ghost" bugs are probably (!?) not involved.

For an easier way to look at fragmentation, try
http://msdn.microsoft.com/en-us/library/ms188917.aspx
sys.dm_db_index_physical_stats

I have noticed that rebuild sometimes does not do as
good a job as reorganize - pluls reorganize chases "ghosts" and
rebuild does not.

But neither gets you to zero fragmentation. I guess I don't really
understand the process or limits, but have been doing some research
recently!

I presume you do have a clustered index on most/all of these tables,
and that that is one (only?) index that you rebuild/reorganize on.

Josh

> The version is version 9 (3042 - SQL Server 2005), with Primary filegroup
> with a single data file.
[quoted text clipped - 24 lines]
>> >
>> >Thanks,

Reply to this Message