DBCC TRACEON - 追蹤旗標 (Transact-SQL)DBCC TRACEON - Trace Flags (Transact-SQL)

本主題適用於: 是SQL Server (從 2012 開始)否Azure SQL Database否Azure SQL 資料倉儲 否平行處理資料倉儲 THIS TOPIC APPLIES TO: yesSQL Server (starting with 2012)noAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

追蹤旗標用來暫時設定特定伺服器性質,或關閉特定行為。Trace flags are used to temporarily set specific server characteristics or to switch off a particular behavior. 例如,如果在啟動 [SQL Server]SQL Server 執行個體時,設定追蹤旗標 3205,就會停用磁帶機的硬體壓縮。For example, if trace flag 3205 is set when an instance of [SQL Server]SQL Server starts, hardware compression for tape drivers is disabled. 追蹤旗標經常用來診斷效能問題,或偵錯預存程序或複雜電腦系統。Trace flags are frequently used to diagnose performance issues or to debug stored procedures or complex computer systems.

下表列出並描述 [SQL Server]SQL Server 中可用的追蹤旗標。The following table lists and describes the trace flags that are available in [SQL Server]SQL Server.

注意

某些追蹤旗標已導入特定的 [SQL Server]SQL Server 版本。Some trace flags were introduced in specific [SQL Server]SQL Server versions. 如需適當版本的詳細資訊,請參閱與特定追蹤旗標相關聯的 Microsoft 支援服務文章。For more information on the applicable version, see the Microsoft Support article associated with a specific trace flag.

重要

在未來的 [SQL Server]SQL Server 版本中,不一定支援追蹤旗標行為。Trace flag behavior may not be supported in future releases of [SQL Server]SQL Server.

追蹤旗標Trace flag DescriptionDescription
139139 在相容性層級較低的資料庫上分析針對特定資料類型導入之相容性層級 130 所改善的精確度和轉換邏輯時,於 DBCC CHECKDBDBCC CHECKTABLEDBCC CHECKCONSTRAINTS 等 DBCC 檢查命令範圍中強制正確的轉換語意。Forces correct conversion semantics in the scope of DBCC check commands like DBCC CHECKDB, DBCC CHECKTABLE and DBCC CHECKCONSTRAINTS, when analyzing the improved precision and conversion logic introduced with compatibility level 130 for specific data types, on a database that has a lower compatibility level. 如需詳細資訊,請參閱此 Microsoft 支援服務文章 (機器翻譯)。For more information, see this Microsoft Support article.

注意: 此追蹤旗標適用於 SQL Server 2016 (13.x)SQL Server 2016 (13.x) RTM CU3、 SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1 和更新版本組建。Note: This trace flag applies to SQL Server 2016 (13.x)SQL Server 2016 (13.x) RTM CU3, SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1 and higher builds.

警告: 追蹤旗標 139 不應在生產環境中持續啟用,其唯一的用途是執行資料庫驗證檢查,如 Microsoft 支援服務文章 (機器翻譯) 所述。WARNING: Trace flag 139 is not meant to be enabled continuously in a production environment, and should be used for the sole purpose of performing database validation checks described in this Microsoft Support article. 在完成驗證檢查後,應立即將它停用。It should be immediately disabled after validation checks are completed.

範圍:只限全域Scope: global only
174174 將 64 位元系統上的 SQL Server Database EngineSQL Server Database Engine 計畫快取貯體計數從 40,009 增加至 160,001。Increases the SQL Server Database EngineSQL Server Database Engine plan cache bucket count from 40,009 to 160,001 on 64-bit systems. 如需詳細資訊,請參閱此 Microsoft 支援服務文章 (機器翻譯)。For more information, see this Microsoft Support article.

注意: 請確定您已徹底測試此選項後再部署到生產環境。Note: Please ensure that you thoroughly test this option, before rolling it into a production environment.

範圍:只限全域Scope: global only
176176 使修正解決當資料表包含計算的分割資料行時,線上重建資料表分割區會發生的錯誤。Enables a fix to address errors when rebuilding partitions online for tables that contain a computed partitioning column. 如需詳細資訊,請參閱此 Microsoft 支援服務文章 (機器翻譯)。For more information, see this Microsoft Support article.

範圍:全域或工作階段Scope: global or session
205205 當統計資料相依預存程序因自動更新統計資料而重新編譯時,回報至錯誤記錄檔。Reports to the error log when a statistics-dependent stored procedure is being recompiled as a result of auto-update statistics. 如需詳細資訊,請參閱此 Microsoft 支援服務文章For more information, see this Microsoft Support article.

範圍:只限全域Scope: global only
260260 列印擴充預存程序動態連結程式庫 (DLL) 的版本控制相關資訊。Prints versioning information about extended stored procedure dynamic-link libraries (DLLs). 如需 GetXpVersion() 的詳細資訊,請參閱建立擴充預存程序For more information about GetXpVersion(), see Creating Extended Stored Procedures.

範圍: 全域或工作階段Scope: global or session
272272 當伺服器意外重新啟動或容錯移轉至次要伺服器時,停用識別預先配置以避免識別欄位的值不連貫。Disables identity pre-allocation to avoid gaps in the values of an identity column in cases where the server restarts unexpectedly or fails over to a secondary server. 請注意,識別快取可用來改善含有識別欄位之資料表上的 INSERT 效能。Note that identity caching is used to improve INSERT performance on tables with identity columns.

注意:SQL Server 2017 (14.x)SQL Server 2017 (14.x) 開始,若要在資料庫層級完成這項作業,請參閱 ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)中的 IDENTITY_CACHE 選項。Note: Starting with SQL Server 2017 (14.x)SQL Server 2017 (14.x), to accomplish this at the database level, see the IDENTITY_CACHE option in ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL).

範圍:只限全域Scope: global only
610610 控制以最低限度方式插入索引資料表的行為。Controls minimally logged inserts into indexed tables. 從 SQL Server 2016 開始不需要此追蹤旗標,因為預設會針對索引資料表開啟最低限度記錄功能。This trace flag is not required starting SQL Server 2016 as minimal logging is turned on by default for indexed tables. 在 SQL Server 2016 中,當大量載入作業導致系統配置新的頁面時,如果符合最低限度記錄的其他所有先決條件,則依序填滿新頁面的所有資料列會以最低限度方式記錄。In SQL Server 2016, when the bulk load operation causes a new page to be allocated, all of the rows sequentially filling that new page are minimally logged if all the other pre-requisites for minimal logging are met. 插入到現有頁面 (未配置新頁面) 以維持索引順序的資料列仍會完整記錄,這些是載入期間因頁面分割而移除的資料列。Rows inserted into existing pages (no new page allocation) to maintain index order are still fully logged, as are rows that are moved as a result of page splits during the load. 此外也必須開啟索引的 ALLOW_PAGE_LOCKS (預設為「開啟」) 以啟用最低限度記錄作業,因為配置期間需要頁面鎖定,也因此只會記錄頁面或範圍配置。如需詳細資訊,請參閱資料載入效能指南 (英文)。It is also important to have ALLOW_PAGE_LOCKS turned ON for indexes (which is ON by default) for minimal logging operation to work as page locks are acquired during allocation and thereby only page or extent allocations are logged.For more information, see Data Loading Performance Guide.

範圍:全域或工作階段Scope: global or session
634634 停用背景資料行存放區壓縮工作。Disables the background columnstore compression task. [SQL Server]SQL Server 會針對含有未壓縮資料的資料行存放區索引資料列群組,定期執行 Tuple Mover 背景工作,一次壓縮一個資料列群組。 periodically runs the Tuple Mover background task that compresses columnstore index rowgroups with uncompressed data, one such rowgroup at a time.

壓縮資料行存放區可提升查詢效能,但同時也會耗用系統資源。Columnstore compression improves query performance but also consumes system resources. 您可以手動控制資料行存放區的壓縮時間,方法是在您選擇的時間,停用追蹤旗標為 634 的背景壓縮工作,然後明確地叫用 ALTER INDEX...REORGANIZE 或 ALTER INDEX...REBUILD。You can control the timing of columnstore compression manually, by disabling the background compression task with trace flag 634, and then explicitly invoking ALTER INDEX...REORGANIZE or ALTER INDEX...REBUILD at the time of your choice.

範圍: 只限全域Scope: global only
652652 停用頁面預先提取掃描。Disables page pre-fetching scans. 如需詳細資訊,請參閱此 Microsoft 支援服務文章 (機器翻譯)。For more information, see this Microsoft Support article.

範圍:全域或工作階段Scope: global or session
661661 停用準刪除記錄移除程序。Disables the ghost record removal process. 如需詳細資訊,請參閱此 Microsoft 支援服務文章 (機器翻譯)。For more information, see this Microsoft Support article.

範圍:只限全域Scope: global only
692692 在大量載入資料到堆積或叢集索引時停用快速插入。Disables fast inserts while bulk loading data into heap or clustered index. SQL Server 2016 (13.x)SQL Server 2016 (13.x) 開始,當資料庫處於簡單或大量記錄復原模式時,預設會啟用最低限度方式記錄的快速插入,以最佳化插入記錄到新頁面的插入效能。Starting SQL Server 2016 (13.x)SQL Server 2016 (13.x), fast inserts is enabled by default leveraging minimal logging when database is in simple or bulk logged recovery model to optimize insert performance for records inserted into new pages. 使用快速插入,每個大量載入批次會取得新範圍,藉由略過現有範圍可用空間的配置查閱以最佳化插入效能。With fast inserts, each bulk load batch acquires new extent(s) bypassing the allocation lookup for existing extent with available free space to optimize insert performance.

使用快速插入,小批次的大量載入會導致物件耗用的未使用空間增加,因此建議您針對每個批次使用大批次以完全填滿範圍。With fast inserts, bulk loads with small batch sizes can lead to increased unused space consumed by objects hence it is recommended to use large batchsize for each batch to fill the extent completely. 如果增加批次大小不可行,這個追蹤旗標能有助於減少保留的未使用空間,但會犧牲效能。If increasing batchsize is not feasible, this traceflag can help reduce unused space reserved at the expense of performance.

注意: 這個追蹤旗標適用於 SQL Server 2016 (13.x)SQL Server 2016 (13.x) RTM 和更新版本組建。Note: This trace flag applies to SQL Server 2016 (13.x)SQL Server 2016 (13.x) RTM and higher builds.

範圍:全域或工作階段Scope: global or session
715715 啟用資料表鎖定,以大量載入到不含非叢集索引的堆積。Enables table lock for bulk load operations into a heap with no non-clustered indexes. 啟用此追蹤旗標時,大量複製資料到資料表時,大量載入作業會取得大量更新 (BU) 鎖定。When this trace flag is enabled, bulk load operations acquire bulk update (BU) locks when bulk copying data into a table. 大量更新 (BU) 鎖定允許多個執行緒將資料同時大量載入到相同資料表,同時禁止未大量載入資料的其他處理序存取該資料表。Bulk update (BU) locks allow multiple threads to bulk load data concurrently into the same table, while preventing other processes that are not bulk loading data from accessing the table.

此行為類似使用者在執行大量載入時明確指定 TABLOCK 提示,或針對大量載入的指定資料表啟用 sp_tableoption 資料表鎖定。The behavior is similar to when the user explicitly specifies TABLOCK hint while performing bulk load, or when the sp_tableoption table lock on bulk load is enabled for a given table. 不過,啟用此追蹤旗標時,在不變更任何查詢或資料庫的情況下,此行為即為預設值。However, when this trace flag is enabled, this behavior becomes default without any query or database changes.

