Hi,
We have recently had a problem where a custom .Net SharePoint site, which has a custom .Net workflow which has been working well for a number of months, has suddenly started to cause multiple locks within the database and consuming all the available space on tempDB to the point it fills the drive up (90GB+). The content database that it is querying is 25GB.
From the monitoring tool we have, it identified a query that was taking up CPU / resources (will post below due to size).
Normally the SQL server runs at 1-10%(at most) CPU and no locking (and if there is, it happens that quickly it doesn't register).
The SQL server has 100GB memory allocated to it.
Has anyone else come across this situation?
If anyone has any pointers / suggestions I would be very grateful.
Thank you
Dave
(@LFFP uniqueidentifier,@SITEID uniqueidentifier,@L2 uniqueidentifier,@L3 uniqueidentifier,@DN nvarchar(4000),@NUMROWS bigint,@RequestGuid uniqueidentifier) SELECT TOP(@NUMROWS) t1.[LeafName] AS c0, t3.[nvarchar4] AS c10c5, t1.[MetaInfo] AS c15, UserData.[tp_ItemOrder], t1.[ParentVersionString] AS c28, t2.[nvarchar4] AS c2c5, t1.[Type] AS c8, UserData.[tp_ModerationStatus], UserData.[tp_Created], t1.[Size] AS c23, UserData.[nvarchar1], UserData.[nvarchar6], UserData.[tp_WorkflowInstanceID], t4.[tp_ID] AS c18c4, UserData.[ntext1], t5.[nvarchar1] AS c3, t3.[nvarchar1] AS c10c3, t2.[nvarchar1] AS c2c3, UserData.[tp_ID], t1.[ProgId] AS c13, t1.[LTCheckoutUserId] AS c21, UserData.[nvarchar5], t1.[ItemChildCount] AS c26, UserData.[tp_GUID], UserData.[bit1], t1.[TimeCreated] AS c1, UserData.[tp_Editor], t1.[IsCheckoutToLocal] AS c12, t1.[ScopeId] AS c17, UserData.[tp_Author], t4.[tp_Created] AS c18c7, t1.[CheckinComment] AS c25, UserData.[tp_UIVersionString], t2.[tp_Created] AS c2c7, t3.[nvarchar6] AS c10c6, t4.[nvarchar6] AS c18c6, t1.[Size] AS c20, UserData.[nvarchar2], t1.[ParentLeafName] AS c29, UserData.[nvarchar7], t2.[nvarchar6] AS c2c6, t1.[TimeLastModified] AS c9, 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.[Id] AS c16, UserData.[tp_ContentTypeId], UserData.[tp_WorkflowVersion], UserData.[nvarchar4], UserData.[tp_CheckoutUserId], UserData.[tp_Version], t4.[nvarchar4] AS c18c5, UserData.[tp_IsCurrentVersion], UserData.[nvarchar9], t3.[tp_ID] AS c10c4, UserData.[tp_HasCopyDestinations], UserData.[tp_Level], t1.[SortBehavior] AS c14, t1.[DirName] AS c19, t1.[ClientId] AS c22, t1.[FolderChildCount] AS c27, t2.[tp_ID] AS c2c4, UserData.[nvarchar3], UserData.[tp_Modified], t4.[nvarchar1] AS c18c3, UserData.[tp_UIVersion], t1.[ETagVersion] AS c30, UserData.[nvarchar8], t3.[tp_Created] AS c10c7, UserData.[tp_CopySource], UserData.[tp_InstanceID] FROM AllDocs AS t1 WITH ( INDEX=AllDocs_Url, NOLOCK ) INNER LOOP JOIN 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) ) AND (t1.Level = UserData.tp_Level) AND (t1.[DeleteTransactionId] = 0x ) LEFT OUTER LOOP JOIN AllUserData AS t2 WITH ( NOLOCK, INDEX=AllUserData_PK ) 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 = @L2) AND (UserData.tp_ListId = @L3) LEFT OUTER LOOP JOIN AllUserData AS t3 WITH ( NOLOCK, INDEX=AllUserData_PK ) 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 = @L2) AND (UserData.tp_ListId = @L3) LEFT OUTER LOOP 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_Level] = 1) AND (t4.[tp_IsCurrentVersion] = CONVERT(bit,1) ) AND (t4.[tp_CalculatedVersion] = 0 ) AND (t4.[tp_DeleteTransactionId] = 0x ) AND (t4.tp_ListId = @L2) AND (UserData.tp_ListId = @L3) LEFT OUTER LOOP JOIN AllUserData AS t5 WITH ( NOLOCK, INDEX=AllUserData_PK ) ON (t1.[LTCheckoutUserId]=t5.[tp_ID]) 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 = @L2) WHERE ( (UserData.tp_Level = 1) ) AND (UserData.tp_SiteId=@SITEID) AND (UserData.tp_RowOrdinal=0) AND (t1.SiteId=@SITEID AND (t1.DirName=@DN)) ORDER BY t1.[LeafName] ASC OPTION (FORCE ORDER, MAXDOP 1)
- Edited by SlightlyDave Wednesday, September 02, 2015 10:06 PM