By: Ben Snaidero | Updated: 2017-04-27 | Comments | Related: > Query Plans
Problem
I have two SQL Server instances running the same version. Each server has a database with the same schema and similar data, but when executing the same query on each machine I get different explain plans which causes the query to run almost 5x longer on one of the machines. What could be causing this?
Solution
Over the years I have spent a good amount of time looking at explain plans and trying to figure out why the optimizer chose a specific query plan. While small data differences can cause a different explain plan let's assume for this exercise that this isn't the issue. If you've worked through almost any SQL Server upgrade you've no doubt come across issues with the cardinality estimator between versions and in some cases had to put in fixes for specific servers, databases and even individual queries in order to fix an issue with an explain plan the optimizer had generated. Given all the options available it can sometimes be difficult to track down which version of the cardinality estimator the optimizer used to generate your query plan.
Before we get into how you can check this, let's first take a look at some of the different options available for changing the optimizer behavior when generating an explain plan for a query.
Using Trace Flags
By using trace flags we can control the behavior of the optimizer for an individual query using the OPTION (QUERYTRACEON ####) hint. Trace flag 9481 can be used when running SQL Server 2014+ with the database compatibility set to level 120+ and this forces the optimizer to use version 70 (SQL Server 2012) of the cardinality estimator when creating the query plan. Trace flag 2312 works in kind of the opposite way. When running SQL Server 2014+ with the database compatibility set to level set to 110 this trace flag forces the query optimizer to use version 120 (the SQL Server 2014 version) of the cardinality estimator when creating the query plan. These trace flags can also be set using the DBCC TRACEON command. If you want this setting enabled at the session level you can run "DBCC TRACEON (####)" and if you want it set globally for the entire instance you can run "DBCC TRACEON (####,-1)".
Database Level Settings
There are two database level options for changing the optimizer's behavior. First is the compatibility level of the database which when updated will set certain behaviors in the database, in our case the optimizer, to the specified version. There is also the database configuration option "LEGACY_CARDINALITY_ESTIMATION" which when set to ON changes the optimizers cardinality estimator to use the SQL Server 2012 and earlier version regardless of the compatibility level setting of the database.
Other query hints
Introduced in SQL Server 2016 was the USE HINT query argument. This hint is similar to the QUERYTRACEON hint as you specify it using the OPTION clause. "USE HINT('FORCE_LEGACY_CARDINALITY_ESTIMATION')" corresponds to trace flag 9481 and "USE HINT('FORCE_LEGACY_CARDINALITY_ESTIMATION')" corresponds to trace flag 2312. The only difference with this new hint is that it has the benefit of not requiring the sysadmin role in order to use it.
What Version of the Cardinality Estimator is Being Used
In order to capture this information we will use one of my favorite tools, SQL Profiler. The information regarding the cardinality estimator is stored in the explain plan for the query so let's add the "Showplan XML" event to a SQL Profiler session as shown in the screenshot below. You can select any extra columns you think would be helpful, but the data we're interested in is contained in the textdata column. The text you will need to look for in the TextData column of your trace is "CardinalityEstimationModelVersion". Depending on your server/database/query setting this attribute will show you what version of the cardinality estimator was used to compile your query plan.
Now that you have your SQL Profiler session running let's go through a couple examples and confirm we can extract the cardinality estimator version that was being used. For these examples we will only look at options that are not part of the actual query being executed (i.e. HINT related options) as the cardinality estimator version can be figured out just by looking at the query itself and the server/database options that are set. Below is the T-SQL to check the database settings and a simple query of one of the system catalog views. Once this is executed you should see some "Showplan XML" events in your SQL Profiler session. If you search the TextData output or if you are storing your output to a table you can build a query to pull out the CardinalityEstimationModelVersion attribute. Either way you should see version that was used to create the explain plan.
Example 1 - Database Compatibility Mode - 130 and LEGACY_CARDINALITY_ESTIMATION = ON
select name,compatibility_level From sys.databases where database_id=db_id(); select name,value From sys.database_scoped_configurations where name like 'LEGACY_CARDINALITY_ESTIMATION'; select count(1) "IndexCount" from sys.indexes;
T-SQL Output:
name compatibility_level ------------- ------------------- test 130 name value --------------------------------- ------- LEGACY_CARDINALITY_ESTIMATION 1 IndexCount ----------- 152
SQL Profiler TextData Column Output excerpt:
<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.5"
Build="13.0.4001.0"><BatchSequence><Batch><Statements><StmtSimple StatementSubTreeCost=
"0.0203051" StatementEstRows="1" SecurityPolicyApplied="false" StatementOptmLevel="FULL"
QueryHash="0xF104E7B72DCB71E7" QueryPlanHash="0xD3C3A1838C79FE81"
StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="70">
Example 2 - Database Compatibility Mode - 110 and DBCC TRACEON (2312,-1)
select name,compatibility_level From sys.databases where database_id=db_id(); dbcc tracestatus(-1); select count(1) "IndexCount" from sys.indexes;
T-SQL Output:
name compatibility_level ------------- ------------------- test 110 TraceFlag Status Global Session --------- ------ ------ ------- 2312 1 1 0 IndexCount ----------- 152
SQL Profiler TextData Column Output excerpt:
<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.5"
Build="13.0.4001.0"><BatchSequence><Batch><Statements><StmtSimple
StatementSubTreeCost="0.0295354" StatementEstRows="1" SecurityPolicyApplied="false"
StatementOptmLevel="FULL" QueryHash="0xF104E7B72DCB71E7" QueryPlanHash="0xD3C3A1838C79FE81"
StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="120">
As you can see from these two examples this gives you a really easy way to confirm what version of the cardinality estimator your query plan was compiled with.
Next Steps
- Read more options for "USE HINT" query hint argument
- Other tips on trace flags
- Read more information on query explain plans
- Read other tips on SQL Profiler
- Changing compatibility level after an upgrade
About the author
Ben Snaidero has been a SQL Server and Oracle DBA for over 10 years and focuses on performance tuning.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2017-04-27