範圍: 全域或工作階段Scope: global or session
834834 針對緩衝集區使用 Microsoft Windows 大型分頁配置。Uses Microsoft Windows large-page allocations for the buffer pool. 如需詳細資訊,請參閱此 Microsoft 支援服務文章 (機器翻譯)。For more information, see this Microsoft Support article.

注意: 如果您使用 SQL Server 2012 (11.x)SQL Server 2012 (11.x)SQL Server 2017SQL Server 2017 的資料行存放區索引功能,則不建議開啟追蹤旗標 834。Note: If you are using the Columnstore Index feature of SQL Server 2012 (11.x)SQL Server 2012 (11.x) to SQL Server 2017SQL Server 2017, we do not recommend turning on trace flag 834.

範圍:只限全域Scope: global only
845845 [SQL Server]SQL Server 的服務帳戶啟用「鎖定記憶體中的分頁」權限時,在 [SQL Server]SQL Server 的標準 SKU 上啟用鎖定的分頁。Enables locked pages on Standard SKUs of [SQL Server]SQL Server, when the service account for [SQL Server]SQL Server has the Lock Pages in Memory privilege enabled. 如需詳細資訊,請參閱此 Microsoft 支援服務文章 (機器翻譯) 和伺服器記憶體伺服器組態選項上的文件頁面。For more information, see this Microsoft Support article and the documentation page on Server Memory Server Configuration Options.

注意:SQL Server 2012 (11.x)SQL Server 2012 (11.x) 開始,預設會針對標準 SKU 啟用此行為,而且不得使用追蹤旗標 845。Note: Starting with SQL Server 2012 (11.x)SQL Server 2012 (11.x) this behavior is enabled by default for Standard SKUs, and trace flag 845 must not be used.

範圍:只限全域Scope: global only
902902 安裝累計更新或 Service Pack 時略過執行資料庫升級指令碼。Bypasses execution of database upgrade script when installing a Cumulative Update or Service Pack. 如果在指令碼升級模式期間發生錯誤,建議您連絡 Microsoft SQL 客戶服務及支援 (CSS) 取得進一步指示。If you encounter an error during script upgrade mode, it is recommended to contact Microsoft SQL Customer Service and Support (CSS) for further guidance. 如需詳細資訊,請參閱此 Microsoft 支援服務文章 (英文)。For more information, see this Microsoft Support article.

警告: 此追蹤旗標用於對指令碼升級模式期間失敗的更新進行疑難排解,而且不支援在生產環境中連續執行。WARNING: This trace flag is meant for troubleshooting of failed updates during script upgrade mode, and it is not supported to run it continuously in a production environment. 資料庫升級指令碼必須順利執行,以完整安裝累積更新和 Service Pack。Database upgrade scripts needs to execute successfully for a complete install of Cumulative Updates and Service Packs. 否則可能會導致 [SQL Server]SQL Server 執行個體發生未預期的問題。Not doing so can cause unexpected issues with your [SQL Server]SQL Server instance.

範圍:只限全域Scope: global only
11171117 當檔案群組中的某個檔案達到自動成長閾值時,檔案群組中的所有檔案都會成長。When a file in the filegroup meets the autogrow threshold, all files in the filegroup grow.

注意:SQL Server 2016 (13.x)SQL Server 2016 (13.x) 開始,此行為由 ALTER DATABASE 的 AUTOGROW_SINGLE_FILE 和 AUTOGROW_ALL_FILES 選項控制,追蹤旗標 1117 沒有任何作用。Note: Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) this behavior is controlled by the AUTOGROW_SINGLE_FILE and AUTOGROW_ALL_FILES option of ALTER DATABASE, and trace flag 1117 has no effect. 如需詳細資訊,請參閱 ALTER DATABASE 檔案及檔案群組選項 (Transact-SQL)For more information, see ALTER DATABASE File and Filegroup Options (Transact-SQL).

範圍: 只限全域Scope: global only
11181118 移除伺服器上大部分的單一頁面配置,以減少 SGAM 頁面的競爭情況。Removes most single page allocations on the server, reducing contention on the SGAM page. 建立新物件時,根據預設,前八頁會從不同的範圍 (混合範圍) 進行配置。When a new object is created, by default, the first eight pages are allocated from different extents (mixed extents). 之後若需要更多頁面時,將會從相同的範圍 (統一範圍) 加以配置。Afterwards, when more pages are needed, those are allocated from that same extent (uniform extent). SGAM 頁面可用以追蹤這些混合範圍,因此若出現多個混合頁面配置,它會很快地成為瓶頸。The SGAM page is used to track these mixed extents, so can quickly become a bottleneck when numerous mixed page allocations are occurring. 這個追蹤旗標會在建立新物件時,從相同的範圍配置所有八個頁面,進而將掃描 SGAM 頁面的需求降到最低。This trace flag allocates all eight pages from the same extent when creating new objects, minimizing the need to scan the SGAM page. 如需詳細資訊,請參閱此 Microsoft 支援服務文章 (機器翻譯)。For more information, see this Microsoft Support article.

注意:SQL Server 2016 (13.x)SQL Server 2016 (13.x) 開始,此行為由 ALTER DATABASE 的 SET MIXED_PAGE_ALLOCATION 選項控制,追蹤旗標 1118 沒有任何作用。Note: Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) this behavior is controlled by the SET MIXED_PAGE_ALLOCATION option of ALTER DATABASE, and trace flag 1118 has no effect. 如需詳細資訊,請參閱 ALTER DATABASE SET 選項 (Transact-SQL)For more information, see ALTER DATABASE SET Options (Transact-SQL).

範圍: 只限全域Scope: global only
12041204 傳回參與死結之鎖定的資源和類型,以及目前受影響的命令。Returns the resources and types of locks participating in a deadlock and also the current command affected. 如需詳細資訊,請參閱此 Microsoft 支援服務文章 (機器翻譯)。For more information, see this Microsoft Support article.

範圍: 只限全域Scope: global only
12111211 停用以記憶體壓力或鎖定個數為基礎的鎖定擴大。Disables lock escalation based on memory pressure, or based on number of locks. SQL Server Database EngineSQL Server Database Engine 不會將資料列或頁面鎖定擴大到資料表鎖定。The SQL Server Database EngineSQL Server Database Engine will not escalate row or page locks to table locks.

使用這個追蹤旗標可能產生大量鎖定。Using this trace flag can generate excessive numbers of locks. 這可能會降低 Database EngineDatabase Engine 的效能,或因記憶體不足而造成 1204 錯誤 (無法配置鎖定資源)。This can slow the performance of the Database EngineDatabase Engine, or cause 1204 errors (unable to allocate lock resource) because of insufficient memory.

如果同時設定了追蹤旗標 1211 和 1224,將會優先採用 1211。If both trace flag 1211 and 1224 are set, 1211 takes precedence over 1224. 但是,由於追蹤旗標 1211 會在每一個情況下防止鎖定擴大 (即使是在記憶體壓力下),所以建議您最好使用 1224。However, because trace flag 1211 prevents escalation in every case, even under memory pressure, we recommend that you use 1224. 如此可避免在使用許多鎖定時,發生「鎖定不足」錯誤。This helps avoid "out-of-locks" errors when many locks are being used.

範圍:全域或工作階段Scope: global or session
12221222 以不符合任何 XSD 結構描述的 XML 格式來傳回參與死結之鎖定的資源和類型,以及目前受影響的命令。Returns the resources and types of locks that are participating in a deadlock and also the current command affected, in an XML format that does not comply with any XSD schema.

範圍:只限全域Scope: global only
12241224 停用以鎖定個數為基礎的鎖定擴大。Disables lock escalation based on the number of locks. 不過,記憶體壓力仍然可以啟動鎖定擴大。However, memory pressure can still activate lock escalation. 如果鎖定物件使用的記憶體數量超出下列其中一個條件, Database EngineDatabase Engine 就會將資料列或頁面鎖定擴大至資料表 (或資料分割) 鎖定:The Database EngineDatabase Engine escalates row or page locks to table (or partition) locks if the amount of memory used by lock objects exceeds one of the following conditions:
  • Database EngineDatabase Engine 所用記憶體的 40%。Forty percent of the memory that is used by Database EngineDatabase Engine. 只有當 sp_configure 的 locks 參數設為 0 時,這個條件才適用。This is applicable only when the locks parameter of sp_configure is set to 0.
  • 使用 sp_configure 的 locks 參數設定之鎖定記憶體的 40%。Forty percent of the lock memory that is configured by using the locks parameter of sp_configure. 如需詳細資訊,請參閱 伺服器設定選項 (SQL Server)伺服器組態選項。For more information, see Server Configuration Options (SQL Server).

如果同時設定了追蹤旗標 1211 和 1224,將會優先採用 1211。If both trace flag 1211 and 1224 are set, 1211 takes precedence over 1224. 但是,由於追蹤旗標 1211 會在每一個情況下防止鎖定擴大 (即使是在記憶體壓力下),所以建議您最好使用 1224。However, because trace flag 1211 prevents escalation in every case, even under memory pressure, we recommend that you use 1224. 如此可避免在使用許多鎖定時,發生「鎖定不足」錯誤。This helps avoid "out-of-locks" errors when many locks are being used.

注意: 您也可以使用 ALTER TABLE 陳述式的 LOCK_ESCALATION 選項來控制鎖定擴大到資料表或 HoBT 層級的資料粒度。Note: Lock escalation to the table-level or HoBT-level granularity can also be controlled by using the LOCK_ESCALATION option of the ALTER TABLE statement.

範圍: 全域或工作階段Scope: global or session
12361236 啟用資料庫鎖定資料分割。Enables database lock partitioning. 如需詳細資訊,請參閱此 Microsoft 支援服務文章 (機器翻譯)。For more information, see this Microsoft Support article.

注意:SQL Server 2012 (11.x)SQL Server 2012 (11.x) SP3 和 SQL Server 2014 (12.x)SQL Server 2014 (12.x) SP1 開始,此行為由引擎控制,追蹤旗標 1236 沒有任何作用。Note: Starting with SQL Server 2012 (11.x)SQL Server 2012 (11.x) SP3 and SQL Server 2014 (12.x)SQL Server 2014 (12.x) SP1 this behavior is controlled by the engine and trace flag 1236 has no effect.

範圍:只限全域Scope: global only
12371237 使 ALTER PARTITION FUNCTION 陳述式遵循目前使用者定義的工作階段死結優先權,而非成為預設情況下可能的死結犧牲者。Allows the ALTER PARTITION FUNCTION statement to honor the current user-defined session deadlock priority instead of being the likely deadlock victim by default. 如需詳細資訊,請參閱此 Microsoft 支援服務文章 (機器翻譯)。For more information, see this Microsoft Support article.

注意:SQL Server 2017 (14.x)SQL Server 2017 (14.x) 和資料庫相容性層級 140 開始,這是預設行為,追蹤旗標 1237 沒有任何作用。Note: Starting with SQL Server 2017 (14.x)SQL Server 2017 (14.x) and database compatibility level 140 this is the default behavior and trace flag 1237 has no effect.

範圍:全域或工作階段或查詢Scope: global or session or query
12601260 停用排程器監視器傾印。Disable scheduler monitor dumps.

