Testing on SQL Server 2014, CTP2, version 12.0.1524, imagine you have two databases – one called T1 and one called T2 configured with database compatibility levels as follows:
USE [master]; GO ALTER DATABASE [T1] SET COMPATIBILITY_LEVEL = 120; GO ALTER DATABASE [T2] SET COMPATIBILITY_LEVEL = 110; GO
Now with database T1, we know that using a compatibility level of 120 means we’ll be using the new cardinality estimator (CE) – assuming we’re in the database session context of a new CE database and don’t have a trace flag disabling the new CE behavior.
Executing the following query in the context of T1 does indeed mean we use the new CE:
USE [T1]; GO SELECT [member_no], [provider_no], [category_no] FROM [dbo].[charge] WHERE [charge_no] = 422;
<StmtSimple StatementCompId=”1″ StatementEstRows=”1″ StatementId=”1″ StatementOptmLevel=”TRIVIAL” CardinalityEstimationModelVersion=”120″ StatementSubTreeCost=”0.0032831″ StatementText=”SELECT [member_no],[provider_no],[category_no] FROM [dbo].[charge] WHERE [charge_no]=@1″ StatementType=”SELECT” QueryHash=”0x274BD0D496403EEE” QueryPlanHash=”0x6B5F27FE55FE8A5C” RetrievedFromCache=”true”>
But what if we change the query to be in the context of the T2 database (legacy CE) – but still access data from the T1 database?
USE [T2]; GO SELECT [member_no], [provider_no], [category_no] FROM [T1].[dbo].[charge] WHERE [charge_no] = 422;
Now we see the query used a legacy CE:
<StmtSimple StatementCompId=”1″ StatementEstRows=”1″ StatementId=”1″ StatementOptmLevel=”TRIVIAL” CardinalityEstimationModelVersion=”70″ StatementSubTreeCost=”0.0032831″ StatementText=”SELECT [member_no],[provider_no],[category_no] FROM [T1].[dbo].[charge] WHERE [charge_no]=@1″ StatementType=”SELECT” QueryHash=”0x274BD0D496403EEE” QueryPlanHash=”0x6B5F27FE55FE8A5C” RetrievedFromCache=”true”>
What if the cross-database query is executed from a new CE session context – but the destination is the legacy CE?
USE [T1]; GO SELECT [member_no], [provider_no], [category_no] FROM [T2].[dbo].[charge] WHERE [charge_no] = 422;
In this scenario, the query uses the new CE – based on the database session context – even though the destination database is set to compatibility level 110.
What about accessing data from two databases (rather than my previous example of just accessing data from one database)? The following example results in a legacy CE plan:
USE [T2]; GO SELECT [member_no], [provider_no], [category_no] FROM [T1].[dbo].[charge] WHERE [charge_no] = 422 UNION SELECT [member_no], [provider_no], [category_no] FROM [T2].[dbo].[charge] WHERE [charge_no] = 422; GO
And this query results in a new CE plan:
USE [T1]; GO SELECT [member_no], [provider_no], [category_no] FROM [T1].[dbo].[charge] WHERE [charge_no] = 422 UNION SELECT [member_no], [provider_no], [category_no] FROM [T2].[dbo].[charge] WHERE [charge_no] = 422; GO
So – bottom line – using the new CE doesn’t mean just changing the database compatibility level. Database session context also matters.