Montoya
asked on
SharePoint 2010 SQL Wait times are very high
Our SQL server is reporting locks and very increased wait times.
It says it is mostly related to this : (@LFFP uniqueidentifier,@SITEID uniqueidentifier,@L2 uniqueidentifier,@L3 uniqueidentifier,@L4 uniqueidentifier,@L5 uniqueidentifier,@L6 uniqueidentifier,@L7 uniqueidentifier,@II int,@LISTID uniqueidentifier,@RequestG uid uniqueidentifier,@L11 uniqueidentifier) SELECT ScopeId, Acl, AnonymousPermMask FROM Perms WITH (INDEX=Perms_Url) WHERE SiteId=@SITEID AND DelTransId = 0x AND ScopeId IN (SELECT tp_ScopeId FROM Lists WITH (NOLOCK) WHERE tp_WebId=@L11 AND tp_ID = @L2 UNION SELECT t1.ScopeId FROM AllUserData AS UserData WITH(INDEX=AllUserData_PK) INNER JOIN Docs AS t1 WITH(NOLOCK) ON (UserData.[tp_ID]=t1.[Docl ibRowId]) AND (UserData.[tp_RowOrdinal] = 0) AND (t1.SiteId=UserData.tp_Sit eId) AND (t1.SiteId = @SITEID) AND (t1.ParentId = UserData.tp_ParentId) AND (t1.Id = UserData.tp_DocId) AND (UserData.tp_ListId = @L2) AND (UserData.[tp_ListID] =@LISTID) AND (UserData.[tp_CalculatedVe rsion] = 0 ) AND (UserData.[tp_IsCurrentVer sion] = CONVERT(bit,1) ) AND (UserData.[tp_DeleteTransa ctionId] = 0x ) LEFT OUTER LOOP JOIN AllUserData AS t2 WITH(NOLOCK,INDEX=AllUserD ata_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_DeleteTransactionI d] = 0x ) AND (t2.tp_ListId = @L3) AND (UserData.tp_ListId = @L2) LEFT OUTER LOOP JOIN AllUserData AS t3 WITH(NOLOCK,INDEX=AllUserD ata_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_DeleteTransactionI d] = 0x ) AND (t3.tp_ListId = @L4) AND (UserData.tp_ListId = @L2) LEFT OUTER LOOP JOIN AllUserData AS t4 WITH(NOLOCK,INDEX=AllUserD ata_PK) ON (UserData.[int4]=t4.[tp_ID ]) AND (UserData.[tp_RowOrdinal] = 0) AND (t4.[tp_RowOrdinal] = 0) AND ( (t4.tp_Level = 1) ) AND (t4.[tp_IsCurrentVersion] = CONVERT(bit,1) ) AND (t4.[tp_CalculatedVersion] = 0 ) AND (t4.[tp_DeleteTransactionI d] = 0x ) AND (t4.tp_ListId = @L5) AND (UserData.tp_ListId = @L2) LEFT OUTER LOOP JOIN AllUserData AS t5 WITH(NOLOCK,INDEX=AllUserD ata_PK) ON (UserData.[int5]=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_DeleteTransactionI d] = 0x ) AND (t5.tp_ListId = @L6) AND (UserData.tp_ListId = @L2) LEFT OUTER LOOP JOIN AllUserData AS t6 WITH(NOLOCK,INDEX=AllUserD ata_PK) ON (UserData.[tp_Author]=t6.[ tp_ID]) AND (UserData.[tp_RowOrdinal] = 0) AND (t6.[tp_RowOrdinal] = 0) AND ( (t6.tp_Level = 1) ) AND (t6.[tp_IsCurrentVersion] = CONVERT(bit,1) ) AND (t6.[tp_CalculatedVersion] = 0 ) AND (t6.[tp_DeleteTransactionI d] = 0x ) AND (t6.tp_ListId = @L7) AND (UserData.tp_ListId = @L2) LEFT OUTER LOOP JOIN AllUserData AS t7 WITH(NOLOCK,INDEX=AllUserD ata_PK) ON (UserData.[tp_Editor]=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_DeleteTransactionI d] = 0x ) AND (t7.tp_ListId = @L7) AND (UserData.tp_ListId = @L2) WHERE (UserData.[tp_CalculatedVe rsion] = 0 ) AND (UserData.[tp_IsCurrentVer sion] = CONVERT(bit,1) ) AND (UserData.[tp_DeleteTransa ctionId] = 0x ) AND (UserData.tp_ListID=@LISTI D) AND ( (UserData.tp_Level = 1) ) AND (UserData.tp_RowOrdinal=0) AND ((UserData.[tp_ID] = @II))) OPTION (FORCE ORDER, MAXDOP 1)
I need to fix it, but not knowing where that statement is coming from... I'm kinda stuck.
Any advice?
It says it is mostly related to this : (@LFFP uniqueidentifier,@SITEID uniqueidentifier,@L2 uniqueidentifier,@L3 uniqueidentifier,@L4 uniqueidentifier,@L5 uniqueidentifier,@L6 uniqueidentifier,@L7 uniqueidentifier,@II int,@LISTID uniqueidentifier,@RequestG
I need to fix it, but not knowing where that statement is coming from... I'm kinda stuck.
Any advice?
This should be showing up in the SharePoint ULS logs too. Any further details there?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Mine is the specific answer. Still awarding points for helping.