affinity mask 伺服器組態選項affinity mask Server Configuration Option

適用於: 是SQL Server 否Azure SQL Database 否Azure SQL 資料倉儲 否平行處理資料倉儲 APPLIES TO: yesSQL Server noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

注意

未來的 Microsoft SQL Server 版本將移除這項功能。This feature will be removed in a future version of Microsoft SQL Server. 請勿在新的開發工作中使用此功能,並且儘速修改使用此功能的應用程式。Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible. 請改用 ALTER SERVER CONFIGURATION (Transact-SQL)Use ALTER SERVER CONFIGURATION (Transact-SQL) instead.

為了執行多工作業, MicrosoftMicrosoft Windows 有時會在不同的處理器之間移動處理序執行緒。To carry out multitasking, MicrosoftMicrosoft Windows sometimes move process threads among different processors. 雖然從作業系統的觀點來看很有效率,但是在繁重的系統負載下,這項活動可能會降低 SQL ServerSQL Server 的效能,因為每個處理器快取會重複地重新載入資料。Although efficient from an operating system point of view, this activity can reduce SQL ServerSQL Server performance under heavy system loads, as each processor cache is repeatedly reloaded with data. 在這些情況中,將特定執行緒指定給處理器,可降低處理器重新載入的情形並減少跨處理器移轉執行緒的問題 (藉此減少內容切換),進而提升效能,而執行緒與處理器之間的關聯則稱為處理器相似性。Assigning processors to specific threads can improve performance under these conditions by eliminating processor reloads and reducing thread migration across processors (thereby reducing context switching); such an association between a thread and a processor is called processor affinity.

SQL ServerSQL Server 透過兩個相似性遮罩選項支援處理器相似性:affinity mask (也稱為 CPU affinity mask) 與 affinity I/O mask。supports processor affinity by means of two affinity mask options: affinity mask (also known as CPU affinity mask) and affinity I/O mask. 如需 affinity I/O mask 選項的詳細資訊,請參閱 affinity Input-Output mask 伺服器組態選項For more information on the affinity I/O maskoption, see affinity Input-Output mask Server Configuration Option. 擁有 33 到 64 個處理器的 CPU 與 I/O 相似性支援需要分別另外使用 affinity64 mask 伺服器組態選項affinity64 Input-Output mask 伺服器組態選項CPU and I/O affinity support for servers with 33 to 64 processors requires the additional use of the affinity64 mask Server Configuration Option and affinity64 Input-Output mask Server Configuration Option, respectively.

注意

擁有 33 到 64 個處理器之伺服器的相似性支援只能在 64 位元的作業系統上使用。Affinity support for servers with 33 to 64 processors is only available on 64-bit operating systems.

affinity mask 選項存在於較早的 SQL ServerSQL Server版本中,可動態控制 CPU 相似性。The affinity mask option, which existed in earlier releases of SQL ServerSQL Server, dynamically controls CPU affinity.

SQL ServerSQL Server中,不需要重新啟動 SQL ServerSQL Server執行個體,即可設定 affinity mask 選項。In SQL ServerSQL Server, the affinity mask option can be configured without requiring a restart of the instance of SQL ServerSQL Server. 使用 sp_configure 時,您必須在設定組態選項之後,執行 RECONFIGURE 或 RECONFIGURE WITH OVERRIDE。When you are using sp_configure, you must run either RECONFIGURE or RECONFIGURE WITH OVERRIDE after setting a configuration option. 當您使用 SQL Server ExpressSQL Server Express時,變更 affinity mask 選項後需要重新啟動。When you are using SQL Server ExpressSQL Server Express, changing the affinity mask option does require a restart.

對 affinity mask 的變更會動態發生,允許視需要啟動或關閉 SQL ServerSQL Server中繫結處理序執行緒的 CPU 排程器。Changes to the affinity masks occur dynamically, allowing for on-demand startup and shutdown of the CPU schedulers that bind process threads within SQL ServerSQL Server. 這會因伺服器上的條件變更而發生。This can occur as conditions change on the server. 例如,若將 SQL ServerSQL Server 的新執行個體加入伺服器,則可能必須對 affinity mask 選項進行調整,以便分散處理器負載。For example, if a new instance of SQL ServerSQL Server is added to the server, it may be necessary to make adjustments to the affinity mask option to redistribute processor load.

