2

I need to understand the below scenario

On Sql server 2014 with compatibility mode of 2008R2, having 80 logical processors with 20 distributed amongst 4 numa nodes. I. E numa node 1 has 0-19 , numa node 2 has 20-39 and so on.

While checking the cpu usage, we believe workload should be equally distributed amongst all of them. Sometime i see like equal distribution to some extent amongst all numa nodes to their processors.

But some time lets say during maintainence window like backup or index main jobs or runs, why is that lets say numa node 0 and 1 will not have any equal usage compares to 2 and 3 or vice versa. As far as i think it should be equally distributed.

Note we are running enterprise core edition.

Is this expected or something missing in the settings of these cores?

Update@ As said by Randolph, some process can go single threaded, i got it, but why out of the 80 they are using 80-90 % of cpu on few processors while other sitting idle. Cant the workload be equally divided when say there are 10-15 with high usage showing while remaining 50 odd processors sitting idle

Improve this question
5
  • You should also know that when things go parallel, they don't necessarily go parallel equally. So if work was divided up between you and I for example, one of us may be quite faster than the other. Then, you can compound that by multiple queries going parallel at the same time.
    – S3S
    Nov 14, 2018 at 19:02
  • @scsimon: Thanks, but can that be an issue for so many processor which are available. Is there a fix or suggestion or will this be impacting performance
    – BeginnerDBA
    Nov 14, 2018 at 19:07
  • This may help explain why you sometimes see more balance: SQL Server Parallel Query Placement Decision Logic.
    – Erik Darling
    Nov 14, 2018 at 21:37
  • @sp_BlitzErik , thank you will check. Erik, can trace flag 8079 be helpful here even though no cmemt waits.
    – BeginnerDBA
    Nov 14, 2018 at 21:56
  • @BeginnerDBA Likely not. You'd probably want to open a support case with Microsoft about that, though, rather than take the blind advice of a random stranger on the internet.
    – Erik Darling
    Nov 14, 2018 at 22:48

1 Answer 1

Reset to default
2

The short answer is that the behavior you indicate is expected. A single thread by definition only exists on a single logical processor, so for a large single-thread operation, you will not see it equally spread across all nodes. It can't be since it only exists in one place.

The longer answer is that SQL Server backup operations use one thread per data file for reading, and one thread per backup file being written to. The reader thread will create a set of buffers to store information which will subsequently be read from by the writing thread.

For instance if your database uses one file on the D:\ drive, and you are backing up to a single file on the E:\ drive, you will have one thread reading from D:\ and storing that information in buffer, and another thread reading from information in the buffers and writing it to the single file on the E:\ drive.

If you back up your database to 4 files, you will have 5 threads: one reading from D:\ and putting information into the buffers, and four getting information from buffers and writing to the separate files on E:.

In NUMA architecture, the design is that these related threads will exist on the same node to optimize memory access - after all, the four writer threads want information from the buffers that the associated reader thread is using. It only makes sense to keep them on the same node.

For further reading see this post by Shanky_621.

Improve this answer

Your Answer

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

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