该方案产生于2005年,当时我接手了某产业库的设计.我们在开发这个产品时,在数据库中引入了100万条记录作为开发版数据库.开发过程非常顺利,前台程序的测试速度也非常好.但我们在后来将当前的5亿条数据库追加到开发库进行测试时,发现无论什么情况,都无法从数据库中取出记录,发现在读取数据库时,磁盘灯一直在亮,而性能性能监视器里的CPU使用率很底.此时的SQLSERVER服务器会呈的假死状态.
后来我想我们的数据库为优化而预留的空间被填满后,数据会被追加到数据库文件的最后页.会不会是因为这个原因,导至了磁盘的磁头寻道,读取的效率下降?而造成了一个高I/O的假象?基于这个想法,我再次到查询分析器中运行了前台取值存储过程,然后用WIDNOWS的性能监视器观察,发现数据读取的值真的是很低.
基于这个原因,所以,我认为如果将数据库的表按聚集索引的方向重排一次再回写,一定会大大减少磁盘的寻道,读取时间.从而大大提高数据库的读取效率.下面的过程就是基于这个原因而写的.
首先说明,下面这个存储过程具有一定的危险性,而且,我们已经对以年度为序,纵向分表.对年度数据表,名称都是有规则的,如这里的是 MC_CP_DATA_####(四位的年).在使用这个过程时,一定要结合你当前的实际情况.
--------------------------------------
-- 数据表的数据重组物理优化方案.
-- MSTOP 2005/5/2
--------------------------------------
-----------------------------------------------
--表优化记录.
-----------------------------------------------
--分段截取函数
IF EXISTS(SELECT [NAME] FROM SYSOBJECTS WHERE [NAME]='Fun_SplitStr') BEGIN
DROP FUNCTION Fun_SplitStr
END
GO
CREATE FUNCTION DBO.Fun_SplitStr(
@S VARCHAR(8000), --包含多个数据项的字符串
@POS INT, --要获取的数据项的位置
@SPLIT VARCHAR(10) --数据分隔符
)RETURNS VARCHAR(512)
AS
BEGIN
IF @S IS NULL RETURN(NULL)
DECLARE @SPLITLEN INT
SELECT @SPLITLEN=LEN(@SPLIT+'A')-2
WHILE @POS>1 AND CHARINDEX(@SPLIT,@S+@SPLIT)>0
SELECT @POS=@POS-1,
@S=STUFF(@S,1,CHARINDEX(@SPLIT,@S+@SPLIT)+@SPLITLEN,'')
RETURN LEFT(@S,CHARINDEX(@SPLIT,@S+@SPLIT)-1)
END
GO
-----------------------------------------
-- 文件路径.
-----------------------------------------
IF EXISTS(SELECT [NAME] FROM SYSOBJECTS WHERE [NAME]='Prc_FilePath') BEGIN
DROP PROC Prc_FilePath
END
GO
CREATE Proc Prc_FilePath(
@Var_Path VarChar(4000),
@Var_String VarChar(4000) OutPut
) As Begin
Declare @Tab_Tmp Table (Int_ID Int,Var_Str VarChar(128) )
Declare @Var_S1 Varchar(4000)
Declare @Int_ID Int
SET @Var_Path=REPLACE(@Var_Path,'\\','\')
SET @Int_ID=1
SET @Var_S1=dbo.FUN_SplitStr(@Var_Path,@Int_ID,'\')
WHILE LEN(@Var_S1)>0 BEGIN
INSERT INTO @Tab_Tmp VALUES(@Int_ID,@Var_S1)
SET @Int_ID=@Int_ID+1
SET @Var_S1=dbo.FUN_SplitStr(@Var_Path,@Int_ID,'\')
END
DELETE FROM @Tab_Tmp WHERE Int_ID = (SELECT MAX(Int_ID) FROM @Tab_Tmp)
SET @Var_String=''
SELECT @Var_String=@Var_String + Var_Str + '\' From @Tab_Tmp Order By Int_ID
IF RIGHT(@Var_String,1)<>'\' AND LEN(@Var_String)>0 BEGIN
SET @Var_String=@Var_String + '\'
END
End
GO
--------------------------------------
--记录需优化表的表信息.
--------------------------------------
IF EXISTS(SELECT [NAME] FROM SYSOBJECTS WHERE [NAME]='SY_Table_Optimize') BEGIN
DROP TABLE SY_Table_Optimize
END
GO
CREATE TABLE SY_Table_Optimize (
NVR_DBNAME NVARCHAR(128) NOT NULL,
NVR_TABLENAME NVARCHAR(128) NOT NULL,
NVR_TABLEGROUPNAME NVARCHAR(128) NOT NULL,
NVR_TABLEGROUPPATH NVARCHAR(256) NULL,
INT_UpRowCount INT NULL,
DAT_UpDate DATETIME Null
CONSTRAINT [PK_SY_Table_Optimize] PRIMARY KEY CLUSTERED ([NVR_DBNAME],[NVR_TABLENAME]))
GO
-----------------------------------------------------------------------------------
-- 初始化 SY_Table_Optimize ,
-- 组名和文件名必须是同名.如:文件名是 ABC.NDF ,则组名是: ABC ,并且,一个文件一个组一个表.
-----------------------------------------------------------------------------------
INSERT INTO SY_Table_Optimize
SELECT DB_NAME(),A.[NAME],C.groupname,D.[FILENAME],B.[ROWS],GETDATE()
FROM SYSOBJECTS AS A inner join SYSINDEXES AS B
ON A.ID=B.ID AND A.XTYPE='U' AND B.INDID<2 AND A.[NAME] LIKE 'MC_CP_DATA%'
INNER JOIN sysfilegroups AS C ON B.GROUPID=C.GROUPID
INNER JOIN sysfiles AS D ON C.GROUPID=D.GROUPID
GO
---------------------------------
-- 物理优化所有的表.
-- 在作业里加一个作业:
-- EXECUTE dt_optimize_all_table
---------------------------------
IF EXISTS(SELECT [NAME] FROM SYSOBJECTS WHERE [NAME]='dt_optimize_all_table') BEGIN
DROP PROC dt_optimize_all_table
END
GO
CREATE PROC dt_optimize_all_table(
@INT_VALVE INT=50000 --数据库优化阀值.
) WITH ENCRYPTION AS BEGIN
DECLARE @NVR_DBNAME NVARCHAR(64) --数据库名.
DECLARE @NVR_DBPATH NVARCHAR(256) --数据库所在路径.
DECLARE @INT_ROWCOUNT INT --当前表的总行数.
DECLARE @INT_NEWROWCOUNT INT
DECLARE @NVR_TABLENAME NVARCHAR(256) --表名.
DECLARE @NVR_OLEGROUPNAME NVARCHAR(256)
DECLARE @NVR_NEWGOUPNAME NVARCHAR(256)
DECLARE @NVR_CMD NVARCHAR(4000)
DECLARE @INT_ROW INT
DECLARE @NVR_TABLEGROUPPATH NVARCHAR(4000)
SET @NVR_DBNAME=DB_NAME();
SELECT @INT_ROW=MAX(ABS(T2.[ROWS]-T1.INT_UpRowCount)) FROM
(
SELECT NVR_TABLENAME,INT_UpRowCount
FROM SY_Table_Optimize WHERE NVR_DBNAME=@NVR_DBNAME
) AS T1 INNER JOIN
(
SELECT A.[NAME],B.[ROWS]
FROM SYSOBJECTS AS A,SYSINDEXES AS B
WHERE A.ID=B.ID AND A.XTYPE='U' AND B.INDID<2
) AS T2 ON T1.NVR_TABLENAME=T2.[NAME]
IF @INT_ROW>=@INT_VALVE BEGIN
--------------------------------------------
--先清理一次日志.因为处理要需要大量的磁盘空间.
--------------------------------------------
SET @NVR_CMD='DUMP TRANSACTION ' + @NVR_DBNAME + ' WITH NO_LOG'
EXECUTE(@NVR_CMD)
SET @NVR_CMD='DBCC SHRINKFILE(2, 0)'
EXECUTE(@NVR_CMD)
--断开所有相关连接.要一个个断开.
DECLARE @INT_SPID INT
SET @NVR_CMD=''
SELECT @INT_SPID=MIN(SPID) FROM MASTER.DBO.SYSPROCESSES WHERE DBID=DB_ID() AND SPID<>@@spid
SET @INT_SPID=ISNULL(@INT_SPID,-1)
WHILE @INT_SPID>0 BEGIN
SET @NVR_CMD=N' KILL ' + RTRIM(@INT_SPID) + ';'
EXECUTE SP_EXECUTESQL @NVR_CMD
SELECT @INT_SPID=MIN(SPID) FROM MASTER.DBO.SYSPROCESSES WHERE DBID=DB_ID() AND SPID>@INT_SPID AND SPID<>@@spid
SET @INT_SPID=ISNULL(@INT_SPID,-1)
END
-----------------------------------------
SELECT @NVR_TABLENAME=MIN(NVR_TABLENAME) FROM SY_Table_Optimize WHERE NVR_DBNAME=@NVR_DBNAME;
WHILE LEN(@NVR_TABLENAME)>0 BEGIN
-----------------------------------------------------
SELECT @NVR_OLEGROUPNAME=NVR_TABLEGROUPNAME,
@INT_ROWCOUNT=INT_UpRowCount,
@NVR_TABLEGROUPPATH=REPLACE(NVR_TABLEGROUPPATH,'\\','\')
FROM SY_Table_Optimize WHERE NVR_DBNAME=@NVR_DBNAME AND NVR_TABLENAME=@NVR_TABLENAME;
--路径要从系统表来.
SELECT @NVR_TABLEGROUPPATH=REPLACE(D.[FILENAME],'\\','\')
FROM SYSOBJECTS AS A inner join SYSINDEXES AS B
ON A.ID=B.ID AND A.XTYPE='U' AND B.INDID<2 AND A.[NAME]=@NVR_TABLENAME
INNER JOIN sysfilegroups AS C ON B.GROUPID=C.GROUPID
INNER JOIN sysfiles AS D ON C.GROUPID=D.GROUPID
-----------------------------------------------------
EXECUTE Prc_FilePath @NVR_TABLEGROUPPATH,@NVR_DBPATH OUTPUT
SELECT @INT_NEWROWCOUNT=B.[ROWS]
FROM SYSOBJECTS AS A,SYSINDEXES AS B
WHERE A.ID=B.ID AND A.XTYPE='U' AND B.INDID<2 AND A.[NAME]=@NVR_TABLENAME;
--如果当前行数改变大于某个值,则优化.
IF ABS(@INT_NEWROWCOUNT-@INT_ROWCOUNT)>=@INT_VALVE BEGIN
EXECUTE dt_optimize_table @NVR_DBNAME,@NVR_DBPATH,@NVR_TABLENAME,@NVR_OLEGROUPNAME,@NVR_NEWGOUPNAME OUTPUT;
--更新优化记录.
UPDATE SY_Table_Optimize SET NVR_TABLEGROUPNAME=@NVR_NEWGOUPNAME,INT_UpRowCount=@INT_NEWROWCOUNT,DAT_UpDate=GETDATE() WHERE NVR_DBNAME=@NVR_DBNAME AND NVR_TABLENAME=@NVR_TABLENAME;
END
SELECT @NVR_TABLENAME=MIN([NVR_TABLENAME]) FROM SY_Table_Optimize WHERE NVR_DBNAME=@NVR_DBNAME AND NVR_TABLENAME>@NVR_TABLENAME;
SET @NVR_TABLENAME=ISNULL(@NVR_TABLENAME,'');
END
END
--查看,是否有新的表..
INSERT INTO SY_Table_Optimize
SELECT @NVR_DBNAME,A.[NAME],C.groupname,D.[FILENAME],B.[ROWS],GETDATE()
FROM SYSOBJECTS AS A inner join SYSINDEXES AS B
ON A.[ID]=B.[ID] AND A.XTYPE='U' AND B.INDID<2 AND A.[NAME] LIKE 'MC_CP_DATA%' AND A.[NAME] NOT IN ( SELECT NVR_TABLENAME FROM SY_Table_Optimize )
INNER JOIN sysfilegroups AS C ON B.GROUPID=C.GROUPID
INNER JOIN sysfiles AS D ON C.GROUPID=D.GROUPID
END
GO
---------------------------------
-- 对指定的表进行物理优化.
---------------------------------
IF EXISTS(SELECT [NAME] FROM SYSOBJECTS WHERE [NAME]='dt_optimize_table') BEGIN
DROP PROC dt_optimize_table
END
GO
CREATE PROC dt_optimize_table (
@NVR_DBNAME NVARCHAR(64), --数据库名.
@NVR_DBPATH NVARCHAR(256), --数据库所在路径.
@NVR_TABLENAME NVARCHAR(256), --表名.
@NVR_OLEGROUPNAME NVARCHAR(256), --上一个文件组的名称.
@NVR_NEWGOUPNAME NVARCHAR(256) OUTPUT --新的组文件名.
) WITH ENCRYPTION AS BEGIN
DECLARE @NVR_NEWID NVARCHAR(16) --新的表文件编号.
DECLARE @NVR_CMD NVARCHAR(4000)--命令
DECLARE @NVR_TMPTABLENAME NVARCHAR(256) --暂时表名
DECLARE @INT_TRANSACTION INT
SET @NVR_NEWID= LEFT(REPLACE(NEWID(),'-',''),16)
SET @NVR_TMPTABLENAME=@NVR_TABLENAME + '_' + @NVR_NEWID
SET @NVR_NEWGOUPNAME=@NVR_TMPTABLENAME
--添加一个文件组.
SET @NVR_CMD='ALTER DATABASE [' + @NVR_DBNAME + '] ADD FILEGROUP [' + @NVR_TMPTABLENAME + ']'
EXECUTE(@NVR_CMD)
--向文件组中添加一个文件
SET @NVR_CMD='
ALTER DATABASE [' + @NVR_DBNAME + '] ADD FILE(
NAME = N''' + @NVR_TMPTABLENAME + ''',
FILENAME = N''' + @NVR_DBPATH + @NVR_TMPTABLENAME + '.NDF'' ,
SIZE = 3,
FILEGROWTH = 10%)
TO FILEGROUP [' +@NVR_TMPTABLENAME + ']'
EXECUTE(@NVR_CMD)
--------------------------------------------
--事务段
--------------------------------------------
SET @INT_TRANSACTION=1
BEGIN TRANSACTION
--------------------------------------------
--在该文件中添加表. **这里要手工修改**.
--------------------------------------------
SET @NVR_CMD='
CREATE TABLE [' + @NVR_TMPTABLENAME + '] (
[VAR_GOODSID] [varchar](8) NOT NULL ,
[VAR_AREAID] [varchar](8) NOT NULL ,
[BIG_DATE] [int] NOT NULL ,
[INT_INDEXID] [int] NOT NULL ,
[FLO_VALUE] [float] NULL ,
CONSTRAINT [PK_' + @NVR_TMPTABLENAME + '] PRIMARY KEY
(
[VAR_GOODSID],
[VAR_AREAID],
[BIG_DATE],
[INT_INDEXID]
) ON [' + @NVR_TMPTABLENAME + ']
) ON [' + @NVR_TMPTABLENAME + ']'
EXECUTE(@NVR_CMD)
IF @@ERROR<>0 BEGIN
SET @INT_TRANSACTION=-1
GOTO RollTRANSACTION
END
--将数据移到新的表.
SET @NVR_CMD='INSERT INTO ' + @NVR_TMPTABLENAME
+ ' SELECT * FROM ' + @NVR_TABLENAME + ' ORDER BY VAR_GOODSID,VAR_AREAID,BIG_DATE,INT_INDEXID'
EXECUTE(@NVR_CMD)
-- --在表上建立一个日期的索引.
-- SET @NVR_CMD='CREATE INDEX IX_' + @NVR_TMPTABLENAME + '_DATE ON ' + @NVR_TMPTABLENAME + ' (BIG_DATE)'
-- EXECUTE(@NVR_CMD)
IF @@ERROR<>0 BEGIN
SET @INT_TRANSACTION=-1
GOTO RollTRANSACTION
END
--删除原表.
SET @NVR_CMD='DROP TABLE ' + @NVR_TABLENAME
EXECUTE(@NVR_CMD)
IF @@ERROR<>0 BEGIN
SET @INT_TRANSACTION=-1
GOTO RollTRANSACTION
END
--提交事务.
COMMIT TRANSACTION
SET @INT_TRANSACTION=0
--------------------------------------------
--事务段
--------------------------------------------
--将表名改为原来表名.
SET @NVR_CMD='exec sp_rename ''' + @NVR_TMPTABLENAME + ''', ''' + @NVR_TABLENAME + ''''
EXECUTE(@NVR_CMD)
--回滚事务.
RollTRANSACTION:
IF @INT_TRANSACTION=-1 BEGIN
ROLLBACK TRANSACTION
END ELSE BEGIN
--删除原来的表文件.
SET @NVR_CMD='
ALTER DATABASE [' + @NVR_DBNAME + ']
REMOVE FILE ' + @NVR_OLEGROUPNAME
EXECUTE(@NVR_CMD)
SET @NVR_CMD='
ALTER DATABASE [' + @NVR_DBNAME + ']
REMOVE FILEGROUP ' + @NVR_OLEGROUPNAME
EXECUTE(@NVR_CMD)
END
--------------------------------------------
--清理日志
--------------------------------------------
SET @NVR_CMD='DUMP TRANSACTION ' + @NVR_DBNAME + ' WITH NO_LOG'
EXECUTE(@NVR_CMD)
SET @NVR_CMD='DBCC SHRINKFILE(2, 0)'
EXECUTE(@NVR_CMD)
END
GO