0

I have an issue with performing sql queries, that I dont know what to do about. Tried many things and many creative Google searches.

I have a website that connects (ADO.NET) to a SQL Server 14 (we switched from version 10 where everything worked). From the website a search is performed and when the search result only returns one row, then everything works fine. When the search result returns more than a single row, then I get a timeout from SQL server. 

Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the 
server is not responding.

The SQL query is excatly the same. Only the parameter changes. All SQL queries works fine within SQL Management studio. The SQL Query uses a few views and joins and is very simple with just one parameter. Within management studio it takes 2-3 seconds to complete.

If I change back to the identical database on the old SQL Server 10, then it works again.

What I have tried:

1. Checked the execution plan and its identical for both searches.
2. Changed server memory allocation.
3. sp_refresview.
4. SQL Profiler gives no warnings or raised eyebrows.
5. sp_who2 shows no blocking.
6. Changed Remote Query Timeout to 0 (no timeout).
7. Increased timeout in web.config to 120 seconds.
8. set arithabort = on.
9. set nocount = on.

Still get the timeout.

I'm so confused...

EDIT

As far as I can see, the SQL Profiler tells me that the SQL transactions completes without any problems. But for some reason the result never reaches the web server. It only happens for specific SQL Queries so for the most part everything is fine with the communication to/from the SQL Server and the web server.

Also when I run the website locally from Visual Studio, then everything works perfectly! No timeouts or anything. I only happens for SQL Query XYZ from the web server.

Christian
  • 1,080
  • 1
  • 20
  • 37
  • 1
    Turn on the query store and monitor the queries and execution plans. https://docs.microsoft.com/en-us/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store?view=sql-server-ver15 When you "Checked the execution plan and its identical for both searches." it's very easy to get that wrong. It's unlikely that the same plan executes differently. What is the database compatibility mode? Keeping it at 100 might be a temporary workaround. https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level?view=sql-server-ver15 – David Browne - Microsoft Apr 23 '20 at 13:04
  • 1
    "_Checked the execution plan and its identical for both searches._" Do you mean you've checked the cached execution plans used by your web application? Because if you're looking at the "Actual Execution Plan" in SSMS it will almost certainly be different due to different @@OPTIONS values between SSMS and your ADO.NET application. – AlwaysLearning Apr 23 '20 at 13:53
  • Not sure what I did but now it works every second time. The queries that didn't work before now works, but now new different ones don't work. – Christian Apr 23 '20 at 16:49
  • Now the error is back. I SOMETIMES get timeout from the SQL Server to the web server. Its not random as it seems to only be for specific queries. Accordingly to SQL Profiler then the SQL transaction completes. But the Server never recieves the response so it timeouts... – Christian Apr 27 '20 at 14:51
  • possible duplicate https://stackoverflow.com/questions/2736638 – devio Apr 27 '20 at 15:41
  • @devio I will look into that... The SQL completes the transaction but never returns to the web server. But only for specific views and queries. I should maybe note that it is a SQL Server Express running in production. – Christian Apr 28 '20 at 16:04

0 Answers0