SQL Server2005内存大问题

Question

代码如下：
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER procedure [dbo].[p_SaveComputerInfo]
@computerID nvarchar(150),
@xmlContent xml,
@isDel int,
@IsSaveRes int --is keep the exists res
as
DECLARE @idoc int
Declare @ErrorCode int
declare @count int
declare @deptID nvarchar(150)
declare @osno nvarchar(150)
declare @computerNum int
declare @NetID nvarchar(150)
declare @ScanID uniqueidentifier
declare @ScanType nvarchar(150)
declare @InventoryNos nvarchar(2000)
begin
set @ErrorCode=0
-----------------------------------------------------------------------------
EXEC sp_xml_preparedocument @idoc OUTPUT, @xmlContent
-----------------------------------------------------------------------------
--插入电脑主信息
select *
into #Temp
from  OPENXML (@idoc, '/Root/Computer',1)
  WITH( ComputerID nvarchar(150),
ScanID uniqueidentifier,
ScanType int,
ScanTime datetime,
UserName nvarchar(150),
NetID nvarchar(150),
DeptID nvarchar(150),
OSNo nvarchar(150),
RoomNo nvarchar(150),
WorkStationNo nvarchar(150),
GroupBelongs  nvarchar(150),
computernum  int,
computername nvarchar(150),
InventoryNo   nvarchar(2000),
IPAddress   nvarchar(150)   )
select @deptID=DeptID,@computerNum=computernum,@NetID=NetID,@ScanID=ScanID
   ,@ScanType=ScanType,@InventoryNos=InventoryNo,@osno=OSNo
from #Temp
--获取licence

-----------------------------------------------------------------------------
--create table #tComputerid(computerid nvarchar(150),inventoryno nvarchar(150))
select @computerID as computerid,@InventoryNos as inventoryno
into #tComputerid  delete from #tComputerid if @ScanType=2--如果是组扫描
begin

insert into #tComputerid 
select @deptID+Myname+@osno,Myname
from SF_GetTableBySplit(','+@InventoryNos+',',',')

end
else
begin
insert into #tComputerid values (@computerID,@InventoryNos)

end

Answer 1

--提取软件信息到临时表#tSoftware
select *,SoftwareType=-1
into  #tS
FROM   OPENXML (@idoc, '/Root/Products/Product',1)
WITH ( ProductName   nvarchar(150),
  Version       nvarchar(150),
  Publisher     nvarchar(150),
  ProductID     nvarchar(150),
  ProductKey    nvarchar(150),
  HelpLink      nvarchar(150),
  HelpTelephone nvarchar(150),
  InstallDate nvarchar(150),
  RegCompany nvarchar(150),
  RegOwner nvarchar(150),
  Res nvarchar(150)
) T
--定义res临时表，
Create table #tRes( computerID nvarchar(150) collate database_default,
productName nvarchar(300) collate database_default,
version nvarchar(300) collate database_default,
publisher nvarchar(300) collate database_default,
res nvarchar(1000) collate database_default
   )
if @IsSaveRes=1 
begin
insert into #tRes(computerID,productName,version,publisher,res)
select distinct S.computerID,S.productName,S.version,S.publisher,S.res
from Software S,#tComputerid T
where s.computerID = T.computerid
  and (S.res!='' and S.res is not null) 
end
-------删除信息开始
delete from sysinfo where exists 
(
select 1 from #tComputerid t
where sysinfo.computerid = t.computerid
) 
delete from software where exists 
(
select 1 from #tComputerid t
where software.computerid = t.computerid
)
delete from driver where exists 
(
select 1 from #tComputerid t
where driver.computerid = t.computerid
)
delete from computer where exists 
(
select 1 from #tComputerid t
where computer.computerid = t.computerid
)
-------删除信息结束

/*classify software items*/
--licenceType=4(personal licence)
update #tS set SoftwareType = 4
from Licence L 
where #tS.SoftwareType=-1 and #tS.ProductName = L.productName
  and #tS.Version =  L.version
  and #tS.Publisher = L.publisher
  and L.licenceType = 4
  and L.owner = @NetID
