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
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
CXCONSUMER waits on those statements, when normally there aren't any
Unfortunately I do not have "bad" execution plan as monitoring tool says "No query plan sampled for this query."
MyTable has 225+ million rows, tID is clustered index key (BIGINT, although not unique)
Here are my questions:
Stored procedure has input parameter, yes, BUT these particular statements do not have parameters, they use temp table instead
Is this parameter sniffing in my scenario, or something else ?
Apparently it does Index Scan on MyTable during problem time, instead of using seek on Clustered index (tID) as it mostly does. Why it can be ?
What should be my further steps to investigate and fix this issue ? I am about to enable Query Store and implement logging to table from this SP, but what else ? and what to look at in Query Store ?