執行緒和工作架構指南Thread and Task Architecture Guide

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

執行緒是一種作業系統功能,可讓應用程式邏輯分成數個並行的執行路徑。Threads are an operating system feature that lets application logic be separated into several concurrent execution paths. 當複雜的應用程式有許多可同時執行的工作時,此功能就很有用處。This feature is useful when complex applications have many tasks that can be performed at the same time.

作業系統執行應用程式的執行個體時,會建立一個稱為處理序的單位來管理這個執行個體。When an operating system executes an instance of an application, it creates a unit called a process to manage the instance. 處理序有執行緒。The process has a thread of execution. 這是應用程式的程式碼所執行的程式化指令序列。This is the series of programming instructions performed by the application code. 例如,如果簡易應用程式中有單一的一組可序列執行的指令,那麼應用程式將僅有一個執行路徑或一個執行緒。For example, if a simple application has a single set of instructions that can be performed serially, there is just one execution path or thread through the application. 較為複雜的應用程式可能會有多個工作,但這些工作可能需要一前一後執行,而不是以序列的方式執行。More complex applications may have several tasks that can be performed in tandem, instead of serially. 應用程式可針對每一項工作啟動個別的處理序來達成此目的。The application can do this by starting separate processes for each task. 不過,啟動處理序是很耗費資源的作業。However, starting a process is a resource-intensive operation. 反之,應用程式可啟動個別的執行緒,Instead, an application can start separate threads. 這些就比較節省資源。These are relatively less resource-intensive. 另外,每個執行緒跟那些與處理序相關聯的其他執行緒,可分開排程執行。Additionally, each thread can be scheduled for execution independently from the other threads associated with a process.

執行緒能讓複雜的應用程式,以更有效率的方式來使用 CPU,即使電腦只有一個 CPU。Threads allow complex applications to make more effective use of a CPU, even on computers that have a single CPU. 只有一個 CPU 時,一次只能執行一個執行緒。With one CPU, only one thread can execute at a time. 如果有一個執行緒執行不需使用 CPU 的長時間作業,像是磁碟讀寫,另一個執行緒便可以一直執行,直到第一個作業完成為止。If one thread executes a long-running operation that does not use the CPU, such as a disk read or write, another one of the threads can execute until the first operation is completed. 由於應用程式可在其他執行緒等待作業完成時執行一些執行緒,所以使 CPU 發揮最大功效。By being able to execute threads while other threads are waiting for an operation to be completed, an application can maximize its use of the CPU. 特別是多使用者、需要大量磁碟 I/O 的應用程式 (例如資料庫伺服器) 更是如此。This is especially true for multi-user, disk I/O intensive applications such as a database server. 有多個微處理器或 CPU 的電腦,可同時讓每個 CPU 執行一個執行緒。Computers that have multiple microprocessors or CPUs can execute one thread per CPU at the same time. 例如,如果一部電腦有 8 個 CPU,就可以同時執行 8 個執行緒。For example, if a computer has eight CPUs, it can execute eight threads at the same time.

SQL Server 批次或工作排程SQL Server Batch or Task Scheduling

配置執行緒給 CPUAllocating Threads to a CPU

根據預設,SQL ServerSQL Server 的每個執行個體會啟動各個執行緒,且作業系統會根據負載從 SQL ServerSQL Server 的執行個體,將執行緒散發給電腦上的 CPU。By default, each instance of SQL ServerSQL Server starts each thread, and the operating system distributes threads from instances of SQL ServerSQL Server among the CPUs on a computer based on load. 如果已在作業系統層級啟用處理序親和性,則作業系統就會將每個執行緒指派給特定的 CPU。If process affinity has been enabled at the operating system level, then the operating system assigns each thread to a specific CPU. 相反地,SQL Server Database EngineSQL Server Database Engine會將 SQL ServerSQL Server 背景工作執行緒指派給排程器,以便將這些執行緒平均散發給處理器 (CPU)。In contrast, the SQL Server Database EngineSQL Server Database Engine assigns SQL ServerSQL Server worker threads to schedulers that distribute the threads evenly among the processors (CPUs).

