Efcore: SqlServer for Ubuntu: DbContextPooling & SequenceEndToEnd tests hits frequent timeouts

[smitpatel]

Disabled tests on Ubuntu
Double_dispose_concurrency_test
Concurrency_test
Can_use_sequence_end_to_end
Can_use_sequence_end_to_end_from_multiple_contexts_concurrently_async

Using SqlServer 2017 on Ubuntu 16.04

[smitpatel]

Stack trace from Can_use_sequence_end_to_end_from_multiple_contexts_concurrently_async

System.Data.SqlClient.SqlException : Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
---- System.ComponentModel.Win32Exception : Unknown error 258
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)
   at System.Data.SqlClient.TdsParserStateObject.ReadSniSyncOverAsync()
   at System.Data.SqlClient.TdsParserStateObject.TryReadNetworkPacket()
   at System.Data.SqlClient.TdsParserStateObject.TryPrepareBuffer()
   at System.Data.SqlClient.TdsParserStateObject.TryReadByte(Byte& value)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteScalar()
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary`2 parameterValues) in /home/entityframework/BuildAgent/work/46872f3c7fa72c0c/src/EFCore.Relational/Storage/Internal/RelationalCommand.cs:line 224
   at Microsoft.EntityFrameworkCore.ValueGeneration.Internal.SqlServerSequenceHiLoValueGenerator`1.GetNewLowValue() in /home/entityframework/BuildAgent/work/46872f3c7fa72c0c/src/EFCore.SqlServer/ValueGeneration/Internal/SqlServerSequenceHiLoValueGenerator.cs:line 54
   at Microsoft.EntityFrameworkCore.ValueGeneration.HiLoValueGeneratorState.Next[TValue](Func`1 getNewLowValue) in /home/entityframework/BuildAgent/work/46872f3c7fa72c0c/src/EFCore/ValueGeneration/HiLoValueGeneratorState.cs:line 66
   at Microsoft.EntityFrameworkCore.ValueGeneration.ValueGenerator`1.NextValueAsync(EntityEntry entry, CancellationToken cancellationToken) in /home/entityframework/BuildAgent/work/46872f3c7fa72c0c/src/EFCore/ValueGeneration/ValueGenerator`.cs:line 49
   at Microsoft.EntityFrameworkCore.ValueGeneration.ValueGenerator.NextAsync(EntityEntry entry, CancellationToken cancellationToken) in /home/entityframework/BuildAgent/work/46872f3c7fa72c0c/src/EFCore/ValueGeneration/ValueGenerator.cs:line 40

[ajcvickers]

@saurabh500 @geleems These are all tests which hit the database pretty hard with multiple connections. Do you think these failures might be related to the Linux/SQL Server issues that are being investigated?

[ajcvickers]

@saurabh500 @geleems Any updates on this?

[saurabh500]

@ajcvickers are these tests targeting the local sql server?

[ajcvickers]

@saurabh500 Not sure, but I don't think so. @smitpatel?

[geleems]

@smitpatel @ajcvickers

It seems this issue was already reported, and the fix was merged to both master and release/2.0.0.
Please see below for the details.

SqlConnection failure issue when having multiple concurrent connections

We had an SqlConnection failure issue when having multiple concurrent connections. It was due to attempting to connect by asynchronously to server even when synchronous open() is called. (Issue https://github.com/dotnet/corefx/issues/25620).
The fix was merged to master branch 1.5 month ago (https://github.com/dotnet/corefx/pull/26200), and also merged to release/2.0.0 branch today (https://github.com/dotnet/corefx/pull/26247)

Performance issue when targeting local SQL Server

There was a performance issue on .NET Core Linux when it specifically targets local SQL Server. It was due to socket packet size setup (Issue https://github.com/dotnet/corefx/issues/24480), and the fix was merged 1.5 month ago to master branch (https://github.com/dotnet/corefx/pull/25621).
This fix also merged to release/2.0.0 branch today (https://github.com/dotnet/corefx/pull/26244)

[ajcvickers]

@geleems Thanks for the update. Assigning to @smitpatel to re-enable the tests and monitor.

[smitpatel]

This did not work. Test still failed. Reverted in b4a4a8d34227f34fc3236e9303f97f91823d8296

[ajcvickers]

@saurabh500 @geleems These tests are still failing for us on SQL Server running on Linux. The tests pass on SQL Server for Windows, so it seems like this is an issue somewhere below the level of EF--either in SQLClient or SQL Server. We are closing this on our side since we have test coverage for EF code, but it would probably be a good idea for you guys to follow up some more on why this is failing on Linux.

[saurabh500]

@ajcvickers this is the next item we are picking up for perf investigation.
@smitpatel @ajcvickers Do you know if EF Core on Windows against SQL server on Linux works OK?
I am trying to understand if its the SqlClient networking or could this be server networking that needs to be looked into.

[ajcvickers]

@saurabh500 We only test everything on Linux or everything on Windows for this case so we don't have any info if the tests also fail when running Windows pointing at Linux or vice versa.

[geleems]

@smitpatel, @ajcvickers
Could you please provide sample repro for me to reproduce the issue in my local environment?

[ajcvickers]

@geleems It's my understanding that you guys are able to run EF tests--at least, that was the case last time I talked with @saurabh500. Given that, you should be able to run the given tests on Linux and reproduce the issue. Or have you tried this and are not finding it to repro?

[saurabh500]

@geleems You need to run the EF tests.

You can build the EF Clone and run

~/.dotnet/dotnet test --framework netcoreapp2.0 -v d --no-build
The above command runs all the tests.

You may want to run this particular failing test using following instructions
Running tests selectively using dotnet test is done via “filter” arg. From help printed out in console. --filter <EXPRESSION> Run tests that match the given expression. Examples: Run tests with priority set to 1: --filter "Priority = 1" Run a test with the specified full name: --filter "FullyQualifiedName=Namespace.ClassName.MethodName" Run tests that contain the specified name: --filter "FullyQualifiedName~Namespace.Class" More info on filtering support: https://aka.ms/vstest-filtering

After this you run the test, which may or may not reproduce the issue based on the configuration of the VM.
To extract a SQLClient specific test, I would attach a SQL Profiler to the Sql server and see what queries are being executed and try to repro the issue using SqlClient.

I would start by running the tests on Linux against a local Linux server vs a Windows SQL Server to see if the server is acting up in this case. If there is a difference in the server side behavior, then this may be a server issue. Else we need to isolate the problem on the SqlClient.

If you figure that this is a SqlClient perf issue, you could enable managed SNI on Windows and run the tests and collect tracing using PerfView.exe That should give you insights on CPU hotspots if any. Else you may want to capture the thread time in PerfView to figure out if we are waiting for any thread scheduling.

As a first step, I recommend setting up the tests on Linux and run this specific test against the two server SKUs to find what component is a bottle neck

[smitpatel]

I am testing out with code on Ubuntu and server on widows.

[geleems]

@smitpatel
What is the new stack trace you got after testing with .NET Core that includes those fixes I mentioned above?
at System.Data.SqlClient.TdsParserStateObject.ReadSniSyncOverAsync() should not appear in your new stack trace since ReadSniSyncOverAsync() method was removed by the fixes I mentioned above.

[smitpatel]

@geleems - Stack trade did not change. Can you tell minimum version of SqlClient to be targeted to get the fix?

Also, running on Linux targeting SqlServer on windows did work without error.

[geleems]

@smitpatel
Sorry I was confused with ConnectSyncOverAsync() method that was removed. Please ignore my last comment.
Does the issue only happen when it targets local Linux SQL Server? Or it does not matter whether it targets local SQL Server or remote SQL Server?

[saurabh500]

@smitpatel @ajcvickers I am looking into the EFCore code and I see that the SqlClient version it depends on is from 12/16/2017

Can you update the version to the latest and run the tests again? Since then there have been 2 more performance fixes. If this upgrade can be easily done and validations be run in your CI, that would be quite nice before we dive deeper into this issue.

[smitpatel]

@saurabh500 - What is the latest version of SqlClient? It is not straight fwd to upgrade dependency in the code but i can try to upgrade locally and run the test on linux

[saurabh500]

The latest would be 4.5.0-preview2-26202-05
https://dotnet.myget.org/feed/dotnet-core/package/nuget/System.Data.SqlClient

[smitpatel]

@saurabh500 - I tested with 4.5.0-preview2-26202-05 but it still failed. Even a different test started failing which did not fail in past.

[saurabh500]

@smitpatel what is the other test failing?
Thanks for trying out the updated version.

[smitpatel]

new test failed after updating
MonsterFixupChangedChangingSqlServerTest.Can_build_monster_model_and_seed_data_using_principal_navigations

Same stack trace.

[ajcvickers]

Moving this to backlog to re-enable the test when the external issues are fixed.

[AndriySvyryd]

We should try this again with the new SqlClient