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

本文深入探讨了SQL查询在数据库碎片管理中的应用,通过分析系统分区、物理统计信息,以及构建针对性的SQL脚本来重建或重组索引,以提高数据库性能和减少碎片。具体操作包括查询具有高碎片率的索引、生成相应的SQL脚本,并执行这些脚本以优化数据库结构。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

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);

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值