Database Scoped Configurations

As most of you know, I’m working to update all of my SQL Server knowledge, which hasn’t gone as deep as I would like since SQL Server 2012.

So there are a couple things we’re going to focus on today, first of which is the top in the list which I’ve highlighted, Legacy Cardinality Estimation: 300w, 768w" sizes="(max-width: 840px) 100vw, 840px" data-recalc-dims="1" />

By default, this value is turned off and as the optimizer is essential to database performance and in each database platform, handled differently, it’s worth starting to disect.  The Database Scoped Configuration settings, which are database specific, and can be accessed via the SSMS, right clicking on the database and left clicking on Database Properties and on Options.

The second is to query sys.database_scoped_configurations:

SELECT name, value 
 FROM sys.database_scoped_configurations;

The values from here will be displayed as the numerical values for ON=1 and OFF=0 300w" sizes="(max-width: 280px) 100vw, 280px" data-recalc-dims="1" />

Only the MAXDOP, (Maximum Degree of Parallelism) is a numerical value vs. On/Off.  This is proven after I save my update to the MAX DOP shown at the properties for the database that will immediately change the value of DOP allowed from none to four.  Needless to say, this was just to prove the update and I reverted it to 0 vs. a distinct value. 300w" sizes="(max-width: 298px) 100vw, 298px" data-recalc-dims="1" />

The Legacy_Cardinality_Estimation is part of a larger feature that controls feature versioning choices for the optimizer in SQL Server.

Legacy Cardinality Estimation

Oracle DBAs have used the CARDINALITY hint for some time and it should be understood that this may appear to be similar, but is actually quite different.  As hinting in TSQL is a bit different than PL/SQL, we can compare similar queries to assist:

SELECT CustomerId, OrderAddedDate 
FROM OrderTable 
WHERE OrderAddedDate >= '2016-05-01';

Where you might first mistake the CE hint for the following CARDINALITY hint in Oracle:


This would be incorrect and the closest hint in Oracle to SQL Server’s legacy CE hint would be the optimizer feature hint:

SELECT /*+ optimizer_features_enable('') */ ORD.CUSTOMER_ID, ORD.ORDER_DATE FROM ORDERS ORD 
WHERE ORD.ORDER_DATE >= '2016-05-01';

If you’re wondering why I chose a 9i version to force the optimizer to, keep reading and you’ll come to understand.

To check the compatibility version, as well as setting it per database in SQL Server, we would perform the following:

SELECT ServerProperty('ProductVersion');    

SELECT, d.compatibility_level FROM sys.databases AS d 
WHERE = '';

To update the compatibility version to the latest version to match our SQL Server vNext database engine, we’d run the following command:


Let’s assume we just upgraded our database to 140, (SQL Server 2017) and are experiencing some serious performance issues that didn’t appear during testing.  We could choose to set the compatibility level to 130, (SQL Server 2016) to temporarily address the performance problem, allowing the optimizer to use previous version features, while we troubleshoot the issue.


Now database engine and compatibility is an odd thing, just like in  Oracle.  To understand the versions that are out there, here’s a handy reference: 236w" sizes="(max-width: 436px) 100vw, 436px" data-recalc-dims="1" />

Now you will notice that their isn’t a value for SQL Server 7 when setting compatibility.  Even though 70 might be the version value, the ability to set back to it requires a different answer to the fix than you see for newer versions.  First of all, if you’re still forcing compatibility down to version 7, we need to have a serious talk, but if you need to force compatibility back to version 7, this is where we get into crazy land… </p />

    	  	<div class=