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

Set nocount on
declare CUR_Dx Cursor Local
For

With
PT as 
(
	Select Object_id,index_id,Partition_count = count(*)
	From sys.partitions
	Group by Object_id,index_id
),
DIX as 
(
	select
		DDIPS.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_count
	From sys.dm_db_index_physical_stats(DB_ID(),null,null,null,'LimiTed') as DDIPS
	Inner join sys.Objects as O
		on DDIPS.object_id = O.Object_id
	Inner join sys.schemas as S
		on S.Schema_id = O.Schema_id
	Inner join sys.indexes as IX
		ON DDIPS.Object_id = IX.Object_id
			and DDIPS.index_id = IX.Index_id
	Inner join PT
		on DDIPS.Object_id = PT.Object_id
			and DDIPS.index_id = PT.index_id
	where DDIPS.avg_fragmentation_in_percent > 5
		and DDIPS.index_id > 0 
),
DIXSQL AS 
(
	Select SQL = N'Alter Index '+ Quotename(index_name)
			+N' on '+ quotename(Schema_name) +N'.'+quotename(Object_name)
			+ case
				when 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'' end
	From DIX
)
Select * from DIXSQL
--Select * from DIX

Declare @sql nvarchar(max)
open CuR_dx
Fetch CUR_Dx into @sql
while @@FETCH_STATUS = 0
Begin
	Exec sp_executesql @sql
	RAISERROR (N' EXECUTED: %s',10,1,@sql) with NOWAIT
	FETCH CUR_DX INTO @SQL
End
CLOSE CUR_DX
DEALLOCATE CUR_DX
SET 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
 
 
 
 
 select
		DDIPS.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.name
	From sys.dm_db_index_physical_stats(DB_ID(),null,null,null,'LimiTed') as DDIPS
	Inner join sys.Objects as O
		on DDIPS.object_id = O.Object_id
	Inner join sys.schemas as S
		on S.Schema_id = O.Schema_id
	Inner join sys.indexes as IX
		ON DDIPS.Object_id = IX.Object_id
			and DDIPS.index_id = IX.Index_id
	where DDIPS.avg_fragmentation_in_percent > 5
		and DDIPS.index_id > 0 
		
		


SELECT *
FROM sys.dm_db_index_physical_stats (DB_ID(),OBJECT_ID(N'dbo.EC_Transaction'), NULL , NULL, NULL);