sys.dm_db_index_physical_stats函数分析索引-游标

Set nocount ondeclare CUR_Dx Cursor LocalForWithPT as (Select Object_id,index_id,Partition_count = count(*)From sys.partitionsGroup by Object_id,index_id),DIX as (selectDDIPS.object_id,DDIPS.index_id,DDIPS.partition_number,DDIPS.avg_fragmentation_in_percent,object_name = O.name,Schema_name = s.name,index_name = IX.name,Partition_count = PT.Partition_countFrom sys.dm_db_index_physical_stats(DB_ID(),null,null,null,'LimiTed') as DDIPSInner join sys.Objects as Oon DDIPS.object_id = O.Object_idInner join sys.schemas as Son S.Schema_id = O.Schema_idInner join sys.indexes as IXON DDIPS.Object_id = IX.Object_idand DDIPS.index_id = IX.Index_idInner join PTon DDIPS.Object_id = PT.Object_idand DDIPS.index_id = PT.index_idwhere DDIPS.avg_fragmentation_in_percent > 5and DDIPS.index_id > 0 ),DIXSQL AS (Select SQL = N'Alter Index '+ Quotename(index_name)+N' on '+ quotename(Schema_name) +N'.'+quotename(Object_name)+ casewhen avg_fragmentation_in_percent < 30 then N' REORGANIZE'Else N' REBUILD' END+ CASE WHEN Partition_count > 1 then N' Partition = '+ convert(nvarchar(20),Partition_number)Else N'' endFrom DIX)Select * from DIXSQL--Select * from DIXDeclare @sql nvarchar(max)open CuR_dxFetch CUR_Dx into @sqlwhile @@FETCH_STATUS = 0BeginExec sp_executesql @sqlRAISERROR (N' EXECUTED: %s',10,1,@sql) with NOWAITFETCH CUR_DX INTO @SQLEndCLOSE CUR_DXDEALLOCATE CUR_DXSET NOCOUNT OFF --EXECUTED: Alter Index [lsn_time_mapping_clustered_idx] on [cdc].[lsn_time_mapping] REBUILD --EXECUTED: Alter Index [lsn_time_mapping_nonunique_idx] on [cdc].[lsn_time_mapping] REBUILD     selectDDIPS.object_id,DDIPS.avg_fragmentation_in_percent,DDIPS.index_id,DDIPS.partition_number,DDIPS.avg_fragmentation_in_percent,object_name = O.name,Schema_name = s.name,index_name = IX.nameFrom sys.dm_db_index_physical_stats(DB_ID(),null,null,null,'LimiTed') as DDIPSInner join sys.Objects as Oon DDIPS.object_id = O.Object_idInner join sys.schemas as Son S.Schema_id = O.Schema_idInner join sys.indexes as IXON DDIPS.Object_id = IX.Object_idand DDIPS.index_id = IX.Index_idwhere DDIPS.avg_fragmentation_in_percent > 5and DDIPS.index_id > 0 SELECT *FROM sys.dm_db_index_physical_stats (DB_ID(),OBJECT_ID(N'dbo.EC_Transaction'), NULL , NULL, NULL);