DispForm.aspx taking 5+ minutes to load on SP2010 Server, high CPU utilization

Question

We have one list that has about 55 columns. Most of these are very simple columns (number, text, choice), though there are four that are Lookups to another list. All four lookups columns lookup items on the same list. There are also four calculated columns that calculate based on number fields in the List.

We are not experiencing this issue on any other lists in the site, so we've ruled out our custom master page as the issue. There are also no custom scripts or other customization running on this page. The page is equally slow to load in Firefox and in IE9. While it is loading, the CPU utilization on our database server jumps from 0-2% up to 50-65%, and then goes back down when the page finally loads. If we do two of these requests at the same time, the CPU pegs out at 100%. It's the sqlservr.exe process that's pegging out.

We've enabled Developer Dashboard, and you can see from the output below that one of the select statements is running for 807332.42 ms. We also installed Fiddler to watch the HTTP traffic, but that doesn't seem to tell us much.

Does anyone have any ideas about further troubleshooting this? Any help would be very much appreciated.

The following are some info taken from the dashboard:

proc_FetchDocForHttpGet 24.27 ms EXEC proc_AddAuditEntry 20.26 ms
proc_FetchDocForHttpGet 20.07 ms
proc_GetListMetaDataAndEventReceivers 34.84 ms
proc_GetListMetaDataAndEventReceivers 11.57 ms SELECT t20.[ntext6], 12.88 ms SELECT t20.[ntext6], 128.94 ms proc_GetTpWebMetaDataAndListMetaData 15.35 ms
proc_GetTpWebMetaDataAndListMetaData 22.44 ms
proc_GetListMetaDataAndEventReceivers 19.29 ms
proc_GetListMetaDataAndEventReceivers 19.58 ms SELECT t20.[ntext6], 15.06 ms proc_GetListItemWorkflows 41.56 ms proc_GetTpWebMetaDataAndListMetaData 20.97 ms
proc_GetListMetaDataAndEventReceivers 18.67 ms
proc_GetListMetaDataAndEventReceivers 14.59 ms
proc_GetListMetaDataAndEventReceivers 15.34 ms
proc_GetListMetaDataAndEventReceivers 18.89 ms SELECT t20.[ntext6], 13.67 ms proc_SecListSiteGroupsContainingUser 8.02 ms proc_SecListScopeGroups 7.74 ms proc_SecGetUserPermissionOnGroup 5.20 ms proc_SecGetUserPermissionOnGroup 5.21 ms proc_SecGetUserPermissionOnGroup 5.31 ms
proc_SecGetUserPermissionOnGroup 5.91 ms
proc_SecGetUserPermissionOnGroup 5.19 ms
proc_SecGetUserPermissionOnGroup 5.13 ms
proc_SecGetUserPermissionOnGroup 5.31 ms
proc_SecGetUserPermissionOnGroup 5.15 ms
proc_SecGetUserPermissionOnGroup 5.91 ms
proc_SecGetUserPermissionOnGroup 5.55 ms
proc_SecGetUserPermissionOnGroup 5.05 ms
proc_SecGetUserPermissionOnGroup 5.01 ms
proc_SecGetUserPermissionOnGroup 5.45 ms
proc_SecGetUserPermissionOnGroup 4.94 ms
proc_SecGetUserPermissionOnGroup 4.83 ms
proc_SecGetUserPermissionOnGroup 4.79 ms
proc_SecGetUserPermissionOnGroup 4.80 ms
proc_SecGetUserPermissionOnGroup 4.70 ms
proc_SecGetUserPermissionOnGroup 5.02 ms
proc_SecGetUserPermissionOnGroup 4.73 ms
proc_SecGetUserPermissionOnGroup 4.54 ms
proc_SecGetUserPermissionOnGroup 4.99 ms
proc_SecGetUserPermissionOnGroup 4.79 ms SELECT t20.[ntext6], 807332.42 ms

Answer 1

I'm not sure why this works, but after working with some freelance developers and Microsoft support, I found a solution.

What we learned is that if we create a brand new DispForm.aspx page using Sharepoint Designer, this issue goes away. However, this opened up a few other cans of worms:

• Any lookup fields were rendering as straight HTML code, so we had to write some JavaScript to fix that.
• Our EditForm.aspx page also started loading very slowly, so we had to follow the same process there.
• While we set our new DispForm page to be the default, that didn't seem to apply to Search results. Fixing this was a bit tricky -- we had to delete our original DispForm and rename the new one to be simply "DispForm.aspx".
  • By creating a new DispForm.aspx, the form is no longer automatically updated when we add or delete columns, which is kind of a pain. Trying to figure out how to re-establish that connection.

Here's the most interesting part which neither the developer nor Microsoft support can explain. The original, out-of-the-box DispForm.aspx page was only 275 lines of code. The new one, though, is over 1000. It seems that the original page was executing incredibly complex queries (verified in Developer Dashboard) that put a huge burden on the SQL Server. But when we created the new form, the new code did more of the processing on the app side, which seems to have made the queries more straight-forward.

If anyone has any explanations as to why this is happening, or suggestions for re-establishing the connection between the new DispForm and the list definition, I'd welcome it. I hope this helps others who might experience this problem with large lists.