SQL Server 2008 CPU Performance monitoring

CPU Performance Diagnostics

CPU Architecture

The current mainstream enterprise servers can be categorized into three categories: SMP (symmetric Multi processing, symmetric multi-processing architecture), NUMA (non-uniform Memory access, inconsistent storage access architecture), and MPP (massive Parallel processing, mass parallel processing architecture)

SMP (symmetric Multi processing)

SMP is a very common architecture. In SMP mode, multiple processors are symmetrically connected to system memory, and all processors access system memory at an equal cost.  The advantage of this is that access to memory is equal, consistent; The disadvantage is because everyone is consistent, in the traditional SMP system, all processors share the system bus, so when the number of processors increased, the system bus competition rapidly increased, the system bus has become a performance bottleneck, so the current SMP The number of processors in the system is generally only dozens of, and scalability is greatly limited.

MPP (massive Parallel processing)

MPP is the logical division of the entire system into multiple nodes, each node's processor can only access its own local resources, is a completely unshared architecture. Data exchange between nodes requires software implementation. Its advantage is that scalability is very good, the disadvantage is that data exchange difficulties with each other, the need to control a lot of software work to achieve communication and task allocation, scheduling, for the general enterprise applications is too complex, inefficient.

NUMA (non-uniform Memory Access)

NUMA architecture is in a sense a combination of SMP and MPP features: Logically the entire system is divided into multiple nodes, each node can access local memory resources, but also access to remote memory resources, but access to local memory resources far faster than remote memory resources. It has the advantage of taking into account the characteristics of SMP and MPP, easy to manage, extensibility is good; The disadvantage is that the time required to access remote memory resources is very large.

But the world's top-ranked mainframes are based on MPP architectures, AMD's first support for NUMA architectures, and while Intel has been a supporter of SMP, it has evolved from Xeon processors and Core i7 to NUMA architecture.

SQL Server has increased support for NUMA starting with the 2000 version of SP4, and has been fully implemented since version 2005; The SQL Server2008 R2 version bathroom adds support for the MPP architecture, but in the current case of SQL Server, it mainly supports SMP and NUMA two architectures.