對相似性位元遮罩的修改需要 SQL ServerSQL Server 啟用新的 CPU 排程器並停用現有的 CPU 排程器。Modifications to the affinity bitmasks require SQL ServerSQL Server to enable a new CPU scheduler and disable the existing CPU scheduler. 然後,新批次可在新的或剩餘的排程器上加以處理。New batches can then be processed on the new or remaining schedulers.

為了啟動新的 CPU 排程器, SQL ServerSQL Server 會建立新的排程器,並將它加入其標準排程器的清單中。To start a new CPU scheduler, SQL ServerSQL Server creates a new scheduler and adds it to the list of its standard schedulers. 新的排程器只能用於新內送的批次。The new scheduler is considered only for the new incoming batches. 目前批次會繼續在相同的排程器上執行。Current batches continue to run on the same scheduler. 工作者會在釋出時或建立新工作者時,合併到新的排程器。The workers migrate to the new scheduler as they free up, or as new workers are created.

排程器上所有的批次都完成活動並結束,才能關閉排程器。Shutting down a scheduler requires all batches on the scheduler to complete their activities and exit. 必須關閉的排程器會標示為離線,如此就不會在此排程器上排定新批次。A scheduler that has been shut down is marked as offline so that no new batch is scheduled on it.

不論新增或移除新的排程器,只要伺服器保持運作,永久性系統工作就會繼續在排程器上執行,例如,鎖定監視、檢查點、系統工作執行緒 (處理 DTC) 及訊號處理序。Whether a new scheduler is added or removed, the permanent system tasks such as lockmonitor, checkpoint, system task thread (processing DTC), and signal process continue to run on the scheduler while the server is operational. 這些永久性系統工作不會動態地移轉。These permanent system tasks do not dynamically migrate. 若要將這些系統工作的處理器負載分散到不同排程器上,必須重新啟動 SQL ServerSQL Server 執行個體。To redistribute processor load for these system tasks across schedulers, it is necessary to restart the SQL ServerSQL Server instance. 如果 SQL ServerSQL Server 嘗試關閉與永久性系統工作關聯的排程器,工作會繼續在離線的排程器上執行 (沒有移轉)。If SQL ServerSQL Server attempts to shut down a scheduler associated with a permanent system task, the task continues to run on the offline scheduler (no migration). 此排程器已繫結到修改的相似性遮罩中的處理器,且不應在變更之前放置任何負載於此處理器上。This scheduler is bound to the processors in the modified affinity mask and should not put any load on the processor it was affinitized with before the change. 具有多餘的離線排程器並不會明顯影響到系統的負載。Having extra offline schedulers, should not significantly affect the load of the system. 如果不是這樣的狀況,重新設定這些工作需要重新啟動資料庫伺服器。If this is not the case, a database server reboot is required to reconfigure these tasks.

I/O affinity mask 會直接影響到 I/O 相似性工作 (例如 lazywriter 與 logwriter)。The I/O affinity tasks (such as lazywriter and logwriter) are directly affected by the I/O affinity mask. 如果 lazywriter 與 logwriter 工作並未相似化,則會遵循針對其他永久性工作所定義的相同規則,例如鎖定監視或檢查點。If the lazywriter and logwriter tasks are not affinitized, they follow the same rules defined for the other permanent tasks such as lockmonitor or checkpoint.

為了確定新的相似性遮罩有效,RECONFIGURE 命令會驗證一般的 CPU 和 I/O 相似性是否互斥。To ensure that the new affinity mask is valid, the RECONFIGURE command verifies that the normal CPU and I/O affinities are mutually exclusive. 如果不是這樣的狀況,會將錯誤訊息回報至用戶端工作階段和 SQL ServerSQL Server 錯誤記錄檔,表示不建議這樣的設定。If this is not the case, an error message is reported to the client session and to the SQL ServerSQL Server error log, indicating that such a setting is not recommended. 執行 RECONFIGURE WITH OVERRIDE 選項可允許未互斥的 CPU 和 I/O 相似性。Running RECONFIGURE WITH OVERRIDE options allows CPU and I/O affinities that are not mutually exclusive.

