Some info, if not a definitive answer
It's been blogged recently
There is a whitepaper too. See the section "Maintaining Statistics in SQL Server 2008" where there are some conditions that sound like affect you. Example:
One limitation of the automatic update logic is that it tracks changes to columns in the statistics, but not changes to columns in the predicate. If there are many changes to the columns used in predicates of filtered statistics, consider using manual updates to keep up with the changes.
At the end there are some settings to check too: what if OFF at the DB level which overrides an ON at the index/stat level?
HTH...
I don't see it stated anywhere as to what version of the .NET Framework is being used, but given that this question was asked in May of 2014, and .NET Framework version 4.5 came out on 2012-08-15, the following note on SqlConnection.ConnectionString for the Asynchronous Processing
/ Async
keyword seems relevant:
This property is ignored beginning in .NET Framework 4.5.
So, it might help to find out the exact version of Entity Framework being used, and the target version of the .NET Framework.
However, it kinda sounds like this issue might be a result of connection pooling. I suspect connection pooling because after the error message about the connection being closed, the session was still around.
The first thing to try is to simply disable connection pooling by adding the following to the connection string:
Pooling=false;
If the problem never comes back, then this was most likely the issue. I hesitate to say that this was the issue since it isn't exactly proof. It could have merely reduced the frequency from "once every few days" to once every few weeks or even months. But, if this does improve things, yet the app hits the server frequently and you would prefer to use connection pooling if at all possible, then you can try to reintroduce connection pooling, but in limited sense: you can set a low value for the Connection Lifetime
keyword:
Connection Lifetime=2;
This will have connections close upon returning to the connection pool, if they are over 2 seconds old. This allows for a series of quick query executions to go through without each of them spending time establishing the connection, while at the same time not allowing the connection to linger out there, possibly holding locks, etc.
Even if the error still occurs, by not having connection pooling keeping the session and connection alive, that should allow for proper cleanup, releasing of locks, etc.
Just to have this stated since it was questioned in comments on the question: Multiple Active Result Sets (MARS) is used by Entity Framework to do Lazy Loading. So if the app has been developed to use Lazy Loading, then turning off MARS is not an option.
Best Answer
You can't enable async auto-update stats for just one table. The setting is at the database level.
I would recommend keeping auto-update stats enabled and also having an UPDATE STATISTICS job that runs on a schedule, perhaps daily. You could test doing a 10% sampling on the large tables rather than a more expensive fullscan.