Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I asked the question below on Stackoverflow without any answers, however, someone suggested I ask the question on serverfault also. Hopefully someone can help.

I recently publish my ASP.Net MVC 3 web application (Entity Framework 4.1 for Data Persistence) to a live server. The application is located on one web server and the database (SQL Server 2008) is located on another separate server.

The site has only been live a few days with very few hits, and because of this there are very few records contained within the database tables (10- 20 max).

I also have ELMAH (Error Logging Modules and Handlers for ASP.NET) integrated into the site and I have received a few instances of the following error already

System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Below shows the full details as supplied by ELMAH

System.Data.EntityException: The underlying provider failed on Open. ---> System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error) at System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParserStateObject.ReadNetworkPacket() at System.Data.SqlClient.TdsParser.ConsumePreLoginHandshake(Boolean encrypt, Boolean trustServerCert, Boolean& marsCapable) at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity) at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, SqlConnection owningObject) at System.Data.SqlClient.SqlInternalConnectionTds.LoginWithFailover(Boolean useFailoverHost, ServerInfo primaryServerInfo, String failoverHost, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, TimeoutTimer timeout) at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, TimeoutTimer timeout, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.SqlClient.SqlConnection.Open() at System.Data.EntityClient.EntityConnection.OpenStoreConnectionIf(Boolean openCondition, DbConnection storeConnectionToOpen, DbConnection originalConnection, String exceptionCode, String attemptedOperation, Boolean& closeStoreConnectionOnFailure) --- End of inner exception stack trace --- at System.Data.EntityClient.EntityConnection.OpenStoreConnectionIf(Boolean openCondition, DbConnection storeConnectionToOpen, DbConnection originalConnection, String exceptionCode, String attemptedOperation, Boolean& closeStoreConnectionOnFailure) at System.Data.EntityClient.EntityConnection.Open() at System.Data.Objects.ObjectContext.EnsureConnection() at System.Data.Objects.ObjectQuery1.GetResults(Nullable1 forMergeOption) at System.Data.Objects.ObjectQuery1.System.Collections.Generic.IEnumerable<T>.GetEnumerator() at System.Collections.Generic.List1..ctor(IEnumerable1 collection)
at System.Linq.Enumerable.ToList[TSource](IEnumerable
1 source) System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContext controllerContext, IDictionary2 parameters) at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionary2 parameters) at System.Web.Mvc.ControllerActionInvoker.<>c_DisplayClass15.b_12() at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodFilter(IActionFilter filter, ActionExecutingContext preContext, Func1 continuation) at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodWithFilters(ControllerContext controllerContext, IList1 filters, ActionDescriptor actionDescriptor, IDictionary`2 parameters) at System.Web.Mvc.ControllerActionInvoker.InvokeAction(ControllerContext controllerContext, String actionName)

The occasions where this error has happened have been due to SELECT queries. These select queries are very simple, ie,

Select col1, col2, col3 from table where col1 = 1

And as I have stated, there is very little data within the database, so it isn’t as if the queries are pulling back 100’s of records.

I have done some research into this using both the Internet and Stackoverflow. Some people suggest to increase the dbContext CommandTimeout Property, however, I don’t understand why I should need to do this. Yes, if I were pulling back loads of records, but as I have said, these are simple SELECT statements pulling back 1-5 records at the most.

I also know that developers need to keep an eye on the SQL generated by the Entity Framework, so, I downloaded and ran this trial version http://www.datawizard.com in order to get a look at the SQL being created for the Entity Framework SELECT queries that were causing a problem. Again, the profiler showed these queries to be very simple syntax, so I don’t think that is the problem.

I am beginning to think maybe it has something to do with my database and web application being on separate servers, however, I am out of ideas!

Please if anyone can help or advise me with this issue, it would be greatly appreciated.

Thanks everyone.

share|improve this question
    
Just so you know, there is also a dba site on stack exchange now, which would be a good resource if we can't solve your issue here. –  HopelessN00b Aug 24 '12 at 17:04

2 Answers 2

To eliminate client or network issues, run the resulting SQL in a management studio with client statistics enabled, and observe the runtimes and effort required to execute the query.

You can follow the SELECT query with GO to execute it an arbitrary number of times in succession, so you can get an average over a meaningful sample set:

SELECT col1, col2, col3 FROM table WHERE something
GO 1000

Also consider that, since such a framework hides much if not most of the actual SQL from view, that may not be the actual problem; perhaps it tries to massively parallelize the query, or it holds a hard (exclusive read-write) lock on the table(s) due to the ORM involved.

share|improve this answer
    
Thanks for this. I will give it a try. –  tgriffiths Aug 24 '12 at 13:31

From the stacktrace you provided, this is not a command timeout, but rather a connection timeout (there's this line: System.Data.SqlClient.TdsParser.ConsumePreLoginHandshake(...)). This could be a known bug in VS 2010, if you are using mirroring, see this blog post for more info.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.