Nov 10 2020 05:18 PM
If this has been addressed, I apologize. I have a situation where my SharePoint SQL server will periodically spike to 100% CPU utilization and render SharePoint unusable. I have a four-server SharePoint 2013 on-premise farm. When this condition happens, I am able to restart SQL Server and the problem goes away. Today, we were able to determine the source of the issue was a long-running query that was running in over 200 simultaneous processes. I do not recognize the query and I am wondering if someone can help me identify it or help me determine where it if coming from. The source SQL is as follows:
SELECT
NV.Eid,
NV.EidParent,
NV.ElementType,
CASE
WHEN
NV.ElementType = 1 OR
NV.NavSequence = 1
THEN
NV.Url
ELSE
CASE WHEN (DATALENGTH(D.DirName) = 0) THEN D.LeafName WHEN (DATALENGTH(D.LeafName) = 0) THEN D.DirName ELSE D.DirName + N'/' + D.LeafName END
END + ISNULL(NV.QueryString, ''),
NV.Name,
NV.NodeMetainfo,
NV.NonNavPage,
NV.NavSequence,
NV.ChildOfSequence,
NV.IsDocLib,
NV.DateLastModified,
CASE
WHEN
NV.Eid = 0 OR
NV.ElementType = 1 OR
NV.NavSequence = 1
THEN
0x0
ELSE
D.ScopeId
END,
CASE
WHEN
NV.Eid = 0 OR
NV.ElementType = 1 OR
NV.NavSequence = 1
THEN
0
WHEN
D.ListId IS NULL
THEN
1
WHEN
D.DoclibRowId IS NULL
THEN
2
ELSE
3
END,
NV.NameResource,
D.Type
FROM
TVF_NavNodes_SiteWeb(@SiteId, @WebId) AS NV
OUTER APPLY
TVF_Docs_NoLock_Id_Level(NV.SiteId, NV.DocId, 1) AS D
ORDER BY
NV.EidParent,
NV.RankChild
OPTION (FORCE ORDER)
Any help would be appreciated. Thank you.
Nov 11 2020 07:23 AM
@7008332020 What is generating that query, if known?
Nov 11 2020 08:24 AM
Nov 11 2020 10:37 AM
@Trevor Seward Thanks for the reply! I absolutely agree that it sounds like navigation. We use the term store terms to define the structure for global navigation at the site collection level and inherit it at all sites below that. We don't do dynamic metadata navigation based on site or library.
Are you thinking that the query is generated when a user is using site navigation? Like maybe something in the nav structure is screwed up and needs to be rebuilt?
Thanks!
Nov 11 2020 10:54 AM
Solution@SteveUpchurch I don't think anything is "wrong", per se. It's just an expensive query (indeed this had pref problems as recently as this year in SPO). As to what exactly is generating the query, I'd have to go sproc hunting :)
Curious to know if you've applied any patches recently and/or if you're at the Nov 2020 CU level, yet.
Nov 16 2020 07:23 AM
@Trevor SewardThanks, Trevor. I am not yet at the November 2020 CU level. Perhaps that will provide some different results. I understand what you are saying about it being an expensive query. While we have been using the same navigation method for several years, the CPU utilization problem has only started occurring more recently, like within the past 2 to 3 months, and then only sporadically. I have been unable to find a pattern or a trigger.
I will apply the Nov 2020 CU and see if that helps. In addition, I am in the process of migrating the environment to on-premise SP 2019 and I might re-think the way navigation is handled in the new environment.
Nov 16 2020 07:41 AM
Nov 11 2020 10:54 AM
Solution@SteveUpchurch I don't think anything is "wrong", per se. It's just an expensive query (indeed this had pref problems as recently as this year in SPO). As to what exactly is generating the query, I'd have to go sproc hunting :)
Curious to know if you've applied any patches recently and/or if you're at the Nov 2020 CU level, yet.