关于SQL2000与SQL2005性能比较

这个问题比较模糊，，，我把函数也搞出来吧，总的来说，是一个计算仓库进销存的函数！

 
  数据量可以说好庞大，比较复杂的函数！！现在的问题是在一台双核的服务器，2G内存，运行的时候，SQL2000能顺利完成，虽然比较久。但SQL2005却知道时间过长跳出！我想问，如果服务器很一般的情况下，SQL2000的效率是否比2005好呢？ 专家门给点专业点的意见吧！

34 个解决方案

#1

顶~~~~

#2

以下均為轉帖,僅供參考

执行缓存以优化SQL Server的内存占用 
作者：superhasty  2007-11-29 
    在论坛上常见有朋友抱怨，说SQL Server太吃内存了。这里笔者根据经验简单介绍一下内存相关的调优知识。首先说明一下SQL Server内存占用由哪几部分组成。SQL Server占用的内存主要由三部分组成：数据缓存(Data Buffer)、执行缓存(Procedure Cache)、以及SQL Server引擎程序。SQL Server引擎程序所占用缓存一般相对变化不大，则我们进行内存调优的主要着眼点在数据缓存和执行缓存的控制上。本文主要介绍一下执行缓存的调优。数据缓存的调优将在另外的文章中介绍。 
    对于减少执行缓存的占用，主要可以通过使用参数化查询减少内存占用。 
    1、使用参数化查询减少执行缓存占用 
我们通过如下例子来说明一下使用参数化查询对缓存占用的影响。为方便试验，我们使用了一台没有其它负载的SQL Server进行如下实验。 
    下面的脚本循环执行一个简单的查询，共执行10000次。 
    首先，我们清空一下SQL Server已经占用的缓存： 
    dbcc freeproccache 
    然后，执行脚本： DECLARE @t datetime 
SET @t = getdate() 
SET NOCOUNT ON 
DECLARE @i INT, @count INT, @sql nvarchar(4000) 
SET @i = 20000 
WHILE @i <= 30000 
BEGIN 
SET @sql = 'SELECT @count=count(*) FROM P_Order WHERE MobileNo = ' + cast( @i as varchar(10) ) 
EXEC sp_executesql @sql ,N'@count INT OUTPUT', @count OUTPUT 
SET @i = @i + 1 
END 
PRINT DATEDIFF( second, @t, current_timestamp ) 
    输出： 
DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。     11 
    使用了11秒完成10000次查询。 
    我们看一下SQL Server缓存中所占用的查询计划： Select Count(*) CNT,sum(size_in_bytes) TotalSize From sys.dm_exec_cached_plans 
    查询结果：共有2628条执行计划缓存在SQL Server中。它们所占用的缓存达到： 
    92172288字节 = 90012KB = 87 MB。 
    我们也可以使用dbcc memorystatus 命令来检查SQL Server的执行缓存和数据缓存占用。 
    执行结果如下： 
    执行缓存占用了90088KB，有2629个查询计划在缓存里，有1489页空闲内存（每页8KB）可以被数据缓存和其他请求所使用。     我们现在修改一下前面的脚本，然后重新执行一下dbcc freeproccache。再执行一遍修改后的脚本： 

DECLARE @t datetime 
SET @t = getdate() 
SET NOCOUNT ON 
DECLARE @i INT, @count INT, @sql nvarchar(4000) 
SET @i = 20000 
WHILE @i <= 30000 
BEGIN 
SET @sql = 'select @count=count(*) FROM P_Order WHERE MobileNo = @i' 
EXEC sp_executesql @sql, N'@count int output, @i int', @count OUTPUT, @i 
SET @i = @i + 1 
END 
PRINT DATEDIFF( second, @t, current_timestamp ) 
    输出： 
    DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。     1 
    即这次只用1秒钟即完成了10000次查询。 
    我们再看一下sys.dm_exec_cached_plans中的查询计划： 
