CPU being utilized 100% after upgrading MS SQL Server 2014 from 2008R2

[sdhanalakoti]

Hi,

We have SAP BW 7.0 SP31 on Windows Server 2012R2 and MS SQL Server 2014.

Whenever the Batch jobs are running, CPU is going 100% utilized and it is becoming a concern for our OS team.

We did increase the CPU cores from 10 cores to 14cores. But the issue still persists.

Can anyone point me the direction i should be troubleshooting.

Thanks and Regards,

Satish Dhanalakoti

[luisdarui]

Hi Satish,

What is the process consuming CPU? Is SQL Server?

Best Regards

Luis

[sdhanalakoti]

Hi Luis,

Whenever CPU is 100% utilized, MS SQL Server is taking up most of it(almost 95%)

Thanks and Regards,

Satish Dhanalakoti

[luisdarui]

Satish,

1) Open the DBACockpit and go to check the Performance > Overview screen. Check for any red alert in the "alert" tab. Usually there might be some problematic queries consuming too much CPU.

2) Check out for Locks in your database. Check DBACockpit > Performance > Locks.

3) check "Database Processes and select only active requests checkbox, order by CPU and check what is the SID (SQL Server) and PID (SAP) are running and consuming most CPU.

You can also try this approach from here: https://www.mssqltips.com/sqlservertip/2454/how-to-find-out-how-much-cpu-a-sql-server-process-is-rea....

Best Regards,

Luis Darui