70-450 · Question #43
You are the database administrator for a SQL Server that hosts an application defined as follows: The database consists of 4 data files and one log file. The physical server has 16 CPUs and 64 GB of…
The correct answer is C. Configure four software non-uniform memory access (soft NUMA) nodes on the server. There are a number of terms in the question that don't come up often. The lazy writer is partially responsible for writing data from memory buffers to the disk. It's called 'lazy' because it only functions when the amount of free buffers becomes low. Otherwise, it doesn't do…
Question
You are the database administrator for a SQL Server that hosts an application defined as follows:
The database consists of 4 data files and one log file. The physical server has 16 CPUs and 64 GB of memory. Hardware non-uniform memory access (NUMA) is not available on this server platform. Users of the application report that it is responding slowly. You use SQL Server Profiler to find out that the I/O performance issue is related to the lazywriter process. You need to improve the performance of the application by reducing the I/O performance issue. What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)
Options
- AAdd additional RAM to the server.
- BAdd additional data files to the database.
- CConfigure four software non-uniform memory access (soft NUMA) nodes on the server.
- DConfigure a CPU affinity mask on the server to limit the instance of SQL Server to four
How the community answered
(16 responses)- A6% (1)
- B13% (2)
- C81% (13)
Explanation
There are a number of terms in the question that don't come up often. The lazy writer is partially responsible for writing data from memory buffers to the disk. It's called 'lazy' because it only functions when the amount of free buffers becomes low. Otherwise, it doesn't do much. NUMA organizes the way that CPUs access memory: The benefits of soft-NUMA include reducing I/O and lazy writer bottlenecks on computers with many CPUs and no hardware NUMA. There is a single I/O thread and a single lazy writer thread for each NUMA node. Depending on the usage of the database, these single threads may be a significant performance bottleneck. Configuring four soft-NUMA nodes provides four I/O threads and four lazy writer threads, which could increase performance. CXPACKET waits occur due to problems with the timing of multiple processors. It's low here, so we know that's not the issue.
Topics
Community Discussion
No community discussion yet for this question.