MSSQL 重新组织和重新生成索引的存储过程

通过它来重新组织或重新生成索引来修复索引碎片。

--==============================================================================
--	Description: 系统索引优化作业 --作业调用
--	Created  By: MYL
--	Create Date: 2019-10-28
--	Altered  By: --
--	Alter  Date: --
--	Checked  By: --
--	Check  Date: --
-- Return: 
--=====================================================================================
/*
--单表
EXEC PJ_System_IndexOptimalize @table_id=null,@table_name='M_XXXX',@_fragmentation=40,@_page_count=1000
--all
EXEC PJ_System_IndexOptimalize @table_id=null,@table_name=null,@_fragmentation=40,@_page_count=1000
*/
ALTER PROCEDURE [dbo].[PJ_System_IndexOptimalize]
    (
	@table_id varchar(256), --表对象ID
	@table_name varchar(256), --表名称
    @_fragmentation float =40, --逻辑碎片 > 5% 且 < = 30% 修复语句 ALTER INDEX REORGANIZE ,   > 30% ALTER INDEX REBUILD WITH (ONLINE = ON) 1
	@_page_count bigint =1000 --索引页
)
AS
    BEGIN
        DECLARE  @db_id varchar(256),@SqlText nvarchar(2560);

        SET @table_id = CASE @table_id WHEN NULL THEN NULL ELSE Object_Id(@table_name)END;

		SET @db_id = Db_Id()
        
        DECLARE @tb_index TABLE
              (iid int IDENTITY(1, 1),
               fragmentation float,
               page_count bigint,
               iRow bigint,
               tbl_name nvarchar(128),
               index_name nvarchar(256),
               SqlText nvarchar(2560));

        SET NOCOUNT ON; --不返回Transact-SQL 语句影响的行数，该设置减少了网络流量，因此可提高性能

        BEGIN TRY;

		--avg_fragmentation_in_percent 值	修复语句 > 5% 且 < = 30%	ALTER INDEX REORGANIZE
		--avg_fragmentation_in_percent 值> 30%	ALTER INDEX REBUILD WITH (ONLINE = ON) 1
			WITH cte_index
				AS (   SELECT Schema_Name(o.[schema_id]) AS [schema_name], o.name AS parent_name, o.[type] AS parent_type, i.name, i.type_desc, s.avg_fragmentation_in_percent AS fragmentation, s.page_count,
							  p.partition_number, p.[rows], IsNull(lob.is_lob_legacy, 0) AS is_lob_legacy, IsNull(lob.is_lob, 0) AS is_lob, CASE WHEN ds.[type] = 'PS' THEN 1 ELSE 0 END AS is_partitioned
						   FROM sys.dm_db_index_physical_stats(@db_id, @table_id, NULL, NULL, NULL) s
							   JOIN sys.partitions p WITH (NOLOCK)
								   ON s.[object_id] = p.[object_id] AND s.index_id = p.index_id AND s.partition_number = p.partition_number
							   JOIN sys.indexes i WITH (NOLOCK)
								   ON i.[object_id] = s.[object_id] AND i.index_id = s.index_id
							   LEFT JOIN (   SELECT c.[object_id], index_id = IsNull(i.index_id, 1), is_lob_legacy = Max(CASE WHEN c.system_type_id IN ( 34, 35, 99 ) THEN 1 END),
													is_lob = Max(CASE WHEN c.max_length = -1 THEN 1 END)
												 FROM sys.columns c WITH (NOLOCK)
													 LEFT JOIN sys.index_columns i WITH (NOLOCK)
														 ON c.[object_id] = i.[object_id] AND c.column_id = i.column_id AND i.index_id > 0
												WHERE c.system_type_id IN ( 34, 35, 99 ) OR c.max_length = -1
												GROUP BY c.[object_id], i.index_id) lob
								   ON lob.[object_id] = i.[object_id] AND lob.index_id = i.index_id
							   JOIN sys.objects o WITH (NOLOCK)
								   ON o.[object_id] = i.[object_id]
							   JOIN sys.data_spaces ds WITH (NOLOCK)
								   ON i.data_space_id = ds.data_space_id
						  WHERE i.[type] IN ( 1, 2 ) AND i.is_disabled = 0 AND i.is_hypothetical = 0 AND s.index_level = 0 AND s.alloc_unit_type_desc = 'IN_ROW_DATA' AND o.[type] IN ( 'U', 'V' ))
			INSERT @tb_index (fragmentation, page_count, iRow, tbl_name, index_name, SqlText)
			  SELECT cte_index.fragmentation, cte_index.page_count, cte_index.rows, cte_index.parent_name, cte_index.name,
					 SqlText = +Iif(cte_index.fragmentation > 30,
									'ALTER INDEX [' + cte_index.[name] + '] ON [' + [schema_name] + '].[' + cte_index.parent_name
									+ '] REBUILD PARTITION = ALL 
			WITH
			(SORT_IN_TEMPDB = ON, ONLINE = OFF, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)'                            + Char(13) + Char(10),
									'ALTER INDEX [' + cte_index.name + '] ON [' + [schema_name] + '].[' + cte_index.parent_name + '] REORGANIZE
			 WITH
			(LOB_COMPACTION = ON)')
				  FROM cte_index;

            DECLARE @i int = 1, @icount int,@cur_id int;

            SELECT @icount = Count(1)
                FROM @tb_index;

			PRINT '索引总数量：'+ Cast(@icount AS nvarchar)

			--清理页面数小的
            DELETE FROM @tb_index
               WHERE page_count < @_page_count;
			--清理碎片少的
            DELETE FROM @tb_index
               WHERE fragmentation < @_fragmentation;

            SELECT @cur_id = Count(1)
                FROM @tb_index;

			PRINT '待重建数量：'+ Cast(@cur_id AS nvarchar)

            WHILE @i <= @icount
                BEGIN
				    SET @cur_id=0	
                    SELECT @SqlText = SqlText,@cur_id=iid
                        FROM @tb_index
                       WHERE iid = @i;
					IF @cur_id > 0
						BEGIN
							PRINT @SqlText
							EXEC sp_executesql @SqlText;
						END
						
                    SET @i = @i + 1;
                END;
        END TRY
        BEGIN CATCH
            SET NOCOUNT OFF;
			PRINT @SqlText;
            THROW; --再抛一次错误
        END CATCH;

    END;