none
Query sometimes is very slow under the same conditions RRS feed

  • Question

  • So I have a perplexing problem here. There is a query in my project that in 99 percent of time takes 200 ms to complete on my local machine. However, sometimes under the same circumstances it takes 25 seconds. It is happening for a while and then problem goes away.


    I have SQL Server 2019 Development Edition installed and this happens on average once per day during normal usage of the project.


    I had SQLEXPRESS 2019 installed before and the same problem was occurring once per hour. So I installed Dev Edition to use SQL Profiler and track the issue and for a while I was thinking that it was solved, but then it started happening again, just not so often.


    Neither blocking nor dead locking reports in SQL Profiler showed anything when the query was struggling. At least by studying the traces I have confirmed execution times and made sure the problem was happening on SQL Server side and not in other places (desktop client or web service). I have analyzed execution plans, and they are to me they are identical for both normal and slow execution.


    The query is calling SP_EXECUTESQL with large text command passed to it and runs basically a bunch of joins for search purposes.


    I have run out of ideas what might cause this. I tried to run hundreds of such queries in parallel mode but failed to reproduce this situation. It just happens out of the blue on its own.


    What might cause this? What other steps I can do to track the issue?


    Thanks!


    Friday, January 17, 2020 8:19 AM

All replies

  • Have you thought of turning on Query Store?
    Friday, January 17, 2020 7:31 PM
  • Didn't know about it. Seems like a good idea. It looks like maybe a set of queries that happen before might cause the problem.

    Still interesting that when query takes a long time it is always almost exactly 25 seconds. Looks like timeout is happening somewhere inside SQL Server. But for what reason?

    Also, I started to chip away pieces of this query to narrow down the cause and couldn't find a pattern. It starts executing normally when I remove ORDER BY and when I remove some columns from SELECT list, also when I remove AND statement. It seems like when the "shape" of query changes, SQL Server executes it differently.

    Also, this period when query takes 25 seconds is limited. When I do experimenting it is around 10 executions, then it runs normally.

    Saturday, January 18, 2020 6:09 AM
  • It could be interesting see the execution plan and io statistics to get an idea of the amount of pages read.

    Have you think about data buffers?

    Thx


    JMF

    Sunday, January 19, 2020 11:23 AM
  • Hi EugeneKr,

    This issue may be related to parameter sniffing.  You could try to add OPTION (RECOMPILE) to the end.

    Please refer to the following articles and thread might help:

    Parameter Sniffing

    SQL Query Optimization Techniques in SQL Server: Parameter Sniffing

    sp_executesql is slow with parameters

    Hope this could help you.

    Best Regards,

    Amelia


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, January 20, 2020 5:40 AM
  • Thanks for all the replies, I will provide more data
    • The query runs on development machine, there is not much else, no load or data variation
    • Query is run with exactly the same parameters and returns the same set of data (3 rows), but it usually takes 200 ms while sometimes starts taking almost exactly 25 seconds. Looks like SQL Server waits for some locked resource, timeouts but then returns correct data anyway, no error info is shown.
    • Query is quite simple, just a set of joins

    I have also converted it from text form to query format and the problem persists. I also tried to strip it to the minimum by removing parts of it. Result is quite perplexing - now when I remove any part of it, (ORDER BY clause, or AND statement, or even a column from SELECT statement), it runs quickly. When I ctrl-z my edits, it runs 25 seconds again.

    Monday, January 20, 2020 9:13 AM
  • So I issued exec sp_updatestats and no more 25 seconds query executions. Same data, same queries. I believe however it will start degrading again after a while. Which worries me as if db engine comes to a screeching halt on dev database with a hundred records how soon it will become unusable on prod system? Maybe we'll have to update stats every hour or so?
    Wednesday, January 22, 2020 8:24 AM