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

3 votes

1 answer

498 views

sql-server performance stored-procedures sql-server-2017 parameter-sniffing

                          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 ?


                        

Asked by Aleksey Vitsko (6195 rep)

Nov 21, 2022, 12:52 PM
Last activity: Nov 23, 2022, 10:21 PM

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

Related Questions