2.1 Planning the Processor subsystem

2.1 Planning the Processor subsystem

The processor (Processor) is the computing core and the control core of the computer. The CPU used by the common PC and server (central processing Unit, referred to as CPUs) is a microprocessor.

2.1.1 X86 and X64 processors

The computer executes the machine instruction at the bottom, and the processor can be divided into different platforms depending on the instruction set. The processor platforms that can run SQL Server are mainly X86, X64, and IA64.

The X86 platform is compatible with Intel's i386 32-bit instruction set, and the values are sent to the 32-bit registers for additive operations. The minimum supported X86 processor type for SQL Server is Intel Pentium III or a compatible processor.

The X64 platform uses 64-bit instruction sets, the first of which was the AMD64 processor family that AMD introduced in 1999, and later Intel introduced the AMD64-compatible Intel EM64T processor family. Since AMD64 is basically consistent with Intel EM64T, many hardware and software products use neutral vocabularies to indicate compatibility with both platforms and are therefore called X64.

Compared to X86, the X64 platform uses an enhanced bus structure that delivers more data to the cache and processors, and improves parallel processing for multiprocessor (more than 64 processors), so the performance of the X64 is impressive. The minimum supported X64 processor types for SQL Server are AMD Opteron, AMD Athlon 64, and Intel Xeon, Intel Pentium IV, which supports Intel EM64T.

Since 2010, Microsoft has released a server offering that basically supports only the X64 platform, such as Windows server R2, Exchange 2010, SharePoint 2010, Lync 2010, and so on.

The IA64 processor type is an Itanium (Itanium) processor. It is a processor developed by Intel Corporation with HP and is incompatible with IA64. Windows 2000 Premium Server Limited edition (Advanced Server Limited Edition) began to support this processor. Due to the poor market share of Itanium, the new products released by Microsoft in 2012 (Windows Server 2012, SQL Server 2012, and Visual Studio 2012) No longer support the Itanium processor.

The ARM (Advanced RISC machines) processor is primarily designed for portable devices and features low cost, high performance, and low power consumption, and can run Windows 8 RT and subsequent versions of the operating system on your tablet. However, SQL Server does not currently support ARM processors.

The Alpha processor was originally designed and manufactured by DEC, and Compaq's acquisition of DEC in 2001 announced that the Alpha processor would be progressively transferred to Intel's Itanium architecture. After Hewlett Packard Corporation acquired Compaq, the last Alpha new product was released in 2004. This processor is supported in both SQL Server 2000 and previous versions. But Microsoft stopped selling the Alpha processor-enabled Windows NT operating system in 1999, and Windows 2000 no longer supported the processor in version Bete 3.

The main frequency of the processor, the clock frequency at which the processor core is operating, is an important indicator of processor performance. When you install SQL Server, the Setup program checks the processor type and frequency of your computer. The following table lists the minimum frequencies required for each version of SQL Server.

Version	X86 processor	X64 processor	IA64 processor
SQL Server 2005	-MHz	1.0 GHz	1.0 GHz
SQL Server 2008	1.0 GHz	1.4 GHz	1.0 GHz
SQL Server R2	1.0 GHz	1.4 GHz	1.0 GHz
SQL Server 2012	1.0 GHz	1.4 GHz	Not supported
SQL Server 2014	1.0 GHz	1.4 GHz	Not supported

Tips:

There is a WOW64 (windows-on-windows 64-bit) subsystem in the X64 operating system. It can provide impersonation for existing 32-bit applications, enabling most 32-bit applications to run on 64-bit operating systems without modification.

WOW64 provided only to meet compatibility requirements, a 32-bit application may experience performance problems running in WOW64.

2.1.2 NUMA

When the frequency of a single processor reaches its limit, a popular way to improve performance is to add more processors, one of which is the SMP (symmetric multi-processing, symmetric multi-processing) architecture. In this architecture, all processors are the same, they have equal access to a centralized memory, and share the memory bus and I/O bus.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/5A/07/wKioL1T0SWbBij65AAHxG1mZ8XA582.jpg "title=" SMP. PNG "alt=" Wkiol1t0swbbij65aahxg1mz8xa582.jpg "/>

When multiple processors simultaneously request access to the same resource (for example, the same memory address), there is a need for a mechanism to solve the problem of resource contention. Therefore, when there is a large number of processors, or there is a complex logical association between multiple parallel tasks performed, simply increasing the number of processors does not lead to significant performance gains.

The main feature of the SMP architecture is the sharing of resources (memory, I/O, etc.) in the system, which can be a bottleneck (especially in memory) because of each sharing link, resulting in a very limited ability to scale. The General SMP architecture uses 2 to 4 CPUs with a maximum of 8 CPUs.

Another multi-processor architecture is MPP (Massive Parallel processing, massively parallel processing). In this architecture, the resources (processor, memory, I/O, etc.) are divided into multiple SMP nodes, the processors in the nodes can only access resources in the same node, and the resources of other nodes need to be connected through the network of nodes outside the node. The main feature of MPP is that the nodes do not share resources, often can achieve hundreds of nodes, thousands of processors in parallel processing.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/5A/0B/wKiom1T0SHXze5vOAAH0E3Ep6eg625.jpg "title=" MPP. PNG "alt=" Wkiom1t0shxze5voaah0e3ep6eg625.jpg "/>