Select Count(*) CNT,sum(size_in_bytes) TotalSize From sys.dm_exec_cached_plans 
    查询结果：共有4条执行计划被缓存。它们共占用内存： 172032字节 = 168KB。 
    如果执行dbcc memorystatus，则得到结果： 
    有12875页空闲内存(每页8KB)可以被数据缓存所使用。 
    到这里，我们已经看到了一个反差相当明显的结果。在现实中，这个例子中的前者，正是经常被使用的一种执行SQL脚本的方式（例如：在程序中通过合并字符串方式拼成一条SQL语句，然后通过ADO.NET或者ADO方式传入SQL Server执行）。 
    解释一下原因： 
    我们知道，SQL语句在执行前首先将被编译并通过查询优化引擎进行优化，从而得到优化后的执行计划，然后按照执行计划被执行。对于整体相似、仅仅是参数不同的SQL语句，SQL Server可以重用执行计划。但对于不同的SQL语句，SQL Server并不能重复使用以前的执行计划，而是需要重新编译出一个新的执行计划。同时，SQL Server在内存足够使用的情况下，此时并不主动清除以前保存的查询计划（注：对于长时间不再使用的查询计划，SQL Server也会定期清理）。这样，不同的SQL语句执行方式，就将会大大影响SQL Server中存储的查询计划数目。如果限定了SQL Server最大可用内存，则过多无用的执行计划占用，将导致SQL Server可用内存减少，从而在执行查询时尤其是大的查询时与磁盘发生更多的内存页交换。如果没有限定最大可用内存，则SQL Server由于可用内存减少，从而会占用更多内存。 
  对此，我们一般可以通过两种方式实现参数化查询：一是尽可能使用存储过程执行SQL语句（这在现实中已经成为SQL Server DBA的一条原则），二是使用sp_executesql 方式执行单个SQL语句（注意不要像上面的第一个例子那样使用sp_executesql)。 
    在现实的同一个软件系统中，大量的负载类型往往是类似的，所区别的也只是每次传入的具体参数值的不同。所以使用参数化查询是必要和可能的。另外，通过这个例子我们也看到，由于使用了参数化查询，不仅仅是优化了SQL Server内存占用，而且由于能够重复使用前面被编译的执行计划，使后面的执行不需要再次编译，最终执行10000次查询总共只使用了1秒钟时间。 
   2、检查并分析SQL Server执行缓存中的执行计划 
    通过上面的介绍，我们可以看到SQL缓存所占用的内存大小。也知道了SQL Server执行缓存中的内容主要是各种SQL语句的执行计划。则要对缓存进行优化，就可以通过具体分析缓存中的执行计划，看看哪些是有用的、哪些是无用的执行计划来分析和定位问题。     通过查询DMV: sys.dm_exec_cached_plans,可以了解数据库中的缓存情况，包括被使用的次数、缓存类型、占用的内存大小等。 SELECT usecounts, cacheobjtype, objtype,size_in_bytes, plan_handle FROM sys.dm_exec_cached_plans 
    通过缓存计划的plan_handle可以查询到该执行计划详细信息，包括所对应的SQL语句：
SELECT TOP 100 usecounts, objtype, p.size_in_bytes, [sql].[text] FROM sys.dm_exec_cached_plans p OUTER APPLY sys.dm_exec_sql_text (p.plan_handle) sql 
ORDER BY usecounts 
    我们可以选择针对那些执行计划占用较大内存、而被重用次数较少的SQL语句进行重点分析。看其调用方式是否合理。另外，也可以对执行计划被重复使用次数较多的SQL语句进行分析，看其执行计划是否已经经过优化。进一步，通过对查询计划的分析，还可以根据需要找到系统中最占用IO、CPU时间、执行次数最多的一些SQL语句，然后进行相应的调优分析。篇幅所限，这里不对此进行过多介绍。读者可以查阅联机丛书中的：sys.dm_exec_query_plan内容得到相关帮助。 

细化解析：SQL Server占用过多内存的问题 

经常有网友会问，SQL Server占用了太多的内存，而且还会不断的增长；或者说已经设置了使用内存，可它没有用到那么多，这是怎么一回事儿呢？ 

　　下面，我们来具体看以看SQL Server是怎样使用内存的。 

　　最大的开销一般是用于数据缓存，如果内存足够，它会把用过的数据和觉得你会用到的数据统统扔到内存中，直到内存不足的时候，才把命中率低的数据给清掉。所以一般我们在看statistics io的时候，看到的physics read都是0。 

　　其次就是查询的开销，一般地说，hash join是会带来比较大的内存开销的，而merge join和nested loop的开销比较小，还有排序和中间表、游标也是会有比较大的开销的。所以用于关联和排序的列上一般需要有索引。 

