Stored procedure sometimes takes a long time to execute query in SQL Server

Question

I have strange issue and I am getting more confused about this. I am using UWP on the .NET framework and using SQL Server as database. The problem is my application working too much slow and when I check my stored procedure execution time then I am notice that may be a problem is from the database stored procedure side.

My stored procedure sometimes takes 4-5 seconds to execute and sometimes takes more like 15-16 seconds and sometimes it takes more than 1 minute.

My questions are:

1. Why is it taking a lot more time sometimes, but not every time?

2. How can I solve this issue and boost my application execution time?

This is the stored procedure:

ALTER PROCEDURE [dbo].[SPNAME]  
    @HouseID INT,      
    @FromDate DATETIME,
    @ToDate DATETIME,   
    @PageNumber INT,
    @PageSize INT, 
    @RollID INT   
AS 
BEGIN
    DECLARE @RoomID INT = 0
    DECLARE @UnitID INT = 0
    DECLARE @HouseCode INT = 0
    DECLARE @StartRow INT
    DECLARE @EndRow INT

    SET @StartRow = @PageNumber 
    SET @StartRow = ((@StartRow-1) * @PageSize) + 1
    SET @EndRow = (@StartRow + @PageSize) - 1; 

    IF OBJECT_ID('tempdb.dbo.#TableName') IS NOT NULL 
       DROP TABLE dbo.#TableName     

    SELECT 
        @HouseCode = [HouseCode],
        @UnitID = UnitId 
    FROM 
        [dbo].[tblHouse] 
    WHERE 
        [HouseId] = @HouseID   

    SELECT @RoomID = RoomCode 
    FROM [dbo].[tblUnit] u
    LEFT JOIN tblRoom h ON u.RoomID = h.Room 
    WHERE UnitID = @UnitID

    SELECT MAX(RoNum) RoNum 
    INTO dbo.#tableName 
    FROM [dbo].[TAbleNAme] 
    WHERE FanId = @HouseCode 
      AND InActive = 0
      AND ((InactivatedOn IS NULL AND StopDate IS NULL) 
           OR (ISNULL(InactivatedOn, StopDate) BETWEEN @FromDate AND @ToDate) 
           OR (ISNULL(InactivatedOn,StopDate) >= @ToDate)) 
      AND (FirstFillDate <= @ToDate)       
  GROUP BY 
      OrigRoNum

  SELECT 
      K.Topic, K.Comment, K.RoNum 
  INTO
      #TmptblRoCom 
  FROM 
      [dbo].[TableNAme] K
  LEFT JOIN 
      #TableName T ON K.RoNum = T.RoNum 
  WHERE 
      K.Topic = 'Nar Auto' 
      AND ISNUMERIC(SUBSTRING(K.Comment, 0, 3)) = 1 
      
  SELECT ButtonTypeID, ISNULL(SortOrder, 17) AS SortOrder 
  INTO dbo.#TableName
  FROM [dbo].[TAbleName] 
  WHERE NHID = @RoomID AND NHSortID = 29

  SELECT 
      DIN,
      'LU: ' + LTRIM(RTRIM(ReasonCode)) + 
      'Exp:' + ISNULL(FORMAT(ExpiryDate, 'dd/MM/yyyy'), 'Indefinite') AS HLCode 
  INTO
      dbo.#TableName
  FROM
      [dbo].[TableNAme] 
  WHERE 
      PatID = @HouseCode 
      AND (ExpiryDate IS NULL OR ExpiryDate > GETDATE())

  SELECT 
      X.DgID AS DumbID,
      X.ID AS RID,
      CASE 
          WHEN DR.BandName IS NULL
              THEN '@' + dm.Description 
          ELSE CONCAT(BandName, ' ', strength) 
      END AS DugName,
      StrengthType, FormType,
      CASE 
          WHEN GericName IS NULL
              THEN
                  CASE 
                      WHEN BandName IS NULL 
                          THEN '@' + dm.Description 
                          ELSE CONCAT(DR.BandName, ' ', DR.strength) 
                  END 
              ELSE CONCAT(DR.GericName, ' ', DR.strengthTypr) 
      END AS GericName,
      DR.[Description],
      dbo.GetDirectionWithFullForm (X.IG,' ') AS Direction,
      CAST(X.RoNum AS BIGINT) AS RoNum,
      FORMAT(X.FirstFillDate,'dd/MM/yyyy') AS FirstFillDate,
      FORMAT(X.LastFillDate,'dd/MM/yyyy') AS DugOrderDate,
      ISNULL(TP.[ID],0) AS RollID,
      TP.[Cont] AS Cont,
      TP.[DisContinue] AS Disc,
      TP.[Hold] AS Hold,
      CAST(m.OrderType AS INT) AS OrderType,
      pl.HLCode,
      rc.Comment,
      TP.[Comments]
  FROM 
      dbo.#TAbleNAme  rn 
  INNER JOIN 
      [dbo].[RollRx] X ON rn.RoNum = X.RoNum           
  INNER JOIN 
      [dbo].[RollDg] DR ON X.[DgID] = DR.ID      
  LEFT JOIN 
      [dbo].[RollDgMix] DM ON X.MixID = DM.ID             
  LEFT JOIN 
      [dbo].[RollPreviousNumber] TP ON X.DrgID = TP.[RollMedicationID] 
                                    AND X.[ID] = TP.[RoID] 
                                    AND TP.[HouseID] = @HouseID 
                                    AND TP.[TableName] = @RollID
  LEFT JOIN 
      dbo.#TableNAme m ON m.TypeID = X.TypeID 
  LEFT JOIN 
      dbo.#TableName pl ON pl.DNNUM = X.DNNUM
  LEFT JOIN 
      dbo.#TableName rc ON rc.RoNum = X.RoNum
END

Execution Plan

Answer 1

Yes sounds like parameter sniffing or lack of memory issue (this has made it block for me at times when I am running to much on my box), look at the execution plan.

Run the stored proc with your parameters and see if there is a difference here, by right clicking on the execution plan select, below shows an example.