Sql-server – How to diagnose MSSQL high CPU spike

sql server

I've had my server CPU spiked to 100% for about 20 minutes. The database was partially unresponsive in certain parts.( i know this because users complained that they cant use certain features, but they could login, and use other areas, only not specific). Before I could check current queries it was gone. I want to see what was the cause of it. Does anyone have some kind of instructions on this? How do I actually get to the root cause of this. How to see what query caused this? I am using MSSQL

edit:
I found in RING BUFFER LOGS which record id and at what time CPU spiked. Is this something that can bring me to root of issue ?

Best Answer

If you don't have something that captured the queries being executed at the time, all you can do is guess. In case you're on SQL Server 2016 or higher, and you have Query Store enabled for the affected database, I'll mention that you could track down long-running or slow queries there. If you don't have query store or some other way of tracking activity after a problem, you might want to look into a monitoring tool, or at least some simpler "home grown" solutions, like logging sp_WhoIsActive to a table.

If the queries that caused this problem are still in the plan cache, you could try running sp_BlitzCache to find the most CPU-intensive queries:

EXEC master.dbo.sp_BlitzCache 
    @DatabaseName = N'YourDatabaseName',
    @SortOrder = 'avg cpu';

To be clear, there's no way to know for sure if those queries are the problem ones, but it might be your best bet at this point.

You could also try to narrow down the tables involved (based on which areas of the application or features you said you know were inaccessible). Once you've done that, you could run sp_BlitzIndex on that table and get a breakdown on how it's being used:

EXEC master.dbo.sp_BlitzIndex 
    @DatabaseName = N'YourDatabaseName', 
    @SchemaName = N'ProbablyDbo', 
    @TableName = N'YourTableName';

That will show you if there are indexes being scanned a lot, or experiencing lock escalation, or long lock waits, etc. Again, this would all be sort of circumstantial evidence of potential problems. But it might help.

Related Question