SQL Server CPU load - Tracking process via SPID results in dead end? [closed]

Question

We have been trying to track down a mysterious bug in production which comes up every now and then and can last even up to 10 minutes, it seems it was reproducible either by a scheduled task I executed, or just running two other sinister SP's in quick succession, then we receive this steady CPU load with no apparent cause after the SP's are long finished.

Please take a look at the screenshot reproduced on our QA server:

I have followed the instructions on this article to no success, as you can see DBCC INPUTBUFFER(6) returns no results!

https://www.mssqltips.com/sqlservertip/2454/how-to-find-out-how-much-cpu-a-sql-server-process-is-really-using/

Apparently my question is unclear, I was figuring anyone who knows what DBCC INPUTBUFFER(ID) does would then realise from the screenshot I am not receiving results in the third results pane where I should be. So my question was, why am I not receiving this information, or moreover how can I get to the root of the task causing the high CPU load in the SQL server process?

Answer 1

The WhoIsActive procedure returns you how much CPU a query is using. I'd set up a job that runs this procedure every few minutes and log it to a table.

If you need help setting that up, this is a useful post by Brent Ozar on how to set it up quickly. (Code here copied from the post). Remember to change your variables accordingly of course.

SET NOCOUNT ON;

DECLARE @retention INT = 7,
        @destination_table VARCHAR(500) = 'WhoIsActive',
        @destination_database sysname = 'Crap',
        @schema VARCHAR(MAX),
        @SQL NVARCHAR(4000),
        @parameters NVARCHAR(500),
        @exists BIT;

SET @destination_table = @destination_database + '.dbo.' + @destination_table;

--create the logging table
IF OBJECT_ID(@destination_table) IS NULL
    BEGIN;
        EXEC dbo.sp_WhoIsActive @get_transaction_info = 1,
                                @get_outer_command = 1,
                                @get_plans = 1,
                                @return_schema = 1,
                                @schema = @schema OUTPUT;
        SET @schema = REPLACE(@schema, '<table_name>', @destination_table);
        EXEC ( @schema );
    END;

--create index on collection_time
SET @SQL
    = 'USE ' + QUOTENAME(@destination_database)
      + '; IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(@destination_table) AND name = N''cx_collection_time'') SET @exists = 0';
SET @parameters = N'@destination_table varchar(500), @exists bit OUTPUT';
EXEC sys.sp_executesql @SQL, @parameters, @destination_table = @destination_table, @exists = @exists OUTPUT;

IF @exists = 0
    BEGIN;
        SET @SQL = 'CREATE CLUSTERED INDEX cx_collection_time ON ' + @destination_table + '(collection_time ASC)';
        EXEC ( @SQL );
    END;

--collect activity into logging table
EXEC dbo.sp_WhoIsActive @get_transaction_info = 1,
                        @get_outer_command = 1,
                        @get_plans = 1,
                        @destination_table = @destination_table;

--purge older data
SET @SQL
    = 'DELETE FROM ' + @destination_table + ' WHERE collection_time < DATEADD(day, -' + CAST(@retention AS VARCHAR(10))
      + ', GETDATE());';
EXEC ( @SQL );

When you notice the issue occurs, check your logging table and look at the CPU column, this will give you an idea which queries are actually making your CPU go up.

Once you find out the queries that are causing this, you'll be able to solve your issue, if you need more help on that specific query, you could post it here in another question.