範圍:只限全域Scope: global only
14481448 讓複寫記錄讀取器向前移動,即使非同步次要尚未認可收到變更也一樣。Enables the replication log reader to move forward even if the async secondaries have not acknowledged the reception of a change. 即使這個追蹤旗標已啟用,記錄讀取器一定會等候同步次要。Even with this trace flag enabled the log reader always waits for the sync secondaries. 記錄讀取器不會超過同步次要的最小認可。The log reader will not go beyond the min ack of the sync secondaries. 這個追蹤旗標會套用至 [SQL Server]SQL Server 執行個體,而不只可用性群組、可用性資料庫或記錄讀取器執行個體。This trace flag applies to the instance of [SQL Server]SQL Server, not just an availability group, an availability database, or a log reader instance. 立即生效,不必重新啟動。Takes effect immediately without a restart. 您可以事先或在非同步次要失敗時啟動這個追蹤旗標。This trace flag can be activated ahead of time or when an async secondary fails. 如需詳細資訊,請參閱此 Microsoft 支援服務文章 (機器翻譯)。For more information, see this Microsoft Support article.

範圍:只限全域Scope: global only
14621462 停用非同步可用性群組的記錄資料流壓縮。Disables log stream compression for asynchronous availability groups. 非同步可用性群組上預設會啟用此功能,以最佳化網路頻寬。This feature is enabled by default on asynchronous availability groups in order to optimize network bandwidth. 如需詳細資訊,請參閱 微調可用性群組的壓縮For more information, see Tune compression for availability group.

範圍:只限全域Scope: global only
18001800 [SQL Server]SQL Server Always On 和「記錄傳送」環境中,當主要和次要複本記錄檔使用不同磁區大小的磁碟時啟用 [SQL Server]SQL Server 最佳化。Enables [SQL Server]SQL Server optimization when disks of different sector sizes are used for primary and secondary replica log files, in [SQL Server]SQL Server Always On and Log Shipping environments. 只有在交易記錄檔位於磁區大小是 512 個位元組之磁碟上的 SQL Server 執行個體時,才需要啟用此追蹤旗標。This trace flag is only required to be enabled on SQL Server instances with transaction log file residing on disk with sector size of 512 bytes. 在磁區大小為 4k 的磁碟上「不需」啟用此追蹤旗標。It is not required to be enabled on disk with 4k sector sizes. 如需詳細資訊,請參閱此 Microsoft 支援服務文章 (機器翻譯)。For more information, see this Microsoft Support article.

範圍: 只限全域Scope: global only
23012301 啟用進階決策支援最佳化。Enable advanced decision support optimizations. 如需詳細資訊,請參閱此 Microsoft 支援服務文章 (機器翻譯)。For more information, see this Microsoft Support article.

範圍: 全域、工作階段和查詢Scope: global and session and query
23122312 可讓您將查詢最佳化工具基數估計模型設定為 SQL Server 2014 (12.x)SQL Server 2014 (12.x)SQL Server 2017SQL Server 2017 版本,而不根據資料庫的相容性層級。Enables you to set the query optimizer cardinality estimation model to the SQL Server 2014 (12.x)SQL Server 2014 (12.x) through SQL Server 2017SQL Server 2017 versions, dependent of the compatibility level of the database. 如需詳細資訊,請參閱此 Microsoft 支援服務文章 (機器翻譯)。For more information, see Microsoft Support article.

SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1 開始,若要在查詢層級完成此操作,請新增 USE HINT 'FORCE_DEFAULT_CARDINALITY_ESTIMATION' 查詢提示,而不要使用此追蹤旗標。Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1, to accomplish this at the query level, add the USE HINT 'FORCE_DEFAULT_CARDINALITY_ESTIMATION' query hint instead of using this trace flag.

範圍:全域或工作階段或查詢Scope: global or session or query
23352335 使 [SQL Server]SQL Server 假設在查詢最佳化期間,有固定的記憶體數量可供使用。Causes [SQL Server]SQL Server to assume a fixed amount of memory is available during query optimization. 它不會限制 [SQL Server]SQL Server 授與執行查詢的記憶體。It does not limit the memory [SQL Server]SQL Server grants to execute the query. 資料快取、查詢執行和其他取用者仍可使用為 [SQL Server]SQL Server 設定的記憶體。The memory configured for [SQL Server]SQL Server will still be used by data cache, query execution and other consumers. 如需詳細資訊,請參閱此 Microsoft 支援服務文章 (機器翻譯)。For more information, see this Microsoft Support article.

注意: 請確定您已徹底測試此選項後再部署到生產環境。Note: Please ensure that you thoroughly test this option, before rolling it into a production environment.

範圍:全域或工作階段或查詢Scope: global or session or query
23402340 使 [SQL Server]SQL Server 在計劃產生時不使用排序作業 (批次排序) 以取得最佳化巢狀迴圈聯結。Causes [SQL Server]SQL Server not to use a sort operation (batch sort) for optimized Nested Loops joins when generating a plan. 如需詳細資訊,請參閱此 Microsoft 支援服務文章 (機器翻譯)。For more information, see this Microsoft Support article.

SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1 開始,若要在查詢層級完成此操作,請新增 USE HINT 'DISABLE_OPTIMIZED_NESTED_LOOP' 查詢提示,而不要使用此追蹤旗標。Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1, to accomplish this at the query level, add the USE HINT 'DISABLE_OPTIMIZED_NESTED_LOOP' query hint instead of using this trace flag.

注意: 請確定您已徹底測試此選項後再部署到生產環境。Note: Please ensure that you thoroughly test this option, before rolling it into a production environment.

範圍:全域或工作階段或查詢Scope: global or session or query
23712371 將固定自動更新統計資料閾值變更為動態自動更新統計資料閾值。Changes the fixed auto update statistics threshold to dynamic auto update statistics threshold. 如需詳細資訊,請參閱此 Microsoft 支援服務文章 (機器翻譯)。For more information, see this Microsoft Support article.

注意:SQL Server 2016 (13.x)SQL Server 2016 (13.x) 開始及根據資料庫相容性層級 130,此行為由引擎控制,追蹤旗標 2371 沒有任何作用。Note: Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) and under the database compatibility level 130, this behavior is controlled by the engine and trace flag 2371 has no effect.

範圍:只限全域Scope: global only
23892389 針對遞增值啟用自動產生的快速統計資料 (長條圖修正)。Enable automatically generated quick statistics for ascending keys (histogram amendment). 如果設定追蹤旗標 2389,且前置統計資料資料行標示為遞增,則在查詢編譯時將調整用來預估基數的長條圖。If trace flag 2389 is set, and a leading statistics column is marked as ascending, then the histogram used to estimate cardinality will be adjusted at query compile time. 如需詳細資訊,請參閱此 Microsoft 支援服務文章 (機器翻譯)。For more information, see this Microsoft Support article.

注意: 請確定您已徹底測試此選項後再部署到生產環境。Note: Please ensure that you thoroughly test this option, before rolling it into a production environment.

注意: 此追蹤旗標不適用於 CE 120 版本或更新版本。Note: This trace flag does not apply to CE version 120 or above. 請改用追蹤旗標 4139。Use trace flag 4139 instead.

範圍:全域或工作階段或查詢Scope: global or session or query
23902390 針對遞增或不明值啟用自動產生的快速統計資料 (長條圖修正)。Enable automatically generated quick statistics for ascending or unknown keys (histogram amendment). 如果設定追蹤旗標 2390,且前置統計資料資料行標示為遞增或不明,則在查詢編譯時將調整用來預估基數的長條圖。If trace flag 2390 is set, and a leading statistics column is marked as ascending or unknown, then the histogram used to estimate cardinality will be adjusted at query compile time. 如需詳細資訊,請參閱此 Microsoft 支援服務文章 (機器翻譯)。For more information, see this Microsoft Support article.

注意: 請確定您已徹底測試此選項後再部署到生產環境。Note: Please ensure that you thoroughly test this option, before rolling it into a production environment.

注意: 此追蹤旗標不適用於 CE 120 版本或更新版本。Note: This trace flag does not apply to CE version 120 or above. 請改用追蹤旗標 4139。Use trace flag 4139 instead.

範圍:全域或工作階段或查詢Scope: global or session or query
24222422 超過 Resource Governor REQUEST_MAX_CPU_TIME_SEC 設定所設定的時間上限時,啟用 SQL Server Database EngineSQL Server Database Engine 以中止要求。Enables the SQL Server Database EngineSQL Server Database Engine to abort a request when the maximum time set by Resource Governor REQUEST_MAX_CPU_TIME_SEC configuration is exceeded. 如需詳細資訊,請參閱此 Microsoft 支援服務文章 (機器翻譯)。For more information, see this Microsoft Support article.

注意: 此追蹤旗標適用於 SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP2、SQL Server 2017 (14.x)SQL Server 2017 (14.x) CU3,和更新的組建。Note: This trace flag applies to SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP2, SQL Server 2017 (14.x)SQL Server 2017 (14.x) CU3, and higher builds.

範圍:全域Scope: global
24302430 啟用其他鎖定類別清除作業。Enables alternate lock class cleanup. 如需詳細資訊,請參閱此 Microsoft 支援服務文章 (英文)。For more information, see this Microsoft Support article.

範圍:只限全域Scope: global only
24532453 變更足夠數目的資料列時,允許資料表變數觸發重新編譯。Allows a table variable to trigger recompile when enough number of rows are changed. 如需詳細資訊,請參閱此 Microsoft 支援服務文章 (機器翻譯)。For more information, see this Microsoft Support article.

注意: 請確定您已徹底測試此選項後再部署到生產環境。Note: Please ensure that you thoroughly test this option, before rolling it into a production environment.

範圍:全域或工作階段或查詢Scope: global or session or query
24672467 根據哪個節點有最新的已配置執行緒而定,啟用替代的平行背景工作執行緒配置原則。Enables an alternate parallel worker thread allocation policy, based on which node has the least allocated threads. 如需詳細資訊,請參閱平行查詢處理For more information, see Parallel Query Processing. 請參閱設定最大工作者執行緒伺服器設定選項,以取得有關如何設定最大工作者執行緒伺服器選項的詳細資訊。Refer to Configure the max worker threads Server Configuration Option for information on configuring the max worker threads server option.

注意: 查詢平行處理原則 (DOP) 必須容納在單一節點中,才能使用此替代原則,否則會改為使用預設執行緒配置原則。Note: Query degree of parallelism (DOP) has to fit into a single node for this alternate policy to be used, or the default thread allocation policy is used instead. 使用此追蹤旗標時,建議您在執行查詢時不要指定超過單一節點中排程器數目的 DOP,您所指定的 DOP 應該小於或等於單一節點中的排程器數目。Using this trace flag, it is not recommended to execute queries specifying a DOP over the number of schedulers in a single, with queries specifying a DOP below or equal to the number of schedulers in a single node.

注意: 請確定您已徹底測試此選項後再部署到生產環境。Note: Please ensure that you thoroughly test this option, before rolling it into a production environment.

範圍:只限全域Scope: global only
24692469 INSERT INTO ... SELECT 和磁碟分割資料行存放區索引之間啟用其他交換。Enables alternate exchange for INSERT INTO ... SELECT into a partitioned columnstore index. 如需詳細資訊,請參閱此 Microsoft 支援服務文章 (機器翻譯)。For more information, see this Microsoft Support article.

範圍:全域或工作階段或查詢Scope: global or session or query
25282528 利用 DBCC CHECKDB、DBCC CHECKFILEGROUP 和 DBCC CHECKTABLE 來停用物件的平行檢查。Disables parallel checking of objects by DBCC CHECKDB, DBCC CHECKFILEGROUP, and DBCC CHECKTABLE. 依預設,查詢處理器會自動判斷平行處理原則的程度。By default, the degree of parallelism is automatically determined by the query processor. 最大平行處理原則程度的設定方式與平行查詢相同。The maximum degree of parallelism is configured just like that of parallel queries. 如需詳細資訊,請參閱 設定 max degree of parallelism 伺服器組態選項For more information, see Configure the max degree of parallelism Server Configuration Option.

