0

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

2
  • Yes, I did run it on the azure server via SSMS. Hmm so the latency is to be expected? We have other apps on the server that utilize the same azure server. But they are nothing like ASPNET Membership. Would it be worthwhile to look at possibly migrating to ASPNET Core Identity? I've seen that this is the newer version of Memberships.
    – Raquel
    Apr 30, 2020 at 16:59
  • I wouldn't say that such latency is expected, but it might be "normal" if the link between your app servers and the Azure VM is slow. I can't say if switching to Identity would help, as it's still not clear why the current page you described loads so slowly. Apr 30, 2020 at 20:36

1 Answer 1

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.