Processor Affinity in SQL Server

SQL Server is typically run on multiprocessor servers, which is now particularly prevalent. The reason is the increasing popularity of multi-core processors.

In a multiprocessor environment, then, the Windows operating system (in fact, starting with 2000) usually puts the process tasks in a team and then lets the processing tasks take the processor to compute in turn.

The advantage of this is that each computing task can get an approximate average processing resource, although it is not guaranteed that one processing task can get the same processor each time. It's like a carnival. We queued up for an exciting project (such as free fall, in fact I never took part in the project), and everyone went up a round and couldn't guarantee to be in the same position every time.

But back to SQL Server, SQL Server does not like this kind of processing mechanism.

You may know that there is something in the processor called in-chip caching, in-chip caching has 1, 2, 3 grade points.

0vJ O9e4 i?,g3v _8o14943301 We assume that the processor is going to compute A, B, c three tasks, the processor first operation a task, a task has not finished when its game time is over, so the processor in the acceptance of B will calculate the required data B load into the 1-slice cache, The data for a task, which we assume the processor has not yet completed, is moved to the 2-level slice cache, or level 3.

When the A-mission comes back to the calculation, the processor recovers the required data from the 2-level slice cache, depending on whether the data is still in the Level 2 cache, because there are many factors that make it not there, for example, when the a task comes back, it is not the same processor that received it. Of course a task cannot be expected to be in front of the processor has its calculation data (of course, calculate a task back to the same processor, or because other tasks occupy the processor's 2-slice cache, resulting in the data it was originally stored in the replacement).

If the processor finds that the a task data is still in Level 2 cache, the operating system thinks this is the hit of level 2 cache, and if it is not, it is not hit level 2 cache. So we know that the operating system is very eager to hit the level 2 cache each time, because this will save a lot of time to reload the data from memory into the slice cache.

Most operating systems do not have a lot of computational data to deal with, so these tasks do not require much concern about the caching of the slice. At the same time, most low-end servers do not have very large cache, so they are less concerned about the problem. But for SQL Server running on a server with a larger cache, the problem is a bit more serious.

In a high-end PC server (why is it a PC server, because Windows can now run on some vendors ' minicomputer platforms, such as HP's Superdome), typically a single processor's in-chip caching is 2m-4m, And these servers can have 8 or more processors, and SQL Server's computing task relies on a lot of data, so a task in SQL Server doesn't want it to find its data out when it's back to the processor.

To solve this problem, SQL Server has a configuration entry for this processor affinity (Processor Affinity), and when this option is enabled, tasks in SQL Server will remember that they originally worked on that processor. When they have the chance to return to the processor again they will look for their way home--only with the original processor. (In fact, the process is a bit more complicated, and interested friends can learn more about scheduling in SQL Server).