注意: 平行 DBCC 檢查通常應為啟用 (預設值)。Note: Parallel DBCC checks should typically be enabled (default). 查詢處理器會針對 DBCC CHECKDB 檢查的每份資料表或資料表批次重新評估和自動調整平行處理原則。The query processor reevaluates and automatically adjusts parallelism for each table or batch of tables checked by DBCC CHECKDB.

一般使用案例是當系統管理員知道 DBCC CHECKDB 完成前伺服器負載會增加,因此選擇手動減少或停用平行處理原則,以增加並行的其他使用者工作負載。The typical use scenario is when a system administrator knows that server load will increase before DBCC CHECKDB completes, and so chooses to manually decrease or disable parallelism, in order to increase concurrency with other user workload. 不過,在 DBCC CHECKDB 中停用平行檢查,可能需要更多時間才能完成。However, disabling parallel checks in DBCC CHECKDB can cause it to take longer to complete.

注意: 如果使用 TABLOCK 選項執行 DBCC CHECKDB,而且已停用平行處理原則,資料表鎖定的時間可能會更長。Note: If DBCC CHECKDB is executed using the TABLOCK option and parallelism is disabled, tables may be locked for longer periods of time.

注意:SQL Server 2014 (12.x)SQL Server 2014 (12.x) SP2 開始,MAXDOP 選項可用來覆寫陳述式之 sp_configure 的 max degree of parallelism 設定選項。Note: Starting with SQL Server 2014 (12.x)SQL Server 2014 (12.x) SP2, a MAXDOP option is available to override the max degree of parallelism configuration option of sp_configure for the statement.

範圍:全域或工作階段Scope: global or session
25492549 執行 DBCC CHECKDB 命令會假設每個資料庫檔案位於唯一的磁碟機上。Runs the DBCC CHECKDB command assuming each database file is on a unique disk drive. DBCC CHECKDB 命令會跨所有資料庫檔案建立要在每個唯一磁碟機內讀取的內部分頁清單。DBCC CHECKDB command builds an internal list of pages to read per unique disk drive across all database files. 此邏輯判斷唯一磁碟機的依據,是每個檔案之實際檔案名稱的磁碟機代號。This logic determines unique disk drives based on the drive letter of the physical file name of each file.

注意: 除非您已知每個檔案是以唯一實體磁碟為基礎,否則請勿使用此追蹤旗標。Note: Do not use this trace flag unless you know that each file is based on a unique physical disk.

注意: 雖然此追蹤旗標可改善目標是使用 PHYSICAL_ONLY 選項之 DBCC CHECKDB 命令的效能,但某些使用者可能無法察覺效能有任何改善。Note: Although this trace flag improve the performance of the DBCC CHECKDB commands which target usage of the PHYSICAL_ONLY option, some users may not see any improvement in performance. 此追蹤旗標可改善磁碟 I/O 資源用量,但磁碟資源的基礎效能可能會限制 DBCC CHECKDB 命令的整體效能。While this trace flag improves disk I/O resources usage, the underlying performance of disk resources may limit the overall performance of the DBCC CHECKDB command. 如需詳細資訊,請參閱此 Microsoft 支援服務文章 (機器翻譯)。For more information, see this Microsoft Support article.

範圍:只限全域Scope: global only
25622562 無論資料庫中的索引數目為何,一律在單一「批次」中執行 DBCC CHECKDB 命令。Runs the DBCC CHECKDB command in a single "batch" regardless of the number of indexes in the database. 根據預設,DBCC CHECKDB 命令會運用「批次」概念來嘗試限制其產生的索引或「事實」數目,以將 tempdb 資源數量減到最低。By default, the DBCC CHECKDB command tries to minimize tempdb resources by limiting the number of indexes or "facts" that it generates by using a "batches" concept. 此追蹤旗標會將所有處理強制成一個批次。This trace flag forces all processing into one batch.

使用此追蹤旗標的其中一個影響,是 tempdb 的空間需求可能會增加。One effect of using this trace flag is that the space requirements for tempdb may increase. Tempdb 可能成長至 DBCC CHECKDB 命令處理之使用者資料庫的 5% 或以上。Tempdb may grow to as much as 5% or more of the user database that is being processed by the DBCC CHECKDB command.

注意: 雖然此追蹤旗標可改善目標是使用 PHYSICAL_ONLY 選項之 DBCC CHECKDB 命令的效能,但某些使用者可能無法察覺效能有任何改善。Note: Although this trace flag improve the performance of the DBCC CHECKDB commands which target usage of the PHYSICAL_ONLY option, some users may not see any improvement in performance. 此追蹤旗標可改善磁碟 I/O 資源用量,但磁碟資源的基礎效能可能會限制 DBCC CHECKDB 命令的整體效能。While this trace flag improves disk I/O resources usage, the underlying performance of disk resources may limit the overall performance of the DBCC CHECKDB command. 如需詳細資訊,請參閱此 Microsoft 支援服務文章 (機器翻譯)。For more information, see this Microsoft Support article.

範圍:只限全域Scope: global only
25662566 除非指定 DATA_PURITY 選項,請執行不含資料純度檢查的 DBCC CHECKDB 命令。Runs the DBCC CHECKDB command without data purity check unless DATA_PURITY option is specified.

注意: 預設會啟用資料行值的完整性檢查,而不需要 DATA_PURITY 選項。Note: Column-value integrity checks are enabled by default and do not require the DATA_PURITY option. 對於從舊版 SQL Server 升級的資料庫,在毫無錯誤的情況下完成對資料庫執行 DBCC CHECKDB WITH DATA_PURITY 至少一次之前,依預設不啟用資料行值的完整性檢查。For databases upgraded from earlier versions of SQL Server, column-value checks are not enabled by default until DBCC CHECKDB WITH DATA_PURITY has been run error free on the database at least once. 此後,依預設 DBCC CHECKDB 會檢查資料行值的完整性。After this, DBCC CHECKDB checks column-value integrity by default. 如需詳細資訊,請參閱此 Microsoft 支援服務文章 (機器翻譯)。For more information, see this Microsoft Support article.

範圍:只限全域Scope: global only
30233023 啟用 CHECKSUM 選項,做為 BACKUP 命令的預設值。Enables CHECKSUM option as default for BACKUP command. 如需詳細資訊,請參閱此 Microsoft 支援服務文章 (英文)。For more information, see this Microsoft Support article.

注意:SQL Server 2014 (12.x)SQL Server 2014 (12.x) 開始,此行為是由設定「備份總和檢查碼預設值」設定選項控制。Note: Starting with SQL Server 2014 (12.x)SQL Server 2014 (12.x) this behavior is controlled by setting the backup checksum default configuration option. 如需詳細資訊,請參閱 伺服器設定選項 (SQL Server)伺服器組態選項。For more information, see Server Configuration Options (SQL Server).

範圍:全域和工作階段Scope: global and session
30423042 略過預設備份壓縮預先配置演算法,讓備份檔案只會視需要成長以達到其最終大小。Bypasses the default backup compression pre-allocation algorithm to allow the backup file to grow only as needed to reach its final size. 如果您只要配置壓縮備份所需的實際大小,藉以節省空間,這個追蹤旗標就很有用。This trace flag is useful if you need to save on space by allocating only the actual size required for the compressed backup. 使用此追蹤旗標可能會導致效能稍微降低 (可能會增加備份作業的持續時間)。Using this trace flag might cause a slight performance penalty (a possible increase in the duration of the backup operation). 如需預先配置演算法的詳細資訊,請參閱備份壓縮 (SQL Server)For more information about the pre-allocation algorithm, see Backup Compression (SQL Server).

範圍:只限全域Scope: global only
30513051 使 SQL Server 備份至 URL 記錄到特定的錯誤記錄檔。Enables SQL Server Backup to URL logging to a specific error log file. 如需詳細資訊,請參閱 SQL Server 備份至 URL 的最佳做法和疑難排解For more information, see SQL Server Backup to URL Best Practices and Troubleshooting.

範圍:只限全域Scope: global only
32053205 依預設,如果磁帶機支援硬體壓縮,DUMP 或 BACKUP 陳述式就會使用它。By default, if a tape drive supports hardware compression, either the DUMP or BACKUP statement uses it. 當使用這個追蹤旗標時,您可以停用磁帶機的硬體壓縮。With this trace flag, you can disable hardware compression for tape drivers. 當您要與其他不支援壓縮的站台或磁帶機交換磁帶時,這非常有用。This is useful when you want to exchange tapes with other sites or tape drives that do not support compression.

範圍:全域或工作階段Scope: global or session
32263226 根據預設,每項成功的備份作業都會在 [SQL Server]SQL Server 錯誤記錄檔與系統事件記錄檔中,加入一個項目。By default, every successful backup operation adds an entry in the [SQL Server]SQL Server error log and in the system event log. 如果您經常建立記錄備份,這些成功訊息可能會快速累積,因而產生龐大的錯誤記錄檔,讓您難以尋找其他訊息。If you create very frequent log backups, these success messages accumulate quickly, resulting in huge error logs in which finding other messages is problematic.

透過這個追蹤旗標,您可以隱藏這些記錄項目。With this trace flag, you can suppress these log entries. 如果您正執行經常記錄備份,而且沒有任何指令碼相依於這些項目,這樣做就會很有用。This is useful if you are running frequent log backups and if none of your scripts depend on those entries.

範圍:只限全域Scope: global only
34273427 為許多連續性交易在 SQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2017 (14.x)SQL Server 2017 (14.x) 的暫存資料表中插入資料時所耗用的 CPU 比在 SQL Server 2014 (12.x)SQL Server 2014 (12.x) 更多的問題,啟用修正。Enables fix for issue when many consecutive transactions inserting data into temp table in SQL Server 2016 (13.x)SQL Server 2016 (13.x) or SQL Server 2017 (14.x)SQL Server 2017 (14.x) consumes more CPU than in SQL Server 2014 (12.x)SQL Server 2014 (12.x). 如需詳細資訊,請參閱此 Microsoft 支援服務文章For more information, see this Microsoft Support article

注意: 此追蹤旗標適用於 SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1 CU2 及更新的組建。Note: This trace flag applies to SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1 CU2 and higher builds. SQL Server 2017 (14.x)SQL Server 2017 (14.x) CU4 開始,此追蹤旗標沒有任何作用。Starting with SQL Server 2017 (14.x)SQL Server 2017 (14.x) CU4 this trace flag has no effect.

範圍:只限全域Scope: global only
34593459 停用平行重做。Disables parallel redo. 如需詳細資訊,請參閱此 Microsoft 支援服務文章Microsoft 支援服務文章For more information, see this Microsoft Support article and Microsoft Support article.

注意: 此追蹤旗標適用於 SQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2017 (14.x)SQL Server 2017 (14.x)Note: This trace flag applies to SQL Server 2016 (13.x)SQL Server 2016 (13.x) and SQL Server 2017 (14.x)SQL Server 2017 (14.x).

範圍:只限全域Scope: global only
34683468 停用 TempDB 上的間接檢查點Disables indirect checkpoints on TempDB.

注意: 此追蹤旗標適用於 SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1 CU5、SQL Server 2017 (14.x)SQL Server 2017 (14.x) CU1 及更新的組建。Note: This trace flag applies to SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1 CU5, SQL Server 2017 (14.x)SQL Server 2017 (14.x) CU1 and higher builds.

