数据库调优有点头疼.
只能根据执行计划,客户端统计来一步步尝试,一点点调优,直到让绝大部分的消耗在聚集索引的查找上才罢休
你们除了查看执行计划和客户端统计,还能根据哪些反馈信息来调优呢?
除了执行计划和客户端统计以外还能看到SQL执行的其他反馈信息吗?怎么看?用哪些命令和语句?又是怎样分析的?达到什么样的条件了才算优化得当?不知道当查询消耗基本都在聚集索引的查找上算不算优化得差不多了,大家一起说说各自的经验吧
只能根据执行计划,客户端统计来一步步尝试,一点点调优,直到让绝大部分的消耗在聚集索引的查找上才罢休
你们除了查看执行计划和客户端统计,还能根据哪些反馈信息来调优呢?
除了执行计划和客户端统计以外还能看到SQL执行的其他反馈信息吗?怎么看?用哪些命令和语句?又是怎样分析的?达到什么样的条件了才算优化得当?不知道当查询消耗基本都在聚集索引的查找上算不算优化得差不多了,大家一起说说各自的经验吧
2.如果大表数据量太大，备份、删除历史数据也是有必要的3.清空日志，收缩数据库。dbcc shinkdatabase(数据库名)
这的确是让很多人头痛的事.更多人提出的解决方案是:换硬件吧,换硬件真的那么有效吗?就算快一倍,从60秒变成了30秒,你的用户一样是无法忍受.
两三年前,我也遇到过这个问题,也被这个问题拆磨了好几天,后来,在优化数据库的过程中.我发现,对大型超大型的数据库来说,软优化是远远不够的.就算换硬件,好象效果也不 怎么好.在经过一系列动作之后,你算是用尽了全身解数了.速度还没有质的改变,抓狂吗? 我为这个问题抓狂过.
哈哈,说了一通废话.开始我今天的真正目的:
经过了必要的软优化,在同样的硬件条件下,如何让你的数据库快起来.下面的过程,就是实现这个目的,对你的表进行物理优化.
过程不长,但收到的效果可以说是立竿见影的,如果你尝试过一切都无效的话,那我建议你试试它.
优化的原理很简单,打个比方吧,数据库中的记录,类似一个结构体,一个数据表,类似一个链表.主键,虽然是B树,本质也是一个指针.无论你对数据库怎样操作,最后增加的记录都 是记录在数据库文件的未尾.唯一改变的,就是记录的指针.虽然,数据文件设置了主键,但它在磁盘上的实体却是无序的.当查找某条记录时,它只能按指针的指向去跳,这要磁盘去移 动,去寻道.我们的效率就消耗在这里.我们要做的,就是重新让数据实体按主键的方向排序.让磁头能迅速地找到我们所要的数据. :)
下面基本是这个解决方案的全部,也是从实质的库中修改过来的.当然,由于每个人的习惯不一样,可能有些出入,但总体的原理,还是一样的.修改一下,即可使用.
使用方法,建立一个作业,每周的周六晚上执行 EXECUTE dt_optimize_all_table 即可.过程 dt_optimize_all_table 有一个阀值,如果某表的改变的记录超过了某个值(默认 是5万),那它就执行整理.所以,如果你如果是按日期来表的话,你的历史表是不会被整理的.再详细的情况就不说了,过程里的说明都有. 注:这个过程其实是有点危险的,在正式应用前,一定要经过几次的测试.另,如果你将本过程用于你的生产中,所产生的任何问题,与我无关. --------------------------------------
--数据表的物理优化方案.
-- MSTOP
-------------------------------------- -----------------------------------------------
--表优化记录.
-----------------------------------------------
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 'BL_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 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_DBPATH=LEFT(NVR_TABLEGROUPPATH, CHARINDEX('\' + NVR_TABLEGROUPNAME ,NVR_TABLEGROUPPATH)-1 ) ---这里要特别留意
FROM SY_Table_Optimize WHERE NVR_DBNAME=@NVR_DBNAME AND NVR_TABLENAME=@NVR_TABLENAME; 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
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
--------------------------------------------
--在该文件中添加表. **这里要手工修改**.
--------------------------------------------
CREATE TABLE [BL_DATA_2003] (
[BIG_DATAAUTOID] [int] NOT NULL ,
[BIG_AREAAUTOID] [int] NOT NULL ,
[BIG_EnterTypeAutoID] [int] NOT NULL ,
[VAR_DATE] [varchar] (9) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[FLO_VALUE] [float] NULL ,
CONSTRAINT [PK_DATA_2003] PRIMARY KEY CLUSTERED
(
[BIG_DATAAUTOID],
[BIG_AREAAUTOID],
[BIG_EnterTypeAutoID],
[VAR_DATE]
) ON [MC_INDUSTRY_2003]
) ON [MC_INDUSTRY_2003] SET @NVR_CMD='
CREATE TABLE [' + @NVR_TMPTABLENAME + '] (
[BIG_DATAAUTOID] [int] NOT NULL ,
[BIG_AREAAUTOID] [int] NOT NULL ,
[BIG_EnterTypeAutoID] [int] NOT NULL ,
[VAR_DATE] [varchar] (9) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[FLO_VALUE] [float] NULL ,
CONSTRAINT [PK_' + @NVR_TMPTABLENAME + '] PRIMARY KEY
(
[BIG_DATAAUTOID],
[BIG_AREAAUTOID],
[BIG_EnterTypeAutoID],
[VAR_DATE]
) ON [' + @NVR_TMPTABLENAME + ']
) ON [' +@NVR_TMPTABLENAME + ']'
EXECUTE(@NVR_CMD) IF @@ERROR <>0 BEGIN
SET @INT_TRANSACTION=-1
GOTO RollTRANSACTION
END -- --在表上建立一个日期的索引.如果有索引的话.在此添加.
-- SET @NVR_CMD='CREATE INDEX IX_' + @NVR_TMPTABLENAME + '_DATE ON ' + @NVR_TMPTABLENAME + ' (BIG_DATE)'
-- EXECUTE(@NVR_CMD) --将数据移到新的表.
SET @NVR_CMD='INSERT INTO ' + @NVR_TMPTABLENAME + ' SELECT * FROM ' + @NVR_TABLENAME --在此视你实质情况而定,可加入主键一至的 ORDER BY
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)
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
大致分析如下：
两条线：其一数据库性能优化，其中涉及性能数据的提取，分析，问题诊断。
比如根据数据诊断，发现内存较小，对于TB的数据库而言，内存只有4G或8G，虽然开了AWE等配置但是怎么优化基本都要从硬件下手，即使把索引优到铆，也会发现数据不是在缓冲区，而是频繁从磁盘读写。
二是应用程序优化，其中涉及模块或功能在开发时是否编写的最合理。
比如开发人员在编写前台应用时，where中根本没用到索引，这里往往就是where a+b+c in (select a+b+c)的一些可能，换做是where exists会进步很多。
针对两条线，我们需要细化一些方式方法：
1、首先明确数据库版本，针对不同版本优化的方面也有细微差别。
比如是LZ是sql2000的调优，从第一步获取性能数据就比较头疼，因为相比2005甚至是2008而言，其所提供的DMV以及监控快照功能都很少甚至没有。此时硬件配置比较合理的前提下，可以考虑将版本升级，当然我也不会轻易这样做。
2、如果提取性能数据呢？
如果能轻易抓到问题，我想LZ也不会发帖了，除非你一点头绪都没有。
此时我们可以用一些sqldiag（sql2000下的名称不同，暂时忘了）或者trace工具定期自动化的获取性能数据，否则你可以做在笔记本前，不停的F5。
当然这里也可以通过对前端应用的瓶颈定位分析，如果真的这样可以忽略上述部分。
比如operator告诉你在操作XXX时非常慢，这里直接可以考虑第二条线了。
3、如何分析性能数据呢？
同样通过readtrace等工具进行分析并观察。不要指望短期内很快可以找到并解决它，通过我认为这都是一个长期的过程。也就是说在一切行动开始前，LZ要有性能基数，否则拿什么比较快慢呢？即使你优化了，怎样知道优化是否有效？operator告诉你快了一点，实际上可能是她今天心情好一点罢了。
如果你还没有做baseline的话，我的建议是马上开始，do it。
忘了，可能您不负责这些，只是想了解而已，那么可以建议您的DBA马上开始，说不定他已经知道该从哪下手了。废话如上，如有不妥请见谅！
up,这位兄台说的没错。在sql server 2000下的工具是pssdiag吧。另一个很有用的工具就是系统自带的perfmon了。我觉得调优有2点很关键
1，你调优的目标是什么？
2，你当前的情况。调优是永无止境的，能够满足一定的标准就行了，因此目标很关键，需要响应时间在多少时间内可以接受，这样的结果就行了。在调优里面，有一个方法非常的重要，那就是建立性能基线，性能基线的建立，可以通过类似于sqldiag或者perfmon这样的工具来建立，调优后进行对比。另外，楼主的情况是系统还未上线，这种情况下，就可以考虑系统里面的数据特点，未来的变化趋势作出一些判断，比如说是写频繁，更新频繁等等。也可以自己通过RML工具进行一些压力测试，这样能更好的发掘出性能问题。一点浅见。