1

My system specifications are;

  • Windows Server 2008 R2 Enterprise SP1
  • 128 GB RAM
  • Intel Xeon E5-2620 (3 processors)
  • MS SQL Server 2012 Standard (11.0.2100.60)

There is 3 NumaNodes and 12 CPU's in them. 6 of them was checked. Yesterday, I have checked all of them (12 CPU's) and after that performance issues started.

First, I've restarted SQL Server. Problems were still going on. After that I have restarted OS, still the same issues was going on.

At night, user called me for performance problems and i unchecked them to 6. I have restarted OS and still performance problems are going on.

When I look resource usage on Task Manager, only 1-2 of them working on %100 performance for 1 second. 7 GB RAM used.

Do you have any suggestions?

closed as too broad by Erik Darling, Sean Gallardy, mustaccio, Jon of All Trades, Marcello Miorelli Jul 9 at 16:22

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • Welcome to DBA.SE. Did you do any change at maxdop or cost threshold for parallelism setting? Which wait time is highest after or below the change? Was there any need of enabling all CPUs? – Learning_DBAdmin Jul 9 at 6:40
  • 1
    What issues do you observe? The query plans cache was cleaned after the OS restart so some queries was able to take a wrong plan. The buffer cache was also cleaned so any data would be read from the disks to memory on first request. – Denis Rubashkin Jul 9 at 6:40
  • Thanks for your replies. I didn't changed any settings on SQL Server or OS. For example, simple Select Top 10K Query duration was less than a second. Now, in sometimes (not always but lots of time) it is about 7 seconds. We published new modules on our ERP program and it's more complex than all other modules about stock and reserve. @Learning_DBAdmin ___ Sure, i have executed lots of SQL queries, i am not writing after my first executes. – Xaithe Jul 9 at 7:22
  • 1
    @Xaithe Can you replicate / execute one of the bad queries and add it's actual execution plan to pastetheplan? This way we get some more insight in why that specific query is taking longer. Also, what does this return: SELECT [name],[value_in_use] FROM sys.configurations where name = 'cost threshold for parallelism' OR name = 'max degree of parallelism'; – Randi Vertongen Jul 9 at 7:41
  • 1
    Here is the result: cost threshold for parallelism 5 max degree of parallelism 0 – Xaithe Jul 9 at 8:49
4

There is 3 NumaNodes and 12 CPU's in them. 6 of them was checked. Yesterday, i have checked all of them (12 CPU's) and after that performance issues started.

Processor affinity

You should look into these affinity settings, disabling / enabling these binds specific schedulers to specific cores. I would not advise to manually change these unless you absolutely know what you are doing. More on setting processor affinity here and here.

If you must do this, look into enabling traceflag 8002 as a startup parameter and restarting SQL Server. This way the schedulers are not bound to cores in a 1 on 1 relationship.

The easier solution is letting SQL Server handle the affinity.

You can do this by right clicking the instance name in Object explorer in SSMS and checking the boxes next to Automatically set processor affinity mask for all processors and Automatically set I/O affinity mask for all processors.

enter image description here


Here is the result: cost threshold for parallelism 5 max degree of parallelism 0

Maxdop & CTFP

You should try changing these settings, these are the default settings and not recommended in most cases. An idea / starting point could be to start with setting cost threshold for parallelism to 50.

How to change this can be found here

Setting Max degree of parallelism to 4 could be a starting point. How to change this can be found here.


Prevent worker splits

I would not set my maxdop higher than the least amount of cores per node configured with processor affinity, as to try to not go cross numa.

But this is harder to force than only changing CTFP & maxdop as you can read in a part of this blogpost by Joe Obbish.

...This plan won’t work if any of the following occur:

  • Any query runs with a query level MAXDOP hint other than 0, 1, 10, or
  • Any query is downgraded in MAXDOP but still runs in parallel.
  • A parallel stats update happens. The last time I checked these run with a query level MAXDOP hint of 16.
  • Something else unexpected happens ...

In the same post, the most reliable method as to not go cross node:

The most reliable method by far is to have a single hard NUMA node, so if you have a VM that fits into a single socket of a VM host and you care about performance then ask your friendly VM administrator for some special treatment.

Source


Max memory setting for hard numa.

You should also look into setting your max memory leaving some for the OS and divisible by 3 for Hard-NUMA.

Since you are on SQL Server 2012 standard edition, the max amount of memory for your buffer pool = 64 but you could set more for the rest of the caches. More on that here

Upgrading to at least SQL Server 2014 standard edition will give you the ability to use 128GB for the buffer pool, but I would advise to to a higher version than 2014.

An idea could be capping at 98304MB = 96GB of max memory.

Configuration commands:

EXEC sys.sp_configure N'cost threshold for parallelism', N'50'
GO
EXEC sys.sp_configure N'max degree of parallelism', N'4'
GO
EXEC sys.sp_configure N'max server memory (MB)', N'98304'
GO
RECONFIGURE WITH OVERRIDE
GO

Remember to monitor your queries and act accordingly after changing these settings. If some of these settings do not improve your situation, tweak them.


Getting help in the future

If there are other issues with certain queries, adding the actual query plan & as much information to a different question will help in getting answers faster. You should also check with the infrastructure team if any of these cores are overcommitted.

There should normally not be a reason to set your affinity to use 6 cores if you could use 12 dedicated cores (as you are within 4 sockets / 24 cores for your standard licensing).

1

Adding on top of Randi's awesomeness, I'm curious if this is a physical or virtual box. Either way, you should confirm the correct distribution of memory vs CPU nodes by running

SELECT * FROM sys.[dm_os_nodes]
SELECT * FROM sys.[dm_os_memory_nodes]

Expected result is to see the same count of nodes between the two DMVs (note, node 64 is reserved for DAC usage). If the count differs, then you either have unevenly distributed memory banks (and this can only be solved by manually redistributing them), OR you have virtual NUMA enabled (solutions here depend on your hypervisor, on VMWare is as simple as unchecking the "Hot Add CPU" box from the VM configs).

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