範圍:只限全域Scope: global only
36083608 防止 [SQL Server]SQL Server 自動啟動並復原任何資料庫,但 master 資料庫除外。Prevents [SQL Server]SQL Server from automatically starting and recovering any database except the master database. 若起始了需要 tempdb 的活動,則會復原 model 並建立 tempdbIf activities that require tempdb are initiated, then model is recovered and tempdb is created. 其他資料庫會在存取時啟動和復原。Other databases will be started and recovered when accessed. 但是,某些功能 (例如快照集隔離和讀取認可的快照集) 可能無法運作。Some features, such as snapshot isolation and read committed snapshot, might not work. 用於移動系統資料庫移動使用者資料庫Use for Move System Databases and Move User Databases.

注意: 請勿在一般作業期間使用。Note: Do not use during normal operation.

範圍:只限全域Scope: global only
36253625 使用 '******' 來遮罩某些錯誤訊息的參數,藉以限制傳回給非 sysadmin 固定伺服器角色成員之使用者的資訊量。Limits the amount of information returned to users who are not members of the sysadmin fixed server role, by masking the parameters of some error messages using '******'. 這樣做有助於避免洩漏機密資訊。This can help prevent disclosure of sensitive information.

範圍:只限全域Scope: global only
41364136 除非使用了 OPTION(RECOMPILE), WITH RECOMPILE or OPTIMIZE FOR <value>,否則請停用參數探查。Disables parameter sniffing unless OPTION(RECOMPILE), WITH RECOMPILE or OPTIMIZE FOR <value> is used. 如需詳細資訊,請參閱此 Microsoft 支援服務文章 (機器翻譯)。For more information, see this Microsoft Support article.

SQL Server 2016 (13.x)SQL Server 2016 (13.x) 開始,若要在資料庫層級完成此操作,請參閱 ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL) 中的 PARAMETER_SNIFFING 選項。Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x), to accomplish this at the database level, see the PARAMETER_SNIFFING option in ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL).

若要在查詢層級完成此操作,請新增 OPTIMIZE FOR UNKNOWN 查詢提示To accomplish this at the query level, add the OPTIMIZE FOR UNKNOWN query hint. SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1 開始,在查詢層級完成此操作的第二個選項是新增 USE HINT 'DISABLE_PARAMETER_SNIFFING' 查詢提示,而不要使用此追蹤旗標。Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1, a second option to accomplish this at the query level is to add the USE HINT 'DISABLE_PARAMETER_SNIFFING' query hint instead of using this trace flag.

注意: 請確定您已徹底測試此選項後再部署到生產環境。Note: Please ensure that you thoroughly test this option, before rolling it into a production environment.

範圍:全域或工作階段Scope: global or session
41374137 SQL Server 2012 (11.x)SQL Server 2012 (11.x) 及更舊版本的查詢最佳化工具基數評估模型下,於評估說明關聯性之篩選條件的 AND 述詞時,會導致 [SQL Server]SQL Server 以最少的選擇性產生計劃。Causes [SQL Server]SQL Server to generate a plan using minimum selectivity when estimating AND predicates for filters to account for correlation, under the query optimizer cardinality estimation model of SQL Server 2012 (11.x)SQL Server 2012 (11.x) and earlier versions. 如需詳細資訊,請參閱此 Microsoft 支援服務文章 (英文)。For more information, see this Microsoft Support article.

SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1 開始,在使用舊版 CE 時,若要在查詢層級完成此操作,請新增 USE HINT 'ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES' 查詢提示,而不要使用此追蹤旗標。Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1, to accomplish this at the query level, add the USE HINT 'ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES' query hint instead of using this trace flag when using the Legacy CE.

注意: 請確定您已徹底測試此選項後再部署到生產環境。Note: Please ensure that you thoroughly test this option, before rolling it into a production environment.

注意: 此追蹤旗標不適用於 CE 120 版本或更新版本。Note: This trace flag does not apply to CE version 120 or above. 請改用追蹤旗標 9471。Use trace flag 9471 instead.

範圍:全域或工作階段或查詢Scope: global or session or query
41384138 會導致 [SQL Server]SQL Server 產生的計劃不使用資料列目標調整來處理包含 TOP、OPTION (FAST N)、IN 或 EXISTS 關鍵字的查詢。Causes [SQL Server]SQL Server to generate a plan that does not use row goal adjustments with queries that contain TOP, OPTION (FAST N), IN, or EXISTS keywords. 如需詳細資訊,請參閱此 Microsoft 支援服務文章 (英文)。For more information, see this Microsoft Support article.

SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1 開始,若要在查詢層級完成此操作,請新增 USE HINT 'DISABLE_OPTIMIZER_ROWGOAL' 查詢提示,而不要使用此追蹤旗標。Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1, to accomplish this at the query level, add the USE HINT 'DISABLE_OPTIMIZER_ROWGOAL' query hint instead of using this trace flag.

注意: 請確定您已徹底測試此選項後再部署到生產環境。Note: Please ensure that you thoroughly test this option, before rolling it into a production environment.

範圍:全域或工作階段或查詢Scope: global or session or query
41394139 不論索引鍵資料行狀態為何,啟用自動產生的快速統計資料 (長條圖修正)。Enable automatically generated quick statistics (histogram amendment) regardless of key column status. 如果設定追蹤旗標 4139,則不論前置統計資料資料行狀態為何 (遞增、遞增或固定),在查詢編譯時將調整用來預估基數的長條圖。If trace flag 4139 is set, regardless of the leading statistics column status (ascending, descending, or stationary), the histogram used to estimate cardinality will be adjusted at query compile time. 如需詳細資訊,請參閱此 Microsoft 支援服務文章 (機器翻譯)。For more information, see this Microsoft Support article.

SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1 開始,若要在查詢層級完成此操作,請新增 USE HINT 'ENABLE_HIST_AMENDMENT_FOR_ASC_KEYS' 查詢提示,而不要使用此追蹤旗標。Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1, to accomplish this at the query level, add the USE HINT 'ENABLE_HIST_AMENDMENT_FOR_ASC_KEYS' query hint instead of using this trace flag.

注意: 請確定您已徹底測試此選項後再部署到生產環境。Note: Please ensure that you thoroughly test this option, before rolling it into a production environment.

注意: 此追蹤旗標不適用於 CE 70 版本。Note: This trace flag does not apply to CE version 70. 請改用追蹤旗標 2389 和 2390。Use trace flags 2389 and 2390 instead.

範圍:全域或工作階段或查詢Scope: global or session or query
41994199 啟用在 [SQL Server]SQL Server 累積更新和 Service Pack 中發佈的查詢最佳化工具 (QO) 變更。Enables query optimizer (QO) changes released in [SQL Server]SQL Server Cumulative Updates and Service Packs.

根據指定產品版本中最新的資料庫相容性層級,預設會啟用對舊版 [SQL Server]SQL Server 所做的 QO 變更,而不啟用追蹤旗標 4199。QO changes that are made to previous releases of [SQL Server]SQL Server are enabled by default under the latest database compatibility level in a given product release, without trace flag 4199 enabled.

下表摘要說明使用特定資料庫相容性層級和追蹤旗標 4199 時的行為。The following table summarizes the behavior when using specific database compatibility levels and trace flag 4199. 如需詳細資訊,請參閱此 Microsoft 支援服務文章 (機器翻譯)。For more information, see this Microsoft Support article.

資料庫相容性層級 Database compatibility levelTF 4199TF 4199先前的資料庫相容性層級中的 QO 變更QO changes from previous database compatibility levels目前版本 RTM 後的 QO 變更QO changes for current version post-RTM
100 至 120100 to 120關閉Off已停用Disabled已停用Disabled
開啟On已啟用Enabled已啟用Enabled
130130關閉Off已啟用Enabled已停用Disabled
開啟On已啟用Enabled已啟用Enabled
140140關閉Off已啟用Enabled已停用Disabled
開啟On已啟用Enabled已啟用Enabled


SQL Server 2016 (13.x)SQL Server 2016 (13.x) 開始,若要在資料庫層級完成此操作,請參閱 ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL) 中的 QUERY_OPTIMIZER_HOTFIXES 選項。Strting with SQL Server 2016 (13.x)SQL Server 2016 (13.x), to accomplish this at the database level, see the QUERY_OPTIMIZER_HOTFIXES option in ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL).

SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1 開始,若要在查詢層級完成此操作,請新增 USE HINT 'ENABLE_QUERY_OPTIMIZER_HOTFIXES'查詢提示,而不要使用此追蹤旗標。Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1, to accomplish this at the query level, add the USE HINT 'ENABLE_QUERY_OPTIMIZER_HOTFIXES' query hint instead of using this trace flag.

範圍:全域或工作階段或查詢Scope: global or session or query
46104610 將儲存快取項目之雜湊表的大小增加 8 倍。Increases the size of the hash table that stores the cache entries by a factor of 8. 與追蹤旗標 4618 一起使用時,TokenAndPermUserStore 快取存放區中的項目數會增加至 8,192。When used together with trace flag 4618 increases the number of entries in the TokenAndPermUserStore cache store to 8,192. 如需詳細資訊,請參閱此 Microsoft 支援服務文章 (機器翻譯)。For more information, see this Microsoft Support article.

範圍: 只限全域Scope: global only
46164616 讓應用程式角色可以看見伺服器層級的中繼資料。Makes server-level metadata visible to application roles. [SQL Server]SQL Server 中,應用程式角色不能存取本身資料庫之外的中繼資料,因為應用程式角色與伺服器層級主體沒有關聯。In [SQL Server]SQL Server, an application role cannot access metadata outside its own database because application roles are not associated with a server-level principal. 這是和舊版 [SQL Server]SQL Server不同的一項行為變更。This is a change of behavior from earlier versions of [SQL Server]SQL Server. 設定這個全域旗標可停用新限制,使應用程式角色可以存取伺服器層級的中繼資料。Setting this global flag disables the new restrictions, and allows for application roles to access server-level metadata.

範圍:只限全域Scope: global only
46184618 將 TokenAndPermUserStore 快取存放區中的項目數限制為 1,024。Limits the number of entries in the TokenAndPermUserStore cache store to 1,024. 與追蹤旗標 4610 一起使用時,TokenAndPermUserStore 快取存放區中的項目數會增加至 8,192。When used together with trace flag 4610 increases the number of entries in the TokenAndPermUserStore cache store to 8,192. 如需詳細資訊,請參閱此 Microsoft 支援服務文章 (機器翻譯)。For more information, see this Microsoft Support article.

範圍: 只限全域Scope: global only
50045004 暫停 TDE 加密掃描,並導致加密掃描背景工作結束,而不執行任何工作。Pauses TDE encryption scan and causes encryption scan worker to exit without doing any work. 資料庫會繼續處於加密狀態 (加密進行中)。The database will continue to be in encrypting state (encryption in progress). 若要繼續重新加密掃描,請停用追蹤旗標 5004 並執行 ALTER DATABASE <database_name> SET ENCRYPTION ON。To resume re-encryption scan, disable trace flag 5004 and run ALTER DATABASE <database_name> SET ENCRYPTION ON.

範圍: 只限全域Scope: global only
64986498 在沒有足夠記憶體可用時啟用多個大型查詢編譯,以獲得大型閘道的存取權。Enables more than one large query compilation to gain access to the big gateway when there is sufficient memory available. 它是以 SQL Server 目標記憶體的百分之 80 為基礎,且每 25 GB 的記憶體允許一個大型查詢編譯。It is based on the 80 percentage of SQL Server Target Memory, and it allows for one large query compilation per 25 gigabytes (GB) of memory. 如需詳細資訊,請參閱此 Microsoft 支援服務文章 (機器翻譯)。For more information, see this Microsoft Support article.

注意:SQL Server 2014 (12.x)SQL Server 2014 (12.x) SP2 和 SQL Server 2016 (13.x)SQL Server 2016 (13.x) 開始,此行為由引擎控制,追蹤旗標 6498 沒有任何作用。Note: Starting with SQL Server 2014 (12.x)SQL Server 2014 (12.x) SP2 and SQL Server 2016 (13.x)SQL Server 2016 (13.x) this behavior is controlled by the engine and trace flag 6498 has no effect.

