locked
sqlserv.exe cpu 100% due to SharePoint database? users are not able to Save/Submit/Edit/Reject using the eForms SharePoint Application RRS feed

  • Question

  • Hi All,

    The Users are not able to Save/Submit/Edit/Reject the eForm in SharePoint Application for one or more of the following reasons 

    1) The DB Server the databases processes are in suspended State.

    2) The blockages are occurring on on of the SharePoint Content Database.

    3) CPU Usage is 100%.

    I've done some search with the system view sys.dm_exec_requests and I've noticed that a query against a SharePoint database is very time consuming, here it is:

    This is the Blocking Query:

    (@LFFP uniqueidentifier,@SITEID uniqueidentifier,@L2 uniqueidentifier,@L3 uniqueidentifier,@L4TXP nvarchar (4000),@L5TXP nvarchar(4000),@DN nvarchar(4000),@DNEL nvarchar(4000),@LISTID uniqueidentifier,@RequestGuid  uniqueidentifier,@L10 uniqueidentifier,@L11TXP nvarchar(4000),@L12 uniqueidentifier)    DECLARE @ListScopeUrl nvarchar(260)  SELECT @ListScopeUrl = ScopeUrl FROM Perms WITH(INDEX=Perms_Url) WHERE SiteId=@SITEID AND DelTransId = 0x AND 
    ScopeId = @L10; SELECT ScopeId, Acl, AnonymousPermMask FROM Perms WITH (INDEX=Perms_PK) WHERE SiteId=@SITEID AND DelTransId = 0x AND ((ScopeUrl = @ListScopeUrl) OR (ScopeUrl =@L11TXP) OR (ScopeUrl LIKE (@L11TXP + N'/%'))) AND ScopeId IN (SELECT tp_ScopeId FROM Lists WITH (NOLOCK) WHERE tp_WebId=@L12 AND tp_ID = @L2 UNION 

    SELECT t1.ScopeId FROM AllUserData AS UserData WITH(INDEX=AllUserData_PK) 

    INNER JOIN Docs AS t1 WITH(NOLOCK) ON (UserData.[tp_ID]=t1.[DoclibRowId]) AND (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_ListId = @L2) AND 
    (UserData.[tp_ListID] =@LISTID) AND (UserData.[tp_CalculatedVersion] = 0 ) AND 

    (UserData.[tp_IsCurrentVersion] = CONVERT(bit,1) ) AND 
    (UserData.[tp_DeleteTransactionId] = 0x ) LEFT OUTER LOOP JOIN AllUserData AS t2 WITH(NOLOCK,INDEX=AllUserData_PK) 
    ON (UserData.[int1]=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 = @L3) AND (UserData.tp_ListId = @L2) LEFT OUTER LOOP JOIN 
    AllUserData AS t3 WITH(NOLOCK,INDEX=AllUserData_PK) ON (UserData.[tp_Author]=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 = @L3) AND 
    (UserData.tp_ListId = @L2) LEFT OUTER LOOP JOIN AllUserData AS t4 WITH(NOLOCK,INDEX=AllUserData_PK) ON (UserData.
    [tp_Editor]=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 = @L3) AND (UserData.tp_ListId = @L2) WHERE (UserData.
    [tp_CalculatedVersion] = 0 ) AND (UserData.[tp_IsCurrentVersion] = CONVERT(bit,1) ) AND (UserData.
    [tp_DeleteTransactionId] = 0x ) AND (UserData.tp_ListID=@LISTID) AND ( (UserData.tp_Level = 1) ) AND 
    (UserData.tp_SiteId=@SITEID) AND (UserData.tp_RowOrdinal=0) AND (((UserData.[nvarchar1] = @L4TXP) AND (UserData.
    [nvarchar3] = @L5TXP)) AND t1.SiteId=@SITEID AND (t1.DirName=@DN OR t1.DirName LIKE @DNEL+N'/%') AND 
    t1.SortBehavior=0)) OPTION (FORCE ORDER, MAXDOP 1)

    How can I troubleshoot this?

    If anyone has suggestions. thanks in Advance

    ARUN

    Friday, April 24, 2015 12:48 AM

All replies

  • we resolve the problem creating an Index in the list
    Friday, July 15, 2016 12:21 PM