2

We have a problem on our production environment. Users report that pages take several minutes to open. Upon investigating ULS log we found following records which happen to appear every 3-8 minutes while portal is in use and once an our in the night.

12/27/2011 02:29:10.39 
PublishingHttpModule.Init() calling AppDomainUnloadListener.Register()

12/27/2011 02:29:10.39
AppDomainUnloadListener.RegisterSelf() entered lock(this=1707556)

12/27/2011 02:31:42.13
List item query elapsed time: 18055 milliseconds, Additional data (if available): Query HRESULT: 0 List internal name, flags, and URL: {B603651F-05E7-4552-9C68-6C5F88F7AB25}, flags=0x0000000820c01008, URL="<home page>" Query XML: "<Query/>" SQL Query: "       SELECT ScopeId, Acl, AnonymousPermMask FROM Perms WITH (NOLOCK) WHERE SiteId = @L4 AND ScopeId IN (SELECT tp_ScopeId FROM Lists WHERE tp_WebId = @L5 AND tp_ID = @L2 UNION SELECT DISTINCT t1.ScopeId  FROM UserData INNER JOIN Docs AS t1 WITH(NOLOCK) ON (UserData.[tp_ID]=t1.[DoclibRowId] AND UserData.[tp_RowOrdinal] = 0 AND UserData.tp_ListId = @L2) LEFT OUTER JOIN AllUserData AS t2 WITH(NOLOCK, INDEX=AllUserData_PK) ON (UserData.[int1]=t2.[tp_ID] AND UserData.[tp_RowOrdinal] = 0 AND t2.[tp_RowOrdinal] = 0 AND ( (t2.tp_IsCurrent = 1) )  AND t2.[tp_CalculatedVersion] = 0  AND t2.[tp_DeleteTransactionId] = 0x  AND t2.tp_ListId = @L3 AND UserData.tp_ListId = @L2) LEFT OUTER JOIN AllUserData AS t3 WITH(NOLOCK, INDEX=AllUserData_PK) ON (UserData.[int2]=t3.[tp_ID] AND UserData.[tp_RowOrdinal] = 0 AND t3.[tp_RowOrdinal] = 0 AND ( (t3.tp_IsCurrent = 1) )  AND t3.[tp_CalculatedVersion] = 0  AND t3.[tp_DeleteTransactionId] = 0x  AND t3.tp_ListId = @L3 AND UserData.tp_ListId = @L2) LEFT OUTER JOIN AllUserData AS t4 WITH(NOLOCK, INDEX=AllUserData_PK) ON (UserData.[tp_Author]=t4.[tp_ID] AND UserData.[tp_RowOrdinal] = 0 AND t4.[tp_RowOrdinal] = 0 AND ( (t4.tp_IsCurrent = 1) )  AND t4.[tp_CalculatedVersion] = 0  AND t4.[tp_DeleteTransactionId] = 0x  AND t4.tp_ListId = @L3 AND UserData.tp_ListId = @L2) LEFT OUTER JOIN AllUserData AS t5 WITH(NOLOCK, INDEX=AllUserData_PK) ON (UserData.[tp_Editor]=t5.[tp_ID] AND UserData.[tp_RowOrdinal] = 0 AND t5.[tp_RowOrdinal] = 0 AND ( (t5.tp_IsCurrent = 1) )  AND t5.[tp_CalculatedVersion] = 0  AND t5.[tp_DeleteTransactionId] = 0x  AND t5.tp_ListId = @L3 AND UserData.tp_ListId = @L2) WHERE  (UserData.tp_IsCurrent = 1) AND UserData.tp_SiteId=@L4 AND (UserData.tp_DirName=@DN OR UserData.tp_DirName LIKE @DNEL+N'/%') AND UserData.tp_RowOrdinal=0 AND (t1.SiteId=@L4 AND (t1.DirName=@DN OR t1.DirName LIKE @DNEL+N'/%') AND t1.Type=0) ) "

12/27/2011 02:38:06.66

