Alter index rebuild reorganize index reconstruction reorganization fragmentation Rate

Alter procedure zsp_indexesreorganizerebuildsql @ reorganizepercent Int = 10, @ rebuildpercent Int = 15, @ rows Int = 10000, @ online bit = 1, @ fillfactor Int = 85, @ indextype varchar (max) = '1, 2' -- clustering, non-clustering as -- exec zsp_indexesreorganizerebuildsql, 0, 0 begin -- microshaoft declare @ text varchar (max) Declare @ Table (ID int, f INT) set @ text = @ indextype set @ text = Replace (@ text ,'','') Declare @ separator char (1) = ', 'descare @ 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-tertered' 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 @ onlin E = 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) -- 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_ I D = pc. index_id where IX. type in (select F from @ table) and PS. avg_fragmentation_in_percent> = @ reorganizepercent and IX. name is not null -- the preceding statement is referenced online, and the row number is added. You can also use the number of pages to determine and exists (select * From sys. partitions with (nolock) Where object_id = T. object_id and [rows] >=@ rows/* Reference to control the number of record rows, because the rebuild of small tables determines whether to re-build based on many parameter standards. Online Search judgment factor: the threshold cache mechanism for generating query plans, cache filtering, and LRUAlgorithm Pre-read mechanism checkpoint reduces rollback distance intelligent join judgment re-compilation */) -- microshaoft order by IX. type, tablename, avg_fragmentation_in_percent descend