Frequent Blocking in Share point

Hi,

We have Share Point 2007 SP1 databases in SQL Server 2005 Enterprise edition 64 bit with SP3.

We are Using Spot light for Monitoring the SQL instance and from last 3  days, I’m seeing the blocking continuously and the blocking duration is in seconds. Please see the below:

SPID Wait Time Type Resource Command SQL User Program Win User CPU I/O Host Name 
Status Blocking SPID Session SQL DBName 83 641 CXPACKET SELECT ABCmossadmin .Net SqlClient Data Provider mossadmin 0 0 MOSS1 suspended, blocked, blocking 83 (@L0 uniqueidentifier,@L2 uniqueidentifier,@IU int,@L3 uniqueidentifier,@L4 uniqueidentifier,@DN nvarchar(260),@DNEL nvarchar(1024)) SELECT TOP 2147483648 t2.[tp_Created] AS c3c8,t1.[Type] AS c0,t3.[tp_ID] AS c10c5,UserData.[nvarchar10],UserData.[nvarchar15],UserData.[datetime1],t1.[Id] AS c15,t4.[tp_Created] AS c17c8,UserData.[tp_ItemOrder],UserData.[tp_ModerationStatus],UserData.[nvarchar1],UserData.[nvarchar6],UserData.[tp_Created],t1.[CheckinComment] AS c23,UserData.[tp_WorkflowInstanceID],t2.[nvarchar4] AS c3c6,t3.[tp_Created] AS c10c8,UserData.[ntext1],UserData.[nvarchar14],t4.[nvarchar4] AS c17c6,t1.[DirName] AS c18,UserData.[tp_ID],t1.[ProgId] AS c13,UserData.[nvarchar5],UserData.[bit1],t1.[Size] AS c21,UserData.[tp_GUID],t1.[TimeCreated] AS c1,UserData.[tp_Editor],UserData.[tp_Author],t2.[nvarchar1] AS c3c4,t3.[nvarchar4] AS c10c6,UserData.[nvarchar2],UserData.[nvarchar7],UserData.[nvarchar13],UserData.[tp_ContentType],t1.[LTCheckoutUserId] AS c20,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.[ScopeId] AS c16,UserData.[tp_ContentTypeId],t4.[nvarchar1] AS c17c4,UserData.[tp_WorkflowVersion],t1.[ParentVersionString] AS c24,UserData.[nvarchar4],UserData.[tp_CheckoutUserId],UserData.[tp_Version],UserData.[nvarchar9],t5.[nvarchar1] AS c4,UserData.[tp_IsCurrentVersion],t2.[nvarchar5] AS c3c7,t3.[nvarchar1] AS c10c4,UserData.[tp_HasCopyDestinations],UserData.[tp_Level],UserData.[nvarchar12],t1.[MetaInfo] AS c14,t4.[nvarchar5] AS c17c7,t1.[Size] AS c19,t1.[LeafName] AS c2,UserData.[tp_Modified],UserData.[nvarchar3],UserData.[nvarchar8],UserData.[tp_UIVersion],t2.[tp_ID] AS c3c5,t3.[nvarchar5] AS c10c7,UserData.[tp_CopySource],UserData.[nvarchar11],UserData.[tp_InstanceID],t1.[IsCheckoutToLocal] AS c12,t4.[tp_ID] AS c17c5,UserData.[tp_UIVersionString],t1.[ParentLeafName] AS c25 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 [email protected] 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_Editor]=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) LEFT OUTER 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_IsCurrent = 1) ) AND t3.[tp_CalculatedVersion] = 0 AND t3.[tp_DeleteTransactionId] = 0x AND t3.tp_ListId = @L3 AND UserData.tp_ListId = @L4) LEFT OUTER 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_IsCurrent = 1) ) AND t4.[tp_CalculatedVersion] = 0 AND t4.[tp_DeleteTransactionId] = 0x AND t4.tp_ListId = @L3 AND UserData.tp_ListId = @L4) LEFT OUTER JOIN AllUserData AS t5 WITH(NOLOCK, INDEX=AllUserData_PK) ON (t1.[LTCheckoutUserId]=t5.[tp_ID] AND t5.[tp_RowOrdinal] = 0 AND ( (t5.tp_IsCurrent = 1) ) AND t5.[tp_CalculatedVersion] = 0 AND t5.[tp_DeleteTransactionId] = 0x AND t5.tp_ListId = @L3) 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 [email protected] AND ([email protected] OR UserData.tp_DirName LIKE @DNEL+N'/%') AND UserData.tp_RowOrdinal=0 AND ([email protected] AND ([email protected] OR t1.DirName LIKE @DNEL+N'/%') AND t1.Type=0) ORDER BY t1.[Type] Desc,UserData.[tp_ID] Asc OPTION (FORCE ORDER) WSS_Content_Prod 83 0 CXPACKET SELECT ABCmossadmin .Net SqlClient Data Provider mossadmin 0 0 MOSS1 suspended, blocked, blocking 83 (@L0 uniqueidentifier,@L2 uniqueidentifier,@IU int,@L3 uniqueidentifier,@L4 uniqueidentifier,@DN nvarchar(260),@DNEL nvarchar(1024)) SELECT TOP 2147483648 t2.[tp_Created] AS c3c8,t1.[Type] AS c0,t3.[tp_ID] AS c10c5,UserData.[nvarchar10],UserData.[nvarchar15],UserData.[datetime1],t1.[Id] AS c15,t4.[tp_Created] AS c17c8,UserData.[tp_ItemOrder],UserData.[tp_ModerationStatus],UserData.[nvarchar1],UserData.[nvarchar6],UserData.[tp_Created],t1.[CheckinComment] AS c23,UserData.[tp_WorkflowInstanceID],t2.[nvarchar4] AS c3c6,t3.[tp_Created] AS c10c8,UserData.[ntext1],UserData.[nvarchar14],t4.[nvarchar4] AS c17c6,t1.[DirName] AS c18,UserData.[tp_ID],t1.[ProgId] AS c13,UserData.[nvarchar5],UserData.[bit1],t1.[Size] AS c21,UserData.[tp_GUID],t1.[TimeCreated] AS c1,UserData.[tp_Editor],UserData.[tp_Author],t2.[nvarchar1] AS c3c4,t3.[nvarchar4] AS c10c6,UserData.[nvarchar2],UserData.[nvarchar7],UserData.[nvarchar13],UserData.[tp_ContentType],t1.[LTCheckoutUserId] AS c20,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.[ScopeId] AS c16,UserData.[tp_ContentTypeId],t4.[nvarchar1] AS c17c4,UserData.[tp_WorkflowVersion],t1.[ParentVersionString] AS c24,UserData.[nvarchar4],UserData.[tp_CheckoutUserId],UserData.[tp_Version],UserData.[nvarchar9],t5.[nvarchar1] AS c4,UserData.[tp_IsCurrentVersion],t2.[nvarchar5] AS c3c7,t3.[nvarchar1] AS c10c4,UserData.[tp_HasCopyDestinations],UserData.[tp_Level],UserData.[nvarchar12],t1.[MetaInfo] AS c14,t4.[nvarchar5] AS c17c7,t1.[Size] AS c19,t1.[LeafName] AS c2,UserData.[tp_Modified],UserData.[nvarchar3],UserData.[nvarchar8],UserData.[tp_UIVersion],t2.[tp_ID] AS c3c5,t3.[nvarchar5] AS c10c7,UserData.[tp_CopySource],UserData.[nvarchar11],UserData.[tp_InstanceID],t1.[IsCheckoutToLocal] AS c12,t4.[tp_ID] AS c17c5,UserData.[tp_UIVersionString],t1.[ParentLeafName] AS c25 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 [email protected] 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_Editor]=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) LEFT OUTER 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_IsCurrent = 1) ) AND t3.[tp_CalculatedVersion] = 0 AND t3.[tp_DeleteTransactionId] = 0x AND t3.tp_ListId = @L3 AND UserData.tp_ListId = @L4) LEFT OUTER 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_IsCurrent = 1) ) AND t4.[tp_CalculatedVersion] = 0 AND t4.[tp_DeleteTransactionId] = 0x AND t4.tp_ListId = @L3 AND UserData.tp_ListId = @L4) LEFT OUTER JOIN AllUserData AS t5 WITH(NOLOCK, INDEX=AllUserData_PK) ON (t1.[LTCheckoutUserId]=t5.[tp_ID] AND t5.[tp_RowOrdinal] = 0 AND ( (t5.tp_IsCurrent = 1) ) AND t5.[tp_CalculatedVersion] = 0 AND t5.[tp_DeleteTransactionId] = 0x AND t5.tp_ListId = @L3) 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 [email protected] AND ([email protected] OR UserData.tp_DirName LIKE @DNEL+N'/%') AND UserData.tp_RowOrdinal=0 AND ([email protected] AND ([email protected] OR t1.DirName LIKE @DNEL+N'/%') AND t1.Type=0) ORDER BY t1.[Type] Desc,UserData.[tp_ID] Asc OPTION (FORCE ORDER) WSS_Content_Prod 83 16 CXPACKET SELECT ABCmossadmin .Net SqlClient Data Provider mossadmin 0 0 MOSS1 suspended, blocked, blocking 83 (@L0 uniqueidentifier,@L2 uniqueidentifier,@IU int,@L3 uniqueidentifier,@L4 uniqueidentifier,@DN nvarchar(260),@DNEL nvarchar(1024)) SELECT TOP 2147483648 t2.[tp_Created] AS c3c8,t1.[Type] AS c0,t3.[tp_ID] AS c10c5,UserData.[nvarchar10],UserData.[nvarchar15],UserData.[datetime1],t1.[Id] AS c15,t4.[tp_Created] AS c17c8,UserData.[tp_ItemOrder],UserData.[tp_ModerationStatus],UserData.[nvarchar1],UserData.[nvarchar6],UserData.[tp_Created],t1.[CheckinComment] AS c23,UserData.[tp_WorkflowInstanceID],t2.[nvarchar4] AS c3c6,t3.[tp_Created] AS c10c8,UserData.[ntext1],UserData.[nvarchar14],t4.[nvarchar4] AS c17c6,t1.[DirName] AS c18,UserData.[tp_ID],t1.[ProgId] AS c13,UserData.[nvarchar5],UserData.[bit1],t1.[Size] AS c21,UserData.[tp_GUID],t1.[TimeCreated] AS c1,UserData.[tp_Editor],UserData.[tp_Author],t2.[nvarchar1] AS c3c4,t3.[nvarchar4] AS 

