asked on

Index fragmentation doesn't go lower after rebuilding index.

System: Windows Server STD 2012, SQL 2008 STD.

I rebuilt indexes using the maintenance job in SMSS.
It finished with no error.
I checked fragmentation after it(Code to check fragmentation is attached), it still shows 99% fragmentation.

-- Check fragmentation on db(sample)

Declare @dbid INT
Set @dbid =DB_ID('sample')

select dbschemas.[name] as 'Schema', 
                dbtables.[name] as 'Table', 
                dbindexes.[name] as 'Index',
                indexstats.avg_fragmentation_in_percent,
                indexstats.page_count
from sys.dm_db_index_physical_stats (@dbid, NULL, NULL, NULL, NULL) as indexstats
                INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
                INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
                INNER JOIN sys.indexes as dbindexes on dbindexes.[object_id] = indexstats.[object_id]
                                AND indexstats.index_id = dbindexes.index_id
where indexstats.database_id = @dbid
order by indexstats.avg_fragmentation_in_percent desc

Open in new window

So, I pulled out the properties window of the table and checked, it shows;
'fragmentation: 0%
Page fullness 99%/

I'm not an expert in SQL, don't understand what the Page fullness is. But why does the query still show 99% fragmented while the properties window of the table show 0% fragmented? And why does page fullness show 99% and how to reduce? Will it show different fragmentation level if I run the query again?
2016-10-19-22_02_54-SQL-How-to-check.png
2016-10-19-22_02_54-SQL-How-to-check.png
2016-10-19-22_05_10-SQL-How-to-check.png

SOLUTION

arnold

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Nakul Vachhrajani

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

crcsupport

ASKER

No, when I examined the indexes, all non clustered indexes.
So, which means, rebuilding indexes won't help?
I'm not allowed to add or change index type.

crcsupport

ASKER

I just ran rebuildIndex on a heap table (The table doesn't have a clustered Index) using this code;

/*Check fragmentation on table*/
USE nextgen;
GO

Declare @dbid INT
Set @dbid =DB_ID('nextgen')

Declare @tableid INT
Set @tableid =OBJECT_ID('tbRideResArcMaster')

SELECT
     index_id
    ,index_type_desc
    ,index_depth
    ,index_level
    ,avg_fragmentation_in_percent
    ,fragment_count
    ,page_count
    ,record_count
FROM sys.dm_db_index_physical_stats(
     @dbid 
    ,@tableid
    ,NULL
    ,NULL
    ,'DETAILED');
GO

/*Rebuild Index on single index of a table*/
--For investigational purpose, it's better to use SMSS. Right-click on an index, check fragmentation rate, match it to the above fragmentation report to indentify the index, then run 'rebuild'
--Heap

/*Rebuild all indexes on a table */
 DBCC DBREINDEX ('tbRideResArcMaster', ' ', 90);

Open in new window

The heap is still fragmented. Does this mean this table can't be defragmented and doesn't take any benefit of doing rebuildindex? All Indexes were defragmented, but not the heap. I'm curious where rebuildindex will improve or not until a clustred index is added.

crcsupport

ASKER

Forgot to upload the rebuildindex of the heap table image.

SOLUTION

Nakul Vachhrajani

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

crcsupport

ASKER

AH.. think I'm stuck. If rebuilding index doesn't help for this, I don't know why I'm asked to do this from them.
But I just have to do what I was asked by them.

Thank you!

crcsupport

ASKER

By the way, I found a command to help a little defragmenting a heap.
ALTER TABLE .... REBUILD
This will defrag all non clustered indexes and heap as well.
The heap dropped from 99% to 72%, but still a lot fragmented.