none
Document sets has become very slow, strange sql request executed RRS feed

  • Question

  • Hi.

    Everything seemed right on a SharePoint environment; for two weeks it has become very slow; ULS logs indicate a slow request; it seems it is called only when metadata are used, have you ever encountered this problem? Next time another document set of the same content type is read it is very fast. Reading anotehr document set with different content type and again the sql request is slow. Sql request:

    SELECT CASE WHEN t46.[tp_Ordinal] IS NULL OR t46.[tp_Ordinal] = 0 THEN t47.[ntext4] ELSE NULL END AS ntext4, CASE WHEN t46.[tp_Ordinal] IS NULL OR t46.[tp_Ordinal] = 0 THEN t47.[ntext14] ELSE NULL END AS ntext14, CASE WHEN t46.[tp_Ordinal] IS NULL OR t46.[tp_Ordinal] = 0 THEN t47.[ntext6] ELSE NULL END AS ntext6, CASE WHEN t46.[tp_Ordinal] IS NULL OR t46.[tp_Ordinal] = 0 THEN t47.[ntext12] ELSE NULL END AS ntext12, CASE WHEN t46.[tp_Ordinal] IS NULL OR t46.[tp_Ordinal] = 0 THEN t47.[ntext8] ELSE NULL END AS ntext8, CASE WHEN t46.[tp_Ordinal] IS NULL OR t46.[tp_Ordinal] = 0 THEN t47.[ntext1] ELSE NULL END AS ntext1, CASE WHEN t46.[tp_Ordinal] IS NULL OR t46.[tp_Ordinal] = 0 THEN t47.[ntext3] ELSE NULL END AS ntext3, CASE WHEN t46.[tp_Ordinal] IS NULL OR t46.[tp_Ordinal] = 0 THEN t47.[ntext11] ELSE NULL END AS ntext11, CASE WHEN t46.[tp_Ordinal] IS NULL OR t46.[tp_Ordinal] = 0 THEN t47.[ntext16] ELSE NULL END AS ntext16, t46.*, CASE WHEN t46.[tp_Ordinal] IS NULL OR t46.[tp_Ordinal] = 0 THEN t47.[ntext5] ELSE NULL END AS ntext5, CASE WHEN t46.[tp_Ordinal] IS NULL OR t46.[tp_Ordinal] = 0 THEN t47.[ntext7] ELSE NULL END AS ntext7, CASE WHEN t46.[tp_Ordinal] IS NULL OR t46.[tp_Ordinal] = 0 THEN t47.[ntext13] ELSE NULL END AS ntext13, CASE WHEN t46.[tp_Ordinal] IS NULL OR t46.[tp_Ordinal] = 0 THEN t48.[MetaInfo] ELSE NULL END AS c60, CASE WHEN t46.[tp_Ordinal] IS NULL OR t46.[tp_Ordinal] = 0 THEN t47.[ntext2] ELSE NULL END AS ntext2, CASE WHEN t46.[tp_Ordinal] IS NULL OR t46.[tp_Ordinal] = 0 THEN t47.[ntext10] ELSE NULL END AS ntext10, CASE WHEN t46.[tp_Ordinal] IS NULL OR t46.[tp_Ordinal] = 0 THEN t47.[ntext9] ELSE NULL END AS ntext9, CASE WHEN t46.[tp_Ordinal] IS NULL OR t46.[tp_Ordinal] = 0 THEN t47.[ntext15] ELSE NULL END AS ntext15 FROM (SELECT DISTINCT t6.[tp_ID] AS c26, t26.[tp_ID] AS c47, t28.[tp_ID] AS c49, t43.[nvarchar10] AS c49c25, t45.[nvarchar10] AS c51c25, t37.[nvarchar8] AS c20c21, t37.[nvarchar9] AS c22c23, t25.[tp_ID] AS c46, t41.[nvarchar10] AS c47c25, t42.[nvarchar10] AS c48c25, t29.[tp_ID] AS c50, t44.[nvarchar10] AS c50c25, t35.*, t4.[tp_ID] AS c20, t4.[tp_ID] AS c22, t39.[nvarchar10] AS c30c25, t40.[nvarchar10] AS c46c25, t27.[tp_ID] AS c48, t36.[tp_Ordinal], t38.[nvarchar10] AS c26c25, t10.[tp_ID] AS c30, t30.[tp_ID] AS c51 FROM (SELECT TOP(@NUMROWS) t3.[nvarchar9] AS c14c8, t1.[SortBehavior] AS c0, t19.[tp_Created] AS c40c11, t22.[tp_ID] AS c43c5, UserData.[datetime1], t3.[nvarchar12] AS c14c10, 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 c15, UserData.[int26], UserData.[tp_ItemOrder], UserData.[nvarchar1], UserData.[bit2], UserData.[int12], t15.[tp_ID] AS c36c5, t21.[nvarchar6] AS c42c7, t1.[Size] AS c57, t2.[nvarchar4] AS c3c6, t8.[nvarchar10] AS c28c25, t14.[nvarchar6] AS c35c7, t20.[nvarchar11] AS c41c9, UserData.[tp_AppAuthor], UserData.[int25], UserData.[bit1], t5.[nvarchar10] AS c24c25, t13.[nvarchar11] AS c34c9, UserData.[int11], t23.[nvarchar1] AS c44c4, t1.[ETagVersion] AS c68, t1.[TimeCreated] AS c1, t3.[nvarchar11] AS c14c9, t22.[nvarchar4] AS c43c6, t3.[tp_Created] AS c14c11, t16.[nvarchar1] AS c37c4, UserData.[int24], UserData.[nvarchar18], t1.[IsCheckoutToLocal] AS c16, UserData.[int10], t15.[nvarchar4] AS c36c6, t21.[nvarchar9] AS c42c8, t2.[nvarchar6] AS c3c7, UserData.[tp_Level], t14.[nvarchar9] AS c35c8, UserData.[nvarchar17], UserData.[int23], t23.[tp_ID] AS c44c5, t1.[LeafName] AS c2, UserData.[tp_Modified], UserData.[tp_UIVersion], UserData.[nvarchar16], t16.[tp_ID] AS c37c5, t22.[nvarchar6] AS c43c7, UserData.[int22], t3.[tp_ID] AS c14c5, t12.[nvarchar6] AS c33c7, t18.[nvarchar12] AS c39c10, UserData.[int19], t2.[nvarchar12] AS c3c10, t1.[Type] AS c12, t1.[ProgId] AS c17, UserData.[int4], t15.[nvarchar6] AS c36c7, t16.[nvarchar12] AS c37c10, t17.[nvarchar12] AS c38c10, t21.[nvarchar11] AS c42c9, t31.[nvarchar1] AS c52c4, t31.[nvarchar11] AS c52c9, t1.[CheckinComment] AS c59, UserData.[tp_UIVersionString], UserData.[nvarchar7], t15.[nvarchar12] AS c36c10, t21.[nvarchar1] AS c42c4, t1.[Size] AS c54, t2.[nvarchar9] AS c3c8, UserData.[nvarchar15], t13.[nvarchar12] AS c34c10, t14.[nvarchar1] AS c35c4, t14.[nvarchar11] AS c35c9, t14.[nvarchar12] AS c35c10, t18.[nvarchar6] AS c39c7, t20.[nvarchar4] AS c41c6, UserData.[int18], UserData.[int21], t34.[Title] AS c65c64, UserData.[int3], t12.[nvarchar12] AS c33c10, t17.[nvarchar11] AS c38c9, t23.[nvarchar4] AS c44c6, t33.[Title] AS c63c64, UserData.[tp_ModerationStatus], UserData.[nvarchar6], t13.[nvarchar4] AS c34c6, t17.[nvarchar1] AS c38c4, t19.[nvarchar9] AS c40c8, UserData.[tp_Created], UserData.[tp_WorkflowInstanceID], t3.[nvarchar4] AS c14c6, UserData.[nvarchar14], t12.[nvarchar9] AS c33c8, t16.[nvarchar4] AS c37c6, UserData.[int17], t22.[nvarchar9] AS c43c8, UserData.[int20], t2.[tp_Created] AS c3c11, t1.[Id] AS c18, UserData.[tp_DeleteTransactionId], UserData.[int2], t17.[tp_Created] AS c38c11, t18.[tp_Created] AS c39c11, t24.[nvarchar10] AS c45c25, t31.[tp_ID] AS c52c5, t1.[TimeLastModified] AS c13, UserData.[tp_ID], UserData.[nvarchar5], t15.[nvarchar9] AS c36c8, t15.[tp_Created] AS c36c11, t16.[tp_Created] AS c37c11, t21.[tp_ID] AS c42c5, t1.[LTCheckoutUserId] AS c55, UserData.[tp_Editor], t2.[nvarchar11] AS c3c9, t13.[tp_Created] AS c34c11, t14.[tp_Created] AS c35c11, t18.[nvarchar9] AS c39c8, UserData.[int16], t20.[nvarchar6] AS c41c7, UserData.[tp_Author], t2.[nvarchar1] AS c3c4, UserData.[int1], UserData.[nvarchar13], t12.[tp_Created] AS c33c11, t14.[tp_ID] AS c35c5, UserData.[tp_ContentTypeId], t11.[nvarchar11] AS c31c32, t13.[nvarchar6] AS c34c7, t17.[tp_ID] AS c38c5, t19.[nvarchar1] AS c40c4, t19.[nvarchar11] AS c40c9, t22.[nvarchar12] AS c43c10, t23.[nvarchar6] AS c44c7, t23.[nvarchar12] AS c44c10, t1.[ParentVersionString] AS c66, UserData.[nvarchar4], t3.[nvarchar6] AS c14c7, UserData.[tp_Version], UserData.[tp_IsCurrentVersion], t16.[nvarchar6] AS c37c7, UserData.[int15], t20.[nvarchar12] AS c41c10, t21.[nvarchar12] AS c42c10, t22.[nvarchar11] AS c43c9, t32.[nvarchar1] AS c4, t1.[ItemChildCount] AS c61, UserData.[tp_HasCopyDestinations], UserData.[tp_CalculatedVersion], UserData.[nvarchar12], t12.[nvarchar1] AS c33c4, t12.[nvarchar11] AS c33c9, t19.[nvarchar12] AS c40c10, t22.[nvarchar1] AS c43c4, t1.[ScopeId] AS c19, t15.[nvarchar1] AS c36c4, t15.[nvarchar11] AS c36c9, t21.[nvarchar4] AS c42c6, t31.[nvarchar4] AS c52c6, t1.[ClientId] AS c56, UserData.[nvarchar3], UserData.[int14], t18.[nvarchar11] AS c39c9, t2.[tp_ID] AS c3c5, UserData.[tp_CopySource], UserData.[nvarchar11], t14.[nvarchar4] AS c35c6, t18.[nvarchar1] AS c39c4, t20.[nvarchar9] AS c41c8, UserData.[tp_InstanceID], UserData.[int9], t17.[nvarchar4] AS c38c6, t22.[tp_Created] AS c43c11, t23.[nvarchar9] AS c44c8, t23.[tp_Created] AS c44c11, UserData.[int27], UserData.[nvarchar2], t13.[nvarchar9] AS c34c8, UserData.[int13], t19.[tp_ID] AS c40c5, t21.[tp_Created] AS c42c11, t1.[FolderChildCount] AS c62, t1.[ParentLeafName] AS c67, UserData.[nvarchar10], t16.[nvarchar9] AS c37c8, t20.[tp_Created] AS c41c11, t31.[nvarchar12] AS c52c10, UserData.[int8], t12.[tp_ID] AS c33c5, t31.[nvarchar6] AS c52c7, t9.[nvarchar10] AS c29c25, t7.[nvarchar10] AS c27c25, t18.[tp_ID] AS c39c5, UserData.[tp_ParentId], UserData.[tp_DocId], UserData.[int7], t20.[nvarchar1] AS c41c4, t23.[nvarchar11] AS c44c9, UserData.[tp_AppEditor], t13.[nvarchar1] AS c34c4, t17.[nvarchar6] AS c38c7, t19.[nvarchar4] AS c40c6, UserData.[tp_GUID], t3.[nvarchar1] AS c14c4, t16.[nvarchar11] AS c37c9, UserData.[int6], t12.[nvarchar4] AS c33c6, t31.[nvarchar9] AS c52c8, t31.[tp_Created] AS c52c11, t1.[DirName] AS c53, UserData.[tp_WorkflowVersion], UserData.[tp_CheckoutUserId], UserData.[nvarchar9], t18.[nvarchar4] AS c39c6, UserData.[tp_SiteId], UserData.[int5], t20.[tp_ID] AS c41c5, t17.[nvarchar9] AS c38c8, t19.[nvarchar6] AS c40c7, UserData.[nvarchar8], t13.[tp_ID] AS c34c5 FROM AllDocs AS t1 WITH(FORCESEEK(AllDocs_Url(SiteId,DeleteTransactionId)),NOLOCK) INNER LOOP JOIN AllUserData AS UserData ON (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 OR  UserData.tp_Level =255) ) AND (t1.Level = UserData.tp_Level) AND ((UserData.tp_Level = 255 AND t1.LTCheckoutUserId =@IU OR (UserData.tp_Level = 1 AND (UserData.tp_DraftOwnerId IS NULL) OR UserData.tp_Level = 2)AND (t1.LTCheckoutUserId IS NULL OR t1.LTCheckoutUserId <> @IU ))) AND (UserData.[tp_IsCurrentVersion] = CONVERT(bit,1) ) AND (UserData.[tp_CalculatedVersion] = 0 ) AND (UserData.[tp_DeleteTransactionId] = 0x ) AND (t1.[DeleteTransactionId] = 0x ) LEFT OUTER LOOP JOIN AllUserData AS t2 WITH(FORCESEEK(AllUserData_PK(tp_SiteId,tp_ListId,tp_DeleteTransactionId,tp_IsCurrentVersion,tp_ID,tp_CalculatedVersion)),NOLOCK) ON (UserData.[tp_Editor]=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 = @L3 AND t2.tp_SiteId = @SITEID) AND (UserData.tp_ListId = @L4 AND UserData.tp_SiteId = @SITEID) AND (UserData.[tp_IsCurrentVersion] = CONVERT(bit,1) ) AND (UserData.[tp_CalculatedVersion] = 0 ) AND (UserData.[tp_DeleteTransactionId] = 0x ) LEFT OUTER LOOP JOIN AllUserData AS t3 WITH(FORCESEEK(AllUserData_PK(tp_SiteId,tp_ListId,tp_DeleteTransactionId,tp_IsCurrentVersion,tp_ID,tp_CalculatedVersion)),NOLOCK) ON (UserData.[tp_CheckoutUserId]=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 = @L3 AND t3.tp_SiteId = @SITEID) AND (UserData.tp_ListId = @L4 AND UserData.tp_SiteId = @SITEID) AND (UserData.[tp_IsCurrentVersion] = CONVERT(bit,1) ) AND (UserData.[tp_CalculatedVersion] = 0 ) AND (UserData.[tp_DeleteTransactionId] = 0x ) LEFT OUTER LOOP JOIN AllUserData AS t5 WITH(FORCESEEK(AllUserData_PK(tp_SiteId,tp_ListId,tp_DeleteTransactionId,tp_IsCurrentVersion,tp_ID,tp_CalculatedVersion)),NOLOCK) ON (UserData.[int2]=t5.[tp_ID]) AND (UserData.[tp_RowOrdinal] = 0) AND (t5.[tp_RowOrdinal] = 0) AND ( (t5.tp_Level = 1) ) AND (t5.[tp_IsCurrentVersion] = CONVERT(bit,1) ) AND (t5.[tp_CalculatedVersion] = 0 ) AND (t5.[tp_DeleteTransactionId] = 0x ) AND (t5.tp_ListId = @L5 AND t5.tp_SiteId = @SITEID) AND (UserData.tp_ListId = @L4 AND UserData.tp_SiteId = @SITEID) AND (UserData.[tp_IsCurrentVersion] = CONVERT(bit,1) ) AND (UserData.[tp_CalculatedVersion] = 0 ) AND (UserData.[tp_DeleteTransactionId] = 0x ) LEFT OUTER LOOP JOIN AllUserData AS t7 WITH(FORCESEEK(AllUserData_PK(tp_SiteId,tp_ListId,tp_DeleteTransactionId,tp_IsCurrentVersion,tp_ID,tp_CalculatedVersion)),NOLOCK) ON (UserData.[int4]=t7.[tp_ID]) AND (UserData.[tp_RowOrdinal] = 0) AND (t7.[tp_RowOrdinal] = 0) AND ( (t7.tp_Level = 1) ) AND (t7.[tp_IsCurrentVersion] = CONVERT(bit,1) ) AND (t7.[tp_CalculatedVersion] = 0 ) AND (t7.[tp_DeleteTransactionId] = 0x ) AND (t7.tp_ListId = @L5 AND t7.tp_SiteId = @SITEID) AND (UserData.tp_ListId = @L4 AND UserData.tp_SiteId = @SITEID) AND (UserData.[tp_IsCurrentVersion] = CONVERT(bit,1) ) AND (UserData.[tp_CalculatedVersion] = 0 ) AND (UserData.[tp_DeleteTransactionId] = 0x ) LEFT OUTER LOOP JOIN AllUserData AS t8 WITH(FORCESEEK(AllUserData_PK(tp_SiteId,tp_ListId,tp_DeleteTransactionId,tp_IsCurrentVersion,tp_ID,tp_CalculatedVersion)),NOLOCK) ON (UserData.[int5]=t8.[tp_ID]) AND (UserData.[tp_RowOrdinal] = 0) AND (t8.[tp_RowOrdinal] = 0) AND ( (t8.tp_Level = 1) ) AND (t8.[tp_IsCurrentVersion] = CONVERT(bit,1) ) AND (t8.[tp_CalculatedVersion] = 0 ) AND (t8.[tp_DeleteTransactionId] = 0x ) AND (t8.tp_ListId = @L5 AND t8.tp_SiteId = @SITEID) AND (UserData.tp_ListId = @L4 AND UserData.tp_SiteId = @SITEID) AND (UserData.[tp_IsCurrentVersion] = CONVERT(bit,1) ) AND (UserData.[tp_CalculatedVersion] = 0 ) AND (UserData.[tp_DeleteTransactionId] = 0x ) LEFT OUTER LOOP JOIN AllUserData AS t9 WITH(FORCESEEK(AllUserData_PK(tp_SiteId,tp_ListId,tp_DeleteTransactionId,tp_IsCurrentVersion,tp_ID,tp_CalculatedVersion)),NOLOCK) ON (UserData.[int6]=t9.[tp_ID]) AND (UserData.[tp_RowOrdinal] = 0) AND (t9.[tp_RowOrdinal] = 0) AND ( (t9.tp_Level = 1) ) AND (t9.[tp_IsCurrentVersion] = CONVERT(bit,1) ) AND (t9.[tp_CalculatedVersion] = 0 ) AND (t9.[tp_DeleteTransactionId] = 0x ) AND (t9.tp_ListId = @L5 AND t9.tp_SiteId = @SITEID) AND (UserData.tp_ListId = @L4 AND UserData.tp_SiteId = @SITEID) AND (UserData.[tp_IsCurrentVersion] = CONVERT(bit,1) ) AND (UserData.[tp_CalculatedVersion] = 0 ) AND (UserData.[tp_DeleteTransactionId] = 0x ) LEFT OUTER LOOP JOIN AllUserData AS t11 WITH(FORCESEEK(AllUserData_PK(tp_SiteId,tp_ListId,tp_DeleteTransactionId,tp_IsCurrentVersion,tp_ID,tp_CalculatedVersion)),NOLOCK) ON (UserData.[int8]=t11.[tp_ID]) AND (UserData.[tp_RowOrdinal] = 0) AND (t11.[tp_RowOrdinal] = 0) AND ( (t11.tp_Level = 1) ) AND (t11.[tp_IsCurrentVersion] = CONVERT(bit,1) ) AND (t11.[tp_CalculatedVersion] = 0 ) AND (t11.[tp_DeleteTransactionId] = 0x ) AND (t11.tp_ListId = @L5 AND t11.tp_SiteId = @SITEID) AND (UserData.tp_ListId = @L4 AND UserData.tp_SiteId = @SITEID) AND (UserData.[tp_IsCurrentVersion] = CONVERT(bit,1) ) AND (UserData.[tp_CalculatedVersion] = 0 ) AND (UserData.[tp_DeleteTransactionId] = 0x ) LEFT OUTER LOOP JOIN AllUserData AS t12 WITH(FORCESEEK(AllUserData_PK(tp_SiteId,tp_ListId,tp_DeleteTransactionId,tp_IsCurrentVersion,tp_ID,tp_CalculatedVersion)),NOLOCK) ON (UserData.[int9]=t12.[tp_ID]) AND (UserData.[tp_RowOrdinal] = 0) AND (t12.[tp_RowOrdinal] = 0) AND ( (t12.tp_Level = 1) ) AND (t12.[tp_IsCurrentVersion] = CONVERT(bit,1) ) AND (t12.[tp_CalculatedVersion] = 0 ) AND (t12.[tp_DeleteTransactionId] = 0x ) AND (t12.tp_ListId = @L3 AND t12.tp_SiteId = @SITEID) AND (UserData.tp_ListId = @L4 AND UserData.tp_SiteId = @SITEID) AND (UserData.[tp_IsCurrentVersion] = CONVERT(bit,1) ) AND (UserData.[tp_CalculatedVersion] = 0 ) AND (UserData.[tp_DeleteTransactionId] = 0x ) LEFT OUTER LOOP JOIN AllUserData AS t13 WITH(FORCESEEK(AllUserData_PK(tp_SiteId,tp_ListId,tp_DeleteTransactionId,tp_IsCurrentVersion,tp_ID,tp_CalculatedVersion)),NOLOCK) ON (UserData.[int10]=t13.[tp_ID]) AND (UserData.[tp_RowOrdinal] = 0) AND (t13.[tp_RowOrdinal] = 0) AND ( (t13.tp_Level = 1) ) AND (t13.[tp_IsCurrentVersion] = CONVERT(bit,1) ) AND (t13.[tp_CalculatedVersion] = 0 ) AND (t13.[tp_DeleteTransactionId] = 0x ) AND (t13.tp_ListId = @L3 AND t13.tp_SiteId = @SITEID) AND (UserData.tp_ListId = @L4 AND UserData.tp_SiteId = @SITEID) AND (UserData.[tp_IsCurrentVersion] = CONVERT(bit,1) ) AND (UserData.[tp_CalculatedVersion] = 0 ) AND (UserData.[tp_DeleteTransactionId] = 0x ) LEFT OUTER LOOP JOIN AllUserData AS t14 WITH(FORCESEEK(AllUserData_PK(tp_SiteId,tp_ListId,tp_DeleteTransactionId,tp_IsCurrentVersion,tp_ID,tp_CalculatedVersion)),NOLOCK) ON (UserData.[int11]=t14.[tp_ID]) AND (UserData.[tp_RowOrdinal] = 0) AND (t14.[tp_RowOrdinal] = 0) AND ( (t14.tp_Level = 1) ) AND (t14.[tp_IsCurrentVersion] = CONVERT(bit,1) ) AND (t14.[tp_CalculatedVersion] = 0 ) AND (t14.[tp_DeleteTransactionId] = 0x ) AND (t14.tp_ListId = @L3 AND t14.tp_SiteId = @SITEID) AND (UserData.tp_ListId = @L4 AND UserData.tp_SiteId = @SITEID) AND (UserData.[tp_IsCurrentVersion] = CONVERT(bit,1) ) AND (UserData.[tp_CalculatedVersion] = 0 ) AND (UserData.[tp_DeleteTransactionId] = 0x ) LEFT OUTER LOOP JOIN AllUserData AS t15 WITH(FORCESEEK(AllUserData_PK(tp_SiteId,tp_ListId,tp_DeleteTransactionId,tp_IsCurrentVersion,tp_ID,tp_CalculatedVersion)),NOLOCK) ON (UserData.[int12]=t15.[tp_ID]) AND (UserData.[tp_RowOrdinal] = 0) AND (t15.[tp_RowOrdinal] = 0) AND ( (t15.tp_Level = 1) ) AND (t15.[tp_IsCurrentVersion] = CONVERT(bit,1) ) AND (t15.[tp_CalculatedVersion] = 0 ) AND (t15.[tp_DeleteTransactionId] = 0x ) AND (t15.tp_ListId = @L3 AND t15.tp_SiteId = @SITEID) AND (UserData.tp_ListId = @L4 AND UserData.tp_SiteId = @SITEID) AND (UserData.[tp_IsCurrentVersion] = CONVERT(bit,1) ) AND (UserData.[tp_CalculatedVersion] = 0 ) AND (UserData.[tp_DeleteTransactionId] = 0x ) LEFT OUTER LOOP JOIN AllUserData AS t16 WITH(FORCESEEK(AllUserData_PK(tp_SiteId,tp_ListId,tp_DeleteTransactionId,tp_IsCurrentVersion,tp_ID,tp_CalculatedVersion)),NOLOCK) ON (UserData.[int13]=t16.[tp_ID]) AND (UserData.[tp_RowOrdinal] = 0) AND (t16.[tp_RowOrdinal] = 0) AND ( (t16.tp_Level = 1) ) AND (t16.[tp_IsCurrentVersion] = CONVERT(bit,1) ) AND (t16.[tp_CalculatedVersion] = 0 ) AND (t16.[tp_DeleteTransactionId] = 0x ) AND (t16.tp_ListId = @L3 AND t16.tp_SiteId = @SITEID) AND (UserData.tp_ListId = @L4 AND UserData.tp_SiteId = @SITEID) AND (UserData.[tp_IsCurrentVersion] = CONVERT(bit,1) ) AND (UserData.[tp_CalculatedVersion] = 0 ) AND (UserData.[tp_DeleteTransactionId] = 0x ) LEFT OUTER LOOP JOIN AllUserData AS t17 WITH(FORCESEEK(AllUserData_PK(tp_SiteId,tp_ListId,tp_DeleteTransactionId,tp_IsCurrentVersion,tp_ID,tp_CalculatedVersion)),NOLOCK) ON (UserData.[int14]=t17.[tp_ID]) AND (UserData.[tp_RowOrdinal] = 0) AND (t17.[tp_RowOrdinal] = 0) AND ( (t17.tp_Level = 1) ) AND (t17.[tp_IsCurrentVersion] = CONVERT(bit,1) ) AND (t17.[tp_CalculatedVersion] = 0 ) AND (t17.[tp_DeleteTransactionId] = 0x ) AND (t17.tp_ListId = @L3 AND t17.tp_SiteId = @SITEID) AND (UserData.tp_ListId = @L4 AND UserData.tp_SiteId = @SITEID) AND (UserData.[tp_IsCurrentVersion] = CONVERT(bit,1) ) AND (UserData.[tp_CalculatedVersion] = 0 ) AND (UserData.[tp_DeleteTransactionId] = 0x ) LEFT OUTER LOOP JOIN AllUserData AS t18 WITH(FORCESEEK(AllUserData_PK(tp_SiteId,tp_ListId,tp_DeleteTransactionId,tp_IsCurrentVersion,tp_ID,tp_CalculatedVersion)),NOLOCK) ON (UserData.[int15]=t18.[tp_ID]) AND (UserData.[tp_RowOrdinal] = 0) AND (t18.[tp_RowOrdinal] = 0) AND ( (t18.tp_Level = 1) ) AND (t18.[tp_IsCurrentVersion] = CONVERT(bit,1) ) AND (t18.[tp_CalculatedVersion] = 0 ) AND (t18.[tp_DeleteTransactionId] = 0x ) AND (t18.tp_ListId = @L3 AND t18.tp_SiteId = @SITEID) AND (UserData.tp_ListId = @L4 AND UserData.tp_SiteId = @SITEID) AND (UserData.[tp_IsCurrentVersion] = CONVERT(bit,1) ) AND (UserData.[tp_CalculatedVersion] = 0 ) AND (UserData.[tp_DeleteTransactionId] = 0x ) LEFT OUTER LOOP JOIN AllUserData AS t19 WITH(FORCESEEK(AllUserData_PK(tp_SiteId,tp_ListId,tp_DeleteTransactionId,tp_IsCurrentVersion,tp_ID,tp_CalculatedVersion)),NOLOCK) ON (UserData.[int16]=t19.[tp_ID]) AND (UserData.[tp_RowOrdinal] = 0) AND (t19.[tp_RowOrdinal] = 0) AND ( (t19.tp_Level = 1) ) AND (t19.[tp_IsCurrentVersion] = CONVERT(bit,1) ) AND (t19.[tp_CalculatedVersion] = 0 ) AND (t19.[tp_DeleteTransactionId] = 0x ) AND (t19.tp_ListId = @L3 AND t19.tp_SiteId = @SITEID) AND (UserData.tp_ListId = @L4 AND UserData.tp_SiteId = @SITEID) AND (UserData.[tp_IsCurrentVersion] = CONVERT(bit,1) ) AND (UserData.[tp_CalculatedVersion] = 0 ) AND (UserData.[tp_DeleteTransactionId] = 0x ) LEFT OUTER LOOP JOIN AllUserData AS t20 WITH(FORCESEEK(AllUserData_PK(tp_SiteId,tp_ListId,tp_DeleteTransactionId,tp_IsCurrentVersion,tp_ID,tp_CalculatedVersion)),NOLOCK) ON (UserData.[int17]=t20.[tp_ID]) AND (UserData.[tp_RowOrdinal] = 0) AND (t20.[tp_RowOrdinal] = 0) AND ( (t20.tp_Level = 1) ) AND (t20.[tp_IsCurrentVersion] = CONVERT(bit,1) ) AND (t20.[tp_CalculatedVersion] = 0 ) AND (t20.[tp_DeleteTransactionId] = 0x ) AND (t20.tp_ListId = @L3 AND t20.tp_SiteId = @SITEID) AND (UserData.tp_ListId = @L4 AND UserData.tp_SiteId = @SITEID) AND (UserData.[tp_IsCurrentVersion] = CONVERT(bit,1) ) AND (UserData.[tp_CalculatedVersion] = 0 ) AND (UserData.[tp_DeleteTransactionId] = 0x ) LEFT OUTER LOOP JOIN AllUserData AS t21 WITH(FORCESEEK(AllUserData_PK(tp_SiteId,tp_ListId,tp_DeleteTransactionId,tp_IsCurrentVersion,tp_ID,tp_CalculatedVersion)),NOLOCK) ON (UserData.[int18]=t21.[tp_ID]) AND (UserData.[tp_RowOrdinal] = 0) AND (t21.[tp_RowOrdinal] = 0) AND ( (t21.tp_Level = 1) ) AND (t21.[tp_IsCurrentVersion] = CONVERT(bit,1) ) AND (t21.[tp_CalculatedVersion] = 0 ) AND (t21.[tp_DeleteTransactionId] = 0x ) AND (t21.tp_ListId = @L3 AND t21.tp_SiteId = @SITEID) AND (UserData.tp_ListId = @L4 AND UserData.tp_SiteId = @SITEID) AND (UserData.[tp_IsCurrentVersion] = CONVERT(bit,1) ) AND (UserData.[tp_CalculatedVersion] = 0 ) AND (UserData.[tp_DeleteTransactionId] = 0x ) LEFT OUTER LOOP JOIN AllUserData AS t22 WITH(FORCESEEK(AllUserData_PK(tp_SiteId,tp_ListId,tp_DeleteTransactionId,tp_IsCurrentVersion,tp_ID,tp_CalculatedVersion)),NOLOCK) ON (UserData.[int19]=t22.[tp_ID]) AND (UserData.[tp_RowOrdinal] = 0) AND (t22.[tp_RowOrdinal] = 0) AND ( (t22.tp_Level = 1) ) AND (t22.[tp_IsCurrentVersion] = CONVERT(bit,1) ) AND (t22.[tp_CalculatedVersion] = 0 ) AND (t22.[tp_DeleteTransactionId] = 0x ) AND (t22.tp_ListId = @L3 AND t22.tp_SiteId = @SITEID) AND (UserData.tp_ListId = @L4 AND UserData.tp_SiteId = @SITEID) AND (UserData.[tp_IsCurrentVersion] = CONVERT(bit,1) ) AND (UserData.[tp_CalculatedVersion] = 0 ) AND (UserData.[tp_DeleteTransactionId] = 0x ) LEFT OUTER LOOP JOIN AllUserData AS t23 WITH(FORCESEEK(AllUserData_PK(tp_SiteId,tp_ListId,tp_DeleteTransactionId,tp_IsCurrentVersion,tp_ID,tp_CalculatedVersion)),NOLOCK) ON (UserData.[int20]=t23.[tp_ID]) AND (UserData.[tp_RowOrdinal] = 0) AND (t23.[tp_RowOrdinal] = 0) AND ( (t23.tp_Level = 1) ) AND (t23.[tp_IsCurrentVersion] = CONVERT(bit,1) ) AND (t23.[tp_CalculatedVersion] = 0 ) AND (t23.[tp_DeleteTransactionId] = 0x ) AND (t23.tp_ListId = @L3 AND t23.tp_SiteId = @SITEID) AND (UserData.tp_ListId = @L4 AND UserData.tp_SiteId = @SITEID) AND (UserData.[tp_IsCurrentVersion] = CONVERT(bit,1) ) AND (UserData.[tp_CalculatedVersion] = 0 ) AND (UserData.[tp_DeleteTransactionId] = 0x ) LEFT OUTER LOOP JOIN AllUserData AS t24 WITH(FORCESEEK(AllUserData_PK(tp_SiteId,tp_ListId,tp_DeleteTransactionId,tp_IsCurrentVersion,tp_ID,tp_CalculatedVersion)),NOLOCK) ON (UserData.[int21]=t24.[tp_ID]) AND (UserData.[tp_RowOrdinal] = 0) AND (t24.[tp_RowOrdinal] = 0) AND ( (t24.tp_Level = 1) ) AND (t24.[tp_IsCurrentVersion] = CONVERT(bit,1) ) AND (t24.[tp_CalculatedVersion] = 0 ) AND (t24.[tp_DeleteTransactionId] = 0x ) AND (t24.tp_ListId = @L5 AND t24.tp_SiteId = @SITEID) AND (UserData.tp_ListId = @L4 AND UserData.tp_SiteId = @SITEID) AND (UserData.[tp_IsCurrentVersion] = CONVERT(bit,1) ) AND (UserData.[tp_CalculatedVersion] = 0 ) AND (UserData.[tp_DeleteTransactionId] = 0x ) LEFT OUTER LOOP JOIN AllUserData AS t31 WITH(FORCESEEK(AllUserData_PK(tp_SiteId,tp_ListId,tp_DeleteTransactionId,tp_IsCurrentVersion,tp_ID,tp_CalculatedVersion)),NOLOCK) ON (UserData.[tp_Author]=t31.[tp_ID]) AND (UserData.[tp_RowOrdinal] = 0) AND (t31.[tp_RowOrdinal] = 0) AND ( (t31.tp_Level = 1) ) AND (t31.[tp_IsCurrentVersion] = CONVERT(bit,1) ) AND (t31.[tp_CalculatedVersion] = 0 ) AND (t31.[tp_DeleteTransactionId] = 0x ) AND (t31.tp_ListId = @L3 AND t31.tp_SiteId = @SITEID) AND (UserData.tp_ListId = @L4 AND UserData.tp_SiteId = @SITEID) AND (UserData.[tp_IsCurrentVersion] = CONVERT(bit,1) ) AND (UserData.[tp_CalculatedVersion] = 0 ) AND (UserData.[tp_DeleteTransactionId] = 0x ) LEFT OUTER LOOP JOIN AllUserData AS t32 WITH(FORCESEEK(AllUserData_PK(tp_SiteId,tp_ListId,tp_DeleteTransactionId,tp_IsCurrentVersion,tp_ID,tp_CalculatedVersion)),NOLOCK) ON (t1.[LTCheckoutUserId]=t32.[tp_ID]) AND (t32.[tp_RowOrdinal] = 0) AND ( (t32.tp_Level = 1) ) AND (t32.[tp_IsCurrentVersion] = CONVERT(bit,1) ) AND (t32.[tp_CalculatedVersion] = 0 ) AND (t32.[tp_DeleteTransactionId] = 0x ) AND (t32.tp_ListId = @L3 AND t32.tp_SiteId = @SITEID) LEFT OUTER LOOP JOIN AppPrincipals AS t33 WITH(NOLOCK) ON (UserData.[tp_AppAuthor]=t33.[Id]) AND (UserData.[tp_RowOrdinal] = 0) AND (t33.SiteId = @SITEID) AND (UserData.tp_ListId = @L4 AND UserData.tp_SiteId = @SITEID) AND (UserData.[tp_IsCurrentVersion] = CONVERT(bit,1) ) AND (UserData.[tp_CalculatedVersion] = 0 ) AND (UserData.[tp_DeleteTransactionId] = 0x ) LEFT OUTER LOOP JOIN AppPrincipals AS t34 WITH(NOLOCK) ON (UserData.[tp_AppEditor]=t34.[Id]) AND (UserData.[tp_RowOrdinal] = 0) AND (t34.SiteId = @SITEID) AND (UserData.tp_ListId = @L4 AND UserData.tp_SiteId = @SITEID) AND (UserData.[tp_IsCurrentVersion] = CONVERT(bit,1) ) AND (UserData.[tp_CalculatedVersion] = 0 ) AND (UserData.[tp_DeleteTransactionId] = 0x ) WHERE ( (UserData.tp_Level = 1 OR  UserData.tp_Level =255)  AND ( UserData.tp_Level= 255 AND UserData.tp_CheckoutUserId = @IU OR  ( UserData.tp_Level  = 2 AND UserData.tp_DraftOwnerId IS NOT NULL OR UserData.tp_Level  = 1 AND UserData.tp_DraftOwnerId IS  NULL  ) AND ( UserData.tp_CheckoutUserId IS  NULL  OR UserData.tp_CheckoutUserId <> @IU))) AND (UserData.tp_SiteId=@SITEID) AND (UserData.tp_RowOrdinal=0) AND (((t1.[LeafName] = @L6LNP) AND (t1.[DirName] = @L7DNP)) AND t1.SiteId=@SITEID AND (t1.DirName=@DN OR t1.DirName LIKE @DNEL+N''/%'')) ORDER BY t1.[SortBehavior]  DESC ,UserData.[tp_ID]  ASC ) AS t35 LEFT OUTER JOIN AllUserDataJunctions AS t36 WITH(FORCESEEK(AllUserDataJunctions_PK(tp_SiteId,tp_DeleteTransactionId,tp_IsCurrentVersion,tp_ParentId,tp_DocId,tp_CalculatedVersion,tp_Level)),NOLOCK) ON (t36.[tp_DeleteTransactionId] = 0x ) AND (t36.[tp_IsCurrentVersion] = CONVERT(bit,1) ) AND (t36.[tp_CalculatedVersion] = 0 ) AND (t35.[tp_SiteId] = t36.[tp_SiteId] AND t35.[tp_DeleteTransactionId] = t36.[tp_DeleteTransactionId] AND t35.[tp_IsCurrentVersion] = t36.[tp_IsCurrentVersion] AND t35.[tp_ParentId] = t36.[tp_ParentId] AND t35.[tp_DocId] = t36.[tp_DocId] AND t35.[tp_CalculatedVersion] = t36.[tp_CalculatedVersion] AND t35.[tp_Level] = t36.[tp_Level]) LEFT OUTER JOIN AllUserDataJunctions AS t4 WITH(FORCESEEK(AllUserDataJunctions_PK(tp_SiteId,tp_DeleteTransactionId,tp_IsCurrentVersion,tp_ParentId,tp_DocId,tp_CalculatedVersion,tp_Level,tp_FieldId,tp_Ordinal)),NOLOCK) ON (t4.[tp_DeleteTransactionId] = 0x ) AND (t4.[tp_IsCurrentVersion] = CONVERT(bit,1) ) AND (t4.[tp_CalculatedVersion] = 0 ) AND (t35.[tp_SiteId] = t4.[tp_SiteId] AND t35.[tp_DeleteTransactionId] = t4.[tp_DeleteTransactionId] AND t35.[tp_IsCurrentVersion] = t4.[tp_IsCurrentVersion] AND t35.[tp_ParentId] = t4.[tp_ParentId] AND t35.[tp_DocId] = t4.[tp_DocId] AND t35.[tp_CalculatedVersion] = t4.[tp_CalculatedVersion] AND t35.[tp_Level] = t4.[tp_Level]) AND (t36.[tp_Ordinal] = t4.[tp_Ordinal]) AND (t4.[tp_FieldId] = @L11) LEFT OUTER JOIN AllUserDataJunctions AS t6 WITH(FORCESEEK(AllUserDataJunctions_PK(tp_SiteId,tp_DeleteTransactionId,tp_IsCurrentVersion,tp_ParentId,tp_DocId,tp_CalculatedVersion,tp_Level,tp_FieldId,tp_Ordinal)),NOLOCK) ON (t6.[tp_DeleteTransactionId] = 0x ) AND (t6.[tp_IsCurrentVersion] = CONVERT(bit,1) ) AND (t6.[tp_CalculatedVersion] = 0 ) AND (t35.[tp_SiteId] = t6.[tp_SiteId] AND t35.[tp_DeleteTransactionId] = t6.[tp_DeleteTransactionId] AND t35.[tp_IsCurrentVersion] = t6.[tp_IsCurrentVersion] AND t35.[tp_ParentId] = t6.[tp_ParentId] AND t35.[tp_DocId] = t6.[tp_DocId] AND t35.[tp_CalculatedVersion] = t6.[tp_CalculatedVersion] AND t35.[tp_Level] = t6.[tp_Level]) AND (t36.[tp_Ordinal] = t6.[tp_Ordinal]) AND (t6.[tp_FieldId] = @L12) LEFT OUTER JOIN AllUserDataJunctions AS t10 WITH(FORCESEEK(AllUserDataJunctions_PK(tp_SiteId,tp_DeleteTransactionId,tp_IsCurrentVersion,tp_ParentId,tp_DocId,tp_CalculatedVersion,tp_Level,tp_FieldId,tp_Ordinal)),NOLOCK) ON (t10.[tp_DeleteTransactionId] = 0x ) AND (t10.[tp_IsCurrentVersion] = CONVERT(bit,1) ) AND (t10.[tp_CalculatedVersion] = 0 ) AND (t35.[tp_SiteId] = t10.[tp_SiteId] AND t35.[tp_DeleteTransactionId] = t10.[tp_DeleteTransactionId] AND t35.[tp_IsCurrentVersion] = t10.[tp_IsCurrentVersion] AND t35.[tp_ParentId] = t10.[tp_ParentId] AND t35.[tp_DocId] = t10.[tp_DocId] AND t35.[tp_CalculatedVersion] = t10.[tp_CalculatedVersion] AND t35.[tp_Level] = t10.[tp_Level]) AND (t36.[tp_Ordinal] = t10.[tp_Ordinal]) AND (t10.[tp_FieldId] = @L13) LEFT OUTER JOIN AllUserDataJunctions AS t25 WITH(FORCESEEK(AllUserDataJunctions_PK(tp_SiteId,tp_DeleteTransactionId,tp_IsCurrentVersion,tp_ParentId,tp_DocId,tp_CalculatedVersion,tp_Level,tp_FieldId,tp_Ordinal)),NOLOCK) ON (t25.[tp_DeleteTransactionId] = 0x ) AND (t25.[tp_IsCurrentVersion] = CONVERT(bit,1) ) AND (t25.[tp_CalculatedVersion] = 0 ) AND (t35.[tp_SiteId] = t25.[tp_SiteId] AND t35.[tp_DeleteTransactionId] = t25.[tp_DeleteTransactionId] AND t35.[tp_IsCurrentVersion] = t25.[tp_IsCurrentVersion] AND t35.[tp_ParentId] = t25.[tp_ParentId] AND t35.[tp_DocId] = t25.[tp_DocId] AND t35.[tp_CalculatedVersion] = t25.[tp_CalculatedVersion] AND t35.[tp_Level] = t25.[tp_Level]) AND (t36.[tp_Ordinal] = t25.[tp_Ordinal]) AND (t25.[tp_FieldId] = @L14) LEFT OUTER JOIN AllUserDataJunctions AS t26 WITH(FORCESEEK(AllUserDataJunctions_PK(tp_SiteId,tp_DeleteTransactionId,tp_IsCurrentVersion,tp_ParentId,tp_DocId,tp_CalculatedVersion,tp_Level,tp_FieldId,tp_Ordinal)),NOLOCK) ON (t26.[tp_DeleteTransactionId] = 0x ) AND (t26.[tp_IsCurrentVersion] = CONVERT(bit,1) ) AND (t26.[tp_CalculatedVersion] = 0 ) AND (t35.[tp_SiteId] = t26.[tp_SiteId] AND t35.[tp_DeleteTransactionId] = t26.[tp_DeleteTransactionId] AND t35.[tp_IsCurrentVersion] = t26.[tp_IsCurrentVersion] AND t35.[tp_ParentId] = t26.[tp_ParentId] AND t35.[tp_DocId] = t26.[tp_DocId] AND t35.[tp_CalculatedVersion] = t26.[tp_CalculatedVersion] AND t35.[tp_Level] = t26.[tp_Level]) AND (t36.[tp_Ordinal] = t26.[tp_Ordinal]) AND (t26.[tp_FieldId] = @L15) LEFT OUTER JOIN AllUserDataJunctions AS t27 WITH(FORCESEEK(AllUserDataJunctions_PK(tp_SiteId,tp_DeleteTransactionId,tp_IsCurrentVersion,tp_ParentId,tp_DocId,tp_CalculatedVersion,tp_Level,tp_FieldId,tp_Ordinal)),NOLOCK) ON (t27.[tp_DeleteTransactionId] = 0x ) AND (t27.[tp_IsCurrentVersion] = CONVERT(bit,1) ) AND (t27.[tp_CalculatedVersion] = 0 ) AND (t35.[tp_SiteId] = t27.[tp_SiteId] AND t35.[tp_DeleteTransactionId] = t27.[tp_DeleteTransactionId] AND t35.[tp_IsCurrentVersion] = t27.[tp_IsCurrentVersion] AND t35.[tp_ParentId] = t27.[tp_ParentId] AND t35.[tp_DocId] = t27.[tp_DocId] AND t35.[tp_CalculatedVersion] = t27.[tp_CalculatedVersion] AND t35.[tp_Level] = t27.[tp_Level]) AND (t36.[tp_Ordinal] = t27.[tp_Ordinal]) AND (t27.[tp_FieldId] = @L16) LEFT OUTER JOIN AllUserDataJunctions AS t28 WITH(FORCESEEK(AllUserDataJunctions_PK(tp_SiteId,tp_DeleteTransactionId,tp_IsCurrentVersion,tp_ParentId,tp_DocId,tp_CalculatedVersion,tp_Level,tp_FieldId,tp_Ordinal)),NOLOCK) ON (t28.[tp_DeleteTransactionId] = 0x ) AND (t28.[tp_IsCurrentVersion] = CONVERT(bit,1) ) AND (t28.[tp_CalculatedVersion] = 0 ) AND (t35.[tp_SiteId] = t28.[tp_SiteId] AND t35.[tp_DeleteTransactionId] = t28.[tp_DeleteTransactionId] AND t35.[tp_IsCurrentVersion] = t28.[tp_IsCurrentVersion] AND t35.[tp_ParentId] = t28.[tp_ParentId] AND t35.[tp_DocId] = t28.[tp_DocId] AND t35.[tp_CalculatedVersion] = t28.[tp_CalculatedVersion] AND t35.[tp_Level] = t28.[tp_Level]) AND 
    (t36.[tp_Ordinal] = t28.[tp_Ordinal]) AND (t28.[tp_FieldId] = @L17) LEFT OUTER JOIN AllUserDataJunctions AS t29 
    WITH(FORCESEEK(AllUserDataJunctions_PK(tp_SiteId,tp_DeleteTransactionId,tp_IsCurrentVersion,tp_ParentId,tp_DocId,tp_CalculatedVersion,tp_Level,tp_FieldId,tp_Ordinal)),NOLOCK) ON (t29.[tp_DeleteTransactionId] = 0x ) AND 
    (t29.[tp_IsCurrentVersion] = CONVERT(bit,1) ) AND (t29.[tp_CalculatedVersion] = 0 ) AND (t35.[tp_SiteId] = t29.[tp_SiteId] AND t35.[tp_DeleteTransactionId] = t29.[tp_DeleteTransactionId] AND t35.[tp_IsCurrentVersion] = 
    t29.[tp_IsCurrentVersion] AND t35.[tp_ParentId] = t29.[tp_ParentId] AND t35.[tp_DocId] = t29.[tp_DocId] AND t35.[tp_CalculatedVersion] = t29.[tp_CalculatedVersion] AND t35.[tp_Level] = t29.[tp_Level]) AND 
    (t36.[tp_Ordinal] = t29.[tp_Ordinal]) AND (t29.[tp_FieldId] = @L18) LEFT OUTER JOIN AllUserDataJunctions AS t30 
    WITH(FORCESEEK(AllUserDataJunctions_PK(tp_SiteId,tp_DeleteTransactionId,tp_IsCurrentVersion,tp_ParentId,tp_DocId,tp_CalculatedVersion,tp_Level,tp_FieldId,tp_Ordinal)),NOLOCK) ON (t30.[tp_DeleteTransactionId] = 0x ) AND 
    (t30.[tp_IsCurrentVersion] = CONVERT(bit,1) ) AND (t30.[tp_CalculatedVersion] = 0 ) AND (t35.[tp_SiteId] = t30.[tp_SiteId] AND t35.[tp_DeleteTransactionId] = t30.[tp_DeleteTransactionId] AND t35.[tp_IsCurrentVersion] = 
    t30.[tp_IsCurrentVersion] AND t35.[tp_ParentId] = t30.[tp_ParentId] AND t35.[tp_DocId] = t30.[tp_DocId] AND t35.[tp_CalculatedVersion] = t30.[tp_CalculatedVersion] AND t35.[tp_Level] = t30.[tp_Level]) AND 
    (t36.[tp_Ordinal] = t30.[tp_Ordinal]) AND (t30.[tp_FieldId] = @L19) LEFT OUTER LOOP JOIN AllUserData AS t37 
    WITH(FORCESEEK(AllUserData_PK(tp_SiteId,tp_ListId,tp_DeleteTransactionId,tp_IsCurrentVersion,tp_Id,tp_CalculatedVersion)),NOLOCK) ON (t37.[tp_SiteId] = @SITEID) AND (t37.[tp_ListId] = @L5) AND (t37.[tp_Id] = t4.[tp_ID]) 
    AND (t37.[tp_RowOrdinal] = 0) AND (t37.[tp_IsCurrentVersion] = CONVERT(bit,1) ) AND (t37.[tp_CalculatedVersion] = 0 ) AND (t37.[tp_DeleteTransactionId] = 0x ) AND ( (t37.tp_Level = 1) ) LEFT OUTER LOOP JOIN AllUserData AS t38 WITH(FORCESEEK(AllUserData_PK(tp_SiteId,tp_ListId,tp_DeleteTransactionId,tp_IsCurrentVersion,tp_Id,tp_CalculatedVersion)),NOLOCK) ON (t38.[tp_SiteId] = @SITEID) AND (t38.[tp_ListId] = @L5) AND (t38.[tp_Id] = t6.[tp_ID]) AND (t38.[tp_RowOrdinal] = 0) AND (t38.[tp_IsCurrentVersion] = CONVERT(bit,1) ) AND (t38.[tp_CalculatedVersion] = 0 ) AND (t38.[tp_DeleteTransactionId] = 0x ) AND ( (t38.tp_Level = 1) ) LEFT OUTER LOOP JOIN AllUserData AS t39 WITH(FORCESEEK(AllUserData_PK(tp_SiteId,tp_ListId,tp_DeleteTransactionId,tp_IsCurrentVersion,tp_Id,tp_CalculatedVersion)),NOLOCK) ON (t39.[tp_SiteId] = @SITEID) AND (t39.[tp_ListId] = @L5) AND (t39.[tp_Id] = t10.[tp_ID]) AND (t39.[tp_RowOrdinal] = 0) AND (t39.[tp_IsCurrentVersion] = CONVERT(bit,1) ) AND (t39.[tp_CalculatedVersion] = 0 ) AND (t39.[tp_DeleteTransactionId] = 0x ) AND ( (t39.tp_Level = 1) ) LEFT OUTER LOOP JOIN AllUserData AS t40 WITH(FORCESEEK(AllUserData_PK(tp_SiteId,tp_ListId,tp_DeleteTransactionId,tp_IsCurrentVersion,tp_Id,tp_CalculatedVersion)),NOLOCK) ON (t40.[tp_SiteId] = @SITEID) AND (t40.[tp_ListId] = @L5) AND (t40.[tp_Id] = t25.[tp_ID]) AND (t40.[tp_RowOrdinal] = 0) AND (t40.[tp_IsCurrentVersion] = CONVERT(bit,1) ) AND (t40.[tp_CalculatedVersion] = 0 ) AND (t40.[tp_DeleteTransactionId] = 0x ) AND ( (t40.tp_Level = 1) ) LEFT OUTER LOOP JOIN AllUserData AS t41 WITH(FORCESEEK(AllUserData_PK(tp_SiteId,tp_ListId,tp_DeleteTransactionId,tp_IsCurrentVersion,tp_Id,tp_CalculatedVersion)),NOLOCK) ON (t41.[tp_SiteId] = @SITEID) AND (t41.[tp_ListId] = @L5) AND (t41.[tp_Id] = t26.[tp_ID]) AND (t41.[tp_RowOrdinal] = 0) AND (t41.[tp_IsCurrentVersion] = CONVERT(bit,1) ) AND (t41.[tp_CalculatedVersion] = 0 ) AND (t41.[tp_DeleteTransactionId] = 0x ) AND ( (t41.tp_Level = 1) ) LEFT OUTER LOOP JOIN AllUserData AS t42 WITH(FORCESEEK(AllUserData_PK(tp_SiteId,tp_ListId,tp_DeleteTransactionId,tp_IsCurrentVersion,tp_Id,tp_CalculatedVersion)),NOLOCK) ON (t42.[tp_SiteId] = 
    @SITEID) AND (t42.[tp_ListId] = @L5) AND (t42.[tp_Id] = t27.[tp_ID]) AND (t42.[tp_RowOrdinal] = 0) AND (t42.[tp_IsCurrentVersion] = CONVERT(bit,1) ) AND (t42.[tp_CalculatedVersion] = 0 ) AND (t42.[tp_DeleteTransactionId] 
    = 0x ) AND ( (t42.tp_Level = 1) ) LEFT OUTER LOOP JOIN AllUserData AS t43 WITH(FORCESEEK(AllUserData_PK(tp_SiteId,tp_ListId,tp_DeleteTransactionId,tp_IsCurrentVersion,tp_Id,tp_CalculatedVersion)),NOLOCK) ON 
    (t43.[tp_SiteId] = @SITEID) AND (t43.[tp_ListId] = @L5) AND (t43.[tp_Id] = t28.[tp_ID]) AND (t43.[tp_RowOrdinal] = 0) AND (t43.[tp_IsCurrentVersion] = CONVERT(bit,1) ) AND (t43.[tp_CalculatedVersion] = 0 ) AND 
    (t43.[tp_DeleteTransactionId] = 0x ) AND ( (t43.tp_Level = 1) ) LEFT OUTER LOOP JOIN AllUserData AS t44 
    WITH(FORCESEEK(AllUserData_PK(tp_SiteId,tp_ListId,tp_DeleteTransactionId,tp_IsCurrentVersion,tp_Id,tp_CalculatedVersion)),NOLOCK) ON (t44.[tp_SiteId] = @SITEID) AND (t44.[tp_ListId] = @L5) AND (t44.[tp_Id] = t29.[tp_ID]) 
    AND (t44.[tp_RowOrdinal] = 0) AND (t44.[tp_IsCurrentVersion] = CONVERT(bit,1) ) AND (t44.[tp_CalculatedVersion] = 0 ) AND (t44.[tp_DeleteTransactionId] = 0x ) AND ( (t44.tp_Level = 1) ) LEFT OUTER LOOP JOIN AllUserData 
    AS t45 WITH(FORCESEEK(AllUserData_PK(tp_SiteId,tp_ListId,tp_DeleteTransactionId,tp_IsCurrentVersion,tp_Id,tp_CalculatedVersion)),NOLOCK) ON (t45.[tp_SiteId] = @SITEID) AND (t45.[tp_ListId] = @L5) AND (t45.[tp_Id] = 
    t30.[tp_ID]) AND (t45.[tp_RowOrdinal] = 0) AND (t45.[tp_IsCurrentVersion] = CONVERT(bit,1) ) AND (t45.[tp_CalculatedVersion] = 0 ) AND (t45.[tp_DeleteTransactionId] = 0x ) AND ( (t45.tp_Level = 1) )) AS t46 LEFT OUTER 
    JOIN AllDocs AS t48 WITH(FORCESEEK(AllDocs_ParentId(SiteId,DeleteTransactionId,ParentId,Id,Level)),NOLOCK) ON (t48.[SiteId] = t46.[tp_SiteId]) AND (t48.[DeleteTransactionId] = 0x AND t48.[DeleteTransactionId] = 
    t46.[tp_DeleteTransactionId]) AND (t48.[ParentId] = t46.[tp_ParentId]) AND (t48.[Id] = t46.[tp_DocId]) AND (t48.[Level] = t46.[tp_Level] ) LEFT OUTER JOIN AllUserData AS t47 WITH(FORCESEEK(AllUserData_ParentId(tp_SiteId,tp_DeleteTransactionId,tp_IsCurrentVersion,tp_ParentId,tp_DocId,tp_CalculatedVersion)),NOLOCK) ON (t46.[tp_SiteId] = t47.[tp_SiteId] AND t46.[tp_DeleteTransactionId] = t47.[tp_DeleteTransactionId] AND t46.[tp_IsCurrentVersion] = t47.[tp_IsCurrentVersion] AND t46.[tp_ParentId] = t47.[tp_ParentId] AND t46.[tp_DocId] = t47.[tp_DocId] AND t46.[tp_CalculatedVersion] = t47.[tp_CalculatedVersion] AND t46.[tp_Level] = t47.[tp_Level]) AND (t47.[tp_RowOrdinal] =  0) ORDER BY t46.c0  DESC ,t46.tp_ID  ASC ,t46.[tp_Ordinal]  ASC  OPTION (FORCE ORDER, MAXDOP 1)',N'@LFFP uniqueidentifier,@SITEID uniqueidentifier,@IU int,@L3 uniqueidentifier,@L4 uniqueidentifier,@L5 uniqueidentifier,@L6LNP nvarchar(4000),@L7DNP nvarchar(4000),@DN nvarchar(4000),@DNEL nvarchar(4000),@NUMROWS bigint,@L11 uniqueidentifier,@L12 uniqueidentifier,@L13 uniqueidentifier,@L14 uniqueidentifier,@L15 uniqueidentifier,@L16 uniqueidentifier,@L17 uniqueidentifier,@L18 uniqueidentifier,@L19 uniqueidentifier,@RequestGuid uniqueidentifier',@LFFP='00000000-0000-0000-0000-000000000000',@SITEID=...

    Thursday, March 30, 2017 7:19 AM