範圍:只限全域Scope: global only
65276527 在 CLR 整合中第一次發生記憶體不足的例外狀況時停用記憶體傾印的產生。Disables generation of a memory dump on the first occurrence of an out-of-memory exception in CLR integration. 依預設,在 CLR 中第一次發生記憶體不足的例外狀況時, [SQL Server]SQL Server 會產生小型的記憶體傾印。By default, [SQL Server]SQL Server generates a small memory dump on the first occurrence of an out-of-memory exception in the CLR. 追蹤旗標的行為如下:The behavior of the trace flag is as follows:
  • 如果這是用來當做啟動追蹤旗標,則絕不會產生記憶體傾印。If this is used as a startup trace flag, a memory dump is never generated. 不過,如果使用了其他的追蹤旗標,就可能產生記憶體傾印。However, a memory dump may be generated if other trace flags are used.
  • 如果在執行的伺服器上啟用這個追蹤旗標,則不會從該點開始自動產生記憶體傾印。If this trace flag is enabled on a running server, a memory dump will not be automatically generated from that point on. 不過,如果因為在 CLR 中發生記憶體不足的例外狀況而已經產生記憶體傾印,這個追蹤旗標就不會有任何效果。However, if a memory dump has already been generated due to an out-of-memory exception in the CLR, this trace flag will have no effect.

範圍:只限全域Scope: global only
65326532 使 SQL Server 2012 (11.x)SQL Server 2012 (11.x)SQL Server 2014 (12.x)SQL Server 2014 (12.x) 中具有空間資料類型的查詢作業效能改善。Enables performance improvement of query operations with spatial data types in SQL Server 2012 (11.x)SQL Server 2012 (11.x) and SQL Server 2014 (12.x)SQL Server 2014 (12.x). 所提升的效能會依據設定、查詢類型和物件而有所不同。The performance gain will vary, depending on the configuration, the types of queries, and the objects. 如需詳細資訊,請參閱此 Microsoft 支援服務文章 (機器翻譯)。For more information, see this Microsoft Support article.

注意:SQL Server 2016 (13.x)SQL Server 2016 (13.x) 開始,此行為由引擎控制,追蹤旗標 6532 沒有任何作用。Note: Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) this behavior is controlled by the engine and trace flag 6532 has no effect.

範圍:全域和工作階段Scope: global and session
65336533 使 SQL Server 2012 (11.x)SQL Server 2012 (11.x)SQL Server 2014 (12.x)SQL Server 2014 (12.x) 中具有空間資料類型的查詢作業效能改善。Enables performance improvement of query operations with spatial data types in SQL Server 2012 (11.x)SQL Server 2012 (11.x) and SQL Server 2014 (12.x)SQL Server 2014 (12.x). 所提升的效能會依據設定、查詢類型和物件而有所不同。The performance gain will vary, depending on the configuration, the types of queries, and the objects. 如需詳細資訊,請參閱此 Microsoft 支援服務文章 (機器翻譯)。For more information, see this Microsoft Support article.

注意:SQL Server 2016 (13.x)SQL Server 2016 (13.x) 開始,此行為由引擎控制,追蹤旗標 6533 沒有任何作用。Note: Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) this behavior is controlled by the engine and trace flag 6533 has no effect.

範圍:全域和工作階段Scope: global and session
65346534 使 SQL Server 2012 (11.x)SQL Server 2012 (11.x)SQL Server 2014 (12.x)SQL Server 2014 (12.x)SQL Server 2016 (13.x)SQL Server 2016 (13.x) 中具有空間資料類型的查詢作業效能改善。Enables performance improvement of query operations with spatial data types in SQL Server 2012 (11.x)SQL Server 2012 (11.x), SQL Server 2014 (12.x)SQL Server 2014 (12.x) and SQL Server 2016 (13.x)SQL Server 2016 (13.x). 所提升的效能會依據設定、查詢類型和物件而有所不同。The performance gain will vary, depending on the configuration, the types of queries, and the objects. 如需詳細資訊,請參閱此 Microsoft 支援服務文章 (機器翻譯)。For more information, see this Microsoft Support article.

範圍:只限全域Scope: global only
73147314 強制將有效位數/小數位數不明的數值視為帶有 OLE DB 提供者的雙精度浮點數值。Forces NUMBER values with unknown precision/scale to be treated as double values with OLE DB provider. 如需詳細資訊,請參閱此 Microsoft 支援服務文章 (機器翻譯)。For more information, see this Microsoft Support article.

範圍:全域和工作階段Scope: global and session
74127412 啟用分析基礎結構的輕量型查詢執行統計資料。Enables the lightweight query execution statistics profiling infrastructure. 如需詳細資訊,請參閱此 Microsoft 支援服務文章 (機器翻譯)。For more information, see this Microsoft Support article.

範圍:只限全域Scope: global only
74717471 同時在單一資料表上對不同的統計資料執行多個 UPDATE STATISTICSEnables running multiple UPDATE STATISTICS for different statistics on a single table concurrently. 如需詳細資訊,請參閱此 Microsoft 支援服務文章 (機器翻譯)。For more information, see this Microsoft Support article.

注意: 此追蹤旗標適用於 SQL Server 2014 (12.x)SQL Server 2014 (12.x) SP1 及更新的組建。Note: This trace flag applies to SQL Server 2014 (12.x)SQL Server 2014 (12.x) SP1 and higher builds.

範圍:只限全域Scope: global only
77457745 強制查詢存放區在資料庫關機時不排清資料到磁碟。Forces Query Store to not flush data to disk on database shutdown.

注意: 使用這項追蹤可能會導致先前未排清至磁碟的查詢存放區資料在關機時遺失。Note: Using this trace may cause Query Store data not previously flushed to disk to be lost in case of shutdown. 如果要將 [SQL Server]SQL Server 關機,可以使用 SHUTDOWN WITH NOWAIT 命令取代此追蹤旗標來強制立即關機。For a [SQL Server]SQL Server shutdown, the command SHUTDOWN WITH NOWAIT can be used instead of this trace flag to force an immediate shutdown.

範圍:只限全域Scope: global only
77527752 啟用非同步載入查詢存放區。Enables asynchronous load of Query Store.

注意: 如果 [SQL Server]SQL Server 有大量與查詢存放區同步載入 (預設行為) 相關的 QDS_LOADDB 等待工作,請使用此追蹤旗標。Note: Use this trace flag if [SQL Server]SQL Server is experiencing high number of QDS_LOADDB waits related to Query Store synchronous load (default behavior).

範圍:只限全域Scope: global only
78067806 SQL Server ExpressSQL Server Express上啟用專用管理員連接 (DAC)。Enables a dedicated administrator connection (DAC) on SQL Server ExpressSQL Server Express. 依預設, SQL Server ExpressSQL Server Express 上不會保留任何 DAC 資源。By default, no DAC resources are reserved on SQL Server ExpressSQL Server Express. 如需詳細資訊,請參閱 資料庫管理員的診斷連接For more information, see Diagnostic Connection for Database Administrators.

範圍:只限全域Scope: global only
80118011 停用資源監視器的信號緩衝區。Disable the ring buffer for Resource Monitor. 如需詳細資訊,請參閱此 Microsoft 支援服務文章 (機器翻譯)。For more information, see this Microsoft Support article.

範圍:全域和工作階段Scope: global and session
80128012 停用排程器的信號緩衝區。Disable the ring buffer for schedulers. 如需詳細資訊,請參閱此 Microsoft 支援服務文章 (機器翻譯)。For more information, see this Microsoft Support article.

範圍:只限全域Scope: global only
80158015 停用自動偵測及 NUMA 設定。Disable auto-detection and NUMA setup. 如需詳細資訊,請參閱此 Microsoft 支援服務文章 (英文)。For more information, see this Microsoft Support article.

範圍:只限全域Scope: global only
80188018 停用例外狀況信號緩衝區。Disable the exception ring buffer. 如需詳細資訊,請參閱此 Microsoft 支援服務文章 (機器翻譯)。For more information, see this Microsoft Support article.

範圍:只限全域Scope: global only
80198019 停用例外狀況信號緩衝區的堆疊集合。Disable stack collection for the exception ring buffer. 如需詳細資訊,請參閱此 Microsoft 支援服務文章 (機器翻譯)。For more information, see this Microsoft Support article.

範圍:只限全域Scope: global only
80208020 停用工作集監視。Disable working set monitoring. 如需詳細資訊,請參閱此 Microsoft 支援服務文章 (機器翻譯)。For more information, see this Microsoft Support article.

範圍:只限全域Scope: global only
80328032 將快取限制參數還原為 SQL Server 2005SQL Server 2005 RTM 設定,這項設定通常會允許使用更大的快取。Reverts the cache limit parameters to the SQL Server 2005SQL Server 2005 RTM setting which in general allows caches to be larger. 當經常重複使用的快取項目無法納入快取中,以及 針對特定工作負載最佳化伺服器組態選項 無法解決計畫快取的問題時,請使用這項設定。Use this setting when frequently reused cache entries do not fit into the cache and when the optimize for ad hoc workloads Server Configuration Option has failed to resolve the problem with plan cache.

警告: 如果大型快取為其他記憶體取用者 (例如緩衝集區) 提供較少的記憶體,追蹤旗標 8032 可能會導致效能降低。WARNING: Trace flag 8032 can cause poor performance if large caches make less memory available for other memory consumers, such as the buffer pool.

範圍:只限全域Scope: global only
80488048 將 NUMA 分割記憶體物件轉換成 CPU 分割記憶體物件。Converts NUMA partitioned memory objects into CPU partitioned. 如需詳細資訊,請參閱此 Microsoft 支援服務文章 (英文)。For more information, see this Microsoft Support article.

注意:SQL Server 2014 (12.x)SQL Server 2014 (12.x) SP2 和 SQL Server 2016 (13.x)SQL Server 2016 (13.x) 開始,此行為由引擎控制,追蹤旗標 8048 沒有任何作用。Note: Starting with SQL Server 2014 (12.x)SQL Server 2014 (12.x) SP2 and SQL Server 2016 (13.x)SQL Server 2016 (13.x) this behavior is controlled by the engine and trace flag 8048 has no effect.

範圍:只限全域Scope: global only
80758075 收到 64 位元 SQL Server 2012 (11.x)SQL Server 2012 (11.x)SQL Server 2014 (12.x)SQL Server 2014 (12.x) 的記憶體頁面配置錯誤時,請減少 VAS 片段。Reduces VAS fragmentation when you receive memory page allocation errors on a 64-bit SQL Server 2012 (11.x)SQL Server 2012 (11.x) or SQL Server 2014 (12.x)SQL Server 2014 (12.x). 如需詳細資訊,請參閱此 Microsoft 支援服務文章 (機器翻譯)。For more information, see this Microsoft Support article.

注意: 此追蹤旗標適用於 SQL Server 2012 (11.x)SQL Server 2012 (11.x)SQL Server 2014 (12.x)SQL Server 2014 (12.x) RTM CU10 以及 SQL Server 2014 (12.x)SQL Server 2014 (12.x) SP1 CU3。Note: This trace flag applies to SQL Server 2012 (11.x)SQL Server 2012 (11.x), SQL Server 2014 (12.x)SQL Server 2014 (12.x) RTM CU10, and SQL Server 2014 (12.x)SQL Server 2014 (12.x) SP1 CU3. SQL Server 2016 (13.x)SQL Server 2016 (13.x) 開始,此行為由引擎控制,且追蹤旗標 8075 沒有任何作用。Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) this behavior is controlled by the engine and trace flag 8075 has no effect.

