|
SQLSERVER数据表的记录重排物理优化方案
??? 该方案产生于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,@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,'/')
?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,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,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
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
