Trace Flag 9481
In my previous blog post I showed that when the database compatibility level is 120 or higher, trace flag 9481 can force the query optimiser to use the legacy CE (cardinality estimator model version 70) when compiling an execution plan. This trace flag can be enabled at various scopes and allows us to influence the CE behavior depending on the granularity required:
- Query Scope: The QUERYTRACEON query hint specified in the OPTION clause of a query enables you to use trace flags as hints at the query level and enables certain plan-affecting changes in the query optimiser. Query trace flags are active for the context of a specific query.
USE WideWorldImporters; GO SELECT PurchaseOrderID, OrderDate FROM Purchasing.PurchaseOrders WHERE OrderDate = '2016-05-31' OPTION ( QUERYTRACEON 9481 );
- Session Scope: DBCC TRACEON can be used to enable specific trace flags at the session level. Session trace flags are active for a connection and only directly effect that specific connection:
DBCC FREEPROCCACHE; GO DBCC TRACEON (9481); GO SELECT PurchaseOrderID, OrderDate FROM Purchasing.PurchaseOrders WHERE OrderDate = '2016-05-31'; GO DBCC TRACEOFF (9481); GO
- Global Scope: Global trace flags are set at the server level and affect every connection to the SQL Server instance. Global trace flags can be enabled using the following methods:
- DBCC TRACEON: Using the -1 flag with DBCC TRACEON enables the trace flag globally. Note that this doesn’t persist through restarts and would need to be executed again.
DBCC FREEPROCCACHE; GO DBCC TRACEON (9481, -1); GO SELECT PurchaseOrderID, OrderDate FROM Purchasing.PurchaseOrders WHERE OrderDate = '2016-05-31'; GO DBCC TRACEOFF (9481, -1); GO
- Startup Parameter: In SQL Server Configuration Manager, open the properties of your SQL Server instance and add -T9481 under the ‘Startup Parameters’ tab. This requries a restart of the SQL Server instance to take effect. (Note that the ‘Startup Parameters’ tab was introduced in SQL Server 2012, in previous versions of SQL Server startup parameters were added under the ‘Advanced’ tab.)
3. Registry: It is possible to enable trace flags by editing the registry hive but you don’t want to go there, trust me.
When To Use Trace Flag 9481
Query Scope: You’ve moved (migrated/upgraded) to SQL Server 2014 / 2016, your databases are at compatibility level 120 / 130 and using the new CE, your workload is performing well overall but there are a few regressions where a small number of queries actually perform worse. Use Trace Flag 9481 on a per query basis as a temporary measure until you can tune / rewrite the query so it performs well without the hint.
Session Scope: You’re testing regression of specific stored procedures and don’t want to manually edit the stored procedure code to include a query hint. Bear in mind that if there isn’t already a cached plan, an execution plan will be compiled using the legacy CE and all subsequent executions of the stored procedure will use that same plan, even if they do not have the trace flag enabled.
Global Scope: You plan on migrating all databases of a specific SQL Server instance to SQL Server 2014 / SQL Server 2016, you want to leverage new engine features but you want to continue using the legacy CE as you haven’t tested your workload against the new CE yet. With your databases set to the latest compatibilty level and Trace Flag 9481 enabled globally you can get continue to use the legacy CE across the board.
LEGACY_CARDINALITY_ESTIMATION Database Scoped Configuration
We’ve covered how Trace Flag 9481 could be utilised in SQL Server 2014 / 2016 to force the legacy cardinality estimation model and the different scenarios where you might use this trace flag . We’ve seen that TF 9481 can be enabled at various scopes; query, session and globally. But what if you want to enable the legacy CE at the database scope?
In SQL Server 2014, you were limited to using the database compatibility level to control the CE version at the database scope. Setting the compatibility level to 110 or lower will force the legacy CE (cardinality estimation model version 70).
The problem with lowering the database compatibility level is that you can’t leverage the new engine functionality available under the latest compatibility level.
This problem was solved in SQL Server 2016 with the introduction of Database Scoped Configurations which gives you the ability to make several database-level configuration changes for properties that were previously configured at the instance-level. In particular, the LEGACY_CARDINALITY_ESTIMATION database scoped configuration allows you to set the cardinality estimation model independent of the database compatibility level. This option allows you to leverage all new functionality provided with compatibility level 130 but still use the legacy CE in the odd chance that the latest CE casuses severe query regressions across your workload.
The database scoped configurations are exposed via the following mechanisms:
GUI
Via the options tab of the Database Properties window as follows:
T-SQL
A new T-SQL statement ALTER DATABASE SCOPED CONFIGURATION has been introduced to set these new database level configurations for a particular database:
ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON; GO
DMV
There is a new DMV sys.database_scoped_configurations which exposes the configuration values for the current database:
SELECT configuration_id, name, value, value_for_secondary FROM sys.database_scoped_configurations OPTION (RECOMPILE); GO
FORCE_LEGACY_CARDINALITY_ESTIMATION Query Hint
I’ve shown how Trace Flag 9481 can be used with QUERYTRACEON to enable the trace flag at the query level. There are a few issues with this though:
- Executing a query with the QUERYTRACEON option requires membership in the sysadmin fixed server role.
- Trage flag numbers are not intuitive. Anyone not familiar with a particular trace flag number will not know the behaviour it exhibits.
SQL Server 2016 SP1 introduced a new class of query hints under ‘USE HINT’ which lets you influence the query optimiser without elevated credentials or without being a member of the sysadmin server role. Additionally, the new hint options are descriptive to allow for easier understanding of the behaviour they exhibit:
USE WideWorldImporters;
GO
SELECT PurchaseOrderID, OrderDate
FROM Purchasing.PurchaseOrders
WHERE OrderDate = '2016-05-31'
OPTION (USE HINT('FORCE_LEGACY_CARDINALITY_ESTIMATION'));
This is the logical equivalent of using OPTION (QUERYTRACEON 9481).
Find Execution Plans Using the Legacy CE
Use the following query to look at what cardinality estimator model version is being used by the execution plans in your plan cache:
SELECT DB_NAME(qt.dbid) AS [Database], cp.objtype AS [Cached_Plan_Type],
cp.usecounts AS [Use_Counts],
qp.query_plan.value('(//@CardinalityEstimationModelVersion)[1]','INT') AS [CE_Version],
CONVERT(float, (qp.query_plan.value('(//@EstimateRows)[1]', 'varchar(128)'))) AS [Estimated_Rows],
qs.last_rows AS [Last_Rows],
CONVERT(float, (qp.query_plan.value('(//@EstimateRows)[1]', 'varchar(128)'))) - (qs.last_rows) AS [Estimate_Skew],
qs.total_logical_reads / qs.execution_count AS [Avg_Logical_Reads],
CAST((qs.total_elapsed_time ) / qs.execution_count AS DECIMAL(28, 2)) AS [Avg_Execution_Time(µs)],
CAST((qs.total_worker_time) / qs.execution_count AS DECIMAL(28, 2)) AS [Avg_CPU_Time(µs)],
SUBSTRING(qt.TEXT, (qs.statement_start_offset / 2) + 1, ((CASE qs.statement_end_offset WHEN - 1 THEN DATALENGTH(qt.TEXT) ELSE qs.statement_end_offset END - qs.statement_start_offset ) / 2 ) + 1) AS [Query_Text],
qs.query_hash AS [QueryHash],
qp.query_plan AS [QueryPlan]
FROM sys.dm_exec_cached_plans cp WITH (NOLOCK)
CROSS APPLY sys.dm_exec_query_plan (cp.plan_handle) qp
CROSS APPLY sys.dm_exec_sql_text (cp.plan_handle) qt
INNER JOIN sys.dm_exec_query_stats qs ON qs.plan_handle = cp.plan_handle
WHERE qt.text NOT LIKE '%sys.%'
AND DB_NAME(qt.dbid) = 'User_Database'
ORDER BY [Avg_CPU_Time(µs)] DESC OPTION(RECOMPILE);
GO
Follow @SQLScotsman
SQL Server Musings Of A True Scotsman 
Pingback: Forcing The Legacy Cardinality Estimator – Curated SQL