為了執行多工作業 (例如當多個應用程式存取一組相同的處理器時),作業系統有時會在不同的處理器之間移動處理序執行緒。To carry out multitasking, for example when multiple applications access the same set of processors, the operating system sometimes moves 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. 如果已經啟用相似性,作業系統就會將每個執行緒指派給特定的 CPU。If affinity has been enabled, the operating system assigns each thread to a specific CPU.

親和性遮罩選項是使用 ALTER SERVER CONFIGURATION 設定的。The affinity mask option is set by using ALTER SERVER CONFIGURATION. 沒有設定親和性遮罩時,SQL ServerSQL Server 執行個體就會將背景工作執行緒平均配置給尚未遮罩的排程器。When the affinity mask is not set, the instance of SQL ServerSQL Server allocates worker threads evenly among the schedulers that have not been masked off.

警告

不要在作業系統中設定 CPU 親和性,然後又在 SQL ServerSQL Server 中設定親和性遮罩。Do not configure CPU affinity in the 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. 如需詳細資訊,請參閱親和性遮罩選項For more information, see affinity mask option.

當大量用戶端連接到伺服器時,執行緒集區有助於最佳化效能。Thread pooling helps optimize performance when large numbers of clients are connected to the server. 通常,會針對每一個查詢要求建立個別的作業系統執行緒。Usually, a separate operating system thread is created for each query request. 然而,在數以百計的伺服器連接之下,若每個查詢要求都使用一個執行緒,反而會耗用大量的系統資源。However, with hundreds of connections to the server, using one thread per query request can consume large amounts of system resources. 最大背景工作執行緒選項可讓 SQL ServerSQL Server 建立背景工作執行緒集區,以服務更多的查詢要求數量,進而改善效能。The max worker threads option enables SQL ServerSQL Server to create a pool of worker threads to service a larger number of query requests, which improves performance.

使用 lightweight pooling 選項Using the lightweight pooling Option

切換執行緒內容所需的額外負荷可能不會太大。The overhead involved in switching thread contexts may not be very large. 大部分的 SQL ServerSQL Server 執行個體,在將輕量型共用選項設為 0 或 1 時並不會察覺到任何效能上的差異。Most instances of SQL ServerSQL Server will not see any performance differences between setting the lightweight pooling option to 0 or 1. 只有擁有下列特性的電腦上所執行 SQL ServerSQL Server 執行個體,才有可能感受到輕量型共用的好處:The only instances of SQL ServerSQL Server that might benefit from lightweight pooling are those that run on a computer having the following characteristics:

  • 具有多個 CPU 的大型伺服器A large multi-CPU server
  • 所有 CPU 皆以接近最大容量在執行All the CPUs are running near maximum capacity
  • 高層次的內容切換There is a high level of context switching

這些系統在將輕量型共用值設定為 1 時,可能會發現效能有稍微增加。These systems may see a small increase in performance if the lightweight pooling value is set to 1.

重要

請勿針對例行作業使用 Fiber 模式排程。Do not use fiber mode scheduling for routine operation. 這可能會抑制一般內容切換的好處而降低效能,且 SQL ServerSQL Server 的某些元件無法在 Fiber 模式中正確運作。This can decrease performance by inhibiting the regular benefits of context switching, and because some components of SQL ServerSQL Server cannot function correctly in fiber mode. 如需詳細資訊,請參閱輕量型共用For more information, see lightweight pooling.

執行緒和 Fiber 執行Thread and Fiber Execution

Microsoft Windows 使用數值優先權系統,從 1 到 31 的範圍來排程要執行的執行緒。Microsoft Windows uses a numeric priority system that ranges from 1 through 31 to schedule threads for execution. 零是保留給作業系統使用的。Zero is reserved for operating system use. 當有數個執行緒等待執行時,Windows 會先分派有最高優先順序的執行緒。When several threads are waiting to execute, Windows dispatches the thread with the highest priority.

