您现在的位置是：首页> ERP实施维护ERP实施维护
数据库碎片查询及优化
2026-01-31
ERP实施维护浏览量37
简介1：全库查询方法SELECT*FROM(SELECTSO.NAMEASobject_name,SI.NAMEASindex_name,IPS.avg_fragmentation_in_percent,(page_count*8.0/1024.0)ASsize_in_mB,si.type_descFROMsys.Dm_db_index_physical_stats(Db_id(),NULL,NULL
1：全库查询方法
SELECT * FROM (SELECT SO.NAME AS object_name, SI.NAME AS index_name, IPS.avg_fragmentation_in_percent, ( page_count * 8.0 / 1024.0 ) AS size_in_mB, si.type_desc FROM sys.Dm_db_index_physical_stats(Db_id(), NULL, NULL, NULL, NULL) IPS INNER JOIN sys.indexes SI ON SI.index_id = IPS.index_id INNER JOIN sys.objects SO ON SO.object_id = SI.object_id AND IPS.object_id = SO.object_id WHERE alloc_unit_type_desc = 'IN_ROW_DATA' AND index_level = 0 AND SI.NAME IS NOT NULL AND SO.is_ms_shipped = 0) t ORDER BY t.avg_fragmentation_in_percent DESC
2：单个表查询方法
SELECT SO.NAME AS object_name, SI.NAME AS index_name, IPS.avg_fragmentation_in_percent, ( page_count * 8.0 / 1024.0 ) AS size_in_mB, si.type_desc FROM sys.Dm_db_index_physical_stats(Db_id(), NULL, NULL, NULL, NULL) IPS INNER JOIN sys.indexes SI ON SI.index_id = IPS.index_id INNER JOIN sys.objects SO ON SO.object_id = SI.object_id AND IPS.object_id = SO.object_id WHERE alloc_unit_type_desc = 'IN_ROW_DATA' AND index_level = 0 AND SI.NAME IS NOT NULL AND SO.is_ms_shipped = 0 AND so.name = 'T_META_OBJECTTYPE_L'--替换实际表名
2963ebbfd4fd0e7f47c77b09402c9e0f.docx
3：业务无使用的时候运行重建索引脚本
IF Object_id('tempdb..#temp_table_info') <> 0
DROP TABLE #temp_table_info
IF Object_id('tempdb..#indexinfo') <> 0
DROP TABLE #indexinfo
-- 创建临时表
CREATE TABLE #temp_table_info
(
TABLE_NAME SYSNAME
);
CREATE TABLE #indexinfo
(
id INT IDENTITY(1, 1),
object_name SYSNAME,
fragmentpercent FLOAT,
execsql SYSNAME,
objsize_mb FLOAT
);
go
SET nocount ON
DECLARE @record_log_flag BIT = 1 --记录索引重建优化的详细日志到表中，0表示false,1表示true
DECLARE @log_retention_time INT = 7 --日志保留时间，单位是天
DECLARE @i INT
DECLARE @icount INT
DECLARE @sql AS VARCHAR(max)
DECLARE @tbsize AS DECIMAL(19, 3)
DECLARE @driversize AS INT
DECLARE @logunusedsize INT=0
DECLARE @message VARCHAR(300)
DECLARE @tbname SYSNAME
DECLARE @begintime DATETIME
DECLARE @tmpbegintime DATETIME
DECLARE @tmpendtime DATETIME
DECLARE @tmpDurationInSeconds INT
DECLARE @worktime INT = 360
DECLARE @work_end_time DATETIME --任务截至时间
DECLARE @worktimeflag BIT
DECLARE @exectabs VARCHAR(max) = ''
DECLARE @noexectabs VARCHAR(max) = ''
DECLARE @checklogsize BIT=0
DECLARE @currenttablename VARCHAR(max)
DECLARE @delimiter CHAR(1) = ','
DECLARE @startindex INT = 1
DECLARE @fragmentpercent FLOAT
----------可人工调整的部分开始----------
--SET @worktime = 240 --允许脚本执行的工作时间，单位为分钟，默认为6个小时（0表示不限制工作时间，直至所有符合条件的索引重建完成）
--SET @noexectabs='T_BAS_OPERATELOGBK,T_BAS_OPERATELOGBK_S'--人工指定对某些表不进行重建索引，格式为't3,t4',默认为''(即不指定,对全库碎片率超过5的索引进行重建优化)
----------可人工调整的部分结束----------
DECLARE @noexectabslen INT = Len(@noexectabs);
DECLARE @exectabslen INT = Len(@exectabs);
IF @record_log_flag = 1
BEGIN
IF NOT EXISTS (SELECT *
FROM sys.objects
WHERE object_id = Object_id(N'[dbo].[KDIndexMaintenanceLog]')
AND type IN ( N'U' ))
--创建日志表
BEGIN
CREATE TABLE [dbo].KDIndexMaintenanceLog
(
ID INT IDENTITY(1, 1) PRIMARY KEY,-- 自增ID作为主键
[TableName] SYSNAME NOT NULL,
objsize_mb FLOAT NOT NULL,
[AvgFragmentationInPercent] [FLOAT] NOT NULL,
execsql [NVARCHAR](200),
[RebuildStartTime] [DATETIME] NULL,
[RebuildEndTime] [DATETIME] NULL,
[DurationInSeconds] [INT] NULL
);
--创建索引
CREATE NONCLUSTERED INDEX IX_KDIndexMaintenanceLog
ON KDIndexMaintenanceLog (TableName, RebuildStartTime);
END
--清理过期日志
DELETE FROM [dbo].KDIndexMaintenanceLog
WHERE [RebuildStartTime] < Dateadd(DAY, -@log_retention_time, Cast (Getdate() AS DATE))
END
IF @worktime > 0
BEGIN
SET @work_end_time= Dateadd(MINUTE, @worktime, Getdate())
SET @worktimeflag=1
END
ELSE
SET @worktimeflag=0
--判断sql server版本是否高于2008 r2
IF ( Cast(Serverproperty('ProductMajorVersion') AS INT) > 10
OR ( Cast(Serverproperty('ProductMajorVersion') AS INT) = 10
AND Cast(Serverproperty('ProductMinorVersion') AS INT) >= 50 ) )
SET @checklogsize=1
IF ( @worktimeflag = 1
AND @work_end_time <= Getdate() )
BEGIN
SET @message=N'停止执行。当前系统时间为：'
+ CONVERT(VARCHAR(30), Getdate(), 121)
+ N'超过了任务截至时间:' + @work_end_time
RAISERROR(@message,0,1);
RETURN
END;
SET @message=N'开始获取需要优化的表'
+ CONVERT(VARCHAR(30), Getdate(), 121)
RAISERROR(@message,0,1);
IF Len(Ltrim(Rtrim(@exectabs))) > 0
BEGIN
SET @startindex=1
WHILE @startindex <= @exectabslen
BEGIN
SET @currenttablename = Ltrim(Rtrim(Substring(@exectabs, @startindex, CASE
WHEN Charindex(@delimiter, @exectabs, @startindex) = 0 THEN 8000
ELSE Charindex(@delimiter, @exectabs, @startindex) - @startindex
END)));
INSERT INTO #temp_table_info
(TABLE_NAME)
VALUES (@currenttablename);
SET @startindex = CASE
WHEN Charindex(@delimiter, @exectabs, @startindex) = 0 THEN @exectabslen + 1
ELSE Charindex(@delimiter, @exectabs, @startindex)
+ 1
END;
END;
WITH t
AS (SELECT SO.NAME AS object_name,
SI.NAME AS index_name,
IPS.avg_fragmentation_in_percent,
( page_count * 8.0 / 1024.0 ) AS size_in_mB,
si.type_desc
FROM sys.Dm_db_index_physical_stats(Db_id(), NULL, NULL, NULL, NULL) IPS
INNER JOIN sys.indexes SI
ON SI.index_id = IPS.index_id
INNER JOIN sys.objects SO
ON SO.object_id = SI.object_id
AND IPS.object_id = SO.object_id
WHERE alloc_unit_type_desc = 'IN_ROW_DATA'
AND index_level = 0
AND SI.NAME IS NOT NULL
AND SO.is_ms_shipped = 0
AND so.NAME NOT LIKE 'tmp%'
AND so.NAME NOT LIKE 'z%'
AND so.NAME NOT LIKE 'gle%'
AND so.NAME IN(SELECT TABLE_NAME
FROM #temp_table_info)),
m
AS (SELECT object_name,
Max(avg_fragmentation_in_percent) fragmentpercent,
CASE
WHEN Max(avg_fragmentation_in_percent) >= 15 THEN 'dbcc dbreindex(' + object_name
+ ') with no_infomsgs'
WHEN Max(avg_fragmentation_in_percent) >= 5 THEN 'dbcc indexdefrag(0,' + object_name
+ ') with no_infomsgs'
ELSE ''
END execsql,
Sum(size_in_mB) objsize_mb
FROM t
WHERE avg_fragmentation_in_percent > 0
GROUP BY object_name)
INSERT INTO #indexinfo
SELECT m.*
FROM m
WHERE m.execsql <> ''
ORDER BY 2 DESC
END
ELSE IF Len(Ltrim(Rtrim(@noexectabs))) > 0
BEGIN
SET @startindex=1
WHILE @startindex <= @noexectabslen
BEGIN
SET @currenttablename = Ltrim(Rtrim(Substring(@noexectabs, @startindex, CASE
WHEN Charindex(@delimiter, @noexectabs, @startindex) = 0 THEN 8000
ELSE Charindex(@delimiter, @noexectabs, @startindex) - @startindex
END)));
INSERT INTO #temp_table_info
(TABLE_NAME)
VALUES (@currenttablename);
SET @startindex = CASE
WHEN Charindex(@delimiter, @noexectabs, @startindex) = 0 THEN @noexectabslen + 1
ELSE Charindex(@delimiter, @noexectabs, @startindex)
+ 1
END;
END;
WITH t
AS (SELECT SO.NAME AS object_name,
SI.NAME AS index_name,
IPS.avg_fragmentation_in_percent,
( page_count * 8.0 / 1024.0 ) AS size_in_mB,
si.type_desc
FROM sys.Dm_db_index_physical_stats(Db_id(), NULL, NULL, NULL, NULL) IPS
INNER JOIN sys.indexes SI
ON SI.index_id = IPS.index_id
INNER JOIN sys.objects SO
ON SO.object_id = SI.object_id
AND IPS.object_id = SO.object_id
WHERE alloc_unit_type_desc = 'IN_ROW_DATA'
AND index_level = 0
AND SI.NAME IS NOT NULL
AND SO.is_ms_shipped = 0
AND so.NAME NOT LIKE 'tmp%'
AND so.NAME NOT LIKE 'z%'
AND so.NAME NOT LIKE 'gle%'
AND so.NAME NOT IN(SELECT TABLE_NAME
FROM #temp_table_info)),
m
AS (SELECT object_name,
Max(avg_fragmentation_in_percent) fragmentpercent,
CASE
WHEN Max(avg_fragmentation_in_percent) >= 15 THEN 'dbcc dbreindex(' + object_name
+ ') with no_infomsgs'
WHEN Max(avg_fragmentation_in_percent) >= 5 THEN 'dbcc indexdefrag(0,' + object_name
+ ') with no_infomsgs'
ELSE ''
END execsql,
Sum(size_in_mB) objsize_mb
FROM t
WHERE avg_fragmentation_in_percent > 0
GROUP BY object_name)
INSERT INTO #indexinfo
SELECT m.*
FROM m
WHERE m.execsql <> ''
ORDER BY 2 DESC
END
ELSE
BEGIN
WITH t
AS (SELECT SO.NAME AS object_name,
SI.NAME AS index_name,
IPS.avg_fragmentation_in_percent,
( page_count * 8.0 / 1024.0 ) AS size_in_mB,
si.type_desc
FROM sys.Dm_db_index_physical_stats(Db_id(), NULL, NULL, NULL, NULL) IPS
INNER JOIN sys.indexes SI
ON SI.index_id = IPS.index_id
INNER JOIN sys.objects SO
ON SO.object_id = SI.object_id
AND IPS.object_id = SO.object_id
WHERE alloc_unit_type_desc = 'IN_ROW_DATA'
AND index_level = 0
AND SI.NAME IS NOT NULL
AND SO.is_ms_shipped = 0
AND so.NAME NOT LIKE 'tmp%'
AND so.NAME NOT LIKE 'z%'
AND so.NAME NOT LIKE 'gle%'),
m
AS (SELECT object_name,
Max(avg_fragmentation_in_percent) fragmentpercent,
CASE
WHEN Max(avg_fragmentation_in_percent) >= 15 THEN 'dbcc dbreindex(' + object_name
+ ') with no_infomsgs'
WHEN Max(avg_fragmentation_in_percent) >= 5 THEN 'dbcc indexdefrag(0,' + object_name
+ ') with no_infomsgs'
ELSE ''
END execsql,
Sum(size_in_mB) objsize_mb
FROM t
WHERE avg_fragmentation_in_percent > 0
GROUP BY object_name)
INSERT INTO #indexinfo
SELECT m.*
FROM m
WHERE m.execsql <> ''
ORDER BY 2 DESC
END
SET @icount=(SELECT Count(1)
FROM #indexinfo);
SET @message=N'结束获取需要优化的表'
+ CONVERT(VARCHAR(30), Getdate(), 121)
RAISERROR(@message,0,1);
IF @icount = 0
BEGIN
SET @message=N'数据库：' + Db_name() + N'不需要优化'
RAISERROR(@message,0,1);
RETURN
END;
SET @begintime=Getdate()
SET @message=N'数据库：' + Db_name() + N'，开始时间： '
+ CONVERT(VARCHAR(30), @begintime, 121)
+ N' 总共需要执行:' + Cast(@icount AS VARCHAR)
+ N' 条语句'
RAISERROR(@message,0,1);
SET @i=1
WHILE @i <= @icount
BEGIN
SELECT @sql = execsql,
@tbsize = objsize_mb,
@tbname = object_name,
@fragmentpercent = fragmentpercent
FROM #indexinfo
WHERE id = @i
IF( @checklogsize = 1 )
BEGIN
SELECT @driversize = Cast(Cast(available_bytes AS DECIMAL) / ( 1024 * 1024 ) AS BIGINT)
FROM sys.master_files AS f
CROSS APPLY sys.Dm_os_volume_stats(f.database_id, f.file_id)
WHERE f.database_id = Db_id()
AND f.type_desc = 'LOG';
SELECT @logunusedsize = ( total_log_size_in_bytes - used_log_space_in_bytes ) / 1024 / 1024
FROM sys.dm_db_log_space_usage
IF @i = 1
BEGIN
SET @message=N'日志可用空间为(MB)：'
+ Cast(@logunusedsize AS VARCHAR)
+ N';磁盘可用空间为(MB)：'
+ Cast(@driversize AS VARCHAR)
RAISERROR(@message,0,1);
END;
END
IF ( @worktimeflag = 1
AND @work_end_time <= Getdate() )
BEGIN
SET @message=N'停止执行。因为当前系统时间为：'
+ CONVERT(VARCHAR(30), Getdate(), 121)
+ N'超过了开始工作时间范围'
+ CONVERT(VARCHAR(30), @work_end_time, 121)
+ N'不允许执行优化计划'
RAISERROR(@message,0,1);
RETURN
END
IF ( @tbsize > ( @driversize + @logunusedsize )
AND @checklogsize = 1 )
BEGIN
SET @message=N'磁盘可用空间和日志可用空间不足，执行终止。原因：表:' + @tbname
+ N'总需空间大小(MB)：' + Cast(@tbsize AS VARCHAR)
+ N';日志可用空间为(MB)：'
+ Cast(@logunusedsize AS VARCHAR)
+ N';磁盘可用空间为(MB)：'
+ Cast(@driversize AS VARCHAR)
RAISERROR(@message,16,1);
RETURN
END
ELSE
BEGIN
SET @message= Cast(@i AS VARCHAR) + '/'
+ Cast(@icount AS VARCHAR) + N'('
+ Cast(@tbsize AS VARCHAR) + 'MB)：' + @tbname
SET @tmpbegintime=Getdate()
IF @record_log_flag = 1
BEGIN
--记录日志
INSERT INTO KDIndexMaintenanceLog
(TableName,
objsize_mb,
AvgFragmentationInPercent,
execsql,
RebuildStartTime)
VALUES (@tbname,
@tbsize,
@fragmentpercent,
@sql,
@tmpbegintime)
END
EXEC(@sql)
SET @tmpendtime=Getdate()
SET @tmpDurationInSeconds = Datediff(second, @tmpbegintime, @tmpendtime)
IF @record_log_flag = 1
BEGIN
--更新日志表中索引优化完成时间
UPDATE KDIndexMaintenanceLog
SET RebuildEndTime = @tmpendtime,
DurationInSeconds = @tmpDurationInSeconds
WHERE TableName = @tbname
AND RebuildStartTime = @tmpbegintime;
END
SET @message=@message + N':'
+ Cast (@tmpDurationInSeconds AS NVARCHAR)
+ N'秒'
RAISERROR(@message,0,1);
END
SET @i=@i + 1
END
SET @message=N'数据库：' + Db_name() + N'，结束时间： '
+ CONVERT(VARCHAR(30), Getdate(), 121)
+ N' 总耗时(秒)：'
+ Cast(Datediff(ss, @begintime, Getdate()) AS VARCHAR)
RAISERROR(@message,0,1);4：如果以上执行未完成，下次可以继续进行上次未完成的作业
如果部分未执行到可以单独手动执行，确保无业务使用的时候执行
dbcc dbreindex('T_CRM_CUSTOMER')--老版本SQL单表重建索引
-- 重建表的所有索引（离线，会锁表，适合维护窗口）
ALTER INDEX ALL ON T_CRM_CUSTOMER REBUILD;
GO
-- 在线重建索引（需企业版，不锁表，适合生产环境）
ALTER INDEX ALL ON T_CRM_CUSTOMER REBUILD WITH (ONLINE = ON, FILLFACTOR = 90);
GO
-- 仅重建指定索引（例如非聚集索引 IX_T_CRM_CUSTOMER_PHONE）
ALTER INDEX IX_T_CRM_CUSTOMER_PHONE ON T_CRM_CUSTOMER REBUILD;
GO5：查询单表优化后的情况
-- 检查 T_CRM_CUSTOMER 表的所有索引碎片情况
DBCC SHOWCONTIG('T_CRM_CUSTOMER')
GO
-- 详细模式（输出更全的统计信息，推荐）
DBCC SHOWCONTIG('T_CRM_CUSTOMER') WITH ALL_INDEXES, TABLERESULTS
GO上一篇：数据库碎片查询及优化
下一篇：下一篇：已经没有了
随机文章
随机图文
金蝶ERP云星空BOS开发平台平行扩展问题
由于CRM系统升级，造成生产订单自定义字段无法批改。因为CRM升级后对生产订单进行了平行扩展。扩展了2次。现在教你如何删除不需要的BOS平行扩展。因为金蝶BOS开发平台控制了开发商唯一码校验。因此我们需...
金蝶云星空二开单据物料基本单位携带
金蝶云星空二开单据物料的基本单位携带。物料需要引用基本单位属性增加计量单位字段，值更新打钩物料值更新事件配置单位对应关系
金蝶云星空BOS开发计算多个数字相加
BOS二开单据根据每月销售数量计算本年的销售数量合计。在每月销售数量字段上做如下配置。每个月都用一用的公式。保证每个月数值反生改变时，都可以自动更新合销售数量合计。注意销售数量合计字段 即时触发更新事件...
金蝶ERP云星空物料编码加客户名称唯一性检查
金蝶云星空BOS开发单据，多字段唯一性检查。禁止相同客户与相同物料编码输入多次。点保存按钮后最终检查如下
金蝶云星空二开单据显示序号
序号字段标识必须是FSeq
金蝶云星空如何限制组织只能选择库存组织
金蝶云星空如何限制组织只能选择库存组织。打开编辑字段，选择组织右边的组织职能。选择库存职能
金蝶云星空获取当前年份
创建一个整数字段或者文本字段，预测年份。设置预测年份值更新事件。截取创建日期的年份。并且在表单属性的菜单集合，选择保存按钮，调用值更新服务。
金蝶BOS开发平台删除字段后，数据库还有这个字段
很多小伙伴会发现，在金蝶BOS二开过程中，会创建了很多字段。但是后面觉得这些字段不用了。直接在BOS开发界面删除，但是实际上数据库还有这个字段信息。如何彻底删除呢？首先确定好这些字段是无用字段。复制这...
金蝶云星空限制只能选某个组织机构
金蝶云星空在BOS平台如何限制单据只允许某个组织，或者某几个组织。首先去数据库查询组织机构表中字段select*fromT_ORG_Organizations根据查询结果。找到需要的组织机构。去BOS平台找到组织机构字段 设置...
金蝶云星空二开单据使用半年后突然增加一个字段如何把这个字段批量更新
金蝶云星空二开单据已经使用了大半年时间，有很多数据了。但是突然新增一个字段，这个字段以前不存在，所以单据上以前这个字段值就是空的。可以通过字段批改功能批量维护。批改必须保证数据准确性。以下介绍2种方法：...
微信收款码
支付宝收款码