Huge performance problem with async

[equist]

We are trying to scale an application that uses ASP.NET Identity (which is all async) and we have huge performance issues with all async work against SqlClient (we believe it has boiled down to this). We have investigated and tried many different things for a couple of weeks, and we now have very little isolated code that performs more or less equally bad all the time. The code has implementation of both sync and async calls with huge differences between them. We have read about the issues for big fields, but we see the problem on all our queries (without any big fields) and the reproduced slow calls below has no large fields.

We run in Azure with an App Service plan of P1V2 scaled to two instances and an SQL Azure Premium P1: 125 DTUs. We don't see any performance problems at all in SQL Azure. We have a VNET with a private link between the App Service and SQL Azure. We use Azure Load test to run the tests (and we can share the tests too if needed). We also get similar results when testing locally.
We use Stackify Retrace for monitoring and have enabled that through their extension.

Sync code performance when running 10 instances with 250 threads:
90%: 4,69 sek
Average: 1,417 sek
Open Connection: <0,001 sek
Select Logins: 0,002 sek

Async code performance when running 10 instances with 250 threads:
90%: 5,38 sek
Average: 2,235 sek
Open Connection: 1,624 sek
Select Logins: 0,298 sek

Sync code performance when running 45 instances with 250 threads, each thread making 10 calls after each other:
90%: 40,21 sek
Average: 17,502 sek
Open Connection: <0,001 sek
Select Logins: 0,006 sek

Async code performance when running 45 instances with 250 threads, each thread making 10 calls after each other:
90%: 80,97 sek
Average: 35,080 sek
Open Connection: 19,634 sek
Select Logins: 7,975 sek

Table script:
`
CREATE TABLE [security].[AspNetUserLogin](
[LoginProvider] [nvarchar] (100) NOT NULL,
[ProviderKey] [nvarchar] (100) NOT NULL,
[TenantId] [uniqueidentifier] NOT NULL,
[ProviderDisplayName] [nvarchar] (450) NULL,
[UserId] [uniqueidentifier] NOT NULL,
CONSTRAINT [PK_AspNetUserLogin] PRIMARY KEY CLUSTERED
(
[LoginProvider] ASC,
[ProviderKey] ASC,
[TenantId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [security].[AspNetUserLogin] WITH CHECK ADD CONSTRAINT [FK_AspNetUserLogin_AspNetUser_UserId] FOREIGN KEY([UserId])
REFERENCES [security].[AspNetUser] ([Id])
ON DELETE CASCADE
GO

ALTER TABLE [security].[AspNetUserLogin] CHECK CONSTRAINT [FK_AspNetUserLogin_AspNetUser_UserId]
GO
`

Data: The table is empty

ASP.NET Code (.NET 6 and Microsoft.Data.SqlClient 4.1.0 (we have tried with version 5 too). We started with a default .NET 6 template, added support for controllers and removed authorization, https redirection and exception handler.
The controller:
`
using Microsoft.AspNetCore.Identity;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Data.SqlClient;

namespace SyncAsyncPerf.Controllers
{
[Route("api/[controller]")]
[ApiController]
public class LoadTestController : ControllerBase
{
private const string _connectionString =
"Server=tcp:ourserver.database.windows.net;Authentication=Active Directory Managed Identity;Initial Catalog=oursqldb;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;Max Pool Size=5000;Min Pool Size=100";

    [HttpGet]
    public IActionResult Get(Guid userId)
    {
        var sql = $"SELECT [LoginProvider], [ProviderKey], [ProviderDisplayName] FROM [security].[AspNetUserLogin]" +
                    $"WHERE [UserId] = @UserId";

        using var connection = new SqlConnection(_connectionString);
        using var command = connection.CreateCommand();

        command.CommandText = sql;
        var parameter = command.CreateParameter();
        parameter.ParameterName = "@UserId";
        parameter.DbType = System.Data.DbType.Guid;
        parameter.Value = userId;
        command.Parameters.Add(parameter);

        connection.Open();

        using var reader = command.ExecuteReader(System.Data.CommandBehavior.SingleResult | System.Data.CommandBehavior.CloseConnection);

        var result = new List<UserLoginInfo>();
        while (reader.Read())
        {
            result.Add(
                new UserLoginInfo(reader.GetString(0), reader.GetString(1), reader.IsDBNull(2) ? null : reader.GetString(2))
                        );
        }

        return Ok(result);
    }

    [HttpGet("async")]
    public async Task<IActionResult> GetAsync(Guid userId)
    {
        var sql = $"SELECT [LoginProvider], [ProviderKey], [ProviderDisplayName] FROM [security].[AspNetUserLogin]" +
                    $"WHERE [UserId] = @UserId";

        using var connection = new SqlConnection(_connectionString);
        using var command = connection.CreateCommand();

        command.CommandText = sql;
        var parameter = command.CreateParameter();
        parameter.ParameterName = "@UserId";
        parameter.DbType = System.Data.DbType.Guid;
        parameter.Value = userId;
        command.Parameters.Add(parameter);

        await connection.OpenAsync(HttpContext.RequestAborted);

        using var reader = await command.ExecuteReaderAsync(System.Data.CommandBehavior.SingleResult | System.Data.CommandBehavior.CloseConnection, HttpContext.RequestAborted);

        var result = new List<UserLoginInfo>();
        while (await reader.ReadAsync())
        {
            result.Add(
                new UserLoginInfo(reader.GetString(0), reader.GetString(1), reader.IsDBNull(2) ? null : reader.GetString(2))
                        );
        }

        return Ok(result);
    }
}

}
`

Finally, some notes on the connection string. We have tried many different configurations without effecting the underlying problem. If we use defaults for connection pooling the number of errors increases rapidly, especially for async code because of connection timeouts.

[DavoudEshtehari]

Hi @equist

Thank you for sharing here.
For the record, could you specify the OS and target framework in use?

• Related known issues Async opening of connections in parallel is slow/blocking #601, and Slow read from SqlDataReader #1206.

[equist]

.NET 6 on Windows. The App Service, SQL Azure and VNET are all in the same Azure region (North Europe). We see simlar problems when running locally (on a dev box) even though not with same load.

I've read those, but #1206 seemed to only happen intermitent, but we reproduce this all the time and never with acceptable results for async method.
I've also read #601, which lead us to set min pool size and to make a manual call to the service to "warm it up" before running the tests, but as shown we still have bad results.

[KonnectSalon]

Having the same issue here... It's an async issue with the SQL client for large fields - Changing the nvarchar(max) to nvarchar(255) for example helped alot.

[equist]

Having the same issue here... It's an async issue with the SQL client for large fields - Changing the nvarchar(max) to nvarchar(255) for example helped alot.

Large fields is an identified issue since earlier, but we get slow performance for small fields too.

[KonnectSalon]

Having the same issue here... It's an async issue with the SQL client for large fields - Changing the nvarchar(max) to nvarchar(255) for example helped alot.

Large fields is an identified issue since earlier, but we get slow performance for small fields too.

I removed all async db calls but can’t in the identity which is causing me issues.

Seems like sqlclient is just broken for async.

[equist]

When switching to Linux App Service the problems disappeared. We haven't tested the downscaled example used in this issue, but the full application is scaling very well on Linux.

[timothyeckert]

Chiming in with, we are seeing this issue as well and can see it reported within our APM tool. Rolling back to sync call cuts the time in half.

[bedpans]

Async performance is completely unacceptable. In one particular instance changing from ToListAsync to ToList reduced execution time from 18 minutes to 7 seconds. That is 150x speed up. This happens when we are retrieving 3 rows each one having nvarchar(max) column with 25,000,000 characters of data.