SharePoint 2010 - Custom List - Performance Issue

Question

Currently we are using custom list with Person / Group field, each list item having atleast 50+ person (username from people picker) and started performance issue from last 10 days in Prod environment. Please guide me how to resolve this issue. accessing this list from custom code to validate the user role.

Slow Query StackTrace-Managed:
at Microsoft.SharePoint.Utilities.SqlSession.OnPostExecuteCommand(SqlCommand command, SqlQueryData monitoringData)
at Microsoft.SharePoint.Utilities.SqlSession.ExecuteReader(SqlCommand command, CommandBehavior behavior, SqlQueryData monitoringData, Boolean retryForDeadLock)
at Microsoft.SharePoint.SPSqlClient.ExecuteQueryInternal(Boolean retryfordeadlock)
at Microsoft.SharePoint.SPSqlClient.ExecuteQuery(Boolean retryfordeadlock)
at Microsoft.SharePoint.Library.SPRequestInternalClass.GetListItemDataWithCallback2(IListItemSqlClient pSqlClient, String bstrUrl, String bstrListName, String bstrViewName, String bstrViewXml, SAFEARRAYFLAGS fSafeArrayFlags, ISP2DSafeArrayWriter pSACallback, ISPDataCallback pPagingCallback, ISPDataCallback pPagingPrevCallback, ISPDataCallback pFilterLinkCallback, ISPDataCallback pSchemaCallback, ISPDataCallback pRowCountCallback, Boolean& pbMaximalView)
at Microsoft.SharePoint.Library.SPRequest.GetListItemDataWithCallback2(IListItemSqlClient pSqlClient, String bstrUrl, String bstrListName, String bstrViewName, String bstrViewXml, SAFEARRAYFLAGS fSafeArrayFlags, ISP2DSafeArrayWriter pSACallback, ISPDataCallback pPagingCallback, ISPDataCallback pPagingPrevCallback, ISPDataCallback pFilterLinkCallback, ISPDataCallback pSchemaCallback, ISPDataCallback pRowCountCallback, Boolean& pbMaximalView)
at Microsoft.SharePoint.SPListItemCollection.EnsureListItemsData()
at Microsoft.SharePoint.SPListItemCollection.get_Count()
at Microsoft.SharePoint.WebControls.SPDataSourceView.ExecuteSelect(DataSourceSelectArguments selectArguments, String aggregateString, Boolean wantReturn, BaseXsltListWebPart webpart, SPListItem& listItem, SPListItemCollection& listItems, String[]& fieldList)
at Microsoft.SharePoint.WebControls.SingleDataSource.GetXPathNavigatorInternal()
at Microsoft.SharePoint.WebControls.SingleDataSource.GetXPathNavigator()
at Microsoft.SharePoint.WebPartPages.DataFormWebPart.PrepareAndPerformTransform(Boolean bDeferExecuteTransform)
at Microsoft.SharePoint.WebPartPages.DataFormWebPart.PerformSelect()
at Microsoft.SharePoint.WebPartPages.DataFormWebPart.DataBind()
at Microsoft.SharePoint.WebPartPages.DataFormWebPart.EnsureDataBound()
at Microsoft.SharePoint.WebPartPages.DataFormWebPart.CreateChildControls()
at Microsoft.SharePoint.WebPartPages.BaseXsltListWebPart.CreateChildControls()
at Microsoft.SharePoint.WebPartPages.WebPartMobileAdapter.CreateChildControls()
at System.Web.UI.Control.EnsureChildControls()
at System.Web.UI.Control.PreRenderRecursiveInternal()
at System.Web.UI.Control.PreRenderRecursiveInternal()
at System.Web.UI.Control.PreRenderRecursiveInternal()
at System.Web.UI.Control.PreRenderRecursiveInternal()
at System.Web.UI.Control.PreRenderRecursiveInternal()
at System.Web.UI.Control.PreRenderRecursiveInternal()
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
at System.Web.UI.Page.ProcessRequest(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
at System.Web.UI.Page.ProcessRequest()
at System.Web.UI.Page.ProcessRequest(HttpContext context)
at ASP.VIEWPAGE_ASPX__1081523282.ProcessRequest(HttpContext context)
at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)
at System.Web.HttpApplication.PipelineStepManager.ResumeSteps(Exception error)
at System.Web.HttpApplication.BeginProcessRequestNotification(HttpContext context, AsyncCallback cb)
at System.Web.HttpRuntime.ProcessRequestNotificationPrivate(IIS7WorkerRequest wr, HttpContext context)
at System.Web.Hosting.PipelineRuntime.ProcessRequestNotificationHelper(IntPtr managedHttpContext, IntPtr nativeRequestContext, IntPtr moduleData, Int32 flags)
at System.Web.Hosting.PipelineRuntime.ProcessRequestNotification(IntPtr managedHttpContext, IntPtr nativeRequestContext, IntPtr moduleData, Int32 flags)
at System.Web.Hosting.PipelineRuntime.ProcessRequestNotificationHelper(IntPtr managedHttpContext, IntPtr nativeRequestContext, IntPtr moduleData, Int32 flags)
at System.Web.Hosting.PipelineRuntime.ProcessRequestNotification(IntPtr managedHttpContext, IntPtr nativeRequestContext, IntPtr moduleData, Int32 flags)

