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 = @FLN;      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: '@FLN' 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 rishkarthik,

The purpose of this forum is for discussing implementations of the Open Specification documents here: 

https://docs.microsoft.com/en-us/openspecs/main/ms-openspeclp/3589baea-5b22-48f2-9d43-f5bea4960ddb

Since your question is a performance issue with custom lists and does not pertain to one of these specifications, please post your question to the following:

https://docs.microsoft.com/en-us/answers/topics/office-sharepoint-server-development.html

Best regards,
Tom Jebo
Sr Escalation Engineer
Microsoft Open Specifications