SQL Server job gets stuck occasionally

Question

I found my SQL Server job gets stuck occasionally, about once every two months. Since I am not from DBA background, I need some to help to rectify the issue.

So far, I have tried to pinpoint the issue by checking the activity monitor. I found the issue is caused by one of my stored procedures which it will create a temp table to collect data, then the data will be inserted into one of my transaction tables. This table have 400 millions of records.

Whenever this issue occur, I stop the job and:

I rerun the job, the stored procedure can complete
I execute the stored procedure manually, the stored procedure completes

I implemented the SP_BlitzCache, and execute it. I can see it suggest DBCC FREEPROCCACHE (0x0...) on the stored procedure.

CREATE TABLE #dtResult  
( 
    RunningNumber INTEGER,
    , AlphaID BIGINT 
    , BetaID BIGINT
    , Content varchar(100)
    , X varchar(10)
    , Y varchar(10)
) 
 
INSERT INTO #dtResult ( RunningNumber, ...) 
    SELECT RowId AS RunningNumber, 
    ...
    FROM 
    ...


/*** Based on activity monitor, the highest CPU caused by this statement ***/
INSERT INTO tblTransaction ( ... ) 
    SELECT DISTINCT  
        RES.AlphaID
        , b.UnitId
        , RES.BetaID
        , CASE WHEN RES.BinData IS NULL THEN [dbo].[fnGetCode](B.Data, RES.X, RES.Y) ELSE RES.Content END 
        , CONVERT(DATETIME, SUBSTRING(RES.Timestamp, 1, 4) + '-' + SUBSTRING(RES.Timestamp, 5, 2) + '-' + SUBSTRING(RES.Timestamp, 7, 2)  + ' ' + SUBSTRING(RES.Timestamp, 9, 2)  + ':' + SUBSTRING(RES.Timestamp, 11, 2)  + ':' + SUBSTRING(RES.Timestamp, 13, 2)  + '.' + SUBSTRING(RES.Timestamp, 15, 3), 121) 
    FROM 
        #dtResult RES  
    INNER JOIN 
        tblA a with(nolock) ON RES.AlphaID = a.AlphaID  
    INNER JOIN 
        tblB b with(nolock) ON a.UnitId = b.UnitId AND CAST(RES.X AS INTEGER) = b.X AND CAST(RES.Y AS INTEGER) = b.Y 
    INNER JOIN 
        tblC c with(nolock) ON RES.BetaID = c.BetaID 
    LEFT OUTER JOIN 
        tblTransaction t with(nolock) ON RES.AlphaID = t.AlphaID AND RES.BetaID = t.BetaID AND t.UnitId = b.UnitId
    WHERE 
        t.BetaID IS NULL


/* FUNCTION */
CREATE FUNCTION [dbo].[fnGetCode]
(
    @Data   VARCHAR(MAX),
    @SearchX    INT,
    @SearchY    INT
)
RETURNS CHAR(4) 
WITH ENCRYPTION 
AS
BEGIN
    DECLARE @SearchResult CHAR(4)
    DECLARE @Pos INT
    SET @Pos = (@SearchY * @SearchX) + 1
    SET @SearchResult = CONVERT(char(1),SUBSTRING(@Data,@Pos,1), 1) 
    RETURN @SearchResult
END

Try inlining the fuction `[dbo].[fnGetCode]` it will drastically improve performance, instead of calling a function. Also `with(nolock)` might also be an issue, see [this question](https://stackoverflow.com/questions/1452996/is-the-nolock-sql-server-hint-bad-practice) — Preben Huybrechts, Sep 17 '20 at 04:46
In general it is hard to debug an issue that happens infrequently. There could be many causes of poor performance. The Highest CPU statement is not surprising - UDF, substrings and also CAST in your tblB b JOIN can produce high CPU. High CPU by itself does not mean much. If I had a job of debugging it here is what I would ask: How often does the job run (hourly or monthly)? how long does it usually take to complete? When it gets "stuck" does it ever finish or has to be stopped? Does it get stuck on a specific param, data? When run manually, is duration same as the job? These details would help — MojoDBA, Sep 17 '20 at 12:52

SQL Server job gets stuck occasionally

0 Answers0