Solved

Index fragmentation doesn't go lower after rebuilding index.

Posted on 2016-10-19
8
115 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
8 Comments
 
LVL 78

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 13

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
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 
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
 
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 13

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

738 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