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],
 m.[lastname],
 m.[firstname]
FROM [dbo].[charge] AS c
INNER JOIN [dbo].[member] AS m ON
 c.[member_no] + ‘ ‘ = m.[member_no] 
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],
 m.[lastname],
 m.[firstname]
FROM [dbo].[charge] AS c
INNER JOIN [dbo].[member] AS m ON
 c.[member_no] + ‘ ‘ = m.[member_no] 
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

New Course: “SQL Server: Common Query Tuning Problems and Solutions – Part 2″

Today, Pluralsight published my latest course, “SQL Server: Common Query Tuning Problems and Solutions – Part 2“. Part two is 2 hours and 17 minutes long

New Course: “SQL Server: Common Query Tuning Problems and Solutions – Part 1”

Pluralsight published my latest course, “SQL Server: Common Query Tuning Problems and Solutions – Part 1“. Part 1 is 2 hours and 31 minutes and

SQLIntersection Post-Conference Session

I am really happy to announce that Kimberly Tripp and I will be delivering a post-conference in November’s SQLIntersection conference at the MGM Grand. The

Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator

I’ve been working on writing a new Microsoft white paper since January covering the main changes made in the SQL Server 2014 Cardinality Estimator, and

Finding Timeout-Prone Code with max_elapsed_time

Consider a scenario where your application timeout setting is 30 seconds and you’ve been asked to proactively start tuning workloads that are at risk of

Deck from “Practical SQL Server Cardinality Estimation”

I uploaded the deck from my session for SQLSaturday #287 Madison 2014 and you can download it here. It was an excellent event! Great organizers,

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.