Question on SQL Server Indexing.


How to review the fragmentation for databases to determine if particular indexes must be rebuilt or reorganized on-line ?

If needed what are the steps to be followed ?

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

on 2005 and 2008:

CREATE FUNCTION sys_PhysicalIndexStatistics_Wrapper
      @DatabaseID INT,
      @ObjectID INT,
      @IndexID INT,
      @PartitionNumber INT,
      @Mode INT
      DatabaseID SMALLINT,
      ObjectID INT,
      IndexID INT,
      PartitionNumber INT,
      IndexDescription VARCHAR(100),
      AllocationTypeDescription VARCHAR(100),
      IndexDepth TINYINT,
      IndexLevel TINYINT,
      AverageFragmentation FLOAT,
      FragmentCount BIGINT,
      AverageFragmentSize FLOAT,
      TablePageCount BIGINT,
      AveragePageSpaceUsed FLOAT,
      RecordCount BIGINT,
      GhostRecordCount BIGINT,
      VersionGhostRecordCount BIGINT,
      MinimumRecordSize INT,
      MaxRecordSize INT,
      AverageRecordSize FLOAT,
      ForwardedRecordCount BIGINT

    INSERT INTO @IndexStats
            DatabaseID, ObjectID, IndexID, PartitionNumber, IndexDescription, AllocationTypeDescription, IndexDepth,
            IndexLevel, AverageFragmentation, FragmentCount, AverageFragmentSize, TablePageCount,
            AveragePageSpaceUsed, RecordCount, GhostRecordCount, VersionGhostRecordCount, MinimumRecordSize,
            MaxRecordSize, AverageRecordSize, ForwardedRecordCount
            database_id, object_id, index_id, partition_number, index_type_desc, alloc_unit_type_desc, index_depth,
            index_level, avg_fragmentation_in_percent, fragment_count, avg_fragment_size_in_pages, page_count,
            avg_page_space_used_in_percent, record_count, ghost_record_count, version_ghost_record_count, min_record_size_in_bytes,
            max_record_size_in_bytes, avg_record_size_in_bytes, forwarded_record_count

FROM sys.tables t
CROSS APPLY sys_PhysicalIndexStatistics_Wrapper(DB_ID(), object_id, NULL, NULL, NULL) s
WHERE AverageFragmentation > 30

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
parpaaAuthor Commented:
Hi chapmandew,

Could you also give me some sample output here ?
Also is it possible to find the fragmentation without creating any functions/procedures as you specified above ?

Its possible to do it, but you can't easily do it for all tables at one time.  You'd need to loop through them (which I won't provide here).  

here is a sample record:

database_id      object_id      index_id      partition_number      index_type_desc      alloc_unit_type_desc      index_depth      index_level      avg_fragmentation_in_percent      fragment_count      avg_fragment_size_in_pages      page_count      avg_page_space_used_in_percent      record_count      ghost_record_count      version_ghost_record_count      min_record_size_in_bytes      max_record_size_in_bytes      avg_record_size_in_bytes      forwarded_record_count
6      1237579447      1      1      CLUSTERED INDEX      IN_ROW_DATA      2      0      50      2      1      2      NULL      NULL      NULL      NULL      NULL      NULL      NULL      NULL
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

parpaaAuthor Commented:
Thanks Chapmandew for the claear info,

Could you plz let me know when we have to perform re-index or re-build with the help of this information and what fileds we mostly need to consider. I'm new to this process could you let me know in detail on this.

You really only need to consider the avg_fragmentation_in_percent field...anything between 30-40%, probably reorg.  Anything higher, rebuild the index.
parpaaAuthor Commented:
Hi Chapman,

If i have following results, according to what you siad i need to perform rebuild the index rather than reorg. Am i right?

DB_ID      Object_ID               avg_fragmentation_in_%        fragmant_count     avg_fragment_size_in_Pages       page_count
13      10483116      98.8023952095808                        168              6.01785714285714            1011
13      26483173      94.1176470588235                    34               5.08823529411765             173
13      26483173      54.7169811320755                         30               1.76666666666667             53
13      26483173      55.5555555555556                         31               1.74193548387097             54
13      26483173      46.1538461538462                         25               2.08                                      52
13      42483230      89.6551724137931                    29               2.93103448275862             85
13      42483230      33.3333333333333                     7                2.57142857142857             18

From the above results could you provide the detail info of each field and especially page_count.
From my understanding I definitely need to rebuild the index on these indexes.
Also here is the object_id refers to each index?

parpaaAuthor Commented:
Hi Chapman,

Is there anyway i can get IndexName rather than index_id coulumn in the result set.
Could you assist me with this?

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.