　　再次就是对执行计划、系统数据的存储，这些都是比较小的。 

　　我们先来看数据缓存对性能的影响，如果系统中没有其它应用程序来争夺内存，数据缓存一般是越多越好，甚至有些时候我们会强行把一些数据pin在高速缓存中。但是如果有其它应用程序，虽然在需要的时候MS SQL会释放内存，但是线程切换、IO等待这些工作也是需要时间的，所以就会造成性能的降低。这样我们就必须设置MS SQL的最大内存使用。可以在SQL Server 属性（内存选项卡）中找到配置最大使用内存的地方，或者也可以使用sp_configure来完成。如果没有其它应用程序，那么就不要限制MS SQL对内存的使用。 

　　最后我们来看查询的开销，这个开销显然是越低越好，因为我们不能从中得到好处，相反，使用了越多的内存多半意味着查询速度的降低。所以我们一般要避免中间表和游标的使用，在经常作关联和排序的列上建立索引。 

#3

ding

#4

SQL2000的效率是否比2005好呢？
这个不可一概而论，分情况，看你要执行什么了。
假设你写了一个函数，这个函数里用的都是sql2000的东西，可以就2000快；
因为在2005里有些新特性，可以加速运行的速度。
但是如果你用的新特性，在2000中就不能运行了。
ps：不知道lz听懂我的意思了吗？

#5

我是来学习的

#6

引用楼主 wushilin201 的帖子:

这个问题比较模糊，，，我把函数也搞出来吧，总的来说，是一个计算仓库进销存的函数！ 


  数据量可以说好庞大，比较复杂的函数！！现在的问题是在一台双核的服务器，2G内存，运行的时候，SQL2000能顺利完成，虽然比较久。但SQL2005却知道时间过长跳出！我想问，如果服务器很一般的情况下，SQL2000的效率是否比2005好呢？ 专家门给点专业点的意见吧！

这要看你干的什么东西了.

如果都是1+1=2的事情,估计两者都差不多,所以得根据具体的情况来分析.

#7

晕死，一个问题搞不定就上升到SQL2000与SQL2005性能比较上面来了

#8

路过，关注

#9

关注

#10

05新能应该不比02差，估计还是你谢大代码有问题，不能被05正确分析执行

#11

那我把我的代码复制出来，上面几楼的人，，不清楚就别乱评论！！！
本人是觉得代码是没有问题，才拿上来跟大家研究。

alter       	function [dbo].[freeperiodmatstledger](
     @beginday datetime,
     @endday   datetime,
     @plantid  varchar(200),
     @stcode varchar(200),
     @matcode varchar(200),
     @matgroup varchar(200),
     @isactive varchar(5) ,
     @matname	varchar(50)
     )
returns @table table(

     beginday datetime ,
     endday   datetime ,
     plantid varchar(20) ,
     plantname varchar(20),
     stcode varchar(30),
     stname varchar(50),
     oldcode varchar(50),	--旧物料编号     
     matcode varchar(50),
     productcode varchar(50),
     matname varchar(500),
     matgroup varchar(50),
     matgroupname varchar(50),
     special varchar(40),
     brand varchar(80),
     grade varchar(20),
     gradename varchar(40),
     gradememo varchar(60),
     packagecode varchar(100),
     goodsno varchar(100),
     batchcode varchar(100),
     salesuom varchar(20),
     uomrate int,
     baseuomrate int,
    prestock decimal(29,19),   --期初
     preprice decimal(29,19),
     premoney decimal(29,19),
	indigit decimal(29,19),   --入库
     inprice decimal(29,19),
     inmoney decimal(29,19),

     indigit01 decimal(29,19),   --采购入库
     inprice01 decimal(29,19),
     inmoney01 decimal(29,19),

     indigit02 decimal(29,19),   --盘盈入库
     inprice02 decimal(29,19),
     inmoney02 decimal(29,19),

     indigit03	money,
     inmoney03	money ,

     producein money,
     purcharin money,
     movestin money,
     neixiaoin money,
     otherin money,

     outdigit decimal(29,19),--出库
     outprice decimal(29,19),--decimal(38,30),
     outmoney decimal(29,19),

     outdigit01 decimal(29,19),	--领用出库
     outprice01 decimal(29,19), --领用出库
     outmoney01 decimal(29,19),

     outdigit02 decimal(29,19),	--盘亏出库
     outprice02 decimal(29,19), --盘亏出库
     outmoney02 decimal(29,19),

     saleout money,
     zpout money,
     movestout money,
     spsale money,
     otherout money,

     endstock decimal(29,19), --期未
     endprice decimal(29,19), 
     endmoney decimal(29,19),

     cv1 varchar(20),
     cv2 varchar(20),
     cv3 varchar(20),
     cv4 int,
     cv5 varchar(20),cv6 varchar(20),cv7 varchar(20),cv8 varchar(20),cv9 varchar(20),cv10 varchar(20),batchname varchar(100),
     matcostprice money ,
	ratetxt varchar(50), 
	baseuom varchar(50)	
 )
