SQL Server 2016, playing with DATABASE SCOPED CONFIGURATIONS

It’s been a few days since SQL Server 2016 RC0 was released and one of things people have blogged about is the new DATABASE SCOPED CONFIGURATIONS, where you can do modify certain behaviors at database level that before were only possible at server level, like the MAXDOP option. It’s been a few days since SQL Server 2016 RC0 was released and one of things people have blogged about is the new DATABASE SCOPED CONFIGURATIONS, where you can do modify certain behaviors at database level that before were only possible at server level, like the MAXDOP option.

Also you can take advantage of new features but keep using the old cardinality estimator (prior 2014 version) by setting LEGACY_CARDINALITY_ESTIMATION at database level. Before this change you could always use a compatibility level for the database up to SQL 2012 (110), but this is way cooler!

So after reading a few posts, I was wondering how the new SQL Server would behave when you write a cross database query and those settings conflict to each other, so I rolled up my sleeves and prepare this demo to find out.

Before start and considering the thing I wanted to test the most was about parallelism, I needed to get the [AdventureWorks2016CTP3] database big enough for the optimizer to come up with parallel plans if possible, so I used the script created by Adam Machanic (blog|twitter) to grow the database, you can find it here

Also I adapted the script to do the same in the [AdventureWorskDW2016CTP3] as I would use them both to do cross database queries and have different set up for each of them.

Once it’s done, let’s go for it!

This first query, when using the new cardinality estimator does not need to go parallel

-- this does NOT go parallel, with the NEW cardinality estimation
USE AdventureWorks2016CTP3

ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = OFF
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 0
GO

SELECT * 
	FROM dbo.bigTransactionHistory AS th
		INNER JOIN dbo.bigProduct AS p
			ON p.ProductID = th.ProductID
	WHERE p.Color = 'Black'
		AND th.TransactionDate > DATEADD(YY, -1, GETDATE())
GO

But if we want to use the LEGACY_CARDINALITY_ESTIMATION, then it will generate a parallel plan…

-- this goes  parallel, with the OLD cardinality estimation
USE AdventureWorks2016CTP3

ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 0
GO

SELECT * 
	FROM dbo.bigTransactionHistory AS th
		INNER JOIN dbo.bigProduct AS p
			ON p.ProductID = th.ProductID
	WHERE p.Color = 'Black'
		AND th.TransactionDate > DATEADD(YY, -1, GETDATE())

GO

So far so good, the same query behaves differently with each cardinality estimator, that was the plan I guess.

Furthermore, if we want to use the old estimation but we don’t want to go parallel, the MAXDOP will do that for us (set to 1)

-- this does NOT go parallel, with the OLD cardinality estimation, but MAXDOP = 1
USE AdventureWorks2016CTP3

ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 1
GO

SELECT * 
	FROM dbo.bigTransactionHistory AS th
		INNER JOIN dbo.bigProduct AS p
			ON p.ProductID = th.ProductID
	WHERE p.Color = 'Black'
		AND th.TransactionDate > DATEADD(YY, -1, GETDATE())
GO

And then it’s time to start with the fun, joining the OLTP and the DW databases to see how that works, but first I’ll show you the query from the DW that goes parallel under the same settings as the OLTP

-- this goes parallel, with the OLD cardinality estimation
USE AdventureworksDW2016CTP3
GO
ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 0
GO

SELECT * 
	FROM dbo.bigTransactionHistory AS th
		INNER JOIN dbo.DimProductBig AS p
			ON p.ProductKey = th.ProductKey
	WHERE p.Color = 'Black'
		AND th.TransactionDate > DATEADD(YY, -1, GETDATE())
GO

So what if one database is set to use MAXDOP 1 and the other to 0? let’s see

USE AdventureworksDW2016CTP3
GO
ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 1
GO

USE AdventureWorks2016CTP3

ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 0
GO

USE AdventureWorks2016CTP3
GO

SELECT * 
	FROM AdventureWorks2016CTP3.dbo.bigTransactionHistory AS th
		INNER JOIN AdventureWorks2016CTP3.dbo.bigProduct AS p
			ON p.ProductID = th.ProductID
		INNER JOIN AdventureworksDW2016CTP3.dbo.DimProductBig AS dp
			ON dp.ProductKey = p.ProductID
		INNER JOIN AdventureworksDW2016CTP3.dbo.bigTransactionHistory AS th2
			ON th2.ProductKey = dp.ProductKey
	WHERE p.Color = 'Black'
		AND th.TransactionDate > DATEADD(YY, -1, GETDATE())
GO

In this case the query plan goes parallel, but how is that possible if one of the databases involved in the query is restricted to use only single thread plans?

That’s got a proper explanation, I guess, and it is that the query is executed under one single context (scope) and in the query above is the database [AdventureWorks2016CTP3] which is the one that is set to go parallel if needed.

If the same query with same database settings is executed on the scope of [AdventureworksDW2016CTP3] it will not go parallel. See it

USE AdventureworksDW2016CTP3
GO
ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 1
GO

USE AdventureWorks2016CTP3

ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 0
GO

USE AdventureworksDW2016CTP3

SELECT * 
	FROM AdventureWorks2016CTP3.dbo.bigTransactionHistory AS th
		INNER JOIN AdventureWorks2016CTP3.dbo.bigProduct AS p
			ON p.ProductID = th.ProductID
		INNER JOIN AdventureworksDW2016CTP3.dbo.DimProductBig AS dp
			ON dp.ProductKey = p.ProductID
		INNER JOIN AdventureworksDW2016CTP3.dbo.bigTransactionHistory AS th2
			ON th2.ProductKey = dp.ProductKey
	WHERE p.Color = 'Black'
		AND th.TransactionDate > DATEADD(YY, -1, GETDATE())
GO

So in conclusion, all those new database settings are in use only when executing queries under the scope of the database, not just by simply using a table from that database, hence we must be careful to avoid surprises in the future when writing cross database queries.