How can I trace unknown high CPU usage on SQL Server?

Question

I have a troublesome SQL Server instance that is causing up to 95% CPU usage on a 5-6 minute cycle. Stays at this level for about a minute or so then drops again only to start the cycle again. I am able to map the kpid thru perfmon, but when I try to map that kpid back to an SQL spid nothing is returned.

Query used:

SELECT spid, kpid, dbid, cpu, memusage FROM sysprocesses WHERE kpid=3572

I also tried the tasks in this article and although it gave good advice, the troublesome queries are not shown or are not currently active...?

I am not sure where else to check since I can't get an actual query that is running and/or process to optimize. Any assistance is greatly appreciated.

Answer 1

ongoing

If it happens again, I'd suggest using sp_WhoIsActive:

EXEC sp_WhoIsActive
    @get_plans = 1,
    @get_avg_time = 1;

The two additional parameters are to collect execution plans, and to look at the plan cache to see if you're dealing with queries that usually finish quickly, but are now running longer.

past tense

Since you don't have Query Store enabled, you may want to look at the plan cache. It's not always the best place to look, because there are many things that will clear it out, or cause the plans that were causing you the issue to be evicted.

Additionally, if you have optimize for ad hoc workloads enabled, you will only have stubs in place of some queries that may have been the root cause.

An easy way to look at the plan cache is to use sp_BlitzCache:

EXEC sp_BlitzCache
    @SortOrder = 'avg cpu';

EXEC sp_BlitzCache
    @SortOrder = 'executions';

The first will return available plans that used the most average cpu per execution, and the second will return available plans that execute the most frequently.