List item query elapsed time: 384394 milliseconds, Additional data (if available): Query HRESULT: 0 List internal name, flags, and URL: {B603651F-05E7-4552-9C68-6C5F88F7AB25}, flags=0x0000000820c01008, URL="<home page>" Query XML: "<Query/>" SQL Query: "        SET NOCOUNT ON; SET ANSI_WARNINGS OFF;DECLARE @_scopeTbl TABLE(_listId uniqueidentifier NOT NULL,_id uniqueidentifier NOT NULL,_level bit NOT NULL,PRIMARY KEY CLUSTERED(_listId,_id,_level)); DECLARE @_count0_0 INT; SET @_count0_0 = 0; WHILE @_count0_0<31082 BEGIN  INSERT INTO @_scopeTbl VALUES ('B603651F-05E7-4552-9C68-6C5F88F7AB25', CAST(SUBSTRING( @L6 ,@_count0_0*16 + 1, 16) AS uniqueidentifier),0); SET @_count0_0=@_count0_0 + 1; END; DECLARE @_count0_1 INT; SET @_count0_1 = 0; WHILE @_count0_1<1 BEGIN  INSERT INTO @_scopeTbl VALUES ('F4789680-5E4B-4F09-AF5E-F5B35030C420', CAST(SUBSTRING( @L7 ,@_count0_1*16 + 1, 16) AS uniqueidentifier),0); SET @_count0_1=@_count0_1 + 1; END;SET NOCOUNT ON; SET ANSI_WARNINGS ON;SELECT TOP 2147483648 UserData.[ntext2],UserData.[datetime1],UserData.[nvarchar10],t1.[TimeCreated] AS c0,t4.[nvarchar1] AS c37c3,t1.[Id] AS c15,UserData.[tp_ItemOrder],UserData.[tp_Created],t1.[Type] AS c8,UserData.[tp_ModerationStatus],UserData.[nvarchar1],t3.[nvarchar4] AS c36c5,UserData.[nvarchar6],UserData.[bit2],UserData.[bit7],t5.[tp_ID] AS c38c4,UserData.[tp_WorkflowInstanceID],UserData.[ntext1],t2.[tp_Created] AS c35c7,UserData.[nvarchar14],t4.[nvarchar5] AS c37c6,UserData.[float1],UserData.[int2],UserData.[tp_ID],t3.[nvarchar1] AS c36c3,UserData.[nvarchar5],UserData.[bit1],t5.[tp_Created] AS c38c7,UserData.[tp_GUID],t1.[ProgId] AS c13,t1.[LeafName] AS c1,UserData.[bit6],UserData.[tp_Author],UserData.[tp_Editor],UserData.[int1],t2.[nvarchar4] AS c35c5,UserData.[ntext5],UserData.[nvarchar13],t4.[tp_ID] AS c37c4,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 c11,t1.[ScopeId] AS c16,UserData.[tp_ContentTypeId],t3.[nvarchar5] AS c36c6,t5.[nvarchar4] AS c38c5,UserData.[tp_WorkflowVersion],t1.[TimeLastModified] AS c9,UserData.[tp_Version],UserData.[nvarchar4],UserData.[nvarchar9],UserData.[bit5],t4.[tp_Created] AS c37c7,UserData.[tp_IsCurrentVersion],UserData.[tp_HasCopyDestinations],UserData.[tp_Level],t2.[nvarchar1] AS c35c3,UserData.[ntext4],UserData.[datetime3],UserData.[nvarchar12],UserData.[nvarchar17],t1.[MetaInfo] AS c14,t3.[tp_ID] AS c36c4,t1.[DirName] AS c27,t5.[nvarchar1] AS c38c3,UserData.[tp_HasAttachment],UserData.[nvarchar3],UserData.[nvarchar8],UserData.[tp_UIVersion],UserData.[bit4],UserData.[tp_Modified],UserData.[tp_CopySource],t2.[nvarchar5] AS c35c6,UserData.[datetime2],UserData.[nvarchar11],UserData.[ntext3],UserData.[uniqueidentifier1],UserData.[nvarchar16],t4.[nvarchar4] AS c37c5,UserData.[tp_InstanceID],t3.[tp_Created] AS c36c7,UserData.[int4],t5.[nvarchar5] AS c38c6,UserData.[tp_UIVersionString],UserData.[nvarchar2],UserData.[nvarchar7],UserData.[bit3],UserData.[tp_ContentType],t2.[tp_ID] AS c35c4,UserData.[nvarchar15],UserData.[int3] FROM UserData  LEFT OUTER LOOP  JOIN Docs AS t1 WITH(NOLOCK) ON ( 1 = 1  AND UserData.[tp_RowOrdinal] = 0 AND t1.SiteId = UserData.tp_SiteId AND t1.SiteId = @L4 AND t1.DirName = UserData.tp_DirName  AND t1.LeafName = UserData.tp_LeafName  AND t1.Level = UserData.tp_Level  AND t1.IsCurrentVersion = 1 AND (1 = 1)) LEFT OUTER JOIN AllUserData AS t2 WITH(NOLOCK, INDEX=AllUserData_PK) ON (UserData.[int1]=t2.[tp_ID] AND UserData.[tp_RowOrdinal] = 0 AND t2.[tp_RowOrdinal] = 0 AND ( (t2.tp_IsCurrent = 1) )  AND t2.[tp_CalculatedVersion] = 0  AND t2.[tp_DeleteTransactionId] = 0x  AND t2.tp_ListId = @L3 AND UserData.tp_ListId = @L2) LEFT OUTER JOIN AllUserData AS t3 WITH(NOLOCK, INDEX=AllUserData_PK) ON (UserData.[int2]=t3.[tp_ID] AND UserData.[tp_RowOrdinal] = 0 AND t3.[tp_RowOrdinal] = 0 AND ( (t3.tp_IsCurrent = 1) )  AND t3.[tp_CalculatedVersion] = 0  AND t3.[tp_DeleteTransactionId] = 0x  AND t3.tp_ListId = @L3 AND UserData.tp_ListId = @L2) LEFT OUTER JOIN AllUserData AS t4 WITH(NOLOCK, INDEX=AllUserData_PK) ON (UserData.[tp_Author]=t4.[tp_ID] AND UserData.[tp_RowOrdinal] = 0 AND t4.[tp_RowOrdinal] = 0 AND ( (t4.tp_IsCurrent = 1) )  AND t4.[tp_CalculatedVersion] = 0  AND t4.[tp_DeleteTransactionId] = 0x  AND t4.tp_ListId = @L3 AND UserData.tp_ListId = @L2) LEFT OUTER JOIN AllUserData AS t5 WITH(NOLOCK, INDEX=AllUserData_PK) ON (UserData.[tp_Editor]=t5.[tp_ID] AND UserData.[tp_RowOrdinal] = 0 AND t5.[tp_RowOrdinal] = 0 AND ( (t5.tp_IsCurrent = 1) )  AND t5.[tp_CalculatedVersion] = 0  AND t5.[tp_DeleteTransactionId] = 0x  AND t5.tp_ListId = @L3 AND UserData.tp_ListId = @L2) WHERE  UserData.tp_ListID=@L2 AND ( (UserData.tp_IsCurrent = 1) )  AND UserData.tp_SiteId=@L4 AND (UserData.tp_DirName=@DN OR UserData.tp_DirName LIKE @DNEL+N'/%') AND UserData.tp_RowOrdinal=0 AND (t1.SiteId=@L4 AND (t1.DirName=@DN OR t1.DirName LIKE @DNEL+N'/%') AND t1.Type=0) ORDER BY UserData.[tp_ID] Asc OPTION (FORCE ORDER) "

