5

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.

Improve this question
9
  • Do you have example queries and execution plans for the problematic queries? Also changing cost treshold will drop the plan cache as a side effect, so you might as well just run DBCC FREEPROCCACHE and see if that helps too
    – Tom V
    Commented Sep 27, 2016 at 11:15
  • check 4 things - power plan set to high performance mode, max dop value and I guess 89 for cost threshold is a bit high, but your envionment is different than mine, try with a lower value e.g. 35 and see along with are you using the new CE or the old CE and query fixes are on or off ?
    – Kin Shah
    Commented Sep 27, 2016 at 15:35
  • So, 'DBCC FREEPROCCACHE' resolve the issue as well. I switch to high performance mode but sadly it's not solved, threshold is still at 90 but that's not the issue I guess ( ? ). I'm in compatibility mode 120 with SQL Server Query Optimizer ON.
    – Guillaume Philipp
    Commented Sep 28, 2016 at 7:32
  • In my experience, CPU spikes indicate poorly performing queries due to missing indexes. Can you post the query plan for one of the queries running at the time of the spike?
    – datagod
    Commented Nov 30, 2016 at 14:25
  • @GuillaumePhilipp has Microsoft resolved it yet? I suspect I'm facing the same issue. Restarting the SQL Server service didn't help. Restarting Windows resolved it.
    – Jeow Li Huan
    Commented Jan 11, 2017 at 6:22

1 Answer 1

Reset to default
-1

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.

Improve this answer
11
  • The MAXDOP is 8. Indeed i have HT enabled, i'll try to disable it ASAP. I Don't have a spare server so it's not easy to do... Si you yhink this has something to do with MAXDOP ? I don't think so because it seems like it's the fact of changing the valu that help, it's not really about the value itself. Edit : I have 8 files for tempdb
    – Guillaume Philipp
    Commented Sep 27, 2016 at 7:54
  • Please have a test on MAXDOP value '6' and reduce the value for cost threshold to 60 or little less. This should bring more queries above the parallelism threshold, but fewer cores to work with. This could be an easy test, without rebooting the server.
    – Tobias A.
    Commented Sep 27, 2016 at 8:06
  • Or try setting MaxDOP value according to Your NUMA architecture, limiting it to number of cores in single NUMA node. I think it's suggested in Brent Ozar's First Aid kit (blog posts say only about physical cores and link to MS: brentozar.com/archive/2013/09/… )
    – Marcin S.
    Commented Sep 27, 2016 at 8:51
  • 1
    You should try to disable Hyper-Threating and verify the new settings. - why ?
    – Kin Shah
    Commented Sep 27, 2016 at 15:36
  • I'll try this during the weekend and I'll let you know !
    – Guillaume Philipp
    Commented Sep 28, 2016 at 7:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.