Monday, April 13, 2015

Cardinality Estimation (CE) in SQL 2014


Cardinality Estimation (CE) in SQL 2014
In SQL Server 2014, Cardinality Estimation (CE) has been redesigned to improve
Query plan and therefore query performance, CE help us to find cardinality to predict approximate number of rows comparing to old CE.
To achieve this CE use Exponential Back-off algorithm, this supports OLTP and data warehousing workloads.
New CE get enable when SQL Server compatibility mode is 120 means in case of SQL Server 2014 only, if compatibility mode is less than 120 then it will use old CE.
New Trace flag have been introduced, using these trace flag we can force query to use old CE or new CE.

Trace Flag 9481 support old CE and trace Flag 2312 new CE, in any process if both trace flags are used then they cancel each other and use CE based on SQL Server compatibility mode.

We can use new CE or old CE in following options.
For single query, use QueryTraceon option:
/*For Old CE*/
SELECT TOP 10 *
FROM[HumanResources].[Employee] e
OPTION( QUERYTRACEON 9481 )

/*For NEW CE*/
SELECT TOP 10 *
FROM[HumanResources].[Employee] e
OPTION( QUERYTRACEON 2312 )


For session, use DBCC Traceon option:
/* this requires sysadmin permission to enable session specific trace*/
DBCC Traceon(9481) -- for old CE for current session
DBCC Traceon(2312) -- for new CE for current session

For database level use compatibility mode:

For new CE:
ALTER DATABASE AdventureWorks2014
SET COMPATIBILITY_LEVEL = 120

For Old CE:
ALTER DATABASE AdventureWorks2014
SET COMPATIBILITY_LEVEL = 110


For server, use global trace flag:
DBCC Traceon(9481, -1) -- enable old CE globally
DBCC Traceon(2312, -1) -- enable new CE globally

DBCC Traceoff(9481 ,-1) -- disable old CE globally
DBCC Traceoff(2312, -1) -- disable new CE globally

To know the trace flag status execute below dbcc command.
DBCC TRACESTATUS (9481, 2312);
Output:



Let us check the difference in New CE and Old CE using sql server execution plans.
To check this executes below queries in with old and new trace flags in database AdventureWorks2014.
/*Query with old trace flag 9481*/
;WITH [EMP_cte]([BusinessEntityID], [OrganizationNode], [Name], [RecursionLevel])
AS (
SELECT e.[BusinessEntityID], e.[OrganizationNode], p.[FirstName]+ ' '+p.[LastName], 0
FROM [HumanResources].[Employee] e
INNER JOIN [Person].[Person] p
ON p.[BusinessEntityID] = e.[BusinessEntityID]
WHERE e.[BusinessEntityID] = 272
UNION ALL
SELECT e.[BusinessEntityID], e.[OrganizationNode], p.[FirstName] + ' ' + p.[LastName], [RecursionLevel] + 1
FROM [HumanResources].[Employee] e
INNER JOIN [EMP_cte]
ON e.[OrganizationNode].GetAncestor(1) = [EMP_cte].[OrganizationNode]
INNER JOIN [Person].[Person] p
ON p.[BusinessEntityID] = e.[BusinessEntityID]
)

SELECT [EMP_cte].[RecursionLevel], [EMP_cte].[OrganizationNode] as [OrganizationNode],
[EMP_cte].[BusinessEntityID], [EMP_cte].[Name]
FROM [EMP_cte]
INNER JOIN [HumanResources].[Employee] e
ON [EMP_cte].[OrganizationNode].GetAncestor(1) = e.[OrganizationNode]
ORDER BY [RecursionLevel], [EMP_cte].[OrganizationNode]
OPTION( QUERYTRACEON 9481 )

Output of query with Old CE.


/*Query with new trace flag 2312*/
;WITH [EMP_cte]([BusinessEntityID], [OrganizationNode], [Name], [RecursionLevel])
AS (
SELECT e.[BusinessEntityID], e.[OrganizationNode], p.[FirstName]+ ' '+p.[LastName], 0
FROM [HumanResources].[Employee] e
INNER JOIN [Person].[Person] p
ON p.[BusinessEntityID] = e.[BusinessEntityID]
WHERE e.[BusinessEntityID] = 272
UNION ALL
SELECT e.[BusinessEntityID], e.[OrganizationNode], p.[FirstName] + ' ' + p.[LastName], [RecursionLevel] + 1
FROM [HumanResources].[Employee] e
INNER JOIN [EMP_cte]
ON e.[OrganizationNode].GetAncestor(1) = [EMP_cte].[OrganizationNode]
INNER JOIN [Person].[Person] p
ON p.[BusinessEntityID] = e.[BusinessEntityID]
)

SELECT [EMP_cte].[RecursionLevel], [EMP_cte].[OrganizationNode] as [OrganizationNode],
[EMP_cte].[BusinessEntityID], [EMP_cte].[Name]
FROM [EMP_cte]
INNER JOIN [HumanResources].[Employee] e
ON [EMP_cte].[OrganizationNode].GetAncestor(1) = e.[OrganizationNode]
ORDER BY [RecursionLevel], [EMP_cte].[OrganizationNode]
OPTION( QUERYTRACEON 2312 )

Output of query with new CE.


In above execution plans while executing both the queries, first query is with old CE and second is with new CE, in plan query with old CE is taking 97% and with new CE is taking 3%

Summary

• The new CE is calculating combined filter density/selectivity differently
• The new CE is treating ascending/descending key scenarios differently
• There are significant changes in how column densities of different tables in join situations are evaluated and density between those calculated.
• The different changes in calculation can end up in different plans for a query compared with the old cardinality estimation
• It is not necessary in every scenarios new CE query will get benefited, there is quite possibility that old CE may perform better comparing to new CE





No comments :

Post a Comment