根據預設,每個 SQL ServerSQL Server 執行個體的優先權為 7,這稱為一般優先權。By default, each instance of SQL ServerSQL Server is a priority of 7, which is referred to as the normal priority. 這讓 SQL ServerSQL Server 執行緒有夠高的優先權,可以取得足夠的 CPU 資源,而不會影響其他的應用程式。This default gives SQL ServerSQL Server threads a high enough priority to obtain sufficient CPU resources without adversely affecting other applications.

使用優先權提升設定選項,可將 SQL ServerSQL Server 執行個體中執行緒的優先權增加至 13。The priority boost configuration option can be used to increase the priority of the threads from an instance of SQL ServerSQL Server to 13. 這稱為高優先權。This is referred to as high priority. 這個設定讓 SQL ServerSQL Server 執行緒擁有比大部份其他應用程式更高的優先權。This setting gives SQL ServerSQL Server threads a higher priority than most other applications. 因此,每當 SQL ServerSQL Server 執行緒可以執行時,通常系統會先分派執行緒,而且其他應用程式不會預先清空執行緒。Thus, SQL ServerSQL Server threads will generally be dispatched whenever they are ready to run and will not be pre-empted by threads from other applications. 當伺服器僅執行 SQL ServerSQL Server 的執行個體、而沒有執行其他應用程式時,可以改善系統的效能。This can improve performance when a server is running only instances of SQL ServerSQL Server and no other applications. 然而,如果 SQL ServerSQL Server 中發生需要大量記憶體的作業,則其他應用程式可能無法擁有夠高的優先權,以預先清空 SQL ServerSQL Server 執行緒。However, if a memory-intensive operation occurs in SQL ServerSQL Server, however, other applications are not likely to have a high-enough priority to pre-empt the SQL ServerSQL Server thread.

如果您在電腦上執行多個 SQL ServerSQL Server 執行個體,並且僅提高部份執行個體的優先權,則以一般優先權執行的所有執行個體之效能都將受到影響。If you are running multiple instances of SQL ServerSQL Server on a computer, and turn on priority boost for only some of the instances, the performance of any instances running at normal priority can be adversely affected. 另外,如果有開啟優先權提升,就可能會降低伺服器上其他應用程式與元件的效能。Also, the performance of other applications and components on the server can decline if priority boost is turned on. 因此,它應該在嚴格控制的情況下使用。Therefore, it should only be used under tightly controlled conditions.

熱新增 CPUHot Add CPU

熱新增 CPU 是指將 CPU 動態新增到執行中系統的功能。Hot add CPU is the ability to dynamically add CPUs to a running system. 新增 CPU 可發生於實體上新增硬體、邏輯上進行線上硬體分割或是虛擬上透過虛擬化層時。Adding CPUs can occur physically by adding new hardware, logically by online hardware partitioning, or virtually through a virtualization layer. SQL Server 2008SQL Server 2008 開始,SQL ServerSQL Server 便可支援熱新增 CPU。Starting with SQL Server 2008SQL Server 2008, SQL ServerSQL Server supports hot add CPU.

熱新增 CPU 的需求:Requirements for hot add CPU:

  • 需要有支援熱新增 CPU 的硬體。Requires hardware that supports hot add CPU.
  • 需要 64 位元版本的 Windows Server 2008 Datacenter 或適用於 Itanium 系統之作業系統的 Windows Server 2008 Enterprise Edition。Requires the 64-bit edition of Windows Server 2008 Datacenter or the Windows Server 2008 Enterprise Edition for Itanium-Based Systems operating system.
  • 需要 SQL ServerSQL Server Enterprise。Requires SQL ServerSQL Server Enterprise.
  • SQL ServerSQL Server 無法設定為使用軟體 NUMA。cannot be configured to use soft NUMA. 如需有關軟體 NUMA 的詳細資訊,請參閱 軟體 NUMA (SQL Server)For more information about soft NUMA, see Soft-NUMA (SQL Server).

