SQL server 2014 - Cardinality Estimation

Cardinality in data models determines how one table is related to other tables. In sql, cardinality determines uniqueness of data.

There are three types of cardinality

• High Cardinality
• Normal Cardinality
• Low Cardinality

High Cardinality refers to the data which are uncommon or are unique as compared to other values in the same column. Medium Cardinality determines data that is not uncommon but can improve query performance if the index is used. Low cardinality refers to the data which is common in the column and hence in some cases should be avoided with index usage.

Cardinality estimator is redesigned in sql server 2014. One can test the query performance by just altering COMPATIBILITY_LEVEL parameter of database.

COMPATIBILITY_LEVEL -> 110 (SQL 2012)
COMPATIBILITY_LEVEL -> 120 (SQL 2014)

 Note: even though we've got sql server 2014 engine, the compatibility level of database is to be set to 120 i.e. sql 2014.

I'm using Adventure works 2012 database, just altered the compatibility from 110 to 120 and brummm !!! see the performance:

-- Query 1
ALTER DATABASE AdventureWorks2012
SET COMPATIBILITY_LEVEL = 110
GO
exec [dbo].[uspGetEmployeeManagers] 10
go

-- Query 2
ALTER DATABASE AdventureWorks2012
SET COMPATIBILITY_LEVEL = 120
GO
exec [dbo].[uspGetEmployeeManagers] 10
go



 


 For Further information:
Doc Ref: http://msdn.microsoft.com/en-us/library/dn600374.aspx