Because the MPP architecture requires data to be passed across different nodes, the MPP is less efficient than SMP when the transaction size of the OLTP is small. However, there is no competition for memory buses in the MPP schema, and the MPP architecture presents an advantage when the processing requirements of large programs are greater than the shared bus, or if there is little association between operations.

The SQL Server PDW version uses the MPP schema, with each compute node having its own storage node. The PDW satisfies the performance requirements of loading an enterprise-class data warehouse and facilitates linear scaling by increasing the number of nodes.

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/5A/0B/wKiom1T0SJSRiuHnAAGg_2lIF0k011.jpg "title=" Pdw.png "alt=" Wkiom1t0sjsriuhnaagg_2lif0k011.jpg "/>

In the OLTP process, the main bottleneck of the SMP architecture is usually memory. The NUMA (non-uniform memory Access, non-uniform RAM access) architecture combines the advantages of SMP and MPP in memory access, where the processor in all nodes can access the memory of the local node. You can also access the memory of the remote node (that is, the processor can access the entire system's memory). Because access to local memory is faster than accessing the remote node's memory, it is called "non-uniform" memory access.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/5A/07/wKioL1T0SbSSXvxPAAHOW8BvubM177.jpg "title=" NUMA. PNG "alt=" Wkiol1t0sbssxvxpaahow8bvubm177.jpg "/>

NUMA can theoretically be infinitely extensible, enabling the processor subsystem to have high OLTP processing power. Support for NUMA has been provided by SQL Server SP4.

2.1.3 Hyper-Threading

SQL Server uses the operating system's threads to perform concurrent tasks, and thread management is controlled by the operating system kernel. When a thread finishes exiting the CPU and other threads are scheduling the CPU, a context switch will occur, which requires a certain cost and should be avoided as much as possible.

Hyper-Threading (hyper-threading) is a technology released by Intel in 2002. With Hyper-threading, the application can use different parts of the chip at the same time, and a single CPU executes two threads at the same time as two CPUs.

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/5A/0B/wKiom1T0Sa2B2A-KAADi5CjXAMQ924.jpg "title=" Hyper-Threading. jpg "alt=" wkiom1t0sa2b2a-kaadi5cjxamq924.jpg "/>

Although the use of Hyper-Threading technology can execute two threads at the same time, but its performance is not equal to the performance of two CPUs, in theory, Hyper-threading can be used to 15%~30% performance improvement. In fact, hyper-threading can actually degrade performance for some programs or for single-threaded programs. In addition, hyper-Threading technology requires operating system mates, and Windows XP and Windows 2003 and later versions support Hyper-Threading technology.

For SQL Server systems, SQL Server itself already has an extremely efficient scheduling management mechanism to handle its own operating system queues and threads, so hyper-threading is only recommended for high physical CPU load and must be tested in the event of Hyper-threading startup and shutdown Performance of SQL Server. If the value of the Context switches/sec counter for each physical processor is higher than 5000, the impact of hyper-threading on performance is already severe.

Tips:

Hyper-Threading and dual-core processors are different concepts. The concept of a dual-core processor is to combine the two processor chips with a new packaging technology that integrates into a single processor, with two cores in the processor that truly enables multiprocessor collaboration to work. The core of a dual-core processor is independent and can be exchanged for resources, and the latency of communication between core and core is much better than that of multiple single-core processors.

2.1.4 Planning the number of processors

The processor is divided into physical processors and logical processors. The numeric value of a logical processor is the product of the number of physical processors (slots) and the number of cores, for example, 4 physical processors, 10 cores per processor, and 40 logical processors. If Hyper-Threading is enabled, the number of logical processors is doubled, which is 80 logical processors.

Logical processors installed on the computer may not be detected and used by SQL Server because of the following limitations:

Different versions of the operating system limit the number of slots in the physical processor. For example, the Standard edition of Windows Server 2008 is limited to 4 slots, the Enterprise Edition is limited to 8 slots, and the Datacenter Edition supports a maximum of 64 slots. Both standard and Datacenter editions of Windows Server 2012 are limited to 64 slots.

The operating system also limits the number of logical processors. For example, Windows Server 2008 accepts only 64 logical processors until Windows 7 and Windows Server R2 allow more than 64 logical processors. Windows Server 2012 supports a maximum of 640 logical processors (320 logical processors are supported if Hyper-V is enabled).

The number of logical processors detected by the SQL Server product is limited. For example, SQL Server 2008 can detect up to 64 logical processors at a time. SQL Server R2 can detect up to 256 logical processors at a time.

Starting with SQL Server 2012 is licensed in per Core mode, and there are technical limitations for different versions. For example, the Standard Edition is limited to 4 slots or 16 cores, whichever is smaller.

In addition to the above limitations, you may need to manually limit the number of logical processors when you deploy SQL Server in accordance with the purchase license per Core mode.

2.1.5 Planning the load on the processor

Use Performance Monitor to monitor the "%processor time" counter to learn the processor's load, which can be used to determine the use of the logical processor. It is generally considered that the average load should not exceed 30%, up to 50%.

Also, check the peak level of the counter. It is generally considered that the CPU usage should not last more than 70% in 10-15 minutes. If more than 80%, it means you need to upgrade the processor, or you need to add more processors.

This article from "SQLServer2014 series" blog, declined reprint!

2.1 Planning the Processor subsystem