SqlCommand: 'DECLARE @DocParentIdForRF uniqueidentifier SELECT TOP 1 @DocParentIdForRF = Docs.Id FROM Docs WHERE Docs.SiteId = @SITEID AND Docs.DirName = @FDN AND Docs.LeafName = @Frederik Lau Nielsen ; SELECT DISTINCT t4.[tp_ID] AS c9, t7.[tp_Created] AS c9c14, t6.[tp_Ordinal], t7.[tp_ID] AS c9c11, t5.*, t7.[nvarchar6] AS c9c13, t7.[nvarchar1] AS c9c10, t7.[nvarchar4] AS c9c12 FROM (SELECT TOP(@NUMROWS) t1.[TimeCreated] AS c0, UserData.[tp_ID], UserData.[tp_IsCurrentVersion], t1.[ScopeId] AS c4, UserData.[int3], UserData.[bit1], t1.[Type] AS c1, UserData.[tp_ModerationStatus], UserData.[tp_Level], UserData.[tp_SiteId], UserData.[tp_CalculatedVersion], t2.[nvarchar3] AS c5c6, UserData.[bit2], UserData.[nvarchar1], CASE WHEN DATALENGTH(t1.DirName) = 0 THEN t1.LeafName WHEN DATALENGTH(t1.LeafName) = 0 THEN t1.DirName ELSE t1.DirName + N'/' + t1.LeafName END AS c3, UserData.[int1], UserData.[tp_Modified], t1.[SortBehavior] AS c2, UserData.[tp_DeleteTransactionId], UserData.[tp_ParentId], UserData.[tp_DocId], UserData.[nvarchar3], UserData.[int2], t3.[nvarchar5] AS c7c8 FROM AllUserData AS UserData WITH(INDEX=AllUserData_PK) LEFT OUTER LOOP JOIN Docs AS t1 WITH(NOLOCK) ON (UserData.[tp_CalculatedVersion] = 0 ) AND (UserData.[tp_IsCurrentVersion] = CONVERT(bit,1) ) AND (UserData.[tp_DeleteTransactionId] = 0x ) AND (UserData.[tp_RowOrdinal] = 0) AND (t1.SiteId=UserData.tp_SiteId) AND (t1.SiteId = @SITEID) AND (t1.ParentId = UserData.tp_ParentId) AND (t1.Id = UserData.tp_DocId) AND ( (UserData.tp_Level = 1) ) AND (t1.Level = UserData.tp_Level) AND (t1.IsCurrentVersion = 1) AND (t1.Level = 1 OR t1.Level = 2) LEFT OUTER LOOP JOIN AllUserData AS t2 WITH(NOLOCK,INDEX=AllUserData_PK) ON (UserData.[int2]=t2.[tp_ID]) AND (UserData.[tp_RowOrdinal] = 0) AND (t2.[tp_RowOrdinal] = 0) AND ( (t2.tp_Level = 1) ) AND (t2.[tp_IsCurrentVersion] = CONVERT(bit,1) ) AND (t2.[tp_CalculatedVersion] = 0 ) AND (t2.[tp_DeleteTransactionId] = 0x ) AND (t2.tp_ListId = @L2) AND (UserData.tp_ListId = @L3) LEFT OUTER LOOP JOIN AllUserData AS t3 WITH(NOLOCK,INDEX=AllUserData_PK) ON (UserData.[int3]=t3.[tp_ID]) AND (UserData.[tp_RowOrdinal] = 0) AND (t3.[tp_RowOrdinal] = 0) AND ( (t3.tp_Level = 1) ) AND (t3.[tp_IsCurrentVersion] = CONVERT(bit,1) ) AND (t3.[tp_CalculatedVersion] = 0 ) AND (t3.[tp_DeleteTransactionId] = 0x ) AND (t3.tp_ListId = @L4) AND (UserData.tp_ListId = @L3) WHERE (UserData.tp_ListID=@LISTID) AND ( (UserData.tp_Level = 1) ) AND (UserData.tp_SiteId=@SITEID AND (UserData.tp_ParentId=@DocParentIdForRF)) AND (UserData.tp_RowOrdinal=0) AND (t1.SiteId=@SITEID AND (t1.ParentId=@DocParentIdForRF)) ORDER BY UserData.[tp_ID] ASC ) AS t5 LEFT OUTER JOIN UserDataJunctions AS t6 WITH(NOLOCK) ON (t5.[tp_SiteId] = t6.[tp_SiteId] AND t5.[tp_DeleteTransactionId] = t6.[tp_DeleteTransactionId] AND t5.[tp_IsCurrentVersion] = t6.[tp_IsCurrentVersion] AND t5.[tp_ParentId] = t6.[tp_ParentId] AND t5.[tp_DocId] = t6.[tp_DocId] AND t5.[tp_CalculatedVersion] = t6.[tp_CalculatedVersion] AND t5.[tp_Level] = t6.[tp_Level]) LEFT OUTER JOIN UserDataJunctions AS t4 WITH(NOLOCK) ON (t5.[tp_SiteId] = t4.[tp_SiteId] AND t5.[tp_DeleteTransactionId] = t4.[tp_DeleteTransactionId] AND t5.[tp_IsCurrentVersion] = t4.[tp_IsCurrentVersion] AND t5.[tp_ParentId] = t4.[tp_ParentId] AND t5.[tp_DocId] = t4.[tp_DocId] AND t5.[tp_CalculatedVersion] = t4.[tp_CalculatedVersion] AND t5.[tp_Level] = t4.[tp_Level]) AND (t6.[tp_Ordinal] = t4.[tp_Ordinal]) AND (t4.[tp_FieldId] = @L9) LEFT OUTER LOOP JOIN AllUserData AS t7 WITH(NOLOCK,INDEX=AllUserData_PK) ON (t7.[tp_ListId] = @L10) AND (t7.[tp_Id] = t4.[tp_ID]) AND (t7.[tp_RowOrdinal] = 0) AND (t7.[tp_IsCurrentVersion] = CONVERT(bit,1) ) AND (t7.[tp_CalculatedVersion] = 0 ) AND (t7.[tp_DeleteTransactionId] = 0x ) AND ( (t7.tp_Level = 1) ) ORDER BY t5.tp_ID ASC ,t6.[tp_Ordinal] ASC OPTION (FORCE ORDER, MAXDOP 1)' CommandType: Text CommandTimeout: 0 Parameter: '@LFFP' Type: UniqueIdentifier Size: 0 Direction: Input Value: '00000000-0000-0000-0000-000000000000' Parameter: '@SITEID' Type: UniqueIdentifier Size: 0 Direction: Input Value: '33b93065-22c1-477d-b605-e5e7d64f1237' Parameter: '@L2' Type: UniqueIdentifier Size: 0 Direction: Input Value: 'ba785594-a214-4755-9688-f8f1bb0625e0' Parameter: '@L3' Type: UniqueIdentifier Size: 0 Direction: Input Value: '3276012f-b04b-4102-b3e2-612212046ac9' Parameter: '@L4' Type: UniqueIdentifier Size: 0 Direction: Input Value: 'cdd1e9d3-b17c-44a0-b232-0dd104211a83' Parameter: '@FDN' Type: NVarChar Size: 4000 Direction: Input Value: '' Parameter: '@Frederik Lau Nielsen ' Type: NVarChar Size: 4000 Direction: Input Value: 'ServiceProvider' Parameter: '@LISTID' Type: UniqueIdentifier Size: 0 Direction: Input Value: '3276012f-b04b-4102-b3e2-612212046ac9' Parameter: '@NUMROWS' Type: BigInt Size: 0 Direction: Input Value: '101' Parameter: '@L9' Type: UniqueIdentifier Size: 0 Direction: Input Value: '269e8366-5590-4684-baad-17982d0da719' Parameter: '@L10' Type: UniqueIdentifier Size: 0 Direction: Input Value: 'd3742d8e-4b88-40e3-a32a-e473d6101ffd' Parameter: '@RequestGuid' Type: UniqueIdentifier Size: 0 Direction: Input Value: '6cb6743e-9dfe-4b4a-bab9-0239cff79356'

Answer 1

Hi All, Thanks for your help, we are able to fix this issue. its related to space issue, we have increased space and did re-index and is everything working fine now.

Answer 2

User or group field is kind of lookup which references internal hidden User information list. If performance degrades when there are so many users to each list item then may be it worth to reconsider current architecture? E.g. instead of adding users to list item field add them to Sharepoint group and then add this single group to list item

Answer 3

Hi @Karthikeyan Anandhan , thank you for posting in the Q&A forum.

I do not seem to find any error message or anything unusual from the logs you provide. Please confirm:

1. Do you see any error message when you run into performance issue in this list?
2. How may Person or Group columns do you have in this list?
3. Do you have any other special columns (lookup, managed metadata, etc.) displayed in the same list view?

In addition, SharePoint Server 2010 has a limit of 4 bytes for maximum size of a Person or group field, and 6 for the value of row wrapping parameter.
These SharePoint limits can possibly but not necessarily cause performance issues when being reached. Make sure you do not exceed those limits in the first place.

You may check your SharePoint farm and see if you have similar issue in any other lists (except the one you mentioned in the other Answer).

---

If an Answer is helpful, please click "Accept Answer" and upvote it.
**Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread. **