If your application is using ASP.NET Identity or other membership systems such as SQL, Universal or Simple membership, then you might be running into the following issue.
If you are using ASP.NET Identity 2.x, the query for getting profile values (among other things) uses the UPPER() function on Username - this causes any indexes on username to be ignored.
The workaround is to add a computed column with the call to UPPER(Username) and a database index over it. This is extremely effective in improving the performance and it does not require any changes to the application or updating ASP.NET Identity Framework.
Here are sample SQL commands that you would need to run on the database (table and column names may be different based on your app):
SQL Query for ASP.NET Identity 2.1ALTER TABLE dbo.AspNetUsers ADD NormalizedName AS UPPER(UserName);
CREATE NONCLUSTERED INDEX [IX_NormalizedName] ON [dbo].[AspNetUsers] ([NormalizedName] ASC);
Go to the complete details ...