SQL2005 Rebuild Index Not Working

[wackyphill]

After rebuilding an index, it still shows as the same amount of
fragmentation. ANy ideas what's wrong?

I'm determining which indexes to rebuild using the following query:

SELECT
OBJECT_NAME(i.object_id) AS TableName,
i.name AS IndexName,
ips.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL,
'DETAILED') ips
JOIN sys.indexes i ON
i.object_id = ips.object_id
AND i.index_id = ips.index_id
WHERE ips.avg_fragmentation_in_percent > 10

(I know 10% is not enough where a full rebuild is called for, just
wanted to see my fragmentation)

Then I rebuild w/:

ALTER INDEX IX_CustomerName ON Customers REBUILD

When I rerun the 1st query the same amount of fragmentation is shown
as before the rebuild. I'd appreciate any help.

[smithabreddy]

Have you checked the fillfactor defined for these indexes?

(use sys.indexes to get this information)

wa********@yahoo.com wrote:

After rebuilding an index, it still shows as the same amount of
fragmentation. ANy ideas what's wrong?

I'm determining which indexes to rebuild using the following query:

SELECT
OBJECT_NAME(i.object_id) AS TableName,
i.name AS IndexName,
ips.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL,
'DETAILED') ips
JOIN sys.indexes i ON
i.object_id = ips.object_id
AND i.index_id = ips.index_id
WHERE ips.avg_fragmentation_in_percent > 10

(I know 10% is not enough where a full rebuild is called for, just
wanted to see my fragmentation)

Then I rebuild w/:

ALTER INDEX IX_CustomerName ON Customers REBUILD

When I rerun the 1st query the same amount of fragmentation is shown
as before the rebuild. I'd appreciate any help.

[wackyphill]

The Fill_factor is 0 on all my indexes, however I must admit I don't
know what fill factor really means.

[smithabreddy]

Fill Factor - please look this up in Books online.

-------

From BOL: The fill factor option is provided for fine-tuning index data storage
and performance. When an index is created or rebuilt, the fill factor
value determines the percentage of space on each leaf level page to be
filled with data, therefore reserving a percentage of free space for
future growth. For example, specifying a fill factor value of 80 means
that 20 percent of each leaf-level page will be left empty providing
space for index expansion as data is added to the underlying table.

The fill factor value is a percentage from 1 to 100. The server-wide
default of 0 is the optimal choice in the majority of situations. When
fill factor is set to 0, the leaf level is filled almost to capacity,
but some space remains for at least one additional index row. With this
setting, the leaf level space is used efficiently, but room remains for
limited expansion before the page must be split.

Note:
Fill factor values 0 and 100 are the same in all respects.
-------

Not sure why the fragmentation still shows the same. After the
rebuild, can you run the following and the the first SELECT query again
see if the fragmentation has changed? (This is a very resource
intensive operation).

UPDATE STATISTICS Customers WITH FULLSCAN

wa********@yahoo.com wrote: The Fill_factor is 0 on all my indexes, however I must admit I don't
know what fill factor really means.

[wackyphill]

Thanks for the info.

After running UPDATE STATISTICS.

I still received the same results from my 1st query (although I noticed
it took signifigantly longer to run on the 1st attempt after running
UPDATE STATISTICS.

I still have an index at 83.3333333333333 fragmentation. It's wierd.

[Jackie Brophy]

Do you have clustered indexes on the tables? They won't be defragmented
without them.

Jackie
<wa********@yahoo.com> wrote in message
news:11**********************@u72g2000cwu.googlegr oups.com...

Thanks for the info.

After running UPDATE STATISTICS.

I still received the same results from my 1st query (although I noticed
it took signifigantly longer to run on the 1st attempt after running
UPDATE STATISTICS.

I still have an index at 83.3333333333333 fragmentation. It's wierd.

[wackyphill]

Every table has a primary key which I believe (correct me if I'm wrong)
would give them a clustered Index.

[Erland Sommarskog]

(wa********@yahoo.com) writes:

Every table has a primary key which I believe (correct me if I'm wrong)
would give them a clustered Index.

By default, when you create a primary key, this results in a clustered
index. However, that does not mean that just because there is a PK,
that there is a clustered index on the table, as the PK can deliberately
have been created as non-clustered.

(And in many cases, clustering on the PK is not the best choice.)

As for your original question, am I right to suspect that the table
in question is fairly small? If there is only data for 1½ index page,
there will be some free space that will look like fragmentation.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[wackyphill]

I understand your point Erland, but these Primary keys were created w/o
specifying that they should be non-clustered, so I'm certain they are.
Why is it that w/o a clustered index defragmentation on non-clustered
indexes can't occur, or did I misunderstand the previous poster?

You are correct that these tables currently are small by most people's
standards. I'm guessing that the largest one has < 1000 records. Is it
mentioned somewhere in SQL books on-line about free space looking lile
fragmentaion, that I can read up on?

Also, I'm looking for a good SQL Server 2005 book, if anyone has a
recommendation I'd apreciate it. I'm an experienced programmer, but am
not an experienced DBA as far as actually creating and managing tables,
views, and indexes, etc. inteligently. I've usually just written
queries or stored procs that end up getting used in my frontend
programs that interact w/ an existing database someone else had
created.

A book that was more beginner DBA orientated I think would be helpful
to me. Thanks for your input everyone.

[Erland Sommarskog]

(wa********@yahoo.com) writes:

I understand your point Erland, but these Primary keys were created w/o
specifying that they should be non-clustered, so I'm certain they are.
You can always use sp_helpindex to find out.
Why is it that w/o a clustered index defragmentation on non-clustered
indexes can't occur, or did I misunderstand the previous poster?
I don't recall exactly what he said, but you can't defragment the data
pages without a clustering index. The NC indexes are defragmentable
nevertheless.
You are correct that these tables currently are small by most people's
standards. I'm guessing that the largest one has < 1000 records. Is it
mentioned somewhere in SQL books on-line about free space looking lile
fragmentaion, that I can read up on?

Let me put it this way: with tables of that size, there's usually not
much reason to worry about fragementation anyway.

Fragmentation matters when it results in data being spread out on
many extra pages. Say that you have a table with 1 GB of data. If
the data pages are only 50% full, the server needs to read 2 GB of
disk to scan all data. This can affect the performance of the system.

Whether this is spelt out in clear in Books Online, I don't know. But
you can read about the physical architecture starting on
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/33572c3b-343d-45aa-bb42-1d31423ae5cc.htm.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[wackyphill]

Yeah, I was just testing the rebuild process to see that it was
sucessful in correcting fragmentation in the event it became necessary
as well as to add the process into a maintenance plan.

Hopefully this behavior I'm experiencing is like you said, because of
the small amount of data present, as no one else seems to have posted
similar problems.

Thank you for your assistance. I appreciate you sharing your time and
experience.