Performance problems in SQL Server. Parameter Sniffing or not in my scenario?

Here I am facing a performance problem that occurs with particular statements in a stored procedure, that has many statements, on occasion

SP is executed once a second, normally completes less than 50 ms, but during problem time (and that can happen once a month, or few times per week - randomly) much longer, until recompiled

SP has 2 input parameters

SP is used by different applications

Statements usually complete in ~ 1 ms each, but take longer during problem time

I must admit I have only beginner's understanding of what parameter sniffing is and how to fix it.

Should definitely invest more time in education but hard to do with all things that happen in my country, please don't be too strict on me

One statement that is having problem most frequently:

UPDATE MyTable SET tMax = 0 
      WHERE tMax = 1
      and tID in (SELECT b8 FROM #e538)

During problem time, this update statement has a lot of LCK_M_U waits, and begins to deadlock with exact same statement executed from different sessions

Two other statements:

INSERT #e534 (b4, d4, s4, r4)
SELECT tID, tDate, tStatusID, ID
FROM MyTable
WHERE tStatusID = (SELECT min(tStatusID) 
                    FROM MyTable as f, LMyTable 
                    WHERE tID = MyTable.tID 
                          and tType = 1 
                          and ltID = tStatusID 
                          and ltComplete = 1 AND tActive = 1)
and tID in (select b8 from #e538)
AND tActive = 1

and

INSERT #e534 (b4, d4, s4, r4)
SELECT tID, tDate, tStatusID, ID
FROM MyTable
WHERE (tDate = (SELECT top 1 tDate 
                 FROM MyTable as f 
                 WHERE tID = MyTable.tID 
                 and tType = 1 
                 AND tActive = 1 
                 order by tDate desc))
and tType  = 1
AND tActive = 1
and tID in (select b8 from #e538)

Definitely execution plan is very different when compare "good" vs "bad" plans, I can see that from CXPACKET and CXCONSUMER waits on those statements, wh

How many records are in #e538? The engine turns that into a series of OR clauses, which I've found to be sub-optimal after a few dozen. Depending on other factors, if one of your queries has significantly more entries in the temp table, that could shift the plan to one that works well for that scenario, but not for the other.

You could change the query to be something like this to get more consistent performance.

UPDATE M 
SET tMax = 0
FROM MyTable AS M
    INNER JOIN #e538 AS E ON E.b8 = M.tID

Another option (although I would see if the re-write above resolves your issue) would be to add a hint to prevent parallelism. Just add the last line
after your problematic statement.

UPDATE M 
SET tMax = 0
FROM MyTable AS M
    INNER JOIN #e538 AS E ON E.b8 = M.tID 
OPTION (MAXDOP 1)

StackExchange Database Administrators Q#319996, answer score: 6