Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

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.

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.
                Warning This answer is only an extremely short term fix and completely unnecessarily brutal. DBCC DROPCLEANBUFFERS will drop most of the data pages from cache and have no effect. DBCC FREEPROCCACHE will flush the entire procedure cache all just to remove one problematic plan! There is no guarantee that the problem won't reoccur at some future stage. The issue is parameter sniffing. Please see this article for fuller explanation.
– Martin Smith
                Oct 6, 2011 at 16:30
                @MartinSmith: I've read through that article, and had a pretty hard time really grokking it. Can you provide an explanation for what people can do to fix this issue without running these two lines of code, and what people can do to avoid letting this happen in the future?
– StriplingWarrior
                Jul 25, 2014 at 22:11
                @StriplingWarrior another good article here that discusses a variety of possible preventative measures sqlperformance.com/2013/08/t-sql-queries/…
– Martin Smith
                Jul 25, 2014 at 22:33
                use WITH RECOMPILE; on stored procedure. READ: sqladvice.com/blogs/gstark/archive/2008/02/12/…
– Shiham
                Feb 26, 2016 at 4:20
                WITH RECOMPILE is not a good option for a sproc.  That's going to add overhead every time you call it.  Best to fix the underlying issue.
– Adam
                Mar 19, 2019 at 21:20
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_s  As question states, roughly 100 rows being returned (i.e. minimal). Again as question states, this all works interactively in a few seconds. Only when called though ADO.NET do we get the issue. The procedure is functioning fine, unless you call it though ADO.NET to populate a DataSet.  I am stepping through the source code for SqlDataAdapter.Fill to see where/why it hangs.  Thanks.
– crowleym
                May 7, 2009 at 12:40
In short - I fixed my issue by forcing SQL Server to use the most appropriate index to limit lob logical reads when it couldn't figure it out on its own.
In long - 
I just ran into this issue and resolved it in a different way after trying all of the other suggested answers.  In SSMS the query was running in ~3s, but was timing out when called from a .Net MVC web application.
Statistics IO output in SSMS was telling me that there were over 195,500,000 lob logical reads on one table (20M-row table with a clustered columnstore index and also has row indexes, but has no "LOB" columns).  I noticed from the execution plan that a bulk of the load (76%) was coming from an index seek on one of the row indices.  I used the following:
from [table] with (index([clustered columnstore index name]))
in my query to force the usage of the clustered columnstore index and my query was reduced to <1s and the lob logical reads dropped to <6k from >195M, and when calling the SP from the web app now, it is round-tripping in 1.3s.
I tried option recompile, set arithabort on, parameter sniffing, and in the end SQL Server just couldn't figure out which index to use.  This is an edge case too btw, and the only time I have had to force an index in this database.
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.