Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

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

share|improve this question
    
Could you provide some details, like what version of SharePoint, how much RAM on each machine, how much RAM on the SQL box, etc? –  Dave Wise Sep 10 '12 at 15:36
    
Hi, thanks for responding Dave. We're on SP 2010 Enterprise, SQL Server box is 8gb RAM, Intel Xeon E5620@2.4 ghz (2 processors). App server is the same. –  Josh Rogan Sep 10 '12 at 16:34
    
Is the DispForm.aspx page customized at all? –  spevilgenius Sep 10 '12 at 18:34
    
Nope, not one bit. Throughout the course of today, though, we've found some other interesting facts: (1) if we create a new display form, it loads just fine, (2) the query that Sharepoint is automatically building is ridiculously long when trying to load the page, and (3) when we look at the execution plan for the query that's taking so long (latest run was over 15 minutes), a Hash Match Inner Join accounts for 62% of the cost of running the query. We're kind of stumped on that, though at least we have a working page now by replacing the old one with the new one. –  Josh Rogan Sep 10 '12 at 19:24
add comment

1 Answer 1

up vote 0 down vote accepted

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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