Our SQL Server is experiencing sudden high CPU usage since 12:30am today. This server is used internally so nobody should be accessing the server at that time.
I'm not sure why 12 hours later the CPU usage is still at 100%. Looking at the resource monitor on the server it is sqlservr.exe that is using 99% of the CPU.
We have re-index jobs that kick off at 12:30am every other morning, but those jobs were completed. These jobs also update the statistics via DBCC DBREINDEX.
I've been researching for the past 3 hours, but most of the answers have been on tuning queries or troubleshooting a single query that is hogging the CPU. ALL of our queries have been slow today, which is very abnormal.. I've checked multiple times for a single query, but haven't found anything.
I've run an sp_who2 'Active', but there are no processes in the suspended state.
I have tried several different DMV and other queries to pinpoint the issue, but haven't been able to find anything..
It seems like SQL Server is hung up and needs a restart, but I'd like to avoid that at all costs…
Best Answer
Answer converted from comment:
sp_whoisactiveshows the following output:Restarting the SQL Server Service resolved the issue. I think I know why thanks to sp_whoisactive. My theory is we have a query that was running in a loop (the top 8 rows) and that got stuck during the DBCC DBREINDEX that occurred at 12:30AM. This query was using up all of the CPU since it was stuck in that query... Once the server was rebooted, there are hardly any records in the sp_whoisactive. MUCH BETTER!!