as
begin

   declare @periodid varchar(8),

	   @prestock money,
           @premoney money,
           @preprice money,

	   @preperiodend datetime


if @beginday = '' 
begin
  return
end

if datediff(day,@beginday,@endday)<0
begin  
  return
end


---------------------获取基本资料--begin
insert into @table(PlantID, stCode, MatCode,-- productcode, 
                   matname, MatGroup, matgroupname ,BatchCode, batchname, special, 
                   salesUOM, uomrate, baseuomrate,-- grade, gradename, gradememo, 
		   prestock, preprice, premoney,
                   indigit,  inprice,   inmoney,
		   outdigit, outprice,  outmoney,
		   endstock, endprice,  endmoney,  
                   brand,ratetxt,baseuom ,oldcode)

SELECT dbo.iMatStorage.plantid, dbo.iMatStorage.stCode, dbo.iMatStorage.MatCode,-- dbo.imatgeneral.productcode, 
       dbo.imatgeneral.matname, 
      dbo.imatgeneral.MatGroup,dbo.imatgroup.matgroupname, dbo.iMatStorage.BatchCode, dbo.iBatchGeneral.batchname, 
	dbo.imatgeneral.special,
      dbo.imatgeneral.salesUOM, dbo.vmatpackagerate.uomrate, dbo.vmatpackagerate.baseuomrate,  
      0,0,0,
      0,0,0,
      0,0,0,
      0,0,0,
    
      dbo.imatgeneral.brand ,
      dbo.imatgeneral.ratetxt , dbo.imatgeneral.baseuom ,dbo.imatgeneral.oldcode

FROM  dbo.iMatStorage LEFT OUTER JOIN
      dbo.iBatchGeneral INNER JOIN
      dbo.imatgeneral ON dbo.iBatchGeneral.matcode = dbo.imatgeneral.MatCode ON 
      dbo.iMatStorage.MatCode = dbo.iBatchGeneral.matcode AND 
      dbo.iMatStorage.BatchCode = dbo.iBatchGeneral.batchcode LEFT OUTER JOIN
      dbo.vmatpackagerate ON 
      dbo.imatgeneral.BaseUOM = dbo.vmatpackagerate.baseuom AND 
      dbo.imatgeneral.salesUOM = dbo.vmatpackagerate.uom AND 
      dbo.iBatchGeneral.cv1 = dbo.vmatpackagerate.packagecode
      join dbo.imatgroup  on dbo.imatgeneral.matgroup =dbo.imatgroup.matgroup 


where (@plantid ='' or dbo.iMatStorage.plantid in (select * from getinstr(@plantid)))
  and (@stcode = '' or dbo.iMatStorage.stcode in(select * from getinstr(@stcode)))
  and (@matcode = '' or dbo.iMatStorage.matcode in (select * from getinstr(@matcode))) 
  and (@matgroup = '' or dbo.imatgeneral.matgroup in (select cc.matgroup from imatgroup  aa,
       dbo.getinstr(@matgroup) bb,imatgroup cc where aa.matgroup=bb.list and 
       left(cc.treecontrol,len(aa.treecontrol))=aa.treecontrol   ) ) 
  and (@matname='' or dbo.imatgeneral.matname like @matname)

---------------------获取基本资料--end




---------------------处理开始日期期初数--begin
--获取开始日期的期间信息
   select distinct @periodid=periodid, @preperiodend=begindate 
   from    gperiod 
   where   periodtype='库存' and @beginday between begindate and enddate




