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

Question

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 1

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.

Answer 2

I stand corrected - yes, you CAN have both - an OUTPUT parameter as well as a set of rows being returned. You learn something new every day :-)

As to why a timeout happens - hmm.... hard to tell. A quickie little sample works fine for me. Can you post your stored proc (at least relevant bits of it)?

How many rows are we talking about, that get returned here?

At what point in your stored proc are you calculating the number of rows that you need to return back as OUTPUT parameter?

What if you try to add another parameter MaxRows to your one SProc as a test and do a SELECT TOP (@MaxRows)....... on your data? Does that return quickly?

Marc