affinity Input-Output mask 伺服器組態選項affinity Input-Output 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

為了執行多工作業, MicrosoftMicrosoft Windows 有時會在不同的處理器之間移動處理序執行緒。To carry out multitasking, MicrosoftMicrosoft Windows sometimes move process threads among different processors. 雖然從作業系統的觀點來看很有效率,但是在繁重的系統負載下,這項活動可能會降低 MicrosoftMicrosoft SQL ServerSQL Server 的效能,因為每個處理器快取會重複重新載入資料。Although efficient from an operating system point of view, this activity can reduce MicrosoftMicrosoft 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; such an association between a thread and a processor is called processor affinity.

SQL ServerSQL Server 透過兩個相似性遮罩選項支援處理器相似性: affinity mask (也稱為 CPU affinity mask) 與 affinity I/O masksupports processor affinity by means of two affinity mask options: affinity mask (also known as CPU affinity mask) and affinity I/O mask. 如需 affinity mask 選項的詳細資訊,請參閱 affinity mask 伺服器組態選項For more information on the affinity mask option, see affinity 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 I/O mask 選項會將 SQL ServerSQL Server 磁碟 I/O 繫結到指定的 CPU 子集。The affinity I/O mask option binds SQL ServerSQL Server disk I/O to a specified subset of CPUs. 在高階的 SQL ServerSQL Server 線上交易處理 (OLTP) 環境中,此延伸模組可強化 SQL ServerSQL Server 執行緒發行 I/O 的效能。In high-end SQL ServerSQL Server online transactional processing (OLTP) environments, this extension can enhance the performance of SQL ServerSQL Server threads issuing I/Os. 這項增強功能並不支援個別磁碟或磁碟控制器的硬體相似性。This enhancement does not support hardware affinity for individual disks or disk controllers.

affinity I/O mask 的值可指定在多處理器的電腦中,有哪些 CPU 適合處理 SQL ServerSQL Server 磁碟 I/O 作業。The value for affinity I/O mask specifies which CPUs in a multiprocessor computer are eligible to process SQL ServerSQL Server disk I/O operations. 遮罩是一種點陣圖,其中最右邊的位元會指定最低順位 CPU(0),緊鄰其左側的位元則指定次低順位 CPU(1),依此類推。The mask is a bitmap in which the rightmost bit specifies the lowest-order CPU(0), the bit to its immediate left specifies the next-lowest-order CPU(1), and so on. 若要設定 32 個以上的處理器,請同時設定 affinity I/O maskaffinity64 I/O maskTo configure more than 32 processors, set both the affinity I/O mask and the affinity64 I/O mask.

affinity I/O mask 的值如下所示:The values for affinity I/O mask are as follows:

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

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

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

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

  • 若要處理 32 個以上的 CPU,請針對前 32 個 CPU 設定一個 4 位元組的 affinity I/O mask ,並針對剩餘的 CPU 設定最多 4 位元組的 affinity64 I/O maskTo cover more than 32 CPUs, configure a four-byte affinity I/O mask for the first 32 CPUs and up to a four-byte affinity64 I/O mask for the remaining CPUs.

相似性 I/O 模式中的 1 位元,會指定對應的 CPU 適合執行 SQL ServerSQL Server 磁碟 I/O 作業;0 位元則指定不要為對應的 CPU 排程任何 SQL ServerSQL Server 磁碟 I/O 作業。A 1 bit in the affinity I/O pattern specifies that the corresponding CPU is eligible to perform SQL ServerSQL Server disk I/O operations; a 0 bit specifies that no SQL ServerSQL Server disk I/O operations should be scheduled for the corresponding CPU. 若所有的位元都設為零,或未指定 affinity I/O maskSQL ServerSQL Server 磁碟 I/O 會排程給所有適合處理 SQL ServerSQL Server 執行緒的 CPU。When all bits are set to zero, or affinity I/O mask is not specified, SQL ServerSQL Server disk I/O is scheduled to any of the CPUs eligible to process SQL ServerSQL Server threads.

因為設定 SQL ServerSQL Server affinity I/O mask 選項是專門的作業,因此只有在必要時才使用。Because setting the SQL ServerSQL Server affinity I/O mask option is a specialized operation, it should be used only when necessary. 在大部分情況下,Windows 2000 或 Windows Server 2003 的預設相似性都能提供最佳效能。In most cases, the Windows 2000 or Windows Server 2003 default affinity provides the best performance.

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

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

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

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

affinity I/O mask 屬於進階選項。The affinity I/O mask option is an advanced option. 若使用 sp_configure 系統預存程序來變更設定,只有當 show advanced options 設為 1 時,才能變更 affinity I/O maskIf you are using the sp_configure system stored procedure to change the setting, you can change affinity I/O mask only when show advanced options is set to 1. SQL ServerSQL Server中,重新設定 affinity I/O mask 選項需要重新啟動 SQL ServerSQL Server 執行個體。In SQL ServerSQL Server, reconfiguring the affinity I/O mask option requires a restart of the SQL ServerSQL Server instance.

警告

不要在 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 相似性時,最好使用 中的 sp_configure SQL ServerSQL Server選項。CPU affinity is best configured using the sp_configure option in SQL ServerSQL Server.

另請參閱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)