alter procedure zsp_IndexesReorganizeRebuildSQL @ReorganizePercent int = 10 , @RebuildPercent int = 15 , @Rows int = 10000 , @Online bit = 1 , @FillFactor int = 85 , @IndexType varchar(max) = '1,2' --聚集,非聚集 as --exec zsp_IndexesReorganizeRebuildSQL 0,0, 0, 1,0,0 begin --Microshaoft declare @Text varchar(max) declare @Table TABLE(id int,F int) set @Text = @IndexType set @Text = replace(@Text, ' ', '') declare @Separator char(1) = ',' DECLARE @SeparatorLen int SET @SeparatorLen = LEN(@Separator + '$') - 2 set @Text = replace(@Text, ' ', '') declare @i int set @i = 1 WHILE CHARINDEX(@Separator, @Text) > 0 BEGIN declare @v varchar(100) set @v = (LEFT(@Text, CHARINDEX(@Separator, @Text) - 1)) INSERT @Table (id, F) select @i , @v where rtrim(ltrim(@v)) != '' and not exists ( select 1 from @Table where F = @v ) if @@rowcount > 0 begin set @i = @i + 1 end SET @Text = STUFF(@Text ,1, CHARINDEX(@Separator, @Text) + @SeparatorLen, '') END INSERT @Table (id, F) select @i , @Text where rtrim(ltrim(@Text)) != '' and not exists ( select 1 from @Table where F = @Text ) SELECT t.name as TableName , ix.Name as IndexName , case when ix.type in (0) then 'Heap' when ix.type in (1) then 'Clustered' when ix.type in (2) then 'Non-Clustered' when ix.type in (3) then 'XML' when ix.type in (4) then 'Spatial' else 'Unknown' end as IndexType , avg_fragmentation_in_percent , RANK() OVER(ORDER BY avg_fragmentation_in_percent DESC) as Rank_avg_fragmentation_in_percent , ix.fill_factor , 'ALTER INDEX [' + ix.name + '] ON [' + s.name + '].[' + t.name + '] ' + CASE WHEN ps.avg_fragmentation_in_percent >= @RebuildPercent THEN 'REBUILD' ELSE 'REORGANIZE' END + CASE WHEN pc.partition_count > 1 THEN ' PARTITION = ' + CAST(ps.partition_number AS varchar) ELSE '' END + --Microshaoft ' WITH (ONLINE = ' + CASE WHEN @Online = 1 THEN ' on' ELSE ' off' END + CASE WHEN @FillFactor > 0 and @FillFactor < 100 THEN ', PAD_INDEX = on, FILLFACTOR = ' + cast(@FillFactor as varchar) ELSE ', PAD_INDEX = on' END + ')' as [SQL] FROM sys.indexes AS ix with(nolock) INNER JOIN sys.tables t with(nolock) ON t.object_id = ix.object_id INNER JOIN sys.schemas s ON t.schema_id = s.schema_id INNER JOIN ( SELECT object_id , index_id , avg_fragmentation_in_percent , partition_number FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) --with(nolock) ) ps ON t.object_id = ps.object_id AND ix.index_id = ps.index_id INNER JOIN ( SELECT object_id , index_id , COUNT(DISTINCT partition_number) AS partition_count FROM sys.partitions with(nolock) GROUP BY object_id , index_id ) pc ON t.object_id = pc.object_id AND ix.index_id = pc.index_id WHERE ix.Type in ( select F from @Table ) and ps.avg_fragmentation_in_percent >= @ReorganizePercent AND ix.name IS NOT NULL --引用 上面语句是网上的,加了行数判断,也可以用页数量判断 and exists ( select * from sys.partitions with(nolock) where object_id = t.object_id and [rows] >= @Rows /* 引用 控制一下记录行数,因为小表的rebuild等会根据很多参数标准来判断是否要去重建。 网上搜索的判断因子: 生成查询计划的阀值 缓存机制,缓存的筛选,LRU算法 预读机制 checkpoint减少回滚距离 智能join判断 重编译 */ ) --Microshaoft order by ix.Type , TableName , avg_fragmentation_in_percent desc end |