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

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

作業系統工作排程Operating system task scheduling

執行緒是作業系統可以執行的最小處理單位,可讓應用程式邏輯分成數個同時執行路徑。Threads are the smallest units of processing that can be executed by an operating system, and allow the application logic to be separated into several concurrent execution paths. 當複雜應用程式有許多可同時執行的工作時,執行緒就很實用。Threads are 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, that set of instructions is handled as a single task, and there is just one execution path (or thread) through the application. 較為複雜的應用程式可能會有多個能同時 (而非依序) 執行的工作More complex applications may have several tasks that can be performed concurrently instead of serially. 若要這樣做,應用程式可以為每個工作啟動個別處理序 (不過這是資源密集作業),或啟動個別執行緒 (相較之下較不資源密集)。An application can do this by starting separate processes for each task, which is a resource-intensive operation, or start separate threads, which 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 processor (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 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 task scheduling

SQL ServerSQL Server 的範圍中,要求是查詢或批次的邏輯表示法。In the scope of SQL ServerSQL Server, a request is the logical representation of a query or batch. 要求也代表系統執行緒要求的作業,例如檢查點或記錄寫入器。A request also represents operations required by system threads, such as checkpoint or log writer. 要求在其整個生命週期中以各種狀態存在,而且當執行要求所需的資源無法使用 (例如鎖定閂鎖) 時可累積等候。Requests exist in various states throughout their lifetime and can accumulate waits when resources required to execute the request are not available, such as locks or latches. 如需有關要求狀態的詳細資訊,請參閱 sys.dm_exec_requestsFor more information about request states, see sys.dm_exec_requests.

工作代表必須完成以滿足要求的工作單位。A task represents the unit of work that needs to be completed to fulfill the request. 您可以將一或多個工作指派給單一要求。One or more tasks can be assigned to a single request. 平行要求將有數個同時 (而非依序) 執行的作用中工作。Parallel requests will have several active tasks that are executed concurrently instead of serially. 依序執行的要求在任何給定的時間點都只會有一個作用中工作。A request that executes serially will only have one active task at any given point in time. 工作在其整個生命週期中以各種狀態存在。Tasks exist in various states throughout their lifetime. 如需有關工作狀態的詳細資訊,請參閱 sys.dm_os_tasksFor more information about task states, see sys.dm_os_tasks. 處於「暫停」狀態的工作正在等候執行工作所需資源成為可用。Tasks in SUSPENDED state are waiting on resources required to execute the task to become available. 如需有關等候中工作的詳細資訊,請參閱 sys.dm_os_waiting_tasksFor more information about waiting task, see sys.dm_os_waiting_tasks.

SQL ServerSQL Server 背景工作執行緒 (也稱為背景工作角色或執行緒) 是作業系統執行緒的邏輯表示法。A SQL ServerSQL Server worker thread, also known as worker or thread, is a logical representation of an operating system thread. 當執行序列要求時,SQL Server Database EngineSQL Server Database Engine 將會繁衍背景工作角色以執行作用中工作。When executing serial requests, the SQL Server Database EngineSQL Server Database Engine will spawn a worker to execute the active task. 當以資料列模式執行平行要求時,SQL Server Database EngineSQL Server Database Engine 會指派背景工作角色以協調負責完成指派給它們的工作的子背景工作角色。When executing parallel requests in row mode, the SQL Server Database EngineSQL Server Database Engine assigns a worker to coordinate the child workers responsible for completing tasks assigned to them. 為每個工作繁衍的背景工作執行緒數目取決於:The number of worker threads spawned for each task depends on:

  • 要求是否符合平行處理原則的資格 (由查詢最佳化工具判斷)。Whether the request was eligible for parallelism as determined by the Query Optimizer.
  • 根據目前的工作負載,實際可用平行處理原則 (DOP) 為何。What is the actual available degree of parallelism (DOP) in the system based on current load. 這可能會與預估 DOP 有所差異,後者是以平行處理原則的最大程度 (MAXDOP) 伺服器組態為基礎的。This may differ from estimated DOP which is based on the server configuration for max degree of parallelism (MAXDOP). 例如,MAXDOP 伺服器組態選項可能是 8,執行階段的可用 DOP 只能是 2,這會影響查詢效能。For example, the server configuration for MAXDOP may be 8 but the available DOP at runtime can be only 2, which affects query performance.

注意

平行處理原則的最大程度 (MAXDOP) 限制是以個別工作 (而非個別要求) 為基礎所設定的。The max degree of parallelism (MAXDOP) limit is set per task, not per request. 這表示在平行查詢執行期間,單一要求可以繁衍多個工作,而且每個工作都可以使用多個背景工作角色,最多為 MAXDOP 限制。This means that during a parallel query execution, a single request can spawn multiple tasks, and each task can use multiple workers up to the MAXDOP limit. 如需有關 MAXDOP 的詳細資訊,請參閱 設定 max degree of parallelism 伺服器組態選項For more information about MAXDOP, see Configure the max degree of parallelism Server Configuration Option.

排程器 (亦稱為 SOS 排程器) 會管理需要處理時間來代表工作 (Task) 執行工作 (Work) 的背景工作執行緒。A scheduler, also known as SOS scheduler, manages worker threads that require processing time to carry out work on behalf of tasks. 每個排程器都對應到個別處理器 (CPU)。Each scheduler is mapped to an individual processor (CPU). 背景工作角色可在排程器中維持作用中的時間稱為 OS 配量,其最大值為 4 毫秒。The time a worker can remain active in a scheduler is called the OS quantum, with a maximum of 4 ms. 在經過此配量時間之後,背景工作角色會將其時間分配給需要存取 CPU 資源的背景工作角色,並變更其狀態。After its quantum time expires, a worker yields its time to other workers that need to access CPU resources, and changes its state. 這個背景工作角色之間的合作以最大化 CPU 資源存取的機制稱為合作式排程,亦稱為非先佔式排程。This cooperation between workers to maximize access to CPU resources is called cooperative scheduling, also known as non-preemptive scheduling. 接著,背景工作角色中的變更會傳播到與該背景工作角色關聯的工作,以及傳播到與該工作關聯的要求。In turn, the change in worker state is propagated to the task associated with that worker, and to the request associated with the task. 如需有關背景工作角色狀態的詳細資訊,請參閱 sys.dm_os_workersFor more information about worker states, see sys.dm_os_workers. 如需有關排程器的詳細資訊,請參閱 sys.dm_os_schedulersFor more information about schedulers, see sys.dm_os_schedulers .

配置執行緒給 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 processors (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 CPUs.

為了執行多工作業 (例如當多個應用程式存取一組相同的 CPU 時),作業系統有時會在不同的 CPU 之間移動背景工作執行緒。To carry out multitasking, for example when multiple applications access the same set of CPUs, the operating system sometimes moves worker threads among different CPUs. 雖然從作業系統的觀點來看很有效率,但是在繁重的系統負載下,這項活動可能會降低 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. 在這些情況中,將特定執行緒指定給 CPU,可降低處理器重新載入的情形並減少跨 CPU 移轉執行緒的問題 (藉此減少內容切換),進而提升效能,而執行緒與處理器之間的關聯則稱為處理器同質性。Assigning CPUs to specific threads can improve performance under these conditions by eliminating processor reloads and reducing thread migration across CPUs (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 CONFIGURATIONSET PROCESS AFFINITY 選項。Use the 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) 伺服器組態選項,請考慮使用 MAXDOP 選項 調整索引作業的平行處理原則。In addition to adjusting the max degree of parallelism (MAXDOP) server configuration option, consider adjusting the parallelism for index operations using the MAXDOP option. 如需詳細資訊,請參閱 設定平行索引作業For more information, see Configure Parallel Index Operations. 如需有關調整平行處理原則的最大程度伺服器組態選項的詳細資訊,請參閱設定 [平行處理原則的最大程度] 伺服器組態選項For more information and guidelines about adjusting the max degree of parallelism server configuration option, see Configure the max degree of parallelism Server Configuration Option.

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

SQL ServerSQL Server 將會在啟動時動態設定最大工作者執行緒伺服器組態選項。will dynamically configure the max worker threads server configuration option at startup. SQL ServerSQL Server 在啟動時使用可用 CPU 數目與系統架構 (使用記載的公式) 來判斷此伺服器組態。uses the number of available CPUs and the system architecture to determine this server configuration during startup, using a documented formula.

此選項是進階選項,只有具經驗的資料庫管理員或通過認證的 SQL Server 專業人員才可變更。This option is an advanced option and should be changed only by an experienced database administrator or certified SQL Server professional. 如果您懷疑發生效能問題,很可能並非是否能使用背景工作執行緒所致。If you suspect that there is a performance problem, it is probably not the availability of worker threads. 原因較有可能是因為 I/O 等作業導致背景工作執行緒處於等待狀態。The cause is more likely something like I/O that is causing the worker threads to wait. 建議您在變更背景工作執行緒設定的上限前,先找出效能問題的根本原因。It is best to find the root cause of a performance issue before you change the max worker threads setting. 不過,如果您需要手動設定背景工作執行緒數目上限,則此設定值必須一律至少設定為系統上出現的 CPU 數目的七倍。However, if you need to manually set the maximum number of worker threads, this configuration value must always be set to a value of at least seven times the number of CPUs that are present on the system. 如需詳細資訊,請參閱 設定最大工作者執行緒For more information, see Configure the max worker threads.

使用 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.

重要

SQL 追蹤和 SQL Server ProfilerSQL Server Profiler 已被淘汰。SQL Trace and SQL Server ProfilerSQL Server Profiler are deprecated. 包含 Microsoft SQL Server 追蹤和重新執行物件的 Microsoft.SqlServer.Management.Trace 命名空間也會被淘汰。The Microsoft.SqlServer.Management.Trace namespace that contains the Microsoft SQL Server Trace and Replay objects are also deprecated.

這項功能處於維護模式,並可能在 Microsoft SQL Server 的未來版本中移除。This feature is in maintenance mode and may be removed in a future version of Microsoft SQL Server. 請避免在新的開發工作中使用這項功能,並規劃修改目前使用這項功能的應用程式。Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

請改用擴充事件。Use Extended Events instead. 如需延伸事件的詳細資訊,請參閱快速入門:SQL Server 中的延伸事件SSMS XEvent 分析工具For more information on Extended Events, see Quick Start: Extended events in SQL Server and SSMS XEvent Profiler.

注意

適用於 Analysis Services 工作負載的 SQL Server ProfilerSQL Server Profiler「未」遭淘汰,而且將會繼續受支援。SQL Server ProfilerSQL Server Profiler for Analysis Services workloads is NOT deprecated, and will continue to be supported.

設定 TempDB 資料檔案的數目Setting the number of TempDB data files

檔案數目取決於電腦上 (邏輯) 處理器的數目。The number of files depends on the number of (logical) processors on the machine. 一般而言,如果邏輯處理器的數目小於或等於 8,請使用與邏輯處理器數目相同的資料檔案數目。As a general rule, if the number of logical processors is less than or equal to eight, use the same number of data files as logical processors. 如果邏輯處理器的數目大於 8,請使用 8 個資料檔案,要是競爭的情況仍持續發生,請以 4 的倍數增加資料檔案數目,直到競爭縮減到可接受的程度;或是對工作負載/程式碼進行變更。If the number of logical processors is greater than eight, use eight data files and then if contention continues, increase the number of data files by multiples of 4 until the contention is reduced to acceptable levels or make changes to the workload/code. 也請記住有關 TempDB 的其他建議,它位於將 SQL Server 中的 TempDB 效能最佳化Also keep in mind other recommendations for TempDB, available in Optimizing TempDB performance in SQL Server.

不過,只要仔細地考量 tempdb 的並行需求,您就可以減少資料庫管理作業額外負荷。However, by carefully considering the concurrency needs of tempdb, you can reduce database management overhead. 例如,如果系統具有 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