title: Massive Sharepoint query taking forever to run. What is it?
body: <p>using sp_who2 I found that a process is executing a select that has been running for quite some time and is eating up all the resources. Where is this query coming from? The program name = .Net SqlCLient Data Provider instead of any Microsoft Sharepoint Foundation. </p>
  
  <p>I was able to extract the last query ran for the SPID and this is what came up</p>
  
  <pre><code>(@LFFP uniqueidentifier,@SITEID uniqueidentifier,@L2 uniqueidentifier,@L3TXP nvarchar(4000),
  @L4 uniqueidentifier,@L5 uniqueidentifier,@L6 uniqueidentifier,@I7P int,@DN nvarchar(4000),
  @DNEL nvarchar(4000),@LISTID uniqueidentifier,@NUMROWS bigint,@RequestGuid uniqueidentifier)    
  
  SELECT TOP(@NUMROWS) t1.[Type] AS c0, UserDataVersioned.[tp_ModerationStatus], UserDataVersioned.[nvarchar1], 
  UserDataVersioned.[nvarchar21], t5.[nvarchar6] AS c16c10, UserDataVersioned.[tp_Version], t2.[tp_ID] AS c6c8, 
  UserDataVersioned.[int2], t4.[nvarchar1] AS c14c15, t6.[tp_ID] AS c17c8, UserDataVersioned.[tp_HasCopyDestinations], 
  UserDataVersioned.[nvarchar17], UserDataVersioned.[nvarchar12], t6.[nvarchar3] AS c17c26, 
  UserDataVersioned.[tp_WorkflowInstanceID], t1.[ProgId] AS c23, t1.[MetaInfo] AS c3, UserDataVersioned.[tp_Level], 
  UserDataVersioned.[nvarchar5], t2.[tp_Created] AS c6c11, UserDataVersioned.[nvarchar20], t5.[nvarchar3] AS c16c26, 
  UserDataVersioned.[int1], UserDataVersioned.[tp_Author], UserDataVersioned.[tp_Editor], UserDataVersioned.[tp_UIVersion],
   UserDataVersioned.[tp_GUID], t1.[LeafName] AS c18, UserDataVersioned.[tp_ContentTypeId], 
   UserDataVersioned.[nvarchar16], t3.[nvarchar1] AS c12c13, t1.[TimeLastModified] AS c21, t1.[SortBehavior] AS c1, t5.[tp_ID] AS c16c8, 
   t5.[tp_Created] AS c16c11, t6.[tp_Created] AS c17c11, UserDataVersioned.[tp_WorkflowVersion], UserDataVersioned.[tp_IsCurrentVersion], 
   UserDataVersioned.[nvarchar4], UserDataVersioned.[nvarchar9], t6.[nvarchar4] AS c17c9, UserDataVersioned.[nvarchar10], UserDataVersioned.[nvarchar15], t2.[nvarchar4] AS c6c9, 
   UserDataVersioned.[datetime3], t1.[ItemChildCount] AS c24, UserDataVersioned.[tp_CopySource], t1.[Id] AS c4, UserDataVersioned.[ntext2], 
   UserDataVersioned.[tp_HasAttachment], UserDataVersioned.[tp_InstanceID], UserDataVersioned.[tp_ItemOrder], 
   UserDataVersioned.[tp_Created], UserDataVersioned.[tp_ID], UserDataVersioned.[nvarchar3], UserDataVersioned.[nvarchar8], 
   UserDataVersioned.[nvarchar14], t2.[nvarchar1] AS c6c7, UserDataVersioned.[datetime2], t6.[nvarchar1] AS c17c7, UserDataVersioned.[tp_UIVersionString], 
   t1.[TimeCreated] AS c19, t1.[ClientId] AS c22, 
   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 c2, 
   UserDataVersioned.[ntext1], UserDataVersioned.[nvarchar19], t2.[nvarchar6] AS c6c10, t5.[nvarchar4] AS c16c9, UserDataVersioned.[tp_Modified], 
   UserDataVersioned.[nvarchar2], UserDataVersioned.[nvarchar7], UserDataVersioned.[nvarchar13], UserDataVersioned.[datetime1], 
   UserDataVersioned.[int3], t1.[FolderChildCount] AS c25, t1.[ScopeId] AS c5, UserDataVersioned.[nvarchar18], t2.[nvarchar3] AS c6c26, 
   t6.[nvarchar6] AS c17c10, t1.[DirName] AS c20, UserDataVersioned.[nvarchar6], t5.[nvarchar1] AS c16c7
  
    FROM AllUserData AS UserDataVersioned WITH(INDEX=AllUserData_PK) 
    INNER JOIN Docs AS t1 WITH(NOLOCK) ON (UserDataVersioned.[tp_RowOrdinal] = 0) 
    AND (t1.SiteId=UserDataVersioned.tp_SiteId) AND (t1.SiteId = @SITEID) AND (t1.ParentId = UserDataVersioned.tp_ParentId) 
    AND (t1.Id = UserDataVersioned.tp_DocId) AND ( (UserDataVersioned.tp_Level = 1) ) AND (t1.IsCurrentVersion = 1) AND (t1.Level =  1) 
    AND (UserDataVersioned.tp_ListId = @L2) AND (UserDataVersioned.[tp_ListID] =@LISTID) AND (UserDataVersioned.[tp_IsCurrentVersion] = CONVERT(bit,1)   
    OR  UserDataVersioned.[tp_IsCurrentVersion] = CONVERT(bit,0) ) AND (UserDataVersioned.[tp_DeleteTransactionId] = 0x ) 
    INNER JOIN (SELECT CAST(val AS uniqueidentifier) AS InValues FROM dbo.fn_UnpackCsvString(@L3TXP) ) AS Scopes ON (t1.ScopeId = Scopes.InValues) 
    LEFT OUTER LOOP JOIN AllUserData AS t2 WITH(NOLOCK,INDEX=AllUserData_PK) ON (UserDataVersioned.[int1]=t2.[tp_ID]) 
    AND (UserDataVersioned.[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 = @L4) AND (UserDataVersioned.tp_ListId = @L2) 
    LEFT OUTER LOOP JOIN AllUserData AS t3 
    WITH(NOLOCK,INDEX=AllUserData_PK) ON (UserDataVersioned.[int2]=t3.[tp_ID]) AND (UserDataVersioned.[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 = @L5) 
    AND (UserDataVersioned.tp_ListId = @L2) LEFT OUTER LOOP JOIN AllUserData AS t4 WITH(NOLOCK,INDEX=AllUserData_PK) ON (UserDataVersioned.[int3]=t4.[tp_ID]) 
    AND (UserDataVersioned.[tp_RowOrdinal] = 0) AND (t4.[tp_RowOrdinal] = 0) AND ( (t4.tp_Level = 1 OR t4.tp_Level =  2)   AND (t4.tp_IsCurrent = 1 ) ) 
    AND (t4.[tp_IsCurrentVersion] = CONVERT(bit,1) ) AND (t4.[tp_CalculatedVersion] = 0 ) AND (t4.[tp_DeleteTransactionId] = 0x ) AND (t4.tp_ListId = @L6) 
    AND (UserDataVersioned.tp_ListId = @L2) LEFT OUTER LOOP JOIN AllUserData AS t5 WITH(NOLOCK,INDEX=AllUserData_PK) ON (UserDataVersioned.[tp_Author]=t5.[tp_ID]) 
    AND (UserDataVersioned.[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 = @L4) AND (UserDataVersioned.tp_ListId = @L2)
      LEFT OUTER LOOP JOIN AllUserData AS t6 WITH(NOLOCK,INDEX=AllUserData_PK) ON (UserDataVersioned.[tp_Editor]=t6.[tp_ID]) 
      AND (UserDataVersioned.[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 = @L4) AND (UserDataVersioned.tp_ListId = @L2) 
  
      WHERE (UserDataVersioned.[tp_IsCurrentVersion] = CONVERT(bit,1)   
      OR  UserDataVersioned.[tp_IsCurrentVersion] = CONVERT(bit,0) ) AND (UserDataVersioned.[tp_DeleteTransactionId] = 0x ) 
      AND (UserDataVersioned.tp_ListID=@LISTID) AND (UserDataVersioned.tp_RowOrdinal=0) AND ((UserDataVersioned.[tp_ID] = @I7P) 
      AND t1.SiteId=@SITEID AND (t1.DirName=@DN OR t1.DirName LIKE @DNEL+N'/%')) 
      ORDER BY UserDataVersioned.[tp_UIVersion]  DESC ,UserDataVersioned.[tp_ID]  ASC  OPTION (FORCE ORDER, MAXDOP 1)
  </code></pre>
  
  <p>Any ideas what might be running this and what it might be doing? I'm assuming that if it's legit, it's doing some type of index building (not sql indexes).</p>
  
  <p>When I kill the spid, another one comes up running the same query. Looking at the profiler, the query will finish but it starts another one using the exact same details and always for the same site.</p>
  
  <p><strong>Update</strong>: after killing all sharepoint services and disabling the website, profiler still shows the query being executed over and over again. So the query isn't the problem as much as is it's being executed multiple times</p>