issue with locking of database

Question

We are having huge problem with our database. We are using SQL Server 2014 with 2008 compatibility .

Every morning we are getting database locking issues and database uses 100% cpu. So what we do to fix this every morning is to modify below stored procedure and switch between adding and removing NoLock from statements and run procedure again and database is happy. So we are not sure why we sometimes need to add NoLock and sometimes don't need to add.

ALTER Procedure [dbo].[ScanBox]
(
@KollieID  varchar(50) = '',
@SupplierID int = 0,
@BuyerID int = 0
)
As
Set NoCount On

IF @kollieid <> '' AND @supplierid >0 
BEGIN

    SELECT TRPO_KollieID.ID, OrderID, ISNULL(BoxNo, - 1) AS BoxNo, ISNULL(KollieID, '') AS KollieID, KollieNumber,
           ISNULL(LastStatus, - 1) AS LastStatus, LastStatusTime, ISNULL(LastStatusPDA, - 1) AS LastStatusPDA, 
           ISNULL(LastStatusTDLogin, '') AS LastStatusTDLogin, ISNULL(OrginalKollieID,'') AS OrginalKollieID,
           ISNULL(OS.StatusName, '') AS LastStatusText , ISNULL(OrderStatusExternalText,'') AS OrderStatusExternalText ,
           ISNULL(Terminal, '') AS Terminal, ISNULL(TerminalZone,'') AS TerminalZone
    FROM   TRPO_KollieID 
    WITH (NOLOCK)
           LEFT OUTER JOIN OrderStatus os WITH (NOLOCK) on OS.StatusID = TRPO_KollieID.LastStatus       
    WHERE  TRPO_KollieID.KollieID = @KollieID AND (OrderID IN (SELECT  TRPO_Header.ID  
                      FROM TRPO_Header  WITH (NOLOCK)
                           INNER JOIN TRPO_KollieID WITH (NOLOCK) ON TRPO_KollieID.OrderID = TRPO_Header.ID
                      WHERE TRPO_Header.SupplierID = @supplierid AND TRPO_Header.Status <> 'A' AND TRPO_KollieID.KollieID = @kollieid))
END
ELSE IF @kollieid <> '' AND @BuyerID >0 
BEGIN

    SELECT TRPO_KollieID.ID, OrderID, ISNULL(BoxNo, - 1) AS BoxNo, ISNULL(KollieID, '') AS KollieID, KollieNumber,
           ISNULL(LastStatus, - 1) AS LastStatus, LastStatusTime, ISNULL(LastStatusPDA, - 1) AS LastStatusPDA, 
           ISNULL(LastStatusTDLogin, '') AS LastStatusTDLogin, ISNULL(OrginalKollieID,'') AS OrginalKollieID,
           ISNULL(OS.StatusName, '') AS LastStatusText , ISNULL(OrderStatusExternalText,'') AS OrderStatusExternalText ,
           ISNULL(Terminal, '') AS Terminal, ISNULL(TerminalZone,'') AS TerminalZone
    FROM   TRPO_KollieID  
    WITH (NOLOCK)
           LEFT OUTER JOIN OrderStatus os WITH (NOLOCK) on OS.StatusID = TRPO_KollieID.LastStatus 
    WHERE  TRPO_KollieID.KollieID = @KollieID AND OrderID IN (SELECT  TRPO_Header.ID  
                      FROM TRPO_Header WITH (NOLOCK)
                           INNER JOIN TRPO_KollieID  WITH (NOLOCK) ON TRPO_KollieID.OrderID = TRPO_Header.ID
                      WHERE TRPO_Header.BuyerID = @BuyerID AND TRPO_Header.Status <> 'A' AND TRPO_KollieID.KollieID = @kollieid)    



END

What we have tried is

1. we tried to index database.
2. Tried to set isolationlevel to read committed snapshot

Nothing is helping us. Does anyone has very good idea to fix this?

Answer 1

recompilation helps you and not nolock. Try add OPTION(RECOMPILE) OR OPTION (OPTIMIZE)... Or get query paln