Cardinality Estimator Series III- Enabling New Cardinality Estimator (SQL Server 2014) with TRACE FLAG 2312, when your DB Compatibility level set to Legacy Server i.e. SQL Server 2012,2008..

Lets first set the database SQL SERVER 2014 to the legacy compatibility level of 110 i.e. SQL Server 2008 and with TRACE FLAG we will see the new Query Plan.

Changing the COMPATIBLITY LEVEL OF SQL Server 2014 to 110 i.e. SQL Server 2012

use master

go

alter database AdventureWorks2012

set COMPATIBILITY_LEVEL = 110

go

Command(s) completed successfully.

Lets fire the query in both the environment one under  SQL Server 2012 compatibility level 110 and other on SQL Server 2014 Compatibility level 120 by enabling TRACE Flag to 2132

use AdventureWorks2012

go

-- With Legacy Cardinality Estimator 2012

select * from Person.Address

where StateProvinceID =79 and  City = 'Bothell'

-- With New Cardinality Estimator 2014

select * from Person.Address

where StateProvinceID =79 and  City = 'Bothell'

OPTION (QUERYTRACEON 2312);

Lets see both the query plan to the check the Estimated Row Count.

Query with Legacy Compatibility Level of 110 (SQL Server 2012)

Query with Legacy Compatibility Level of 110 (SQL Server 2012)

Now here you can see, in first legacy plan the Estimated No of Rows has 1 count whereas in the second New Cardinality Estimator Plan the Estimated No of Rows has 3 counts to 2312.

Blaze up your code and people will come from miles to see your code burning!

 


Post Reference: Vikram Aristocratic Elfin Share

Cardinality Estimator Series II: Learning Predicates

Predicate: Predicates are the expression which evaluates to TRUE or FALSE or UNKNOWN. They comes in two form

·         Filter Predicate :

·         Join Predicate: 

Filter Predicate: The expression that comes in WHERE or HAVING clause.

Example 1.

select * from Person.Address

where StateProvinceID =79 and  City = 'Bothell'

So here the Filter predicates are StateProvinceID =79 and  City = 'Bothell'

Example 2.

select StateProvinceID, Count(*) as 'MoreThanOneCity'

from Person.Address

group by StateProvinceID

having count(*) > 1

Here the filter predicate is HAVING with a condition of count(*) > 1.

Join Predicate: The join condition that comes in FROM Clause of select query are considered as JOIN Predicate.

Example:

select be.BusinessEntityID,a.AddressLine1 from Person.BusinessEntity be

inner join Person.BusinessEntityAddress bea on be.BusinessEntityID = bea.BusinessEntityID

inner join Person.Address a on bea.AddressID = a.AddressID

Here the Joining condition are considered as Join predicate.

i.e. be.BusinessEntityID = bea.BusinessEntityID

bea.AddressID = a.AddressID

Code like your code shut others mouth!

Post Reference: Vikram Aristocratic Elfin Share

Cardinality Estimator Series I: Learning Selectivity factor of Select Query and Predicate

A predicate is nothing but an expression that you write in your where clause that evaluates to either TRUE, FALSE or UNKNOWN.

Now there are two category of predicate

• Filter Predicate
• Join Predicate

Filter Predicate, the one which is used to search your result set generally the condition you write in ‘WHERE or HAVING’ clause.

Join Predicate, The equality and non-equality operator that are used while joining table in your DML statements are considered as join predicate.

Example

select e.emp_name,d.dept_name from dept d inner join emp e

on d.dept_id = e.dept_id  --Join Predicate

where d.dept_name = 'hr'  --Filter Predicate

Selectivity, It is a measure of how selective your predicate is? The formula to find the selectivity is

Selectivity = Rows qualifies a predicate / Total no of rows present in table.

Selectivity is always lays in the range of 0-1 ,

0.0     is considered as high selectivity

1.0     is considered as low selectivity

For example

select * from Sales.SalesOrderHeader

where SalesOrderID > 74000

so the selectivity of SalesIderId > 7400 will be

=Total record qualify SalesOrderId predicate / Total record present in SalesOrderHeader table

select count(*) as TotalRecord from sales.SalesOrderHeader

TotalRecord

-----------

31465

select count(*) as TotalQualifyingRows from Sales.SalesOrderHeader

where SalesOrderID > 74000

TotalQualifyingRows

-------------------

1123

So the Selectivity will be = 1123/31465.0

               

select 1123/31465.0 as Selectivity

Selectivity

---------------------------------------

0.0356904

Be in this world with your code; make sure your code speak loud!

Post Reference: Vikram Aristocratic Elfin Share

Database still act as though they are running in earlier version even though it has been upgraded to latest version; Setting the Compatibility Mode in SQL Server

Whenever you are attaching or restoring an earlier version of database to the latest version using backup and restore Or using detach and attach method, the compatibility mode of SQL Server does not change automatically.

That’s the reason you are not able to take the advantage of latest feature of latest SQL Server version, and the need arise to change the compatibility level of your database.

Changing the compatibility Mode

Once you restore or attach database, the first thing you need to see the compatibility level for the attached database.

To change the compatibility mode of your database you need

1)       Go to database Node

2)       Go to option

3)       Check the compatibility Property and set the appropriate for your database

The number which are associated with each version of SQL Server are listed below

60 : SQL Server 6.0

65: SQL Server 6.5

70: SQL Server 7.0

80: SQL Server 2000

90: SQL Server 2005

100: SQL Server 2008

110: SQL Server 2012

120: SQL Server 2014

To see/Check the compatibility level of your database

select name,compatibility_level from sys.databases

name                  compatibility_level

--------------------- -------------------

master                120

tempdb                120

model                 120

msdb                  120

TestDB                120

AdventureWorksDW2012  110

AdventureWorks2012    110

AdventureWorks2008R2  100

(8 row(s) affected)

Now here you can see the AdventureWorks2008R2  is on 100 (SQL Server 2008) compatibility level, so if you want to use the feature of SQL Server 2014 you need to set the compatibility level to 120

Changing the Compatibility level of database

use master

go

Alter database AdventureWorks2008R2

set COMPATIBILITY_LEVEL = 120

Command(s) completed successfully.

Now lets see the updated compatibility level of database by querying the sys.databases

select name,compatibility_level from sys.databases

name                  compatibility_level

--------------------- -------------------

master                120

tempdb                120

model                 120

msdb                  120

TestDB                120

AdventureWorksDW2012  110

AdventureWorks2012    110

AdventureWorks2008R2  120

(8 row(s) affected)

So here we can see, the compatibility level of database has changed to 120.

Live with code, that the ultimate source of amusement and pleasure you have on this earth!

 

Post Reference: Vikram Aristocratic Elfin Share