User info retrieval takes too long - ASP.NET Membership I have an application that has been taken from a sharepoint user control and converted to plain web pages. Everything is working fine - I can sign up and retrieve my password, etc. However, when I try and display the registered users on a webpage the results will take 2+ minutes. Going to page 2 of results takes another 2+ minutes and so on. At last count we had less than 3000 users.

More details - In addition to rebranding the app to aspx pages, it has been connected to a different database server. The previous server (Server 2008, SQL server 2005) serves up membership pretty quickly, but the new server (Server 2016, SQL Server 2017) takes forever.  The new server is an Azure server (the original server was on prem).

We have other apps on the server that utilize the same Azure server. But they are nothing like ASP.NET Membership.

Is there a setting or configuration that I am missing when it comes to getting users from Membership Provider? Please let me know if I need to supply more detail.

The tables were restored to the new server from the other one, if that makes a difference.

Here is the execution plan! https://www.brentozar.com/pastetheplan/?id=HkNBZyp_U

Actual execution plan (script as -> execute to): https://www.brentozar.com/pastetheplan/?id=rykOgPuYL

Actual execution plan with @applicationname (execute -> entered value) https://www.brentozar.com/pastetheplan/?id=BkQyYOOF8
The execution plan you provided shows the INSERT INTO...SELECT portion of the procedure running in 19 ms.  The SELECT portion, which produces the actual results, ran in 1 ms.

The specific parameters didn't produce any results, but that's because the @PageSize and @PageIndex parameters were NULL.

Even if you included all the users, the data suggests this procedure would still run in less than a second on these (relatively, for SQL Server) small tables.

All that is to say that the execution plan suggests that this is a problem outside of SQL Server.  

It could be that the application has changed, and is running more queries than expected when that page loads (and this is just one of them).  Or there is significant latency between the app server and the new Azure VM, among probably many other potential issues.

You'll need to work with the application developers to try and narrow down what, exactly, is slow when this page loads.  It does not appear that SQL Serve is taking minutes to produce this list of users.