• In addition to what has already been posted: include the partition_number column, output from the sys.dm_db_index_physical_stats DMV, in the list of columns you consider in your analysis.

    If your database is partitioned, and you have fragmentation on partitions with partition_number > 1, then you need to be able to detect this, and rebuild or reorganize your index with that in mind.

    The generic "ALTER INDEX ... REBUILD/REORGANIZE" commands do not touch non-default partitions; they only affect those partitions with partition_number = 1 (this includes all indexes that are not partitioned).

    Use "ALTER INDEX ... REBUILD PARTITION = ..." and/or the corresponding reorg command to deal with non-default partitions, if they exist.

    I have been bitten recently with this: non-default partitions were not being defragmented by my script, resulting in poor performance.

    Who knew?!

    __________________________________________________________________________________
    SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]