範圍:只限全域Scope: global only
80798079 允許 SQL Server 2014 (12.x)SQL Server 2014 (12.x) SP2 在每一 NUMA 節點回報 8 個或更多 CPU 的系統上質詢硬體配置及自動設定軟體式 NUMA。Allows SQL Server 2014 (12.x)SQL Server 2014 (12.x) SP2 to interrogate the hardware layout and automatically configure Soft-NUMA on systems reporting 8 or more CPUs per NUMA node. 自動軟體式 NUMA 會以感知超執行緒 (HT/邏輯處理器) 的方式運作。The automatic Soft-NUMA behavior is Hyperthread (HT/logical processor) aware. 其他節點的分割和建立可藉由增加接聽程式數目、調整以及網路和加密功能,來調整背景處理的規模。The partitioning and creation of additional nodes scales background processing by increasing the number of listeners, scaling and network and encryption capabilities.

注意: 此追蹤旗標適用於 SQL Server 2014 (12.x)SQL Server 2014 (12.x) SP2。Note: This trace flag applies to SQL Server 2014 (12.x)SQL Server 2014 (12.x) SP2. SQL Server 2016 (13.x)SQL Server 2016 (13.x) 開始,此行為由引擎控制,且追蹤旗標 8079 沒有任何作用。Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) this behavior is controlled by the engine and trace flag 8079 has no effect.

範圍:只限全域Scope: global only
82078207 啟用異動複寫和 CDC 的 singleton 更新。Enables singleton updates for Transactional Replication and CDC. 訂閱者的更新可以複寫為 DELETE 和 INSERT 配對。Updates to subscribers can be replicated as a DELETE and INSERT pair. 這可能不符合商務規則,例如引發 UPDATE 觸發程序。This might not meet business rules, such as firing an UPDATE trigger. 使用追蹤旗標 8207 時,只影響一個資料列的唯一資料行更新 (單一更新) 會複寫為 UPDATE,而不是 DELETE 或 INSERT 配對。With trace flag 8207, an update to a unique column that affects only one row (a singleton update) is replicated as an UPDATE and not as a DELETE or INSERT pair. 如果更新影響存在唯一條件約束的資料行,或是更新影響多個資料列,更新仍會複寫為 DELETE 或 INSERT 配對。If the update affects a column on which a unique constraint exists, or if the update affects multiple rows, the update is still replicated as a DELETE or INSERT pair. 如需詳細資訊,請參閱此 Microsoft 支援服務文章 (機器翻譯)。For more information, see this Microsoft Support article.

範圍:只限全域Scope: global only
87218721 當自動更新統計資料執行時回報至錯誤記錄檔。Reports to the error log when auto-update statistics executes. 如需詳細資訊,請參閱此 Microsoft 支援服務文章For more information, see this Microsoft Support article.

範圍:只限全域Scope: global only
87448744 停用預先擷取巢狀迴圈運算子。Disable pre-fetching for the Nested Loop operator. 如需詳細資訊,請參閱此 Microsoft 支援服務文章 (機器翻譯)。For more information, see this Microsoft Support article.

注意: 如不當使用此追蹤旗標,當 [SQL Server]SQL Server 執行包含巢狀迴圈運算子的計劃時,可能會有額外的實體讀取作業。Note: Incorrect use of this trace flag may cause additional physical reads when [SQL Server]SQL Server executes plans that contain the Nested Loops operator.

範圍:全域和工作階段Scope: global and session
90249024 將全域記錄集區記憶體物件轉換成 NUMA 節點分割的記憶體物件。Converts a global log pool memory object into NUMA node partitioned memory object. 如需詳細資訊,請參閱此 Microsoft 支援服務文章 (英文)。For more information, see this Microsoft Support article.

注意:SQL Server 2012 (11.x)SQL Server 2012 (11.x) SP3 和 SQL Server 2014 (12.x)SQL Server 2014 (12.x) SP1 開始,此行為由引擎控制,追蹤旗標 9024 沒有任何作用。Note: Starting with SQL Server 2012 (11.x)SQL Server 2012 (11.x) SP3 and SQL Server 2014 (12.x)SQL Server 2014 (12.x) SP1 this behavior is controlled by the engine and trace flag 9024 has no effect.

範圍:只限全域Scope: global only
93479347 停用排序運算子的批次模式。Disables batch mode for sort operator. SQL Server 2016 (13.x)SQL Server 2016 (13.x) 導入新批次模式排序運算子,將提升許多分析查詢的效能。 introduced a new batch mode sort operator that boosts performance for many analytical queries. 如需詳細資訊,請參閱此 Microsoft 支援服務文章 (機器翻譯)。For more information, see this Microsoft Support article.

範圍:全域或工作階段或查詢Scope: global or session or query
93499349 停用前 N 個排序運算子的批次模式。Disables batch mode for top N sort operator. SQL Server 2016 (13.x)SQL Server 2016 (13.x) 導入新批次模式前幾個排序運算子,將提升許多分析查詢的效能。 introduced a new batch mode top sort operator that boosts performance for many analytical queries.

範圍:全域或工作階段或查詢Scope: global or session or query
93899389 啟用批次模式運算子的額外動態記憶體授與。Enables additional dynamic memory grant for batch mode operators. 如果查詢無法取得其需要的所有記憶體,它會將資料溢出到 tempdb,因而產生額外的 I/O,並可能會影響查詢效能。If a query does not get all the memory it needs, it spills data to tempdb, incurring additional I/O and potentially impacting query performance. 如果啟用動態記憶體授與追蹤旗標,批次模式運算子可能會要求額外的記憶體,在有額外的記憶體可用時,可避免溢出到 tempdb。If the dynamic memory grant trace flag is enabled, a batch mode operator may ask for additional memory and avoid spilling to tempdb if additional memory is available. 如需詳細資訊,請參閱記憶體管理架構指南的<min memory per query 的作用>一節。For more information, see the Effects of min memory per query section of the Memory Management Architecture Guide.

範圍:全域或工作階段Scope: global or session
93989398 停用自適性聯結運算子可讓選擇的雜湊聯結或巢狀迴圈聯結方法延後到已掃描第一個輸入之後,如 SQL Server 2017 (14.x)SQL Server 2017 (14.x) 中所述。Disables Adaptive Join operator that enables the choice of a Hash join or Nested Loops join method to be deferred until the after the first input has been scanned, as introduced in SQL Server 2017 (14.x)SQL Server 2017 (14.x). 如需詳細資訊,請參閱此 Microsoft 支援服務文章 (機器翻譯)。For more information, see this Microsoft Support article.

注意: 請確定您已徹底測試此選項後再部署到生產環境。Note: Please ensure that you thoroughly test this option, before rolling it into a production environment.

範圍: 全域、工作階段和查詢Scope: global and session and query
94539453 停用批次模式執行。Disables batch mode execution. 如需詳細資訊,請參閱此 Microsoft 支援服務文章 (機器翻譯)。For more information, see this Microsoft Support article.

注意: 請確定您已徹底測試此選項後再部署到生產環境。Note: Please ensure that you thoroughly test this option, before rolling it into a production environment.

範圍: 全域、工作階段和查詢Scope: global and session and query
94719471 SQL Server 2014 (12.x)SQL Server 2014 (12.x)SQL Server 2017SQL Server 2017 版本的查詢最佳化工具基數評估模型下,導致 [SQL Server]SQL Server 針對單一資料表篩選條件以最少的選擇性產生計劃。Causes [SQL Server]SQL Server to generate a plan using minimum selectivity for single-table filters, under the query optimizer cardinality estimation model of SQL Server 2014 (12.x)SQL Server 2014 (12.x) through SQL Server 2017SQL Server 2017 versions.

SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1 開始,若要在查詢層級完成此操作,請新增 USE HINT 'ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES' 查詢提示,而不要使用此追蹤旗標。Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1, to accomplish this at the query level, add the USE HINT 'ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES' query hint instead of using this trace flag.

注意: 請確定您已徹底測試此選項後再部署到生產環境。Note: Please ensure that you thoroughly test this option, before rolling it into a production environment.

注意: 此追蹤旗標不適用於 CE 70 版本。Note: This trace flag does not apply to CE version 70. 請改用追蹤旗標 4137。Use trace flag 4137 instead.

範圍:全域或工作階段或查詢Scope: global or session or query
94769476 SQL Server 2014 (12.x)SQL Server 2014 (12.x)SQL Server 2017SQL Server 2017 版本的查詢最佳化工具基數估計模型下,導致 [SQL Server]SQL Server 使用簡易內含項目假設產生計畫,而不使用預設的基底內含項目假設。Causes [SQL Server]SQL Server to generate a plan using the Simple Containment assumption instead of the default Base Containment assumption, under the query optimizer cardinality estimation model of SQL Server 2014 (12.x)SQL Server 2014 (12.x) through SQL Server 2017SQL Server 2017 versions. 如需詳細資訊,請參閱此 Microsoft 支援服務文章 (機器翻譯)。For more information, see this Microsoft Support article.

SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1 開始,若要在查詢層級完成此操作,請新增 USE HINT 'ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS' 查詢提示,而不要使用此追蹤旗標。Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1, to accomplish this at the query level, add the USE HINT 'ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS' query hint instead of using this trace flag.

注意: 請確定您已徹底測試此選項後再部署到生產環境。Note: Please ensure that you thoroughly test this option, before rolling it into a production environment.

範圍:全域或工作階段或查詢Scope: global or session or query
94819481 可讓您將查詢最佳化工具基數估計模型設定為 SQL Server 2012 (11.x)SQL Server 2012 (11.x) 和更舊版本,而不根據資料庫的相容性層級。Enables you to set the query optimizer cardinality estimation model to the SQL Server 2012 (11.x)SQL Server 2012 (11.x) and earlier versions, irrespective of the compatibility level of the database. 如需詳細資訊,請參閱此 Microsoft 支援服務文章 (機器翻譯)。For more information, see Microsoft Support article.

SQL Server 2016 (13.x)SQL Server 2016 (13.x) 開始,若要在資料庫層級完成此操作,請參閱 ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL) 中的 LEGACY_CARDINALITY_ESTIMATION 選項。Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x), to accomplish this at the database level, see the LEGACY_CARDINALITY_ESTIMATION option in ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL).

SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1 開始,若要在查詢層級完成此操作,請新增 USE HINT 'FORCE_LEGACY_CARDINALITY_ESTIMATION' 查詢提示,而不要使用此追蹤旗標。Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1, to accomplish this at the query level, add the USE HINT 'FORCE_LEGACY_CARDINALITY_ESTIMATION' query hint instead of using this trace flag.

範圍:全域或工作階段或查詢Scope: global or session or query
94859485 停用 DBCC SHOW_STATISTICS 的 SELECT 權限。Disables SELECT permission for DBCC SHOW_STATISTICS.

範圍:只限全域Scope: global only
94889488 使用 SQL Server 2012 (11.x)SQL Server 2012 (11.x)SQL Server 2017SQL Server 2017 版本的查詢最佳化工具基數估計模型時,將資料表值函式的固定估計設為預設值 1 (對應 SQL Server 2008 R2SQL Server 2008 R2 與更舊版本的查詢最佳化工具基數估計模型下的預設值)。Sets the fixed estimation for Table Valued Functions to the default of 1 (corresponding to the default under the query optimizer cardinality estimation model of SQL Server 2008 R2SQL Server 2008 R2 and earlier versions), when using the query optimizer cardinality estimation model of SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL Server 2017SQL Server 2017 versions.

