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

sql-server tsql ado.net timeout output-parameter
crowleym picture crowleym · May 7, 2009 · Viewed 23.5k times · Source

I have a T-SQL stored procedure with the signature

CREATE PROCEDURE MyProc
@recordCount INT OUTPUT
@param1 INT
...

When executed directly in Sql Server the procedure runs in under 5 seconds, returning a few result sets amounting to about 100 rows in total.

Calling this procedure using the ADO.NET SqlDataAdapter.Fill method to populate a Dataset causes a SqlTimeoutException on the SqlCommand after 3 minutes (the specified timeout interval).

Changing the stored procedure so that it no longer has an output parameter, and that the output value required is returned as the last result set, solves the problem, and the whole thing runs in under 5 seconds as expected.

But why?

I don't want to go through my code base and modify all instances of this type of behaviour without understanding if I have really solved the problem.

Another thing to note is this is only apparent on one particular server, which admittedly has a larger dataset than other similar databases we run. Surely not a Sql Server setting?

UPDATE

Stepping into the framework source the issue appears to be in metadata retrieval. The ConsumeMetaData method of the SqlDataReader object hangs indefinitely. However I ran tests on other databases and cannot reproduce, so it is a database specific issue when this procedure is called though ADO.NET... Great.

UPDATE II

Have confirmed the issue still occurs if I change the code to use the OleDbDataAdapter with the SQLOLEDB or SQLNCLI provider types. Definitely to do with the connection.

Answer

crowleym picture crowleym · May 8, 2009

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.

Related questions

SET NOCOUNT ON usage

Inspired by this question where there are differing views on SET NOCOUNT... Should we use SET NOCOUNT ON for SQL Server? If not, why not? What it does Edit 6, on 22 Jul 2011 It suppresses the "xx rows affected" message after any …

Read More
WHERE IN (array of IDs)

I have webservice which is passed an array of ints. I'd like to do the select statement as follows but keep getting errors. Do I need to change the array to a string? [WebMethod] public MiniEvent[] getAdminEvents(int buildingID, DateTime …

Read More
Use of SqlParameter in SQL LIKE clause not working

I have the following code: const string Sql = @"select distinct [name] from tblCustomers left outer join tblCustomerInfo on tblCustomers.Id = tblCustomerInfo.CustomerId where (tblCustomer.Name LIKE '%@SEARCH%' OR tblCustomerInfo.Info LIKE '%@SEARCH%');"; using (var command = new …

Read More