There is a function that is used by many people and sometimes it is terribly dull.

If I create a copy of this function and execute it exclusively for myself in the request where the original function fails, the request is executed instantly, and if it’s 2 minutes from the old one.

What could be the problem?

Does MS SQL have any object level locks?

Query plans are built identical.

Inside the function, all sorts of connections with tables are executed, and so on.

Could it be that the old function used some old statistics on the tables, and the new function received updated statistics and works faster?

UPD

It looks like it's not statistics. If I change the functions in places, then my newly compiled procedure starts to blunt, and the old procedure works fine ...

  • one
    Resetting Plan Cache - DBCC FREEPROCCACHE - helps? - PashaPash ♦
  • 2
    "If you swap procedures in places, then my just-compiled procedure starts to blunt, and the old procedure works fine," then it may be just caching, the first one honestly works, and the second uses a cache. - i-one
  • question - what does swap mean? what is changing? may be the value of the parameters, the use (or not) of the optional parameters? or for example, has the data set changed in the tables used by the function? Or there may be a difference in the external request, which possibly uses the call of this function - ale
  • There was Func_1, which slowed down and was used by a large number of people. I decided to make Func_2 (The structure is similar) and execute in the query where Func_1 braked and the result returned instantly. Then I renamed Func_2 to Func_1, and Func_1 to Func_2. And again began tomroza, since a lot of people began to use the function. Ie it seems that the matter is not in statistics - iluxa1810
  • one
    and besides the name of the functions something else changes? It seems that it is a matter of frequency of use, for example, the blocking of a table when trying to write there from simultaneous calls of this function by different clients. When customers rename the compartment - the problem is 'gone' - ale

1 answer 1

It’s unlikely that you can quickly answer your question, I would recommend reading a useful article about the reasons for this slowdown - if you regularly work with MS SQL, this can be very useful and informative:

http://www.somewheresomehow.ru/fast-in-ssms-slow-in-app-part1/