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

Hi Dennis,

Thanks for this, I will take a look at the articles.

Dave