Tag: Query Store

At load tests of web application using ASP.NET Identity 2.2.1 I turned on Microsoft SQL Server Query Store to monitor performance of T-SQL queries. Analyzing the results I suggest following tips to improve performance of ASP.NET Identity:

1. Disable checks of schema version

As for exec count, the following query was on the first place:

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS where TABLE_NAME=@Table

After some search in source code of ASP.NET Identity I found internal IsIdentityV1Schema method in IdentityDbContext class. This method runs mentioned query to check version of schema, whether DB contains ASP.NET Identity 1.0 schema. To avoid this DB roundtrips you have to set parameter throwIfV1Schema to false in constructor of IdentityDbContext class as shown in code:

public class ApplicationDbContext : IdentityDbContext<ApplicationUser>
{
    public ApplicationDbContext()
        : base("DefaultConnection", throwIfV1Schema: false)
    {
    }
}

The interesting on this issue is fact, that this is set when you create web application project from default template in Visual Studio 2015, but I wrote this code manually, so I didn’t consider the importance of this parameter.

2. Avoid usage of T-SQL UPPER function

When selecting user by username the following query is executed:

SELECT TOP (1)
    [Extent1].[Id] AS [Id],
    [Extent1].[Email] AS [Email],
    [Extent1].[EmailConfirmed] AS [EmailConfirmed],
    [Extent1].[PasswordHash] AS [PasswordHash],
    [Extent1].[SecurityStamp] AS [SecurityStamp],
    [Extent1].[PhoneNumber] AS [PhoneNumber],
    [Extent1].[PhoneNumberConfirmed] AS [PhoneNumberConfirmed],
    [Extent1].[TwoFactorEnabled] AS [TwoFactorEnabled],
    [Extent1].[LockoutEndDateUtc] AS [LockoutEndDateUtc],
    [Extent1].[LockoutEnabled] AS [LockoutEnabled],
    [Extent1].[AccessFailedCount] AS [AccessFailedCount],
    [Extent1].[UserName] AS [UserName]
    FROM [dbo].[AspNetUsers] AS [Extent1]
    WHERE ((UPPER([Extent1].[UserName])) = (UPPER(@p__linq__0))) OR ((UPPER([Extent1].[UserName]) IS NULL) AND (UPPER(@p__linq__0) IS NULL))

Query contains UPPER function which disables usage of index created on UserName column. Execution plan for query is:

Studying default implementation of UserStore class I found that method FindByNameAsync contains LINQ predicate with ToUpper method. This default behaviour can be overriden creating custom UserStore implementation:

public class ApplicationUserStore : UserStore<ApplicationUser>
{
    public ApplicationUserStore(ApplicationDbContext context)
        : base(context)
    {
    }

    public override Task<ApplicationUser> FindByEmailAsync(string email)
    {
        return GetUserAggregateAsync(u => u.Email == email);
    }

    public override Task<ApplicationUser> FindByNameAsync(string userName)
    {
        return GetUserAggregateAsync(u => u.UserName == userName);
    }
}

Than at creating UserManager only use custom UserStore implementation:

public class ApplicationUserManager : UserManager<ApplicationUser>
{
    public ApplicationUserManager(IUserStore<ApplicationUser> store)
        : base(store)
    {
    }

    public static ApplicationUserManager Create(IdentityFactoryOptions<ApplicationUserManager> options, IOwinContext context) 
    {
        var manager = new ApplicationUserManager(new ApplicationUserStore(context.Get<ApplicationDbContext>()));
        // ...
    }
}

After this customization UPPER function is no longer present in query:

SELECT TOP (1)
    [Extent1].[Id] AS [Id],
    [Extent1].[Email] AS [Email],
    [Extent1].[EmailConfirmed] AS [EmailConfirmed],
    [Extent1].[PasswordHash] AS [PasswordHash],
    [Extent1].[SecurityStamp] AS [SecurityStamp],
    [Extent1].[PhoneNumber] AS [PhoneNumber],
    [Extent1].[PhoneNumberConfirmed] AS [PhoneNumberConfirmed],
    [Extent1].[TwoFactorEnabled] AS [TwoFactorEnabled],
    [Extent1].[LockoutEndDateUtc] AS [LockoutEndDateUtc],
    [Extent1].[LockoutEnabled] AS [LockoutEnabled],
    [Extent1].[AccessFailedCount] AS [AccessFailedCount],
    [Extent1].[UserName] AS [UserName]
    FROM [dbo].[AspNetUsers] AS [Extent1]
    WHERE [Extent1].[UserName] = @p__linq__0

Execution plan for query is now:

This solution assumes that DB uses case insensitive collation.

3. Index Email column

In my web application there was also many scenarios where users were queried by email. To support this scenario I add index for Email column. In Package Manager Console I run PowerShell commands to enable migrations and add new migration AspNetUsersEmailIndex:

Enable-Migrations
Add-Migration AspNetUsersEmailIndex

In generated skeleton of AspNetUsersEmailIndex migration class was added code to create index for Email column:

public partial class AspNetUsersEmailIndex : DbMigration
{
    public override void Up()
    {
        CreateIndex("dbo.AspNetUsers", "Email");
    }

    public override void Down()
    {
        DropIndex("dbo.AspNetUsers", new[] { "Email" });
    }
}

Running update database command I got T-SQL of migration:

Update-Database -TargetMigration AspNetUsersEmailIndex -Script

Generated T-SQL script contains CREATE INDEX command:

CREATE INDEX [IX_Email] ON [dbo].[AspNetUsers]([Email])