3

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, 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:

  1. 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 ?

  2. 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 ?

  3. 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 ?

3
  • 1
    Hard to get into specifics without seeing the execution plans, but, no this doesn't look like parameter sniffing. That occurs when you're using parameters. None of the queries in question do. I agree with the answer below. A JOIN is likely to perform better. Nov 21, 2022 at 13:52
  • @GrantFritchey will try to get good plan vs bad Nov 21, 2022 at 13:59
  • To prove parameter sniffing, set up a test. EXEC sp_recompile 'mysp' GO --Generate plan EXEC mysp ParmSet1 --EXEC mysp ParmSet2 GO --Do test EXEC mysp ParmSet1 EXEC mysp ParmSet2 EXEC mysp ParmSet3 EXEC mysp ParmSet4 And then flip which ONE set of parameters is used by the "Generate Plan" step. Note: DECLARE <at>myvar TABLE ... INSERT INTO <at>myvar is perfectly valid for setting up a parameter set. Test values with LOTS of rows against values with very few rows against values with no rows. Nov 21, 2022 at 14:31

1 Answer 1

6

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)
2
  • How many records are in #e538? - can't answer that at this moment, going to implement logging into table so count of records will be logged Nov 21, 2022 at 13:36
  • 1
    Not sure I'd suggest charging in with the query hints just yet. Better to get good evidence that we are seeing parallelism, and, that the parallelism hurts. Otherwise, great answer, upvoted. Nov 21, 2022 at 13:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.