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:

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

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.

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:

TSQL

SELECT CustomerId, OrderAddedDate 
FROM OrderTable 
WHERE OrderAddedDate >= '2016-05-01';
OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'));
go

PL/SQL

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

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

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('9.2.0.7') */ 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.name, d.compatibility_level FROM sys.databases AS d 
WHERE d.name = '<Database_name>';

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

ALTER DATABASE <yourDatabase> SET COMPATIBILITY_LEVEL = 140;

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.

ALTER DATABASE <yourDatabase> SET COMPATIBILITY_LEVEL = 130;

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:

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… 🙂

ALTER DATABASE SCOPED CONFIGURATION LEGACY_CARDINALITY_ESTIMATION = ON;

This option will then allow you to override the compatibility level value of a given database.  What this means is that you can have a database with:

• Product version: 140, (SQL Server 2017)
• Compatibility version: 120, (SQL Server 2016)
• and then turn scoped database configuration on for legacy cardinality estimation and use the optimizer compatibility features for 100, (SQL Server 2012)

When performance challenges occur, this can create an incredibly complex situation.  The optimizer is an incredible set of features and it becomes more complex with each release.  New features can impact how previous optimizer features once performed and when scoped database configuration for legacy cardinality estimates are turned on, it can really create a puzzle box for the DBA to diagnose.  The recommendation is turn on the query store, pull an XML trace of the process performance and then view the specific information at deep detail of what the optimizer did during the execution.

I believe as many do, fixing something right is a better choice than bringing a performance issue requiring an optimizer value override for more than one version.   If you do have to make this decision as a last resort, do so with the idea that its a temporary solution.  I’d feel this way if it was Oracle or SQL Server.  I know there was a demand to have this feature by some powerful customer at some point, but we all know it will end up creating more pain than gain in the end.