SQL SERVER – Simple Demo of New Cardinality Estimation Features of SQL Server 2014

SQL Server 2014 has new cardinality estimation logic/algorithm. The cardinality estimation logic is responsible for quality of query plans and majorly responsible for improving performance for any query. This logic was not updated for quite a while, but in the latest version of SQL Server 2104 this logic is re-designed. The new logic now incorporates various assumptions and algorithms of OLTP and warehousing workload.

Cardinality estimates are a prediction of the number of rows in the query result. The query optimizer uses these estimates to choose a plan for executing the query. The quality of the query plan has a direct impact on improving query performance. ~ Souce MSDN

Let us see a quick example of how cardinality improves performance for a query. I will be using the AdventureWorks database for my example.

Before we start with this demonstration, remember that even though you have SQL Server 2014 to see the effect of new cardinality estimates, you will need your database compatibility mode set to 120 which is for SQL Server 2014. If your server instance of SQL Server 2014 but you have set up your database compatibility mode to 110 or any other earlier version, you will get performance from your query like older version of SQL Server.

Now we will execute following query in two different compatibility mode and see its performance. (Note that my SQL Server instance is of version 2014).

USE AdventureWorks2014
GO
-- -------------------------------
-- NEW Cardinality Estimation
ALTER DATABASE AdventureWorks2014
SET COMPATIBILITY_LEVEL = 120
GO
EXEC [dbo].[uspGetManagerEmployees] 44
GO
-- -------------------------------
-- Old Cardinality Estimation
ALTER DATABASE AdventureWorks2014
SET COMPATIBILITY_LEVEL = 110
GO
EXEC [dbo].[uspGetManagerEmployees] 44
GO

Result of Statistics IO

Compatibility level 120

Table ‘Person’. Scan count 0, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Employee’. Scan count 2, logical reads 7, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Worktable’. Scan count 2, logical reads 7, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Compatibility level 110

Table ‘Worktable’. Scan count 2, logical reads 7, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Person’. Scan count 0, logical reads 137, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Employee’. Scan count 2, logical reads 7, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

You will notice in the case of compatibility level 110 there 137 logical read from table person where as in the case of compatibility level 120 there are only 6 logical reads from table person. This drastically improves the performance of the query. If we enable execution plan, we can see the same as well.

I hope you will find this quick example helpful. You can read more about this in my latest Pluralsight Course.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

About these ads

7 thoughts on “SQL SERVER – Simple Demo of New Cardinality Estimation Features of SQL Server 2014

  1. Pingback: SQL SERVER – Cardinality Estimation and Performance – SQL in Sixty Seconds #072 | Journey to SQL Authority with Pinal Dave

  2. Pingback: SQL SERVER – How to Force New Cardinality Estimation or Old Cardinality Estimation | Journey to SQL Authority with Pinal Dave

  3. i have one user in my database, and I want to grant to him permission to read only those view which he has create else view he can only read. he cannot do drop or update even same in table?
    How can I do this?

    Like

  4. Pingback: SQL SERVER – How to Know Cardinality of Executed Query? | Journey to SQL Authority with Pinal Dave

  5. Pingback: SQL SERVER – Live Plan for Executing Query – SQL in Sixty Seconds #073 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s