I have a brand new server with SQL Server 2016.
It has 24 CPU, about 80go of RAM.
The thing is, sometimes, the CPU gets pretty high (> 70%) with no specific reason at all.
If I look at the 'execute sp_WhoIsActive @get_locks=1' I get about 40 queries running, but without lock, and some of them for more than 30 seconds instead of a few ms.
This append about once a day in production. The only fix I've got so far is changing the option "cost threshold for parallelism" from 90 to 89 and from 89 to 90. Changing this value actually fix the issue in less than 10 seconds and I get a CPU charge getting lower and my user happier.
Do you guys have any idea about the root of this issue? I've thought about plan cache but I don't know what to do with that idea...
Edit : I've disable Intel HyperThreading but the issue is still the same. I've put a task cleaning the cache every hour. Do you have any idea ?
Edit 2 : I have open an issu on Microsoft Support. Apparently there is an issue with page rentention solved with a reboot of SQL Server 2016. I'll let you know ASAP if I have a "real" fixe for this.