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.
Best Answer
Please verify the settings for MAXDOP. Please review suggestions by Microsoft. In most cases the optimal value is the number of dedicated cores, that are available to SQL Server. But maximum value is set to '8'. In some cases that could vary and needs to be tested well to set to higher values.
I assume, that you have two six-core Processors with Hyper-Threating enabled. It is possible, that your queries don't perform very well, with Hyper-Threating enabled. In some cases the SQL Server Performance is better using 12 'real' cores, than 24 combined with Hyper-Threating.
You should try to disable Hyper-Threating and verify the new settings.
Unfortunately you wrote, that this is your production environment. Are you able to verify settings on similar hardware and similar settings?
Furthermore you should have a look on your I/O subsystem. Are there more than one tempdb?
Additional (I can not comment another comment):
DBCC FREEPROCCACHE will effect all user. Try using with option recompile at the SQL Query that effects the Performance issue most.