ADO.NET calling T-SQL Stored Procedure causes a SqlTimeoutException

by

Once I determined that it is the ADO.NET connection at the root of the problem, this thread led me to the answer.

Basically connections through Sql Server Management Studio (SSMS) by default have SET ARITHABORT ON. ADO.NET connections do not.

Setting ARITHABORT OFF and executing the query directly through SSMS gives me the same slow response time.

The main difference when running with or without this setting is a different query plan is created for the two calls. When ARITHABORT was OFF, the SSMS command would use the pre-compiled cached query plan that the ADO.NET connection was using, and therefore timeout.

By running the following commands as administrator on the database all queries run as expected regardless of the ARITHABORT setting.

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

I can only assume a compiled query plan became corrupt, or invalid.

I will go with this as the solution (I have up-voted the answer) on the other thread

Thanks.

More Related Contents:

  1. SQL Server: Isolation level leaks across pooled connections
  2. T-SQL SEQUENCE runs out, no cycle, what happens?
  3. LEFT JOIN vs. LEFT OUTER JOIN in SQL Server
  4. T-SQL: Opposite to string concatenation – how to split string into multiple records [duplicate]
  5. Find a value anywhere in a database
  6. Function to Calculate Median in SQL Server
  7. How to check if a column exists in a SQL Server table
  8. Sql Server string to date conversion
  9. Select columns from result set of stored procedure
  10. Pass Dictionary to Stored Procedure T-SQL
  11. Get dates from a week number in T-SQL
  12. Date conversion and culture: Difference between DATE and DATETIME
  13. How to check if a database exists in SQL Server?
  14. How to remove accents and all chars a..z in sql-server?
  15. tsql returning a table from a function or store procedure
  16. How can I do something like: USE @databaseName
  17. How to split string and insert values into table in SQL Server
  18. How to determine the field value which can not convert to (decimal, float,int) in SQL Server
  19. SQL: Update a row and returning a column value with 1 query
  20. How can I get the sum of multiple datetime values?
  21. Use of Begin / End Blocks and the Go keyword in SQL Server?
  22. How do you determine what SQL Tables have an identity column programmatically
  23. Ampersand (&) operator in a SQL Server WHERE Clause
  24. Avg of float inconsistency
  25. Is there an “IF EXISTS” test for a linked server?
  26. “If not exists” fails on SQL CE
  27. Does the `nodes()` method keep the document order?
  28. Incorrect syntax near ‘)’ calling stored procedure with GETDATE
  29. Generate MD5 hash string with T-SQL
  30. Why can’t Entity Framework see my Stored Procedure’s column information?

Leave a Comment