All replies

  • Hi,

    Thank you for your post.

    This is a quick note to let you know that we are performing research on this issue.

    Best Regards,

    Lee


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    Monday, April 3, 2017 2:54 AM
  • Hi,

    As you mentioned, the performance issue happens only when querying large metadata, and the root cause is in SQL Server. So I would suggest you optimize SQL performance.

    You could try to rebuild database indexes, as the indexes in SQL Server can become fragmented after a long running time.

    Or try to extending the SQL Server physical memory if possible.

    I noticed that in your long SQL, there are several query from the table “AllDocs”, so I would suggest you can look into the this table and optimize the indexes in it.

    Meanwhile, here is one link which provides great information and tools for troubleshooting SharePoint performance issues, as well as SQL performance issues for your reference.

    https://technet.microsoft.com/en-us/library/2009.08.insidesharepoint.aspx

    Best Regards,

    Lee


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    Monday, April 3, 2017 8:33 AM
  • Hi. Thanks for your answers; we are going to read your link.

    Databases do not have a lot of data; it is only the beginning on production environment so it cannot be the root cause. Moreover , database indexes have been rebuilt (database and involved tables).

    We think it is an infrastructure or network issue, but it is so difficult to diagnostic... We made so many tests; migrating databases, moving them, de activating antivirus, saves... If you think about a list of tests that can be performed, do not hesitate :-) For the moment, networks tests are performed (SharePoint servers moved inside the network).

    Monday, April 3, 2017 10:39 AM
  • Another information: when slow requests are performed, sql server processor usage increase a lot; stunning for just a small request...
    Monday, April 3, 2017 10:46 AM
  • Hi,

    Since this issue is complicated, so I recommend you to submit a ticket to our premier support where our engineer can remote help you directly.

    Thanks for your understanding.

    Best Regards,

    Lee


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    Tuesday, April 4, 2017 6:09 AM
  • Hi.

    Thanks for your answer. Sure it is really not simple. Just: second time I come back to the document set, the same request is performed (on other data). And this time it is very fast. Coming back later in 10 mn, slow...

    I will try to contact MS butit is not so simple; is there something easier for gold partners?

    Tuesday, April 4, 2017 9:53 AM
  • Hi,

    For your case, I recommend you contact premier support so you would get help more quickly.

    Best Regards,

    Lee


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    Wednesday, April 5, 2017 2:45 AM
  • Hello Damien, I am facing similar issue in my SharePoint 2016 environment. Do you have any update on this? were you able to resolve this issue?

    Thursday, January 17, 2019 7:23 PM