我们服务器每天都会有一段时间比较卡，从活动监视器看有200个进程，大部分都是“可运行”、“已挂起”，这是不是跟我的存储过程写法有关。请高手们指导我一下，帮我优化一下看看。谢谢。
花点时间和精力帮我看下吧。谢谢
另外，我想问问看，如果启用事务，会不会减少服务器运行SP时消耗的资源？？SP1：
- C# code
alter proc [dbo].[sp_bad]( @d varchar(5), @p varchar(50), @im varchar(20))asbegin declare @c int,@time1 datetime,@end int declare @pros varchar(200) set @pros=',aaa,bbb,ccc,ddd,eee,fff,' set @c=0 select top 1 @c=cc,@time1=time1 from t_badToday where [email protected] if @c=0 begin insert into t_badToday(d,p,im) values(@d,@p,@im) if charindex(',[email protected]+',',@pros)>0 exec [sp_yun] @d,@p,@im,'','1' --调用另一SP else select 0 return end else begin if @c>5 begin select 1 return end else begin update t_badToday set cc=cc+1 where [email protected] if datediff(d,@time1,getdate())=0 begin select 0 return end else begin if charindex(',[email protected]+',',@pros)>0 exec [sp_yun] @d,@p,@im,'','1' --调用另一SP else select 0 return end end end end
SP2：
- C# code
alter proc [dbo].[sp_yun] ( @d nvarchar(5), @p nvarchar(30), @im nvarchar(50), @ip nvarchar(20), @type nvarchar(2) ) as begin declare @flag_a nvarchar(10) declare @flag_c nvarchar(500) declare @count int declare @sign nvarchar(10)declare @waketxt nvarchar(1000) declare @diff int declare @createdate datetime declare @flag_url nvarchar(100) declare @version nvarchar(10)declare @times intdeclare @needdown bit,@times2 int,@mo varchar(20)set @flag_a='' set @flag_c=''set @count=0 set @sign=''set @waketxt=''set @diff=0set @flag_url=''set @version=''set @times=-1 set @needdown=0set @times2=0if(@type='1') -- get url begin set @mo='' set @flag_a='' set @sign='old' set @needdown=null select top 1 @mo=mo,@needdown=needdown,@flag_a=pl,@version=version,@times=times1,@times2=times2 from t_yun where [email protected] if @mo='' or @mo is null begin--插入记录 set @mo='' select @mo=mo,@flag_a=lo,@createdate=chutime from t_chu where [email protected] and [email protected] and [email protected] if @mo='' or @mo is null return if exists(select top 1 1 from t_sp where [email protected] or [email protected]) return select top 1 @version=substring(version,2,3) from t_pro where [email protected] and [email protected] if left(@version,1)='2' set @version='2.0' insert into t_yun(d,p,im,mo,pl,version,times1,times2,intime,chtime,looptime,needdown,autotime,downtimes) values(@d,@p,@im,@mo,@flag_a,@version,8,1,getdate(),@createdate,getdate(),1,getdate(),1) set @needdown=1 set @sign='new' end if @version='2.0' begin set @needdown=0 if (@times is null or @times<1 or @times2 is null or @times2<1) and @sign='old' set @needdown=0 else begin if exists(select top 1 1 from t_code where isvalid=1 and [email protected]_a) begin set @needdown=1 --允许下载 end end end if @needdown is null begin select 0 return end else if @needdown=1 begin if @version='2.0' begin select 2 return end else begin select top 1 @flag_url=url from t_auto_url where id<14 and id>1 order by newid() if(@flag_url is null or @flag_url='') begin select 0 return end else begin if @sign='old' and left(@p,4)!='d001' begin update t_yun set needdown=0,autotime=getdate(),downtimes=downtimes+1 where [email protected] end select @flag_url end end end else begin select 0 return endendend