SQL ServerSQL Server 不會在新增 CPU 之後自動開始使用這些 CPU。does not automatically start to use CPUs after they are added. 這樣可避免 SQL ServerSQL Server 使用可能要供其他用途使用所新增的 CPU。This prevents SQL ServerSQL Server from using CPUs that might be added for some other purpose. 在新增 CPU 之後,請執行 RECONFIGURE 陳述式,以讓 SQL ServerSQL Server 將新的 CPU 辨識為可用資源。After adding CPUs, execute the RECONFIGURE statement, so that SQL ServerSQL Server will recognize the new CPUs as available resources.

注意

如果設定了 affinity64 mask ,則必須修改 affinity64 mask 才能使用新的 CPU。If the affinity64 mask is configured, the affinity64 mask must be modified to use the new CPUs.

在超過 64 個 CPU 之電腦上執行 SQL Server 的最佳做法Best Practices for Running SQL Server on Computers That Have More Than 64 CPUs

使用 CPU 指派硬體執行緒Assigning Hardware Threads with CPUs

請勿使用 affinity mask 和 affinity64 mask 伺服器組態選項,將處理器繫結至特定執行緒。Do not use the affinity mask and affinity64 mask server configuration options to bind processors to specific threads. 這些選項僅限於 64 個 CPU。These options are limited to 64 CPUs. 請改用 ALTER SERVER CONFIGURATION 的 SET PROCESS AFFINITY 選項。Use SET PROCESS AFFINITY option of ALTER SERVER CONFIGURATION instead.

管理交易記錄檔大小Managing the Transaction Log File Size

請勿仰賴自動成長來增加交易記錄檔的大小。Do not rely on autogrow to increase the size of the transaction log file. 增加交易記錄必須是序列處理序。Increasing the transaction log must be a serial process. 擴充記錄可避免在記錄擴充完成之前繼續進行交易寫入作業。Extending the log can prevent transaction write operations from proceeding until the log extension is finished. 不過,您可以將檔案大小設定為夠大的值來支援環境中的典型工作負載,藉此為所有記錄檔預先配置空間。Instead, preallocate space for the log files by setting the file size to a value large enough to support the typical workload in the environment.

針對索引作業設定平行處理原則的最大程度Setting Max Degree of Parallelism for Index Operations

在具有許多 CPU 的電腦上,您可以暫時將資料庫的復原模式設定為大量記錄或簡單復原模式,藉此改善建立或重建索引等索引作業的效能。The performance of index operations such as creating or rebuilding indexes can be improved on computers that have many CPUs by temporarily setting the recovery model of the database to either the bulk-logged or simple recovery model. 這些索引作業可能會產生重要的記錄活動,而且記錄競爭可能會影響 SQL ServerSQL Server 所選擇之平行處理原則的最佳程度。These index operations can generate significant log activity and log contention can affect the best degree of parallelism (DOP) choice made by SQL ServerSQL Server.

此外,請考慮針對這些作業調整平行處理原則的最大程度 (MAXDOP) 伺服器組態選項。In addition, consider adjusting the max degree of parallelism (MAXDOP) server configuration option for these operations. 下列指導方針是以內部測試為基礎,而且屬於一般建議。The following guidelines are based on internal tests and are general recommendations. 您應該嘗試多種不同的 MAXDOP 設定,以便決定適合您環境的最佳設定。You should try several different MAXDOP settings to determine the optimal setting for your environment.

  • 若為完整復原模式,請將「平行處理原則的最大程度」選項的值限制為 8 或更小的值。For the full recovery model, limit the value of the max degree of parallelism option to eight or less.
  • 若為大量記錄模式或簡單復原模式,您就應該考慮將「平行處理原則的最大程度」選項的值設定為高於 8 的值。For the bulk-logged model or the simple recovery model, setting the value of the max degree of parallelism option to a value higher than eight should be considered.
  • 若為已設定 NUMA 的伺服器,平行處理原則的最大程度就不應該超過指派給每個 NUMA 節點的 CPU 數目。For servers that have NUMA configured, the maximum degree of parallelism should not exceed the number of CPUs that are assigned to each NUMA node. 這是因為查詢很可能會使用來自 1 個 NUMA 節點的本機記憶體,以便改善記憶體存取時間。This is because the query is more likely to use local memory from 1 NUMA node, which can improve memory access time.
  • 若為已啟用超執行緒且為 2009 年或之前 (在改善超執行緒功能之前) 製造的伺服器,MAXDOP 值就不應該超過實體處理器的數目,而不是邏輯處理器的數目。For servers that have hyper-threading enabled and were manufactured in 2009 or earlier (before hyper-threading feature was improved), the MAXDOP value should not exceed the number of physical processors, rather than logical processors.