B603651F-05E7-4552-9C68-6C5F88F7AB25 - is a GUID of the task list, and it has several thousands of the elements. What do those request mean and how to get rid of them?

Improve this question
3
  • How big (ie: how many items has) is the task list in question, and do you have unique item level permissions? Also, do you have lookups to other tables? (In particular, what's this? F4789680-5E4B-4F09-AF5E-F5B35030C420). Finally, do you have a query that has no ItemLimit?
    – James Love
    Dec 29, 2011 at 10:54
  • The problem is that we do not have direct access to production environment. It's managed by our customer. We have only ULS logs to look at. It is reported that task list has around 5000 elements with unique permissions. But there are no empty queries in our code.
    – Bulat
    Jan 2, 2012 at 21:21
  • Do you know if anyone has a View set up that returns thousands of rows?
    – James Love
    Jan 2, 2012 at 23:22

1 Answer 1

Reset to default
1

I'd say the unique permissions in a list that size points to the slow performance. I did read somewhere once upon a time that the recommend limit (for performance) was 1000 items with unique permissions in a list, though I can't remember where I read this. Instead, I did my own research and blogged it here: http://e-junkie-chronicles.blogspot.com/2011/03/sharepoint-2010-performance-with-item.html and here: http://e-junkie-chronicles.blogspot.com/2011/03/sharepoint-2010-performance-with-item_23.html

Improve this answer
1

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.