User Rating: 0 / 5

Star InactiveStar InactiveStar InactiveStar InactiveStar Inactive
 

/**************************************************************************
@AvgFragPercentThreshold - Specify to only return partitions with
        the have a logical fragementation greater then the threshold.
        Defaulting to 0 to return all partitions.
@Mode - Mode specifies the scan level that is used to obtain statistics.
        Valid inputs are DEFAULT, NULL, LIMITED, SAMPLED, or DETAILED.
        The default (NULL) is LIMITED. 
        'DETAILED' may be resource intensive
**************************************************************************/
BEGIN
   SET NOCOUNT ON;
   SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
   --
   DECLARE @AvgFragPercentThreshold INT = 0;
   DECLARE @Mode                    SYSNAME = 'DETAILED';
   --
   SELECT OBJECT_NAME(d.object_id)                                             AS [TableName],
          si.name                                                              AS [IndexName],           
          SCHEMA_NAME(CAST(OBJECTPROPERTYEX(d.object_id, 'SchemaId')AS INT))   AS [Schema],              
          d.index_id                                                           AS [IndexId],             
          d.partition_number                                                   AS [Partition],           
          d.index_level                                                        AS [IndexLevel],          
          d.index_depth                                                        AS [IndexDepth],          
          d.avg_fragment_size_in_pages                                         AS [AvgFragSizePages],    
          d.avg_fragmentation_in_percent                                       AS [AvgFragPct],          
          d.avg_page_space_used_in_percent                                     AS [AvgPageSpaceUsedPct], 
          d.page_count                                                         AS [PageCount],           
          d.record_count                                                       AS [RecordCount],         
          d.avg_record_size_in_bytes                                           AS [AvgRecSizeBytes],     
          d.max_record_size_in_bytes                                           AS [MaxRecSizeBytes]      
   FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, -1, NULL , @Mode) d   
   INNER JOIN sys.indexes si ON si.object_id = d.object_id                                   
                            AND si.index_id  = d.index_id                                                                              
   WHERE d.index_id             != 0                                                                     
     AND d.alloc_unit_type_desc  = 'IN_ROW_DATA'                                                       
     AND d.page_count           != 0    
     AND d.avg_fragmentation_in_percent > @AvgFragPercentThreshold;
END;  

Add comment


Security code
Refresh

0
0
0
s2sdefault