Slow running query 'locks' SharePoint 2007

• Question

• 

  

  0

  Sign in to vote

  WE are suffering from problems in our SharePoint environment where the entire site seems to hang. Looking at SQL we can see a query which on average runs for nearly 45 minutes and we strongly suspect that this query is the root cause of the hangs but we have no idea what process invokes this query.

  The query is an inbuilt SharePoint query and is shown below - anyone any idea what this is doing and what process would invoke this query. We seem to get the problem intermittently when uploading a  document using the SharePoint 2007 web service. 

  SELECT TOP 101

        t1.[Type] AS c0

        ,t2.[tp_ID] AS c5c7

        ,t1.[IsCheckoutToLocal] AS c11

        ,UserData.[tp_ContentTypeId]

        ,UserData.[nvarchar10]

        ,UserData.[ntext2]

        ,UserData.[nvarchar15]

        ,t1.[LeafName] AS c4

        ,UserData.[nvarchar4]

        ,UserData.[tp_CheckoutUserId]

        ,UserData.[tp_ModerationStatus]

        ,UserData.[tp_Level]

        ,t2.[nvarchar1] AS c5c6,UserData.[tp_HasCopyDestinations]

        ,UserData.[nvarchar12]

        ,UserData.[tp_Created]

        ,t1.[ScopeId] AS c3

        ,UserData.[tp_ContentType]

        ,UserData.[nvarchar13]

        ,UserData.[nvarchar7]

        ,UserData.[nvarchar14]

        ,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,UserData.[nvarchar3]

         ,t2.[nvarchar5] AS c5c9,UserData.[tp_UIVersion]

         ,UserData.[tp_ID],t2.[tp_Created] AS c5c10

         ,UserData.[nvarchar5],UserData.[tp_CopySource]

         ,UserData.[nvarchar11],t1.[Size] AS c13

         ,t1.[TimeCreated] AS c1

         ,t2.[nvarchar4] AS c5c8

         ,t1.[DirName] AS c12

         FROM UserData

          INNER MERGE  JOIN Docs AS t1 WITH(NOLOCK)

           ON ( 1 = 1  AND UserData.[tp_RowOrdinal] = 0

            AND t1.SiteId = UserData.tp_SiteId

            AND t1.SiteId = @L2 AND t1.DirName = UserData.tp_DirName

            AND t1.LeafName = UserData.tp_LeafName

              AND t1.Level = UserData.tp_Level

                AND  (UserData.tp_Level = 255

                 AND t1.LTCheckoutUserId =@IU

                  OR (UserData.tp_Level = 1

                   AND (UserData.tp_DraftOwnerId IS NULL

                    OR  (UserData.tp_DraftOwnerId <>@IU AND  1=0 ))

                      OR UserData.tp_Level = 2

                       AND (UserData.tp_DraftOwnerId = @IU

                        OR  1=1 )) AND (t1.LTCheckoutUserId IS NULL OR t1.LTCheckoutUserId <> @IU ))

                         AND (1 = 1))

           LEFT OUTER JOIN AllUserData AS t2

            WITH(NOLOCK, INDEX=AllUserData_PK)

             ON (UserData.[tp_CheckoutUserId]=t2.[tp_ID]

              AND UserData.[tp_RowOrdinal] = 0

               AND t2.[tp_RowOrdinal] = 0

                AND ( (t2.tp_IsCurrent = 1) )

                  AND t2.[tp_CalculatedVersion] = 0

                    AND t2.[tp_DeleteTransactionId] = 0x

                      AND t2.tp_ListId = @L3

                       AND UserData.tp_ListId = @L4)

           WHERE  (UserData.tp_Level= 255 AND UserData.tp_CheckoutUserId = @IU

            OR  ( UserData.tp_Level  = 2

             AND UserData.tp_DraftOwnerId IS NOT NULL

              OR UserData.tp_Level  = 1

               AND UserData.tp_DraftOwnerId IS  NULL  )

                AND ( UserData.tp_CheckoutUserId IS  NULL

                  OR UserData.tp_CheckoutUserId <> @IU))

                   AND UserData.tp_SiteId=@L2

                    AND (UserData.tp_DirName=@DN OR UserData.tp_DirName LIKE @DNEL+N'/%')

                     AND UserData.tp_RowOrdinal=0

                      AND (t1.SiteId=@L2 AND (t1.DirName=@DN OR t1.DirName LIKE @DNEL+N'/%')

                       AND t1.Type=0)

           ORDER BY t1.[Type] Desc,UserData.[tp_ID] Asc OPTION (FORCE ORDER)

  ---

  Nigel T

  Tuesday, April 3, 2012 3:53 PM