Link to home
Start Free TrialLog in
Avatar of Montoya
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,@RequestGuid 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.[DoclibRowId]) 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_ListId = @L2) AND (UserData.[tp_ListID] =@LISTID) AND (UserData.[tp_CalculatedVersion] = 0 ) AND (UserData.[tp_IsCurrentVersion] = CONVERT(bit,1) ) AND (UserData.[tp_DeleteTransactionId] = 0x ) 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 = @L3) AND (UserData.tp_ListId = @L2) 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 = @L2) LEFT OUTER LOOP JOIN AllUserData AS t4 WITH(NOLOCK,INDEX=AllUserData_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_DeleteTransactionId] = 0x ) AND (t4.tp_ListId = @L5) AND (UserData.tp_ListId = @L2) LEFT OUTER LOOP JOIN AllUserData AS t5 WITH(NOLOCK,INDEX=AllUserData_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_DeleteTransactionId] = 0x ) AND (t5.tp_ListId = @L6) AND (UserData.tp_ListId = @L2) LEFT OUTER LOOP JOIN AllUserData AS t6 WITH(NOLOCK,INDEX=AllUserData_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_DeleteTransactionId] = 0x ) AND (t6.tp_ListId = @L7) AND (UserData.tp_ListId = @L2) LEFT OUTER LOOP JOIN AllUserData AS t7 WITH(NOLOCK,INDEX=AllUserData_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_DeleteTransactionId] = 0x ) AND (t7.tp_ListId = @L7) AND (UserData.tp_ListId = @L2) WHERE (UserData.[tp_CalculatedVersion] = 0 ) AND (UserData.[tp_IsCurrentVersion] = CONVERT(bit,1) ) AND (UserData.[tp_DeleteTransactionId] = 0x ) AND (UserData.tp_ListID=@LISTID) 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?
Avatar of Jamie McAllister
Jamie McAllister
Flag of Switzerland image

This should be showing up in the SharePoint ULS logs too. Any further details there?
SOLUTION
Avatar of Jamie McAllister
Jamie McAllister
Flag of Switzerland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Montoya
Montoya

ASKER

Mine is the specific answer. Still awarding points for helping.