--获取开始日期的上期期末数  


  update @table 
  set prestock = b.stock,
      --preprice= b.stockvalue / b.stock ,
      preprice= case when isnull(b.stock,0)=0 then 0 else b.stockvalue / b.stock end ,
      premoney=b.stockvalue
  from @table a, imatstbalance b
  where 
    b.periodid = dbo.preperiod(@periodid) and
 --   b.periodid = @periodid and
    a.plantid = b.plantid and
    a.stcode = b.stcode and
    a.matcode = b.matcode and 
    a.batchcode = b.batchcode 

---------------------处理开始日期到结束日期的期中进数--start

 

#12

接着上面

--计算所有单据金额 开始
update @table 
 -- set inmoney = isnull(a.inmoney,0)+isnull(b.inmoney,0) ,indigit = isnull(a.indigit,0)+isnull(b.indigit,0),outdigit = isnull(a.outdigit,0)+isnull(b.outdigit,0)
  set inmoney = isnull(a.inmoney,0)+isnull(b.inmoney,0) ,indigit = isnull(b.indigit,0),outdigit =isnull(b.outdigit,0)
		,indigit01=b.indigit01,inmoney01=b.inmoney01
		,indigit02=b.indigit02,inmoney02=b.inmoney02
		,indigit03=b.indigit03
		,inmoney03=b.inmoney03
		,outdigit01=b.outdigit01
		,outdigit02=b.outdigit02
		,outmoney01=b.outmoney01
		,outmoney02=b.outmoney02


  from @table a, 
 (select plantid,stcode,matcode,batchcode,sum(indigit) as indigit, sum(outdigit) as outdigit ,

		--sum(case when dcflag = '+' then isnull(matcost,0) end)  as inmoney,   --小吴 2008-05-08
		sum(case when dcflag = '+' then isnull(sumtotalmoney2,0) end)  as inmoney ,
		sum(case when reasonid in ('096','196') then isnull(indigit,0) else  0 end) as indigit01,
		sum(case when reasonid in ('096','196') then (case when dcflag = '+' then isnull(sumtotalmoney2,0) end) else  0 end) as inmoney01,	

		sum(case when reasonid in ('098') then isnull(indigit,0) else  0 end) as indigit02,
		sum(case when reasonid in ('098') then (case when dcflag = '+' then isnull(matcost,0) end) else  0 end) as inmoney02,	

		sum(case when reasonid in ('401') then isnull(indigit,0) else  0 end) as indigit03,	--立账差异
		sum(case when reasonid in ('401') then (case when dcflag = '+' then isnull(matcost,0) end) else  0 end) as inmoney03,	

		sum(case when reasonid in ('197','097') then isnull(outdigit,0) else  0 end) as outdigit01 , --生产领用
		sum(case when reasonid='198' then isnull(outdigit,0) else  0 end) as outdigit02  ,  --盘亏数量
		sum(case when reasonid='198' then isnull(sumtotalmoney2,0) else 0 end )as outmoney02 ,--盘亏金额

		sum(case when reasonid in ('197','097')  then isnull(sumtotalmoney2,0) else 0 end )as outmoney01 --生产领用金额

  from Istockledgerlog   -- select * from Istockledgerlog where periodid ='2008-10' and dcflag='-'and reasonid ='097'
  where docdate between @beginday and @endday 
	group by plantid,stcode,matcode,batchcode ) b
  where a.plantid = b.plantid and a.stcode = b.stcode and a.matcode = b.matcode and a.batchcode=b.batchcode
--计算所有单据金额 结束


--  select * from  Istockledgerlog 

update @table 
  set 
   indigit = isnull(b.indigit,0),
    inprice = case when isnull(b.indigit,0)=0 then 0 
                     else isnull(b.inmoney,0)/isnull(b.indigit,0) end  ,
	outmoney =b.outmoney

 

  from @table a, 

 (select plantid,stcode,matcode,batchcode,convert(decimal(38,20),sum(indigit)) as indigit, sum(outdigit) as outdigit ,
 
	sum(case when dcflag = '+' then isnull(sumtotalmoney2,0) end)  as inmoney   ,
	sum( case when dcflag = '-' then isnull(sumtotalmoney2,0) end) as outmoney 
  from Istockledgerlog 
  where docdate between @beginday and @endday 
	 -- and formid<>1510                       --当有对账差异时，只计金额不计数量
	  group by plantid,stcode,matcode,batchcode ) b
  where a.plantid = b.plantid and a.stcode = b.stcode and a.matcode = b.matcode and a.batchcode=b.batchcode