如果您指定嘗試對應到不存在之 CPU 的相似性遮罩,RECONFIGURE 命令會將錯誤訊息回報至用戶端工作階段和 SQL ServerSQL Server 錯誤記錄檔。If you specify an affinity mask that attempts to map to a nonexistent CPU, the RECONFIGURE command reports an error message to both the client session and the SQL ServerSQL Server error log. 在此情況下,使用 RECONFIGURE WITH OVERRIDE 選項沒有作用,而且會再次回報相同的組態錯誤。Using the RECONFIGURE WITH OVERRIDE option has no effect in this case, and the same configuration error is reported again.

您也可以從處理器中排除 SQL ServerSQL Server 活動,此為 Windows 2000 或 Windows Server 2003 作業系統所指定的特定工作負載。You can also exclude SQL ServerSQL Server activity from processors assigned specific workload assignments by the Windows 2000 or Windows Server 2003 operating system. 如果將某處理器的代表位元設成 1,則表示 SQL ServerSQL Server Database Engine 已選取該處理器準備進行執行緒指派。If you set a bit representing a processor to 1, that processor is selected by the SQL ServerSQL Server Database Engine for thread assignment. 若將 affinity mask 設成 0 (預設值),Microsoft Windows 2000 或 Windows Server 2003 排程演算法會設定執行緒的相似性。When you set affinity mask to 0 (the default), the Microsoft Windows 2000 or Windows Server 2003 scheduling algorithms set the thread's affinity. affinity mask 設成任何非零的值時, SQL ServerSQL Server 相似性會將該值解譯為指定適合選取之處理器的位元遮罩。When you set affinity mask to any nonzero value, SQL ServerSQL Server affinity interprets the value as a bitmask that specifies those processors eligible for selection.

藉由將 SQL ServerSQL Server 執行緒從特定處理器中分離,Microsoft Windows 2000 或 Windows Server 2003 可以更有效地評估 Windows 特定處理序的系統處理。By segregating SQL ServerSQL Server threads from running on particular processors, Microsoft Windows 2000 or Windows Server 2003 can better evaluate the system's handling of processes specific to Windows. 例如,系統管理員可以在執行兩個 SQL ServerSQL Server 執行個體 (執行個體 A 與 B) 的 8-CPU 伺服器上,使用 affinity mask 選項將第一組 4 個 CPU 指派到執行個體 A,並將第二組 4 個 CPU 指派到執行個體 B。若要設定 32 個以上的處理器,請同時設定 affinity mask 與 affinity64 mask。For example, on an 8-CPU server running two instances of SQL ServerSQL Server (instance A and B), the system administrator could use the affinity mask option to assign the first set of 4 CPUs to instance A and the second set of 4 to instance B. To configure more than 32 processors, set both the affinity mask and the affinity64 mask. affinity mask 的值如下所示:The values for affinity mask are as follows:

  • 在多處理器的電腦中,一個位元組的 affinity mask 最多可涵蓋 8 個 CPU。A one-byte affinity mask covers up to 8 CPUs in a multiprocessor computer.

  • 在多處理器的電腦中,二個位元組的 affinity mask 最多可涵蓋 16 個 CPU。A two-byte affinity mask covers up to 16 CPUs in a multiprocessor computer.

  • 在多處理器的電腦中,三個位元組的 affinity mask 最多可涵蓋 24 個 CPU。A three-byte affinity mask covers up to 24 CPUs in a multiprocessor computer.

  • 在多處理器的電腦中,四個位元組的 affinity mask 最多可涵蓋 32 個 CPU。A four-byte affinity mask covers up to 32 CPUs in a multiprocessor computer.

  • 若要處理 32 個以上的 CPU,請針對前 32 個 CPU 設定四個位元組的相似性遮罩,並針對剩餘的 CPU 設定最多四個位元組的 affinity64 遮罩。To cover more than 32 CPUs, configure a four-byte affinity mask for the first 32 CPUs and up to a four-byte affinity64 mask for the remaining CPUs.

