locked
Slow running query 'locks' SharePoint 2007 RRS feed

  • Question

  • 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

Answers

  • We found a query that returned all documents in SharePoint (which in our case is 300,000) plus documents. It seems if you do this SharePoint reads all records (and seem to lock up the environemnt). We removed the query and this seems to have fixed the problem.

    Nigel T

    • Marked as answer by NigelT99 Thursday, April 26, 2012 10:24 AM
    Thursday, April 26, 2012 10:24 AM

All replies

  • Hi,

    There are few steps you need to take for it.

    Check the account from which this query is executing, then check the account for the processes. This will give you an idea of the process from which this is originating. 

    As you have specified that this occurs every 45 mins I suspect that there could be some timer jobs affecting the same.

    Check for the timer jobs running at that time.

    I hope this will help you out.


    Thanks, Rahul Rashu

    Wednesday, April 4, 2012 3:24 AM
  • Rahul

    Thanks for the reply - I will look at the account but the key problem is not that the query runs every 45 minutes but that it seems to run at random and when it does it runs for 45 minutes and during this time locks out the Sharepoint site. i.e. no-one can access the site for 45 minutes. Given that we have several hundrd users on the site this is not goood when it happens.

    Nigel


    Nigel T

    Wednesday, April 4, 2012 7:47 AM
  • We found a query that returned all documents in SharePoint (which in our case is 300,000) plus documents. It seems if you do this SharePoint reads all records (and seem to lock up the environemnt). We removed the query and this seems to have fixed the problem.

    Nigel T

    • Marked as answer by NigelT99 Thursday, April 26, 2012 10:24 AM
    Thursday, April 26, 2012 10:24 AM