範圍:全域或工作階段或查詢Scope: global or session or query
94959495 在 INSERT...SELECT 作業的插入期間停用平行處理原則,這項規則同時套用至使用者和暫存資料表。Disables parallelism during insertion for INSERT...SELECT operations and it applies to both user and temporary tables. 如需詳細資訊,請參閱此 Microsoft 支援服務文章 (機器翻譯)For more information, see Microsoft Support article

範圍:全域或工作階段Scope: global or session
95679567 在自動植入期間,針對 Always On 可用性群組啟用資料流的壓縮。Enables compression of the data stream for Always On Availability Groups during automatic seeding. 在自動植入期間,壓縮可以大幅縮短傳輸時間,而且會增加處理器負載。Compression can significantly reduce the transfer time during automatic seeding and will increase the load on the processor. 如需詳細資訊,請參閱自動初始化 Always On 可用性群組微調可用性群組的壓縮For more information, see Automatically initialize Always On availability group and Tune compression for availability group.

範圍:全域或工作階段Scope: global or session
95919591 在 Always On 可用性群組中停用記錄區塊壓縮。Disables log block compression in Always On Availability Groups. SQL Server 2012 (11.x)SQL Server 2012 (11.x)SQL Server 2014 (12.x)SQL Server 2014 (12.x) 中的同步和非同步複本預設會使用記錄區塊壓縮。Log block compression is the default behavior used with both synchronous and asynchronous replicas in SQL Server 2012 (11.x)SQL Server 2012 (11.x) and SQL Server 2014 (12.x)SQL Server 2014 (12.x). SQL Server 2016 (13.x)SQL Server 2016 (13.x) 中,只有非同步複本會使用壓縮。In SQL Server 2016 (13.x)SQL Server 2016 (13.x), compression is only used with asynchronous replica.

範圍:全域或工作階段Scope: global or session
95929592 啟用同步可用性群組的記錄資料流壓縮。Enables log stream compression for synchronous availability groups. 因為壓縮會增加延遲,所以在同步可用性群組上預設會停用這項功能。This feature is disabled by default on synchronous availability groups because compression adds latency. 如需詳細資訊,請參閱 微調可用性群組的壓縮For more information, see Tune compression for availability group.

範圍:全域或工作階段Scope: global or session
99299929 將記憶體中的檢查點檔案大小減少至各 1 MB。Reduces the In-Memory checkpoint files to 1 MB each. 如需詳細資訊,請參閱此 Microsoft 支援服務文章 (機器翻譯)。For more information, see this Microsoft Support article.

範圍:只限全域Scope: global only
99399939 在參考記憶體最佳化資料表或資料表變數的 DML 作業中,啟用平行計畫和平行掃描記憶體最佳化資料表和資料表變數 (只要它們不是 SQL Server 2016 (13.x)SQL Server 2016 (13.x) 中 DML 作業的目標)。Enables parallel plans and parallel scan of memory-optimized tables and table variables in DML operations that reference memory-optimized tables or table variables, as long as they are not the target of the DML operation in SQL Server 2016 (13.x)SQL Server 2016 (13.x). 如需詳細資訊,請參閱此 Microsoft 支援服務文章 (英文)。For more information, see this Microsoft Support article.

注意: 如果同時也明確啟用追蹤旗標 4199,則不需要追蹤旗標 9939。Note: Trace flag 9939 is not needed if trace flag 4199 is also explicitly enabled.

範圍:全域或工作階段或查詢Scope: global or session or query
1020410204 在資料行存放區索引重組期間停用合併/重新壓縮。Disables merge/recompress during columnstore index reorganization. SQL Server 2016 (13.x)SQL Server 2016 (13.x) 中,當重新組織資料行存放區索引時,新功能會自動將任何小型的壓縮資料列群組合併成較大的壓縮資料列群組,並重新壓縮有大量已刪除資料列的任何資料列群組。In SQL Server 2016 (13.x)SQL Server 2016 (13.x), when a columnstore index is reorganized, there is new functionality to automatically merge any small compressed rowgroups into larger compressed rowgroups, as well as recompressing any rowgroups that have a large number of deleted rows.

注意: 追蹤旗標 10204 不適用於在記憶體最佳化資料表上建立的資料行存放區索引。Note: Trace flag 10204 does not apply to columnstore indexes which are created on memory-optimized tables.

範圍:全域或工作階段Scope: global or session
1031610316 內部記憶體最佳化暫存時態表上建立預設索引和額外的索引。Enables creation of additional indexes on internal memory-optimized staging temporal table, beside the default one. 如果您有特定的查詢模式,而其中包含預設索引未涵蓋的資料行,您可以考慮加入額外的索引。If you have specific query pattern that includes columns which are not covered by the default index you may consider adding additional ones.

注意: 記憶體最佳化資料表的系統建立版本時態表,是專為提供高交易輸送量所設計。Note: System-versioned temporal tables for Memory-Optimized Tables are designed to provide high transactional throughput. 請留意,對負責更新或刪除目前資料表資料列的 DML 作業來說,建立額外的索引可能會造成額外負荷。Please be aware that creating additional indexes may introduce overhead for DML operations that update or delete rows in the current table. 使用額外的索引,您的目標應該是在時態性查詢和 DML 額外負荷之間尋求適當的平衡。With the additional indexes you should aim to find the right balance between performance of temporal queries and additional DML overhead.

範圍:全域或工作階段Scope: global or session
1102311023 針對未在 UPDATE STATISTICS 陳述式中明確指定採樣速率的所有後續統計資料更新,停用上次保存的採樣速率。Disables the use of the last persisted sample rate for all subsequent statistics update, where a sample rate is not specified explicitly as part of the UPDATE STATISTICS statement. 如需詳細資訊,請參閱此 Microsoft 支援服務文章 (機器翻譯)。For more information, see this Microsoft Support article.

範圍:全域或工作階段Scope: global or session
1102411024 當任何分割區的修改次數超過總閾值時,使自動更新統計資料功能觸發。Enables triggering the auto update of statistics when the modification count of any partition exceeds the local threshold. 如需詳細資訊,請參閱此 Microsoft 支援服務文章 (機器翻譯)。For more information, see this Microsoft Support article.

注意: 此追蹤旗標適用於 SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP2、SQL Server 2017 (14.x)SQL Server 2017 (14.x) CU3,和更新的組建。Note: This trace flag applies to SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP2, SQL Server 2017 (14.x)SQL Server 2017 (14.x) CU3, and higher builds.

範圍:全域或工作階段Scope: global or session

RemarksRemarks

[SQL Server]SQL Server 中有三種類型的追蹤旗標:查詢、工作階段和全域。In [SQL Server]SQL Server, there are three types of trace flags: query, session and global. 查詢追蹤旗標可用於特定查詢的內容。Query trace flags are active for the context of a specific query. 工作階段追蹤旗標用於某個連接,而且只會在該連接顯示出來。Session trace flags are active for a connection and are visible only to that connection. 全域追蹤旗標是設在伺服器層級,只要是該伺服器上的連接,都看得到它們。Global trace flags are set at the server level and are visible to every connection on the server. 某些旗標只能啟用為全域旗標,某些則可以啟用為全域或工作階段範圍。Some flags can only be enabled as global, and some can be enabled at either global or session scope.

適用的規則如下:The following rules apply:

  • 全域追蹤旗標必須全域啟用,A global trace flag must be enabled globally. 否則追蹤旗標就沒有效果。Otherwise, the trace flag has no effect. 建議您使用 -T 命令列選項,在啟動時啟用全域追蹤旗標。We recommend that you enable global trace flags at startup, by using the -T command line option. 這可確保追蹤旗標在伺服器重新啟動後仍維持使用中。This ensures the trace flag remains active after a server restart.
  • 如果追蹤旗標具有全域、工作階段或查詢的範圍,可以使用適當的範圍加以啟用。If a trace flag has either global, session or query scope, it can be enabled with the appropriate scope. 以工作階段層級啟用的追蹤旗標絕不會影響其他工作階段,而且當開啟該工作階段的 SPID 登出時,該追蹤旗標的效果也隨之消失。A trace flag that is enabled at the session level never affects another session, and the effect of the trace flag is lost when the SPID that opened the session logs out.

請利用下列方法之一,將追蹤旗標設為開啟或關閉:Trace flags are set on or off by using either of the following methods:

  • 使用 DBCC TRACEON 和 DBCC TRACEOFF 命令。Using the DBCC TRACEON and DBCC TRACEOFF commands.
    例如,若要全域啟用 2528 追蹤旗標,請搭配使用 DBCC TRACEON 和 -1 引數:DBCC TRACEON (2528, -1)For example, to enable the 2528 trace flag globally, use DBCC TRACEON with the -1 argument: DBCC TRACEON (2528, -1). 使用 DBCC TRACEON 啟用的全域追蹤旗標,在伺服器重新啟動後就會失去效果。The effect of enabling a global trace flag with DBCC TRACEON is lost on server restart. 若要關閉全域追蹤旗標,請搭配使用 DBCC TRACEOFF 和 -1 引數。To turn off a global trace flag, use DBCC TRACEOFF with the -1 argument.
  • 使用 -T 啟動選項,指定應在啟動期間設定追蹤旗標。Using the -T startup option to specify that the trace flag be set on during startup.
    -T 啟動選項會全域啟用追蹤旗標。The -T startup option enables a trace flag globally. 您不能利用啟動選項啟用工作階段層級的追蹤旗標。You cannot enable a session-level trace flag by using a startup option. 這可確保追蹤旗標在伺服器重新啟動後仍維持使用中。This ensures the trace flag remains active after a server restart. 如需啟動選項的詳細資訊,請參閱 Database Engine 服務啟動選項For more information about startup options, see Database Engine Service Startup Options.
  • 在查詢層級,請使用 QUERYTRACEON 查詢提示 (機器翻譯)。At the query level, by using the QUERYTRACEON query hint.

使用 DBCC TRACESTATUS 命令可判斷目前有哪些使用中的追蹤旗標。Use the DBCC TRACESTATUS command to determine which trace flags are currently active.

範例Examples

下列範例使用 DBCC TRACEON 在伺服器層級為所有工作階段設定追蹤旗標 3205。The following example sets trace flag 3205 on for all sessions at the server level by using DBCC TRACEON.

DBCC TRACEON (3205,-1);  

您可以啟用由特定查詢的追蹤旗標 4199 和 4137 控制的所有計畫影響的 Hotfix。You can enable all plan-affecting hotfixes controlled by trace flags 4199 and 4137 for a particular query.

SELECT x FROM correlated WHERE f1 = 0 AND f2 = 1 OPTION (QUERYTRACEON 4199, QUERYTRACEON 4137)

另請參閱See Also

資料類型 (Transact-SQL)Data Types (Transact-SQL)
DBCC INPUTBUFFER (Transact-SQL)DBCC INPUTBUFFER (Transact-SQL)
DBCC OUTPUTBUFFER (Transact-SQL)DBCC OUTPUTBUFFER (Transact-SQL)
DBCC TRACEOFF (Transact-SQL)DBCC TRACEOFF (Transact-SQL)
DBCC TRACEON (Transact-SQL)DBCC TRACEON (Transact-SQL)
DBCC TRACESTATUS (Transact-SQL)DBCC TRACESTATUS (Transact-SQL)
EXECUTE (Transact-SQL)EXECUTE (Transact-SQL)
SELECT (Transact-SQL)SELECT (Transact-SQL)
SET NOCOUNT (Transact-SQL)SET NOCOUNT (Transact-SQL)
ALTER DATABASE SET 選項 (Transact-SQL)ALTER DATABASE SET Options (Transact-SQL)
ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)
查詢提示 (Transact-SQL)Query Hints (Transact-SQL)