{"id":772,"date":"2016-06-29T13:29:56","date_gmt":"2016-06-29T20:29:56","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/erin\/?p=772"},"modified":"2017-04-13T09:19:32","modified_gmt":"2017-04-13T16:19:32","slug":"sql-server-2016-upgrade-testing-with-the-new-cardinality-estimator-context-matters","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/erin\/sql-server-2016-upgrade-testing-with-the-new-cardinality-estimator-context-matters\/","title":{"rendered":"SQL Server 2016 Upgrade Testing with the New Cardinality Estimator: Context Matters"},"content":{"rendered":"

This week I\u2019ve been working with SQL Server 2016 and it\u2019s been a lot of fun \u2013 this is truly a great release from the SQL Server team.\u00a0 Yesterday I was working on some upgrade testing with regard to the new Cardinality Estimator (CE), introduced in SQL Server 2104 and well explained in this whitepaper<\/a> written by my friend Joe Sack.\u00a0 If you are upgrading to SQL Server 2014 or 2016, you should test not just your upgrade process, but also the performance of your queries with the new CE.\u00a0 In some cases we have seen good\/great improvements in query performance with the new CE, but in other cases we have seen significant regressions which dramatically affect overall system performance.<\/p>\n

There are a variety of things to look for when testing beyond just query duration and resource use \u2013 you should also be looking at estimates and plan shape, which means capturing the execution plan.\u00a0 I was doing just that yesterday, and in my testing I was changing the compatibility mode for the user database and running my queries with the different CEs.\u00a0 I was then confirming the CE version used for the query by looking at the plan and checking the CardinalityEstimationModelVersion attribute.\u00a0 Remember that CE version is tied to the compatibility mode, and the CE that will be used is tied to the current database context<\/strong>.\u00a0 So if you\u2019re running cross database or distributed queries, this is something to which you need to pay attention.<\/p>\n

Consider this scenario\u2026<\/p>\n

I have a new installation of SQL Server 2016, and I restore my user database to the instance.\u00a0 By default the compatibility is not<\/em> changed unless<\/em> the database you\u2019re restoring has a compatibility level of 90 (remember you can upgrade from SQL Server 2005+ to any other version<\/a>).\u00a0 In that case, it will automatically be bumped up to 100 for both SQL Server 2014 and SQL Server 2016.\u00a0 For more details on supported compatibility levels for versions and the differences between the levels, see ALTER Database Compatibility Level<\/a>.<\/p>\n

Because this is a new installation of SQL Server 2016, the system databases have compatibility level of 130.\u00a0 I restore two user databases to my instance (previously running on SQL Server 2012), call them AdventureWorks and WideWorldImporters.\u00a0 I leave the compatibility level for AdventureWorks at 110, and set it to 130 for WideWorldImporters.\u00a0 I then start running queries, some of which are specific to a database, others which query both databases.<\/p>\n

In the case where I\u2019m in the context of AdventureWorks, the old CE version used \u2013 even when I query the WideWorldImporters database.\u00a0 And if I\u2019m in the WideWorldImporters database, querying across to AdventureWorks, the new CE is used.\u00a0 Here\u2019s setup code:<\/p>\n

\r\n\/*\r\n\tRestore the databases\r\n\t(change locations as appropriate)\r\n*\/\r\nUSE [master];\r\nGO\r\n\r\nRESTORE DATABASE [WideWorldImporters]\r\n\tFROM  DISK = N'C:\\Backups\\WideWorldImporters-Full.bak'\r\n\tWITH  FILE = 1,\r\n\tMOVE N'WWI_Primary' TO N'C:\\Databases\\WideWorldImporters\\WideWorldImporters.mdf',\r\n\tMOVE N'WWI_UserData' TO N'C:\\Databases\\WideWorldImporters\\WideWorldImporters_UserData.ndf',\r\n\tMOVE N'WWI_Log' TO N'C:\\Databases\\WideWorldImporters\\WideWorldImporters.ldf',\r\n\tMOVE N'WWI_InMemory_Data_1' TO N'C:\\Databases\\WideWorldImporters\\WideWorldImporters_InMemory_Data_1',\r\n\tNOUNLOAD,\r\n\tREPLACE,\r\n\tSTATS = 5;\r\nGO\r\n\r\nRESTORE DATABASE [AdventureWorks2016]\r\n\tFROM  DISK = N'C:\\Backups\\AW2014_Base.bak'\r\n\tWITH  FILE = 1,\r\n\tMOVE N'AdventureWorks2014_Data' TO N'C:\\Databases\\AdventureWorks2016\\AdventureWorks2016_Data.mdf',\r\n\tMOVE N'AdventureWorks2014_Log' TO N'C:\\Databases\\AdventureWorks2016\\AdventureWorks2016_Log.ldf',\r\n\tNOUNLOAD,\r\n\tREPLACE,\r\n\tSTATS = 5;\r\nGO\r\n\r\n\/*\r\n\tSet the compatibility levels\r\n*\/\r\nUSE [master];\r\nGO\r\nALTER DATABASE [WideWorldImporters] SET COMPATIBILITY_LEVEL = 130;\r\nGO\r\nALTER DATABASE [AdventureWorks2016] SET COMPATIBILITY_LEVEL = 110;\r\nGO\r\n<\/pre>\n
First, run the query in the context of WideWorldImporters, with the execution plan displayed:<\/p>\n
\r\nUSE [WideWorldImporters];\r\nGO\r\n\r\nSET STATISTICS XML ON;\r\nGO\r\n\r\nSELECT\r\n\t[o].[CustomerID],\r\n\t[c].[StoreID],\r\n\t[o].[OrderDate],\r\n\t[ol].[StockItemID],\r\n\t[ol].[Quantity],\r\n\t[ol].[UnitPrice]\r\nFROM [WideWorldImporters].[Sales].[Orders] [o]\r\nJOIN [WideWorldImporters].[Sales].[OrderLines] [ol] on [o].[OrderID] = [ol].[OrderID]\r\nJOIN [AdventureWorks2016].[Sales].[Customer] [c] ON [c].[CustomerID] = [o].[CustomerID]\r\nWHERE [o].[OrderDate] BETWEEN '2016-05-01' AND '2016-05-31'\r\nORDER BY [o].[OrderDate] DESC;\r\nGO\r\n\r\nSET STATISTICS XML OFF;\r\nGO\r\n<\/pre>\n
If you open up the graphical plan, and display the Properties window (F4) and then click on the SELECT operator in the plan, I can see that the CardinalityEstimationModelVersion is 130 (you can also view the XML and do a search to find it there):<\/p>\n
\"Execution<\/a>
Execution plan using the new CE<\/figcaption><\/figure>\n
Now run the query in the context of AdventureWorks, again with the execution plan displayed:<\/p>\n
\r\nUSE [AdventureWorks2016];\r\nGO\r\n\r\nSET STATISTICS XML ON;\r\nGO\r\n\r\nSELECT\r\n\t[o].[CustomerID],\r\n\t[c].[StoreID],\r\n\t[o].[OrderDate],\r\n\t[ol].[StockItemID],\r\n\t[ol].[Quantity],\r\n\t[ol].[UnitPrice]\r\nFROM [WideWorldImporters].[Sales].[Orders] [o]\r\nJOIN [WideWorldImporters].[Sales].[OrderLines] [ol] on [o].[OrderID] = [ol].[OrderID]\r\nJOIN [AdventureWorks2016].[Sales].[Customer] [c] ON [c].[CustomerID] = [o].[CustomerID]\r\nWHERE [o].[OrderDate] BETWEEN '2016-05-01' AND '2016-05-31'\r\nORDER BY [o].[OrderDate] DESC;\r\nGO\r\n\r\nSET STATISTICS XML OFF;\r\nGO\r\n\r\n<\/pre>\n
This time when you look at the graphical plan, the CardinalityEstimationModelVersion is 70, indicating it\u2019s using the old CE:<\/p>\n
\"Execution<\/a>
Execution plan using the old CE<\/figcaption><\/figure>\n
In this example, I have users connecting to both databases, and they can be querying one database or both.\u00a0 Note that if you happen to keep users in the context of one database (e.g. a user database that has no data in it, but you use as a \u201cgateway\u201d to other database), then the compatibility level for that<\/em> database will determine what CE the queries use.\u00a0 The exception to all this, of course, is the CE-related traceflags.\u00a0 You can override the compatibility level and force the optimizer to use a specific CE by using one of two trace flags:<\/p>\n
    \n
  • Use Trace Flag 9481 to revert to the legacy CE behavior from the context of a database with a compatibility level of 120 or higher<\/li>\n
  • Use Trace Flag 2312 to enable to the new CE from the context of a database with a compatibility level below 120<\/li>\n<\/ul>\n
    Therefore, when you\u2019re testing your upgrade to SQL Server 2014 or 2016, take the time to look beyond the basics.\u00a0 Upgrading is not just about making sure nothing breaks, it\u2019s also about making sure that performance doesn\u2019t degrade, even if you don\u2019t change hardware (especially if you don\u2019t change hardware!).\u00a0 We don\u2019t typically expect that upgrading to a newer software version would negatively affect performance, but because the change the SQL Server\u2019s Cardinality Estimator is significant, this is one you definitely want to test.<\/p>\n
     <\/p>\n","protected":false},"excerpt":{"rendered":"
    This week I\u2019ve been working with SQL Server 2016 and it\u2019s been a lot of fun \u2013 this is truly a great release from the SQL Server team.\u00a0 Yesterday I was working on some upgrade testing with regard to the new Cardinality Estimator (CE), introduced in SQL Server 2104 and well explained in this whitepaper […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[17,43],"tags":[],"yoast_head":"\nSQL Server 2016 Upgrade Testing with the New Cardinality Estimator: Context Matters - Erin Stellato<\/title>\n<meta name=\"description\" content=\"When working through SQL Server 2016 upgrade testing, it's important to look at performance, particularly if you're using the new Cardinality Estimator.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/erin\/sql-server-2016-upgrade-testing-with-the-new-cardinality-estimator-context-matters\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL Server 2016 Upgrade Testing with the New Cardinality Estimator: Context Matters - Erin Stellato\" \/>\n<meta property=\"og:description\" content=\"When working through SQL Server 2016 upgrade testing, it's important to look at performance, particularly if you're using the new Cardinality Estimator.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/erin\/sql-server-2016-upgrade-testing-with-the-new-cardinality-estimator-context-matters\/\" \/>\n<meta property=\"og:site_name\" content=\"Erin Stellato\" \/>\n<meta property=\"article:published_time\" content=\"2016-06-29T20:29:56+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-04-13T16:19:32+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2016\/06\/newce.jpg\" \/>\n<meta name=\"author\" content=\"Erin Stellato\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Erin Stellato\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"5 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/sql-server-2016-upgrade-testing-with-the-new-cardinality-estimator-context-matters\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/sql-server-2016-upgrade-testing-with-the-new-cardinality-estimator-context-matters\/\",\"name\":\"SQL Server 2016 Upgrade Testing with the New Cardinality Estimator: Context Matters - Erin Stellato\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#website\"},\"datePublished\":\"2016-06-29T20:29:56+00:00\",\"dateModified\":\"2017-04-13T16:19:32+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158\"},\"description\":\"When working through SQL Server 2016 upgrade testing, it's important to look at performance, particularly if you're using the new Cardinality Estimator.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/sql-server-2016-upgrade-testing-with-the-new-cardinality-estimator-context-matters\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/erin\/sql-server-2016-upgrade-testing-with-the-new-cardinality-estimator-context-matters\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/sql-server-2016-upgrade-testing-with-the-new-cardinality-estimator-context-matters\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQL Server 2016 Upgrade Testing with the New Cardinality Estimator: Context Matters\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/\",\"name\":\"Erin Stellato\",\"description\":\"The SQL Sequel\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158\",\"name\":\"Erin Stellato\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/0c8b485bd54ea26b57e99f79b525f409?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/0c8b485bd54ea26b57e99f79b525f409?s=96&d=mm&r=g\",\"caption\":\"Erin Stellato\"},\"sameAs\":[\"http:\/\/3.209.169.194\/blogs\/erin\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/author\/erin\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"SQL Server 2016 Upgrade Testing with the New Cardinality Estimator: Context Matters - Erin Stellato","description":"When working through SQL Server 2016 upgrade testing, it's important to look at performance, particularly if you're using the new Cardinality Estimator.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/erin\/sql-server-2016-upgrade-testing-with-the-new-cardinality-estimator-context-matters\/","og_locale":"en_US","og_type":"article","og_title":"SQL Server 2016 Upgrade Testing with the New Cardinality Estimator: Context Matters - Erin Stellato","og_description":"When working through SQL Server 2016 upgrade testing, it's important to look at performance, particularly if you're using the new Cardinality Estimator.","og_url":"https:\/\/www.sqlskills.com\/blogs\/erin\/sql-server-2016-upgrade-testing-with-the-new-cardinality-estimator-context-matters\/","og_site_name":"Erin Stellato","article_published_time":"2016-06-29T20:29:56+00:00","article_modified_time":"2017-04-13T16:19:32+00:00","og_image":[{"url":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2016\/06\/newce.jpg"}],"author":"Erin Stellato","twitter_misc":{"Written by":"Erin Stellato","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/sql-server-2016-upgrade-testing-with-the-new-cardinality-estimator-context-matters\/","url":"https:\/\/www.sqlskills.com\/blogs\/erin\/sql-server-2016-upgrade-testing-with-the-new-cardinality-estimator-context-matters\/","name":"SQL Server 2016 Upgrade Testing with the New Cardinality Estimator: Context Matters - Erin Stellato","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#website"},"datePublished":"2016-06-29T20:29:56+00:00","dateModified":"2017-04-13T16:19:32+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158"},"description":"When working through SQL Server 2016 upgrade testing, it's important to look at performance, particularly if you're using the new Cardinality Estimator.","breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/sql-server-2016-upgrade-testing-with-the-new-cardinality-estimator-context-matters\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/erin\/sql-server-2016-upgrade-testing-with-the-new-cardinality-estimator-context-matters\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/sql-server-2016-upgrade-testing-with-the-new-cardinality-estimator-context-matters\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/erin\/"},{"@type":"ListItem","position":2,"name":"SQL Server 2016 Upgrade Testing with the New Cardinality Estimator: Context Matters"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/erin\/","name":"Erin Stellato","description":"The SQL Sequel","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/erin\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158","name":"Erin Stellato","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/0c8b485bd54ea26b57e99f79b525f409?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/0c8b485bd54ea26b57e99f79b525f409?s=96&d=mm&r=g","caption":"Erin Stellato"},"sameAs":["http:\/\/3.209.169.194\/blogs\/erin"],"url":"https:\/\/www.sqlskills.com\/blogs\/erin\/author\/erin\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/posts\/772"}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/comments?post=772"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/posts\/772\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/media?parent=772"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/categories?post=772"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/tags?post=772"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}