Databases Reference
In-Depth Information
scenarios that would benefit from simulated NUMA, so it became a supported feature. It allows you to
group CPUs into soft-NUMA nodes, which can help with performance if you have lots of CPUs and no
hardware NUMA. For example, SQL Server has one I/O thread and one Lazy Writer thread per NUMA
node, which can become a bottleneck on a non-NUMA system, so if you have an eight CPU machine
then configuring 4
×
2-CPU soft-NUMA nodes would make SQL Server use 4
×
I/O threads and 4
×
lazy writer threads without using hardware NUMA.
Best Practice
Use Soft-NUMA to allocate groups of CPUs to specific tasks even when you don't have
hardware NUMA. You can even create soft-NUMA nodes within hardware NUMA
nodes to further control what your CPUs work on.
Summary
This chapter covered a lot of ground that might not be a core skill for the SQL Server Professional but
plays a very important part in SQL Server's performance. These topics need to be understood and taken
into consideration at both the design stage and when supporting a live system.
The chapter centered on the three key server resources: Memory, I/O, and CPU, and covered design
choices and configuration best practices for each area.
For memory, we covered architecture and discussed the configuration options for large memory
support:
/3GB
,
/PAE
, and AWE. We talked about how to decide which options to use and gave
prescriptive guidance to use as a baseline.
For I/O, we briefly covered network versus disk I/O and discussed types of hard disks, SAN and DAS
solutions, storage configuration best practices, and SQL Server file placement.
For CPU, we discussed 32-bit versus 64-bit, multi-core, Hyper-Threading, CPU cache, and NUMA
versus SMP to help you understand how to make an informed decision when purchasing a processor
or computer architecture.
In the next chapter we will look at tuning the database schema to make more efficient use of server
resources to help avoid bottlenecks.
Search WWH ::
Custom Search