Question

You are just employed as a Database Administrator (DBA) at Nusantara Hospital, which now has to cope with increasing number of patients. Currently, the hospital is using Microsoft SQL Server as its database software and installed it locally. There are programmers who design the database system and write web- based user interface. However, as the users of the system are growing, the system is getting slower and sometimes unresponsive. Your role as the DBA organization is to configure and optimize SQL Server, monitor its performance and provide troubleshooting of database related problems. Specifically, there are tasks that you shall work out:

Troubleshooting.

(a) Optimizing CPU usage. Upon inspecting the CPUs, each has 8 cores. What is the optimal number of the maximum degree of parallelism for each CPU? Why do you choose such number?

Answer 1

Upon inspecting the CPUs, each has 8 cores. What is the optimal number of the maximum degree of parallelism for each CPU? Why do you choose such number?

It depends on the number NUMA nodes.

What is a NUMA node?

NUMA stands for Non-Uniform Memory Access

A NUMA node = 1 Physical processor = 1 core

Each of the CPUs have 8 cores, hence, number of NUMA nodes = 8

What is a physical processor?

The actual hardware component. A 4-core CPU has 4 processors. A physical processor is a single core in simple words.

What is a logical processor?

Number of threads allowed per core. If a core can run 2 threads at a time, the number of logical processors = 2, number of core or physical processor = 1

For a 4-core CPU,

number of physical processors = 4

If each core can run 2 threads at a time

number of logical processors = 4*2 = 8

For each of CPUs, maximum degree of parallelism will depend on the number of logical processors per NUMA node

For Servers with multiple NUMA nodes

if logical processors are less than or equal to 8 per NUMA node

Keep parallelism at or below the number of logical processors per NUMA node

Why equal to the number of logical processors?

Because we want each task to get at least 1 logical processor all the time. In other words, all the tasks get at least 1 logical processor.

What happens if parallelism is less than the number of logical processors

2 or more logical processors may work on the same task at a time.

if logical processors are greater than 8 per NUMA node

Keep parallelism at 8

Why not more than 8?

More parallelism means more overhead to put everything back together.

8 is like an accepted standard. A greater may get you a better performance, but it will suffer from more overhead.

I hope it helped you. All the best!