因為設定 SQL ServerSQL Server 處理器相似性是專門的作業,建議您只在必要時才使用。Because setting SQL ServerSQL Server processor affinity is a specialized operation, it is recommended that it be used only when necessary. 在大部分情況下,Microsoft Windows 2000 或 Windows Server 2003 的預設相似性可提供最佳效能。In most cases, the Microsoft Windows 2000 or Windows Server 2003 default affinity provides the best performance. 您也應該在設定相似性遮罩時,考量其他應用程式的 CPU 需求。You should also consider the CPU requirements for other applications when setting the affinity masks. 如需詳細資訊,請參閱您的 Windows 作業系統文件集。For more information, see your Windows operating system documentation.

注意

您可使用「Windows 系統監視器」來檢視和分析個別處理器的使用方式。You can use the Windows System Monitor to view and analyze individual processor usage.

在指定 affinity I/O mask 選項時,您必須與 affinity mask 組態選項連接使用。When specifying the affinity I/O mask option, you must use it in connection with the affinity mask configuration option. 請勿同時在 affinity mask 參數與 affinity I/O mask 選項中啟用相同的 CPU。Do not enable the same CPU in both the affinity mask switch and the affinity I/O mask option. 對應到每個 CPU 的位元組狀態應為下列三個的其中一個:The bits corresponding to each CPU should be in one of these three states:

  • 在 affinity mask 選項與 affinity I/O mask 選項中皆為 0。0 in both the affinity mask option and the affinity I/O mask option.

  • 在 affinity mask 選項中為 1,而在 affinity I/O mask 選項中為 0。1 in the affinity mask option and 0 in the affinity I/O mask option.

  • 在 affinity mask 選項中為 0,在 affinity I/O mask 選項中為 1。0 in the affinity mask option and 1 in the affinity I/O mask option.

警告

不要在 Windows 作業系統中設定 CPU 相似性,然後又在 SQL ServerSQL Server中設定相似性遮罩。Do not configure CPU affinity in the Windows operating system and also configure the affinity mask in SQL ServerSQL Server. 這些設定嘗試達到相同的結果,如果組態不一致,可能會有無法預期的結果。These settings are attempting to achieve the same result, and if the configurations are inconsistent, you may have unpredictable results. SQL ServerSQL Server 設定 CPU 相似性時,最好使用 SQL ServerSQL Server中的 sp_configure 選項。CPU affinity is best configured using the sp_configure option in SQL ServerSQL Server.

範例Example

以設定 affinity mask 選項為例,如果選取處理器 1、2 與 5 為可用,並將位元 1、2、5 設成 1,位元 0、3、4、6 與 7 設成 0,就會指定十六進位值 0x26 (或相當的十進位值 38 )。As an example of setting the affinity mask option, if processors 1, 2, and 5 are selected as available with bits 1, 2, and 5 set to 1 and bits 0, 3, 4, 6, and 7 set to 0, a hexadecimal value of 0x26 or the decimal equivalent of 38 is specified. 位元的編號從右算起。Number the bits from right to left. affinity mask 選項會從 0 到 31 開始計算處理器,所以在下列範例中,計數器 1 代表伺服器上的第二個處理器。The affinity mask option starts counting processors from 0 to 31, so that in the following example the counter 1 represents the second processor on the server.

sp_configure 'show advanced options', 1;  
RECONFIGURE;  
GO  
sp_configure 'affinity mask', 38;  
RECONFIGURE;  
GO  

以下為 8-CPU 系統的 affinity mask 值。These are affinity mask values for an 8-CPU system.

十進位值Decimal value 二進位位元遮罩Binary bit mask 允許 SQL Server 執行緒的處理器數目Allow SQL Server threads on processors
11 0000000100000001 00
33 0000001100000011 0 與 10 and 1
77 0000011100000111 0、1 與 20, 1, and 2
1515 0000111100001111 0、1、2 與 30, 1, 2, and 3
3131 0001111100011111 0、1、2、3 與 40, 1, 2, 3, and 4
6363 0011111100111111 0、1、2、3、4 與 50, 1, 2, 3, 4, and 5
127127 0111111101111111 0、1、2、3、4、5 與 60, 1, 2, 3, 4, 5, and 6
255255 1111111111111111 0、1、2、3、4、5、6 與 70, 1, 2, 3, 4, 5, 6, and 7

