sqlskills-logo-2015-white.png

Cardinality Estimation Model Version

This post is a continuation of the SQL Server 2014 Cardinality Estimator enhancements exploration series:

In this post I just wanted to step back and discuss how to enable and identify the Cardinality Estimator version, at least as it stands in SQL Server 2014 CTP2.

As you saw for earlier posts, I see the new CE behavior by changing a database’s compatibility level as follows:

USE [master]
GO
ALTER DATABASE [Credit] SET COMPATIBILITY_LEVEL = 120;
GO

Then when I execute a query within the database, looking at the execution plan I see the following attribute in the StmtSimple element:

<StmtSimple StatementCompId=”1″ StatementEstRows=”1600000″ StatementId=”1″ StatementOptmLevel=”FULL” CardinalityEstimationModelVersion=”120″ StatementSubTreeCost=”21.0963″ StatementText=”SELECT    c.[charge_no],&#xD;&#xA;        m.[lastname],&#xD;&#xA;        m.[firstname]&#xD;&#xA;FROM [dbo].[charge] AS c&#xD;&#xA;INNER JOIN [dbo].[member] AS m ON&#xD;&#xA;    c.[member_no] + ‘ ‘  =  m.[member_no] &#xD;&#xA;OPTION (RECOMPILE)” StatementType=”SELECT” QueryHash=”0xF698FF11488A3A3D” QueryPlanHash=”0x41F421A8F51D61CE” RetrievedFromCache=”false”>

Note that if my database compatibility level was for an earlier level, I see the following:

<StmtSimple StatementCompId=”1″ StatementEstRows=”1597640″ StatementId=”1″ StatementOptmLevel=”FULL” CardinalityEstimationModelVersion=”70″ StatementSubTreeCost=”21.0895″ StatementText=”SELECT    c.[charge_no],&#xD;&#xA;        m.[lastname],&#xD;&#xA;        m.[firstname]&#xD;&#xA;FROM [dbo].[charge] AS c&#xD;&#xA;INNER JOIN [dbo].[member] AS m ON&#xD;&#xA;    c.[member_no] + ‘ ‘  =  m.[member_no] &#xD;&#xA;OPTION (RECOMPILE)” StatementType=”SELECT” QueryHash=”0xF698FF11488A3A3D” QueryPlanHash=”0x41F421A8F51D61CE” RetrievedFromCache=”false”>

And I saw that value of 70 for compatibility levels 110 (SQL Server 2012), 100 (SQL Server 2008), and 90 (SQL Server 2005).  Perhaps this indicates that the cardinality estimation model being used pre-SQL Server 2014 CTP2 had its last major change back in the (70) SQL Server 7.0 days. Just a guess though.

More to come…

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.