---------------------处理开始日期到结束日期的期中 进数--end

 
---------------------处理开始日期到结束日期的期中 出数--start
 
 

 update @table 
  set 
 
      outprice = case when isnull(outdigit,0)=0 then 0 else isnull(outmoney,0)/isnull(outdigit,0) end 


 


 update @table set endstock = isnull(prestock,0)+isnull(indigit,0)-isnull(outdigit,0),
		   endmoney = isnull(premoney,0)+isnull(inmoney,0)-isnull(outmoney,0),
		   endprice = case when (isnull(prestock,0)+isnull(indigit,0)-isnull(outdigit,0))=0 then 0 
                               else (isnull(premoney,0)+isnull(inmoney,0)-isnull(outmoney,0))/(isnull(prestock,0)+isnull(indigit,0)-isnull(outdigit,0)) end
  from @table 
-----------------------------------------------------------------------------------






-------删除没有数据的记录 2008-07-12
 
 if isnull(@isactive,'')='否'
 begin
 
	delete from @table where     isnull(prestock,0)=0 and isnull(premoney,0)=0 and isnull(indigit,0)=0 and isnull(inmoney ,0)=0 
			 and isnull(outdigit,0)=0 and isnull(outmoney,0)=0 and isnull(endstock,0)=0 and isnull(endmoney,0)=0
 end  

#13

写法很规范，我就不明白有什么问题，有代码有问题的请看看，说不要轻易比较的也请看看，不清楚看我说什么的也请看看，

#14

My GOD!

#15

顶上去

#16

留名

#17

好复杂呀。。代码那么多

#18

你这个配置还是跑sql2000吧

#19

在查询分析器中能否执行,
将ado连接的CommandTimeout属性加大试试

#20

http://hi.baidu.com/tangdecai/blog/item/899a8713706b97d6f7039ead.html

2005的性能！

#21

我是想问，是配置问题，还是其他问题呢？ 

#22

where 里面太多or 了，建议优化一下

#23

sql2005与 xml 交换估计更强了

#24

高手快出手吧！！！

#25

我感觉SQLSERVER2005性能高很多。
同样的数据库，我在2000里运行和2005里运行，感觉2005快了很多。

#26

如果是运行简单的select 语句，确实，SQL2005比SQL2000快很多很多。但复杂的函数，缺不一样

#27

请问高姓大名？怎么用了我们公司的代码？

#28

引用 27 楼 hayate_zhong 的回复:

请问高姓大名？怎么用了我们公司的代码？

不可能的，都是自己重新再写的。你问的问题怎么这么搞笑呀

#29

还是去研究oracl和mysql吧！呵呵

#30

sql2000与sql2005的本质区别是什么呢？

#31

该回复于2010-12-21 09:06:45被版主删除

#32

这事什么一套啊，需要把复杂的事情简单化

#33

遇到过楼主的情况，其实大家应更为理性的看这问题

我们先前的系统在客户那用SQL SERVER 2000 没问题

后来客户端换了台新的专用服务器，安装的是SQL SEVER 2005

结果，速度慢多了


后面用户无法忍受，我们自己也没法忍受，所以就又用SQL SERVER 2000了

一切又恢复到先前的状态了

SQL SERVER 2005 说真的，速度慢。。。


如果你没实实在在的做过，用过

就不要一位 SQL SEVER 后面的数字越大，就越NB

一切是现实说话

#34

SQL 2005只是增加了对其他应用的支持

至于，性能上有很提升，没感觉到-----之所以这么说

是因为是从实际的系统运行的情况来说的


另外，如果你用过SQL 2000 

你再用SQL 2005  你会很不习惯的

我就是，我另可用SQL 2000也不用SQL 2005

因为再我看来，SQL SEVER 2000的性能不必SQL SERVER 2005的性能低


还举个例子来说

window系统

都是windows 后面的数字越大 ，系统就越稳定

那这么win7不是很成功