-1

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: enter image description here

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?

closed as unclear what you're asking by LowlyDBA, HandyD, John aka hot2use, Philᵀᴹ, Marcello Miorelli Nov 8 at 13:05

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    What version are you using? – Anthony Genovese Nov 8 at 6:10
  • The internal table sys.sysprocess is only available for backwards compatibility. Microsoft recommends the use of sys.dm_exec_sessions, sys.dm_exec_connections and sys.dm_exec_requests and for DBCC INPUTBUFFER(spid) Microsoft recommends sys.dm_exec_inputbuffer See comment in link. – John aka hot2use 2 days ago
  • ...seeing as you are using SQL Server version 13.0 (2016). – John aka hot2use 2 days ago
  • Oh and an spid equal or lower than 50 is "normally" a system spid and might not return any data just because it's a system spid. (internal stuff, housekeeping, ....). Are you sure it is the sessoin_id = 6 (old: spid) ? You might want to join the data management views I mentioned above to produce more accurate results. – John aka hot2use 2 days ago
  • Thanks guys.... – GONeale 2 days ago
2

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.

  • By the question I'd say just running sp_whoisactive is gonna be sufficient (without keeping the log table) since he can reproduce the problem. It's not like he doesn't know what SP or query is causing the problem at all or the problem happens too fast to be caught, it's probably some specific step of the whole procedure that takes 10min. – Ronaldo Nov 8 at 11:58
  • If it's one step in the procedure, the whoisactive is going to show which step it's currently running. Which would help him find the specific part of the procedure that's causing his issue. If you log the values you can later check which step was running for a long time. – Yannick Liekens Nov 8 at 12:15
  • Thank @YannickLiekens and Ronaldo. I will give this a shot. – GONeale Nov 11 at 22:55

Not the answer you're looking for? Browse other questions tagged or ask your own question.