Solved

Index fragmentation doesn't go lower after rebuilding index.

Posted on 2016-10-19
8
76 Views
Last Modified: 2016-10-20
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%/

img12016-10-19-22_04_26-DC2-on-WIN2012-S.png
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
0
Comment
Question by:crcsupport
  • 5
  • 2
8 Comments
 
LVL 76

Assisted Solution

by:arnold
arnold earned 150 total points
ID: 41852004
An active db with many inserts/updates could ...

You posting an image, but not the fragmentation data you reference.

You might be trying to rebuild an index that can not be defragmented.
0
 
LVL 11

Accepted Solution

by:
Nakul Vachhrajani earned 350 total points
ID: 41852038
What is Page Fullness?
For comparison purposes, assume if you will that the database table is like a book. Just as text in a book is arranged in a page, so is data in a table in SQL Server.

Page Fullness basically indicates how much of a page is full. The expected number of page fullness depends on a lot of factors. But generally, if you have an sequentially growing number of rows (e.g. in case of a PoS system), you would want the page to be as full as possible. If you have data that may come randomly (e.g. a system that gets data from multiple sources), you may want a lower page fullness so that you can add contents on a page if you find something that applies to that page (like a weekly planner notebook).

Fragmentation % issue
With respect to the fragmentation %, I think the mismatch is due to the different indexes being compared in the query v/s SSMS. Please add the sys.indexes.type_desc column (see code below) in your query.

What you are seeing as 97% fragmented in the query is that of the Clustered index unless the table is a heap.

Based on the SSMS screenshot, I am unable to see any index as "Clustered" on the table in which case the only way to defragment the table is to define a clustered index on the table. Does your table have a clustered index?

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,
                dbindexes.[type],    --0 = Heap, 1 = Clustered Index, 2 = Nonclustered index
                dbindexes.type_desc  --Add these values
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

0
 
LVL 1

Author Comment

by:crcsupport
ID: 41852381
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.
0
 
LVL 1

Author Comment

by:crcsupport
ID: 41852558
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.
0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 1

Author Comment

by:crcsupport
ID: 41852559
Forgot to upload the rebuildindex of the heap table image.
2016-10-20-13_29_37-Index-fragmentat.png
0
 
LVL 11

Assisted Solution

by:Nakul Vachhrajani
Nakul Vachhrajani earned 350 total points
ID: 41852604
Yes, the clustered index is essentially the order of the records in a table. A heap (i.e. table without a clustered index), as the name suggests lacks a logical order in the way records are stored. It's like going to the stores for shopping at the dollar store, or on the day after Thanksgiving/Christmas. The heap is therefore like a pile of rows, and cannot be defragmented.

If this table is controlled by a product vendor, you should get in touch with the vendor to see if you can add a clustered index on it. I can confirm that the moment a suitable clustered index is added on the table, your fragmentation will drop.

Another option is to create an identical table, and pump data from the old table to the new one in order (i.e. your SELECT statement should have an ORDER BY - INSERT INTO newTable SELECT columnList FROM oldTable ORDER BY requiredColumns) and then recreate the required triggers/indexes on the new one and then rename the new table after dropping the old one. But given that you cannot create a clustered index, you won't be able to create new tables.
0
 
LVL 1

Author Comment

by:crcsupport
ID: 41852715
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!
0
 
LVL 1

Author Comment

by:crcsupport
ID: 41852728
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.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how the fundamental information of how to create a table.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now