1

After a set of usual data access queries run, SQL Server 2019 gets into a state where one specific query takes almost exactly 25 seconds to execute. Under normal conditions it takes 200 ms.

When I change the query in some seemingly unrelated ways (remove ORDER BY, some columns from SELECT list or AND condition) it runs normally, when I restore it to initial form, it takes 25 seconds again.

SQL Server maintains this state for around 10 executions of the query. Then it continues to run normally. No blocking or dead lock traces in SQL Profiler.

Why this might happen and how to track the issue?

PS: 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
Improve this question
3
  • 2
    More info could be useful. Query plans, the query itself...
    – Rob Farley
    Commented Jan 18, 2020 at 9:20
  • 1
    Regarding the fixing itself do you have the "last good plan" auto tuning option enabled?
    – Martin Smith
    Commented Jan 18, 2020 at 12:43
  • It is a simple query which always returns 3 rows and takes 200 ms, but the same query with the same parameters and returned data set sometimes starts taking almost exactly 25 seconds.
    – eugenekr
    Commented Jan 20, 2020 at 8:58

3 Answers 3

Reset to default
3

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?

PS: In the end the problem was in too little RAM on dev laptop. Seems like the problem happened when it was full and started interacting with hard drive. This probably caused some kind of locks, because even so execution time was too large. After doubling the RAM the problem went away for good.

Improve this answer
1
  • Just had this exact problem with precisely 25 seconds wait on a previously fast query. sp_updatestats fixed it.
    – James McCormack
    Commented Dec 14, 2023 at 11:21
1

Compare the execution plans. Could be a parameter sniffing issue.

If the plans are the same, compare the IO for the queries - look for physical reads when it’s slow compared to logical reads when it’s quicker. This would be if there’s memory pressure.

Improve this answer
3
  • It runs on local development machine, no stress at all. Always returns 3 rows of data. Query is quite simple. Why would SQL Server took almost exactly 25 seconds to run a simple query? Waiting for some resources and timeouting?
    – eugenekr
    Commented Jan 20, 2020 at 9:00
  • We can't answer that unless you give us anything to go on. that is why we ask for the execution plans. You can even look for wait stats in the actual plan nowadays. So, start with the actual plan!
    – Tibor Karaszi
    Commented Jan 20, 2020 at 11:09
  • Sorry I can't provide specific information on the query since it is proprietary information, but I was wondering if there are common patterns to solving such problems.
    – eugenekr
    Commented Jan 21, 2020 at 7:35
1

Turn on the Query Store, then look at the reports. See

Monitoring performance by using the Query Store

Improve this answer
1
  • I did enable Query Store but my query is nowhere to be found. It just shows statistics for some system queries in "Top Resource Consuming Queries". I believe I should use "Tracked Queries" but where do I get query ID from?
    – eugenekr
    Commented Jan 20, 2020 at 9:03

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.