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