affinity mask 屬於進階選項。The affinity mask option is an advanced option. 若使用 sp_configure 系統預存程序來變更設定,只有當 show advanced options 設為 1 時,才能變更 affinity maskIf you are using the sp_configure system stored procedure to change the setting, you can change affinity mask only when show advanced options is set to 1. 在執行 Transact-SQLTransact-SQL RECONFIGURE 命令之後,新的設定會立即生效,而不需要重新啟動 SQL ServerSQL Server 執行個體。After executing the Transact-SQLTransact-SQL RECONFIGURE command, the new setting takes effect immediately without requiring a restart of the SQL ServerSQL Server instance.

非統一記憶體存取 (NUMA)Non-uniform Memory Access (NUMA)

當使用以非統一記憶體存取 (NUMA) 為基礎的硬體且設定相似性遮罩時,節點中的每一個排程器將相似於它自己的 CPU。When using hardware based non-uniform memory access (NUMA) and the affinity mask is set, every scheduler in a node will be affinitized to its own CPU. 若未設定相似性遮罩,則每一個排程器會相似於 NUMA 節點內的 CPU 群組,且對應到 NUMA 節點 N1 的排程器可在該節點的任何 CPU 上設定工作排程,但不能在與另一個節點相關聯的 CPU 上設定。When the affinity mask is not set, each scheduler is affinitized to the group of CPUs within the NUMA node and a scheduler mapped to NUMA node N1 can schedule work on any CPU in the node, but not on CPUs associated with another node.

在單一 NUMA 節點上執行的任何作業只能使用該節點的緩衝區頁面。Any operation running on a single NUMA node can only use buffer pages from that node. 若作業平行執行於多個節點的 CPU 上,則可使用任何相關節點的記憶體。When an operation is run in parallel on CPUs from multiple nodes, memory can be used from any node involved.

授權問題Licensing Issues

動態相似性受到 CPU 授權的嚴格控制,Dynamic affinity is tightly constrained by CPU licensing. SQL ServerSQL Server 並不允許違反授權原則的任何相似性遮罩選項組態。does not allow any configuration of affinity mask options that violates the licensing policy.

啟動Startup

如果指定的相似性遮罩在 SQL ServerSQL Server 啟動期間或資料庫附加期間違反授權原則,則引擎層會完成啟動程序或資料庫附加/還原作業,然後將相似性遮罩的 sp_configure 執行值重設為零,發出錯誤訊息至 SQL ServerSQL Server 錯誤記錄檔。If a specified affinity mask violates the licensing policy during SQL ServerSQL Server startup or during database attach, the engine layer will complete the startup process or database attach/restore operation, and then it will reset the sp_configure run value for the affinity mask to zero, issuing an error message to the SQL ServerSQL Server error log.

重新設定Reconfigure

如果指定的相似性遮罩在執行 Transact-SQLTransact-SQL RECONFIGURE 命令期間違反授權原則,就會將錯誤訊息回報至用戶端工作階段和 SQL ServerSQL Server 錯誤記錄檔,並要求資料庫管理員重新設定相似性遮罩。If a specified affinity mask violates the licensing policy when running Transact-SQLTransact-SQL RECONFIGURE command, an error message is reported to the client session and to the SQL ServerSQL Server error log, requiring the database administrator to reconfigure the affinity mask. 在此情況下,不會接受任何 RECONFIGURE WITH OVERRIDE 命令。No RECONFIGURE WITH OVERRIDE command is accepted in this case.

另請參閱See Also

監視資源使用量 (系統監視器) Monitor Resource Usage (System Monitor)
RECONFIGURE (Transact-SQL) RECONFIGURE (Transact-SQL)
伺服器組態選項 (SQL Server) Server Configuration Options (SQL Server)
sp_configure (Transact-SQL) sp_configure (Transact-SQL)
ALTER SERVER CONFIGURATION (Transact-SQL)ALTER SERVER CONFIGURATION (Transact-SQL)