SP Slow in Application Fast in SSMS

 In SQL Server 2005, 2008, 2012,  SP Slow in Application Fast in SSMS: Once I had this issue with my job running in the morning 6:00 AM, which was processing some 20 millions records daily within 10 mins. Initially everything was ok, but some days later, I found that the job was running for over 5 hrs. and was creating issues to all other Online Applications.

I googled and found that it can be removed by setting the arithabort property ON.

To set the arithabort property ON, Just give a set command as below :

 



CREATE PROC usp_ProcedureName

       @UserID    VARCHAR(100)

      ,@FromDate  DATETIME

      ,@ToDate    DATETIME

AS

BEGIN

SET NOCOUNT ON

SET ARITHABORT ON



      SELECT  UserID,
UserName,..

      FROM  UserDetails (NOLOCK)

      WHERE TransDate BETWEEN @FromDate AND @ToDate

            

SET ARITHABORT OFF --- If you want to keep it off.

SET NOCOUNT OFF

END

   But, if you are a Developer and not a DBA and don't want to interfere with the SERVER properties; then Make a Variable Sniffing, that means take the parameters value into the local variables as below :

CREATE PROC usp_ProcedureName

       @UserID    VARCHAR(100)

      ,@FromDate  DATETIME

      ,@ToDate    DATETIME

AS

BEGIN

SET NOCOUNT ON

      DECALRE @FromDT   DATETIME, @ToDT   DATETIME

      SET @FromDT = @FromDate

      SET @ToDT   = @ToDate



      SELECT  UserID,
UserName,..

      FROM  UserDetails (NOLOCK)

      WHERE TransDate BETWEEN @FromDT AND @ToDT

            

SET NOCOUNT OFF

END

and here you are done...


Reference: Govind Badkur(http://govindbadkur.blogspot.com)