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.
sys.sysprocess
is only available for backwards compatibility. Microsoft recommends the use ofsys.dm_exec_sessions
,sys.dm_exec_connections
andsys.dm_exec_requests
and forDBCC INPUTBUFFER(spid)
Microsoft recommendssys.dm_exec_inputbuffer
See comment in link. – John aka hot2use Nov 12 at 8:01spid
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 thesessoin_id = 6
(old: spid) ? You might want to join the data management views I mentioned above to produce more accurate results. – John aka hot2use Nov 12 at 8:10