{"id":145,"date":"2016-03-10T14:23:45","date_gmt":"2016-03-10T14:23:45","guid":{"rendered":"https:\/\/sqldoubleg.live-website.com\/?p=145"},"modified":"2016-03-10T15:00:57","modified_gmt":"2016-03-10T15:00:57","slug":"sql-server-2016-playing-with-database-scoped-configurations","status":"publish","type":"post","link":"https:\/\/www.sqldoubleg.com\/es\/2016\/03\/10\/sql-server-2016-playing-with-database-scoped-configurations\/","title":{"rendered":"SQL Server 2016, playing with DATABASE SCOPED CONFIGURATIONS"},"content":{"rendered":"

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<\/a>, where you can do modify certain behaviors at database level that before were only possible at server level, like the MAXDOP option.<\/p>\n

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!<\/p>\n

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.<\/p>\n

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<\/a>|twitter<\/a>) to grow the database, you can find it here<\/a><\/p>\n

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.<\/p>\n

Once it’s done, let’s go for it!<\/p>\n

This first query, when using the new cardinality estimator does not need to go parallel<\/p>\n

\r\n-- this does NOT go parallel, with the NEW cardinality estimation\r\nUSE AdventureWorks2016CTP3\r\n\r\nALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = OFF\r\nALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 0\r\nGO\r\n\r\nSELECT * \r\n\tFROM dbo.bigTransactionHistory AS th\r\n\t\tINNER JOIN dbo.bigProduct AS p\r\n\t\t\tON p.ProductID = th.ProductID\r\n\tWHERE p.Color = 'Black'\r\n\t\tAND th.TransactionDate > DATEADD(YY, -1, GETDATE())\r\nGO\r\n<\/pre>\n
\"01_new_cardinality_no_parallel_plan\"<\/a><\/p>\n
But if we want to use the LEGACY_CARDINALITY_ESTIMATION, then it will generate a parallel plan…<\/p>\n
\r\n-- this goes  parallel, with the OLD cardinality estimation\r\nUSE AdventureWorks2016CTP3\r\n\r\nALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON\r\nALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 0\r\nGO\r\n\r\nSELECT * \r\n\tFROM dbo.bigTransactionHistory AS th\r\n\t\tINNER JOIN dbo.bigProduct AS p\r\n\t\t\tON p.ProductID = th.ProductID\r\n\tWHERE p.Color = 'Black'\r\n\t\tAND th.TransactionDate > DATEADD(YY, -1, GETDATE())\r\n\r\nGO\r\n<\/pre>\n
\"02_old_cardinality_parallel_plan\"<\/a><\/p>\n
So far so good, the same query behaves differently with each cardinality estimator, that was the plan I guess.<\/p>\n
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)<\/p>\n
\r\n-- this does NOT go parallel, with the OLD cardinality estimation, but MAXDOP = 1\r\nUSE AdventureWorks2016CTP3\r\n\r\nALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON\r\nALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 1\r\nGO\r\n\r\nSELECT * \r\n\tFROM dbo.bigTransactionHistory AS th\r\n\t\tINNER JOIN dbo.bigProduct AS p\r\n\t\t\tON p.ProductID = th.ProductID\r\n\tWHERE p.Color = 'Black'\r\n\t\tAND th.TransactionDate > DATEADD(YY, -1, GETDATE())\r\nGO\r\n<\/pre>\n
\"03_old_cardinality_MAXDOP_1_no_parallel_plan\"<\/a><\/p>\n
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<\/p>\n
\r\n-- this goes parallel, with the OLD cardinality estimation\r\nUSE AdventureworksDW2016CTP3\r\nGO\r\nALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON\r\nALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 0\r\nGO\r\n\r\nSELECT * \r\n\tFROM dbo.bigTransactionHistory AS th\r\n\t\tINNER JOIN dbo.DimProductBig AS p\r\n\t\t\tON p.ProductKey = th.ProductKey\r\n\tWHERE p.Color = 'Black'\r\n\t\tAND th.TransactionDate > DATEADD(YY, -1, GETDATE())\r\nGO\r\n<\/pre>\n
\"04_old_cardinality_DW_query_parallel_plan\"<\/a><\/p>\n
So what if one database is set to use MAXDOP 1 and the other to 0? let’s see<\/p>\n
\r\nUSE AdventureworksDW2016CTP3\r\nGO\r\nALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON\r\nALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 1\r\nGO\r\n\r\nUSE AdventureWorks2016CTP3\r\n\r\nALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON\r\nALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 0\r\nGO\r\n\r\nUSE AdventureWorks2016CTP3\r\nGO\r\n\r\nSELECT * \r\n\tFROM AdventureWorks2016CTP3.dbo.bigTransactionHistory AS th\r\n\t\tINNER JOIN AdventureWorks2016CTP3.dbo.bigProduct AS p\r\n\t\t\tON p.ProductID = th.ProductID\r\n\t\tINNER JOIN AdventureworksDW2016CTP3.dbo.DimProductBig AS dp\r\n\t\t\tON dp.ProductKey = p.ProductID\r\n\t\tINNER JOIN AdventureworksDW2016CTP3.dbo.bigTransactionHistory AS th2\r\n\t\t\tON th2.ProductKey = dp.ProductKey\r\n\tWHERE p.Color = 'Black'\r\n\t\tAND th.TransactionDate > DATEADD(YY, -1, GETDATE())\r\nGO\r\n<\/pre>\n
\"06_cross_db_query_parallel\"<\/a><\/p>\n
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?<\/p>\n
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.<\/p>\n
If the same query with same database settings is executed on the scope of [AdventureworksDW2016CTP3] it will not go parallel. See it<\/p>\n
\r\nUSE AdventureworksDW2016CTP3\r\nGO\r\nALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON\r\nALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 1\r\nGO\r\n\r\nUSE AdventureWorks2016CTP3\r\n\r\nALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON\r\nALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 0\r\nGO\r\n\r\nUSE AdventureworksDW2016CTP3\r\n\r\nSELECT * \r\n\tFROM AdventureWorks2016CTP3.dbo.bigTransactionHistory AS th\r\n\t\tINNER JOIN AdventureWorks2016CTP3.dbo.bigProduct AS p\r\n\t\t\tON p.ProductID = th.ProductID\r\n\t\tINNER JOIN AdventureworksDW2016CTP3.dbo.DimProductBig AS dp\r\n\t\t\tON dp.ProductKey = p.ProductID\r\n\t\tINNER JOIN AdventureworksDW2016CTP3.dbo.bigTransactionHistory AS th2\r\n\t\t\tON th2.ProductKey = dp.ProductKey\r\n\tWHERE p.Color = 'Black'\r\n\t\tAND th.TransactionDate > DATEADD(YY, -1, GETDATE())\r\nGO\r\n<\/pre>\n
\"07_cross_db_query_no_parallel\"<\/a><\/p>\n
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.<\/p>\n","protected":false},"excerpt":{"rendered":"
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…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4],"tags":[7,29,28,23],"_links":{"self":[{"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/posts\/145"}],"collection":[{"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/comments?post=145"}],"version-history":[{"count":1,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/posts\/145\/revisions"}],"predecessor-version":[{"id":152,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/posts\/145\/revisions\/152"}],"wp:attachment":[{"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/media?parent=145"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/categories?post=145"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/tags?post=145"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}