--licenceType=5(Drivers/Patches Licence)
update #tS set SoftwareType = 5
from filter F
where #tS.SoftwareType=-1 and #tS.ProductName like F.appsName
  and (#tS.Version like F.appsVersion or F.appsVersion='%')
  and (#tS.Publisher like F.appsPublisher or F.appsPublisher='%')
  and F.isValid = 1 update #tS set SoftwareType = 5
from Licence L 
where #tS.SoftwareType=-1 and #tS.ProductName = L.productName
  and #tS.Version =  L.version
  and #tS.Publisher = L.publisher
  and L.licenceType = 5
--licenceType=1(Departmental Licence)
update #tS set SoftwareType = 1
from Licence L 
where #tS.SoftwareType=-1 and #tS.ProductName = L.productName
  and #tS.Version =  L.version
  and #tS.Publisher = L.publisher
  and L.licenceType = 1
  and L.owner = @deptID
--licenceType=0(总部 Site Licence)
update #tS set SoftwareType = 0
from Licence L 
where #tS.SoftwareType=-1 and #tS.ProductName = L.productName
  and #tS.Version =  L.version
  and #tS.Publisher = L.publisher
  and L.licenceType = 0
--licenceType=3(Freeware)
update #tS set SoftwareType = 3
from Licence L 
where #tS.SoftwareType=-1 and #tS.ProductName = L.productName
  and #tS.Version =  L.version
  and #tS.Publisher = L.publisher
  and L.licenceType = 3
--licenceType=2(shareware)
update #tS set SoftwareType = 2
from Licence L 
where #tS.SoftwareType=-1 and #tS.ProductName = L.productName
  and #tS.Version =  L.version
  and #tS.Publisher = L.publisher
  and L.licenceType = 2

insert into Computer(ComputerID,ScanID,ScanType,ScanTime,UserName,
NetID,DeptID,OSNo,RoomNo,WorkStationNo,
GroupBelongs,InventoryNo,CreateDate,computerName,IPAddress)
select c.computerid,ScanID,ScanType,ScanTime,UserName,
NetID,DeptID,OSNo,RoomNo,WorkStationNo,
GroupBelongs,c.inventoryno,getdate(),computername,IPAddress
from #Temp,#tComputerid c
-----------------------------------------------------------------------------


--将没被过滤的软件按licence分类，存到software表
insert into software(softwareID,ProductName,Version,Publisher,ProductID,
 ProductKey,HelpLink,HelpTelephone,installDate,RegCompany,
 RegOwner,SoftwareType,CreateDate,computerid,
 computerNum,deptID,userID,scanID,Res) select T1.*,Res=isnull(R.res,'')
from
(SELECT   softwareID=newid(),T.ProductName,T.Version,T.Publisher,ProductID,
   ProductKey,HelpLink,HelpTelephone,InstallDate,RegCompany,
   RegOwner,SoftwareType,
   CreateDate=getdate(),c.computerid,
   computerNum=@computerNum,deptID=@deptID,userID=@NetID,scanID=@ScanID
 FROM   #tS T,#tComputerid c
 ) as T1 left join #tRes R on    T1.computerid = R.computerID 
   and T1.ProductName = R.productName
   and T1.Version  = R.version
   and T1.Publisher  = R.Publisher
-----------------------------------------------------------------------------
--wirte by lih 提取驱动程序信息
insert into driver(driverID,drivername,version,publisher,driverdate,driverclass
,createDate,res,computerID,deptID,userID,scanID)
select newid(),t.DN,t.DV,t.DP,t.DD,t.DC,getdate(),t.M
,c.computerid,@deptID,@NetID,@ScanID
from openxml (@idoc,'/Root/Devices/Device',1)
 with (
DN nvarchar(150),
DV nvarchar(150),
DP nvarchar(150),
DD nvarchar(150),
DC nvarchar(150),
M nvarchar(150)
  ) t,#tComputerid c -----------------------------------------------------------------------------
--插入SysInfo信息
SELECT   identity(int,1,1) rownum,  localname
into #tmp
FROM       OPENXML (@idoc, '/Root/',1)
where nodetype=1 and parentid=0
 
SELECT identity(int,1,1) rownum, T.c.query('.') AS result
into #tmp2
FROM   @xmlContent.nodes('/Root/*') T(c)

insert into SysInfo
select newid(),T1.localname,T2.result,'',c.computerid 
from #tmp T1,#tmp2 T2,#tComputerid c
where T1.rownum = T2.rownum and localname not in ('Products','Devices')

drop table #tmp
drop table #tmp2
drop table #Temp
drop table #tComputerid
exec sp_xml_removedocument @idoc
ClearUP:
return @ErrorCode
end

Answer 2

大致看了一下，没看完，太长了， ，回头再看看，先提点建议1、多加内存2、#tmp 过程临时表最好用表变量 @tmp 代替，
   你里面有很多插入临时表的操作，这个操作是比较耗费 sql 资源的

Answer 3

我也没看完呵呵，
1、注意  select into 
2、适当加大tempdb库

Answer 4

没有看完,大致也看了一下需求,
建议.
1.使用表变量替代临时表,
2.对表software进行优化,进行分区,在查询列上建立索引?
3.修改sql server 的配置,加大内存参数.

Answer 5

用profiler跟踪sql,找出耗时的t-sql，进行优化。