Skip to main content
deleted 366 characters in body
Source Link

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

PPS: 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?

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

PPS: 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?

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
added 370 characters in body
Source Link

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

PPS: 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?

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

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

PPS: 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?

added 499 characters in body
Source Link

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

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?

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
Source Link
Loading