Solved

Index fragmentation doesn't go lower after rebuilding index.

Posted on 2016-10-19
8
108 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Show RTF format in an SSRS report 3 39
MSSQL join different row from other table 14 66
access to sql migration 5 23
UPDATE JOIN multiple tables 5 19
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.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

740 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