Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I've inherited of a couple of databases which indexes have never been maintained (rebuilt, reorganized).

I'm using Ola Hallengren's Index maintenance script, I've used the following script to record the difference between the pre-maintenance and post-maintenance values.

Script :

SELECT  OBJECT_NAME(IPS.object_id) AS [TableName] , AS [IndexName] ,
    IPS.Index_type_desc ,
    IPS.avg_fragmentation_in_percent ,
    IPS.avg_fragment_size_in_pages ,
    IPS.avg_page_space_used_in_percent ,
    IPS.record_count ,
    IPS.ghost_record_count ,
   -- ,IPS.avg_fragment_size_in_pages
    sys.dm_db_index_physical_stats(DB_ID(N'test_db'), NULL, NULL, NULL, 'DETAILED') IPS
    sys.tables ST WITH (NOLOCK) ON IPS.object_id = ST.object_id
    sys.indexes SI WITH (NOLOCK) ON IPS.object_id = SI.object_id
                                 AND IPS.index_id = SI.index_id
    ST.is_ms_shipped = 0
    3 Desc, 4 Desc

However, I didn't notice any changes on the various indexes having an avg_fragmentation_in_percent above 30%.

Are index maintenance tasks supported on SQL Server 2008R2 Web Editions?

share|improve this question

marked as duplicate by swasheck, billinkc, Max Vernon, RolandoMySQLDBA, Jon Seigel Oct 19 '13 at 1:06

This question was marked as an exact duplicate of an existing question.

I believe you're bumping up against this. – swasheck Oct 18 '13 at 18:17
Thanks it really help understanding the issue, another ressource - – Raymond Oct 18 '13 at 19:58

For small tables (typically reduced pages count), fragmentation cannot be avoided due to how sql server handles extents.

More info :

share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.