只有当我重新启动SQL Server数据库服务器时，存储过程才能工作

Question

在我公司的一个基于C#、ASP.Net、SQL server 2005和Windows server 2008的内部项目中，当有人尝试登录时，登录存储过程只需要花费无限长的时间。但当我重新启动数据库服务器时，问题就解决了。这种情况每天都会发生。这可能不是数据库加载问题，因为其他存储过程可以正常工作。此外，如果我在存储过程中提取查询，我会在适当的时间得到结果。在存储过程中，除了获取数据外，还有一个if条件，它将用户限制为仅限于某些IP。

我不知道问题出在哪里。有人能帮我解决这个问题吗。提前谢谢。

这是存储过程

ALTER PROCEDURE [dbo].[get_user]          
@username varchar(50), @pwd varchar(50), @passcode varchar(50), @ipaddress varchar(15)          
AS          
BEGIN          
 IF(@ipaddress = 'aaa.aaa.aaa.aaa' OR @ipaddress = 'aaa.aaa.aaa.aaa' OR @ipaddress = 'aaa.aaa.aaa.aaa')          
 BEGIN          
  IF EXISTS(select up_id from tbl_user_profile (nolock) WHERE up_status='Y')          
  BEGIN          
   DECLARE @ri_date_time varchar(20)          
   SET @ri_date_time=convert(varchar(50),getdate(),120)          
   DECLARE @user_exists as int          
   Set @user_exists = 0          
   Set @user_exists = (Select count(*) from tbl_recent_items (nolock) where ri_user_name=@username)          
   IF @user_exists = 0          
   BEGIN          
    BEGIN TRAN          
     INSERT INTO tbl_recent_items (ri_user_name, ri_access_date_time) values (@username, @ri_date_time+'|'+@ipaddress+'^')          
     IF @@error=0 COMMIT TRAN ELSE ROLLBACK TRAN          
   END          
   ELSE          
   BEGIN          
    DECLARE @datetext varchar(max)          
    SET @datetext = (select isnull(ri_access_date_time,'') from tbl_recent_items(nolock) where  ri_user_name=@username)          
    SET @datetext=@ri_date_time+'|'+@ipaddress+'^'+@datetext          
    BEGIN TRAN          
     UPDATE tbl_recent_items Set ri_access_date_time=@datetext where ri_user_name = @username          
     IF @@error=0 COMMIT TRAN ELSE ROLLBACK TRAN          
   END          
  END          
  SELECT CONVERT(varchar(4),up_id) as up_id,LTRIM(RTRIM(isnull(up_user_name,''))) as u_user_name,           
  ISNULL(up_password,'') as u_user_password,isnull(up_first_name,'') as up_first_name,           
  ISNULL(up_middle_name,'') as up_middle_name, isnull(up_last_name,'') as up_last_name,           
  ISNULL(up_branch_id,'') as up_branch_id, isnull(b_branch_id,'') as b_branch_id,          
  ISNULL(b_branch_name,'') as b_branch_name,isnull(up_mobile_phone,'') as up_mobile_phone,          
  --ISNULL(up_pager_phone,'') as up_pager_phone,  
  isnull(up_email,'') as up_email,          
  ISNULL(up_status,'') as u_status,   
  --isnull(up_job_posting_phone,'') as up_job_posting_phone,          
--  ISNULL(up_level,'') as up_level, 
    isnull(up_trainee,'0') as up_trainee,          
  ISNULL(up_senior,'0') as up_senior, isnull(up_team_lead,'0') as up_team_lead, isnull(up_ist,'0')as up_ist, up_hyd_branch,    
  convert(varchar(10),up_password_date,102)as up_password_date    
  FROM tbl_user_profile (nolock)           
  LEFT OUTER JOIN tbl_branches (nolock) on up_branch_id = b_id where           
  up_user_name = @username and up_password =@pwd          
 END          
 IF EXISTS(select up_id from tbl_user_profile (nolock) WHERE up_user_name = @username and up_password = @pwd and up_passcode = @passcode and up_status='Y')          
 BEGIN          
  IF EXISTS(select up_id from tbl_user_profile (nolock) WHERE up_status='Y')          
  BEGIN          
   DECLARE @ri_date_time1 varchar(20)          
   SET @ri_date_time1=convert(varchar(50),getdate(),120)          
   DECLARE @user_exists1 as int          
   Set @user_exists1 = 0          
   Set @user_exists1 = (Select count(*) from tbl_recent_items (nolock) where ri_user_name=@username)          
   IF @user_exists1 = 0          
   BEGIN          
    BEGIN TRAN          
     INSERT INTO tbl_recent_items (ri_user_name, ri_access_date_time) values (@username, @ri_date_time1+'|'+@ipaddress+'^')          
     IF @@error=0 COMMIT TRAN ELSE ROLLBACK TRAN          
   END          
   ELSE          
   BEGIN          
    DECLARE @datetext1 varchar(max)          
    SET @datetext1 = (select isnull(ri_access_date_time,'') from tbl_recent_items(nolock) where  ri_user_name=@username)          
    SET @datetext1=@ri_date_time1+'|'+@ipaddress+'^'+@datetext1          
    BEGIN TRAN          
     UPDATE tbl_recent_items Set ri_access_date_time=@datetext1 where ri_user_name = @username          
     IF @@error=0 COMMIT TRAN ELSE ROLLBACK TRAN          
   END          
  END          
  SELECT CONVERT(varchar(4),up_id) as up_id,LTRIM(RTRIM(isnull(up_user_name,''))) as u_user_name,           
  ISNULL(up_password,'') as u_user_password,isnull(up_first_name,'') as up_first_name,           
  ISNULL(up_middle_name,'') as up_middle_name, isnull(up_last_name,'') as up_last_name,           
  ISNULL(up_branch_id,'') as up_branch_id, isnull(b_branch_id,'') as b_branch_id,          
  ISNULL(b_branch_name,'') as b_branch_name,isnull(up_mobile_phone,'') as up_mobile_phone,          
  --ISNULL(up_pager_phone,'') as up_pager_phone,  
  isnull(up_email,'') as up_email,          
  ISNULL(up_status,'') as u_status,  
   --isnull(up_job_posting_phone,'') as up_job_posting_phone,          
--  ISNULL(up_level,'') as up_level, 
 isnull(up_trainee,'0') as up_trainee,          
  ISNULL(up_senior,'0') as up_senior, isnull(up_team_lead,'0') as up_team_lead,isnull(up_ist,'0')as up_ist, up_hyd_branch,    
  convert(varchar(10),up_password_date,102)as up_password_date    
  FROM tbl_user_profile (nolock)           
  LEFT OUTER JOIN tbl_branches (nolock) on up_branch_id = b_id where           
  up_user_name = @username and up_password =@pwd          
 END          
END

Answer 1

听起来您为该过程缓存了一个糟糕的执行计划。很难说没有看到代码，但参数嗅探可能是一个问题。一个可能的解决方案是使用WITH RECOMPILE选项。这将强制存储过程每次重新编译并生成新的执行计划。

CREATE PROCEDURE YourLoginProc
WITH RECOMPILE
AS
...