SharePoint SQL Server at 100% CPU Utilization

[SteveUpchurch]

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.

[#2]

yes

[#3]

@7008332020 What is generating that query, if known?

[#4]

Based on the query, the assumption would be navigation. Are you using metadata-driven navigation at a site or library level within a site contained in that content database?

[#5]

@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!

[#6 · Accepted 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.

[#7]

@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.

[#8]

If you move to the modern UI, yes you'll have to re-do navigation entirely since it no longer uses metadata-driven navigation. But of course you can stick with the classic UI and continue to use metadata-driven navigation.