如需平行處理原則最大程度選項的詳細資訊,請參閱設定 [平行處理原則的最大程度] 伺服器組態選項For more information about the max degree of parallelism option, see Configure the max degree of parallelism Server Configuration Option.

設定工作者執行緒的數目上限Setting the Maximum Number of Worker Threads

請一律將工作者執行緒的數目上限設定為大於平行處理原則的最大程度的設定。Always set the maximum number of worker threads to be more than the setting for the maximum degree of parallelism. 工作者執行緒的數目一律至少必須設定為目前存在伺服器上之 CPU 數目的七倍。The number of worker threads must always be set to a value of at least seven times the number of CPUs that are present on the server. 如需詳細資訊,請參閱 設定最大工作者執行緒選項For more information, see Configure the max worker threads Option.

使用 SQL 追蹤和 SQL Server ProfilerUsing SQL Trace and SQL Server Profiler

建議您不要在生產環境中使用 SQL 追蹤和 SQL Profiler。We recommend that you do not use SQL Trace and SQL Profiler in a production environment. 執行這些工具的負擔也會隨著 CPU 數目增加而提高。The overhead for running these tools also increases as the number of CPUs increases. 如果您必須在實際執行環境中使用 SQL 追蹤,請將追蹤事件的數目限制為最小值。If you must use SQL Trace in a production environment, limit the number of trace events to a minimum. 請仔細地分析和測試低於負載的每個追蹤事件,並且避免使用大幅影響效能的事件組合。Carefully profile and test each trace event under load, and avoid using combinations of events that significantly affect performance.

設定 tempdb 資料檔案的數目Setting the Number of tempdb Data Files

通常,tempdb 資料檔案的數目應該與 CPU 數目相符。Typically, the number of tempdb data files should match the number of CPUs. 不過,只要仔細地考量 tempdb 的並行需求,您就可以減少資料庫管理作業。However, by carefully considering the concurrency needs of tempdb, you can reduce database management. 例如,如果系統具有 64 個 CPU 而且通常只有 32 個查詢使用 tempdb,則將 tempdb 檔案的數目增加至 64 並不會改善效能。For example, if a system has 64 CPUs and usually only 32 queries use tempdb, increasing the number of tempdb files to 64 will not improve performance.

可以使用超過 64 個 CPU 的 SQL Server 元件SQL Server Components That Can Use More Than 64 CPUs

下表將列出 SQL ServerSQL Server 元件並指出它們是否能使用超過 64 個 CPU。The following table lists SQL ServerSQL Server components and indicates whether they can use more that 64 CPUs.

程序名稱Process name 可執行的程式Executable program 使用超過 64 個 CPUUse more than 64 CPUs
SQL Server Database EngineSQL Server Database Engine Sqlserver.exeSqlserver.exe Yes
Reporting ServicesReporting Services Rs.exeRs.exe No
Analysis ServicesAnalysis Services As.exeAs.exe No
Integration ServicesIntegration Services Is.exeIs.exe No
Service BrokerService Broker Sb.exeSb.exe No
全文檢索搜尋Full-Text Search Fts.exeFts.exe No
SQL Server AgentSQL Server Agent Sqlagent.exeSqlagent.exe No
SQL Server Management StudioSQL Server Management Studio Ssms.exeSsms.exe No
SQL Server 安裝程式SQL Server Setup Setup.exeSetup.exe No