金蝶数据库优化

1. 优化碎片,在对应的业务数据库执行下列的语句：

PAGE_COUNT --处理的表大小，一般推荐业务期间小于1W
AVG_FRAGMENTATION_IN_PERCENT -- 碎片大小，默认大于2% ~ 10% 就可以了

declare @sql varchar(max)
set @sql=''
select @sql=@sql+'dbcc dbreindex('+name+');' from (
select distinct OBJECT_NAME(OBJECT_ID) NAME,PAGE_COUNT,AVG_FRAGMENTATION_IN_PERCENT FROM SYS.DM_DB_INDEX_PHYSICAL_STATS (DB_ID(),NULL,NULL,NULL,NULL)
where OBJECT_NAME(OBJECT_ID) NOT LIKE 'TMP%' AND OBJECT_NAME(OBJECT_ID)  NOT LIKE 'Z[_]%' AND ALLOC_UNIT_TYPE_DESC ='IN_ROW_DATA' AND PAGE_COUNT > 20 and PAGE_COUNT < 2000 AND INDEX_ID = 1 AND AVG_FRAGMENTATION_IN_PERCENT > 2
) a 
exec(@sql)

2. 查看索引碎片情况，索引指标大于2%的表的数量

select distinct OBJECT_NAME(OBJECT_ID) NAME,PAGE_COUNT,AVG_FRAGMENTATION_IN_PERCENT FROM SYS.DM_DB_INDEX_PHYSICAL_STATS (DB_ID(),NULL,NULL,NULL,NULL)
where OBJECT_NAME(OBJECT_ID) NOT LIKE 'TMP%' AND OBJECT_NAME(OBJECT_ID)  NOT LIKE 'Z[_]%' AND ALLOC_UNIT_TYPE_DESC ='IN_ROW_DATA' AND PAGE_COUNT > 20 AND INDEX_ID = 1 AND AVG_FRAGMENTATION_IN_PERCENT > 2
order by AVG_FRAGMENTATION_IN_PERCENT desc

3. 针对某一个表优化, 影响业务最小, 可以根据2查到的数据选择某一个表

查看某一个表详情

dbcc ShowContig('T_SAL_ORDERENTRY_R')

重建某一个表索引

alter index all on T_SAL_ORDERENTRY_R rebuild  with(ONLINE =off, maxdop=4,sort_in_tempdb=on)

4. 快速查看某一个表占用磁盘空间情况