Compatibility Level 130 With Legacy Cardinality

Jos de Bruijn notes that there is a way to use the legacy cardinality estimator even when you are in Compatibility Level 130 (new in SQL Server 2016):

In SQL Server 2016 there are a lot of goodies under Compatibility Level 130, such as performance improvements with Columnstore and In-Memory OLTP and all the Query Optimizer enhancements done over the years under Trace Flag 4199. So we are faced with a situation where there are workloads that can benefit from some enhancements that come with Compatibility Level 130, but that cannot use the new CE.

The solution is to use one of the new database-scoped configuration options, namely the Legacy Cardinality Estimation option. You can enable this using the following ALTER DATABASE command:

I’ve had a very positive experience with the new cardinality estimator, but I certainly appreciate the option being there just in case.

Related Posts

.Net Core On Docker Connecting Via AD To SQL Server

Michal Poreba shows us how to connect Windows Docker containers running .Net Core to SQL Server via Active Directory when the containers are not connected to the domain: The good news is that it is not an unreasonable requirement and it has been done before. The solution is to use Group Managed Service Accounts (gMSA) […]

Read More

Access Violation Error In SQL Server 2016 SP2 CU4

Lonny Niederstadt tracked down an ugly bug in SQL Server 2016 SP2 CU4: When I started investigating, the error was known only as an access violation, preventing some operations related to data cleansing or fact table versioning. It occurred deep within a series of stored procedures.  The execution environment included cross-database DELETE statements, cross-database synonyms, […]

Read More

Categories

April 2016
MTWTFSS
« Mar May »
 123
45678910
11121314151617
18192021222324
252627282930