Take the 2-minute tour ×
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. It's 100% free, no registration required.

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] ,
    SI.name 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.fragment_count 
   -- ,IPS.avg_fragment_size_in_pages
FROM    
    sys.dm_db_index_physical_stats(DB_ID(N'test_db'), NULL, NULL, NULL, 'DETAILED') IPS
JOIN 
    sys.tables ST WITH (NOLOCK) ON IPS.object_id = ST.object_id
JOIN 
    sys.indexes SI WITH (NOLOCK) ON IPS.object_id = SI.object_id
                                 AND IPS.index_id = SI.index_id
WHERE   
    ST.is_ms_shipped = 0
ORDER BY 
    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 has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

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

1 Answer 1

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

More info : http://www.practicalsqldba.com/2012/04/sql-server-measuring-index.html

share|improve this answer

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