c10c6,UserData.[nvarchar2],UserData.[nvarchar7],UserData.[nvarchar13],UserData.[tp_ContentType],t1.[LTCheckoutUserId] AS c20,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.[ScopeId] AS c16,UserData.[tp_ContentTypeId],t4.[nvarchar1] AS c17c4,UserData.[tp_WorkflowVersion],t1.[ParentVersionString] AS c24,UserData.[nvarchar4],UserData.[tp_CheckoutUserId],UserData.[tp_Version],UserData.[nvarchar9],t5.[nvarchar1] AS c4,UserData.[tp_IsCurrentVersion],t2.[nvarchar5] AS c3c7,t3.[nvarchar1] AS c10c4,UserData.[tp_HasCopyDestinations],UserData.[tp_Level],UserData.[nvarchar12],t1.[MetaInfo] AS c14,t4.[nvarchar5] AS c17c7,t1.[Size] AS c19,t1.[LeafName] AS c2,UserData.[tp_Modified],UserData.[nvarchar3],UserData.[nvarchar8],UserData.[tp_UIVersion],t2.[tp_ID] AS c3c5,t3.[nvarchar5] AS c10c7,UserData.[tp_CopySource],UserData.[nvarchar11],UserData.[tp_InstanceID],t1.[IsCheckoutToLocal] AS c12,t4.[tp_ID] AS c17c5,UserData.[tp_UIVersionString],t1.[ParentLeafName] AS c25 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 [email protected] 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_Editor]=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) LEFT OUTER 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_IsCurrent = 1) ) AND t3.[tp_CalculatedVersion] = 0 AND t3.[tp_DeleteTransactionId] = 0x AND t3.tp_ListId = @L3 AND UserData.tp_ListId = @L4) LEFT OUTER 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_IsCurrent = 1) ) AND t4.[tp_CalculatedVersion] = 0 AND t4.[tp_DeleteTransactionId] = 0x AND t4.tp_ListId = @L3 AND UserData.tp_ListId = @L4) LEFT OUTER JOIN AllUserData AS t5 WITH(NOLOCK, INDEX=AllUserData_PK) ON (t1.[LTCheckoutUserId]=t5.[tp_ID] AND t5.[tp_RowOrdinal] = 0 AND ( (t5.tp_IsCurrent = 1) ) AND t5.[tp_CalculatedVersion] = 0 AND t5.[tp_DeleteTransactionId] = 0x AND t5.tp_ListId = @L3) 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 [email protected] AND ([email protected] OR UserData.tp_DirName LIKE @DNEL+N'/%') AND UserData.tp_RowOrdinal=0 AND ([email protected] AND ([email protected] OR t1.DirName LIKE @DNEL+N'/%') AND t1.Type=0) ORDER BY t1.[Type] Desc,UserData.[tp_ID] Asc OPTION (FORCE ORDER) WSS_Content_Prod

Here, all are of same type called [b]CXPACKET[/b] and the sessions showing as

suspended, blocked, blocking

runnable, blocked, blocking

I did not see this type of blocking before that same session blocked and blocking itself.
 
Is this something specific to Share point databases?

Is this type of blocking effects the performance?

If it effects performance, what are the steps to avoid this type of blocking?

Is something we can do on Share Point side or it should be done on SQL Server side only  to avoid this blocking?

I appreciate your inputs

Thank you

Could you please provide your inputs on this? The same blocking  information (that Spot light showing) can be known by dmvs? I will try with those DMV’s to get that blocking information.. Thanks/

Hi,Some blocking  is to be expected with SharePoint, you’ll see this when a crawl is occurring. Are you experiencing any ill effects on the SharePoint site itself?  Is the site

Thanks,The CXPACKET blocking  is occuring throught the day in business hours!! not when Crawilng is running..Yes, we are experiecing slow performance from share  point application..The Indexes & Stats are up to date.The Max degree of Parallism is set to default i.e 0Is share point  recommends any particular value for Max degree of Parallism ?We have 4 Quad core cpu’s i.e 8 processors.. Thanks