Testing on SQL Server 2014, CTP2, version 12.0.1524, imagine you have two databases \u2013 one called T1 and one called T2 configured with database compatibility levels as follows:<\/p>\n
\r\nUSE [master];\r\nGO\r\n\r\nALTER DATABASE [T1] SET COMPATIBILITY_LEVEL = 120;\r\nGO\r\n\r\nALTER DATABASE [T2] SET COMPATIBILITY_LEVEL = 110;\r\nGO\r\n <\/pre>\nNow with database T1, we know that using a compatibility level of 120 means we\u2019ll be using the new cardinality estimator (CE) \u2013 assuming we\u2019re in the database session context of a new CE database and don\u2019t have a trace flag disabling the new CE behavior.<\/p>\n
Executing the following query in the context of T1 does indeed mean we use the new CE:<\/p>\n
\r\nUSE [T1];\r\nGO\r\n\r\nSELECT\u00a0\u00a0\u00a0 [member_no],\r\n[provider_no],\r\n[category_no]\r\nFROM [dbo].[charge]\r\nWHERE [charge_no] = 422;\r\n <\/pre>\n<StmtSimple StatementCompId=”1″ StatementEstRows=”1″ StatementId=”1″ StatementOptmLevel=”TRIVIAL” CardinalityEstimationModelVersion=”120″<\/span> StatementSubTreeCost=”0.0032831″ StatementText=”SELECT [member_no],[provider_no],[category_no] FROM [dbo].[charge] WHERE [charge_no]=@1″ StatementType=”SELECT” QueryHash=”0x274BD0D496403EEE” QueryPlanHash=”0x6B5F27FE55FE8A5C” RetrievedFromCache=”true”><\/p>\n
But what if we change the query to be in the context of the T2 database (legacy CE) \u2013 but still access data from the T1 database?<\/p>\n
\r\nUSE [T2];\r\nGO\r\n\r\nSELECT\u00a0\u00a0\u00a0 [member_no],\r\n[provider_no],\r\n[category_no]\r\nFROM [T1].[dbo].[charge]\r\nWHERE [charge_no] = 422;\r\n <\/pre>\nNow we see the query used a legacy CE:<\/p>\n
<StmtSimple StatementCompId=”1″ StatementEstRows=”1″ StatementId=”1″ StatementOptmLevel=”TRIVIAL” CardinalityEstimationModelVersion=”70″ <\/span>StatementSubTreeCost=”0.0032831″ StatementText=”SELECT [member_no],[provider_no],[category_no] FROM [T1].[dbo].[charge] WHERE [charge_no]=@1″ StatementType=”SELECT” QueryHash=”0x274BD0D496403EEE” QueryPlanHash=”0x6B5F27FE55FE8A5C” RetrievedFromCache=”true”><\/p>\n
What if the cross-database query is executed from a new CE session context – but the destination is the legacy CE?<\/p>\n
\r\nUSE [T1];\r\nGO\r\n\r\nSELECT\u00a0\u00a0\u00a0 [member_no],\r\n[provider_no],\r\n[category_no]\r\nFROM [T2].[dbo].[charge]\r\nWHERE [charge_no] = 422;\r\n <\/pre>\nIn this scenario, the query uses the new CE \u2013 based on the database session context \u2013 even though the destination database is set to compatibility level 110.<\/p>\n
What about accessing data from two databases (rather than my previous example of just accessing data from one database)?\u00a0 The following example results in a legacy CE plan:<\/p>\n
\r\nUSE [T2];\r\nGO\r\n\r\nSELECT\u00a0\u00a0\u00a0 [member_no],\r\n[provider_no],\r\n[category_no]\r\nFROM [T1].[dbo].[charge]\r\nWHERE [charge_no] = 422\r\nUNION\r\nSELECT\u00a0\u00a0\u00a0 [member_no],\r\n[provider_no],\r\n[category_no]\r\nFROM [T2].[dbo].[charge]\r\nWHERE [charge_no] = 422;\r\nGO\r\n <\/pre>\nAnd this query results in a new CE plan:<\/p>\n
\r\nUSE [T1];\r\nGO\r\n\r\nSELECT\u00a0\u00a0\u00a0 [member_no],\r\n[provider_no],\r\n[category_no]\r\nFROM [T1].[dbo].[charge]\r\nWHERE [charge_no] = 422\r\nUNION\r\nSELECT\u00a0\u00a0\u00a0 [member_no],\r\n[provider_no],\r\n[category_no]\r\nFROM [T2].[dbo].[charge]\r\nWHERE [charge_no] = 422;\r\nGO\r\n <\/pre>\nSo \u2013 bottom line \u2013 using the new CE doesn\u2019t mean just changing the database compatibility level.\u00a0 Database session context also matters.<\/p>\n","protected":false},"excerpt":{"rendered":"
Testing on SQL Server 2014, CTP2, version 12.0.1524, imagine you have two databases \u2013 one called T1 and one called T2 configured with database compatibility levels as follows: USE [master]; GO ALTER DATABASE [T1] SET COMPATIBILITY_LEVEL = 120; GO ALTER DATABASE [T2] SET COMPATIBILITY_LEVEL = 110; GO Now with database T1, we know that using […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[42],"tags":[],"class_list":["post-1163","post","type-post","status-publish","format-standard","hentry","category-cardinality-estimation"],"yoast_head":"\n
For the New CE, Database Session Context Matters - Joe Sack<\/title>\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\/joe\/new-ce-database-session-context-matters\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"For the New CE, Database Session Context Matters - Joe Sack\" \/>\n<meta property=\"og:description\" content=\"Testing on SQL Server 2014, CTP2, version 12.0.1524, imagine you have two databases \u2013 one called T1 and one called T2 configured with database compatibility levels as follows: USE [master]; GO ALTER DATABASE [T1] SET COMPATIBILITY_LEVEL = 120; GO ALTER DATABASE [T2] SET COMPATIBILITY_LEVEL = 110; GO Now with database T1, we know that using […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/joe\/new-ce-database-session-context-matters\/\" \/>\n<meta property=\"og:site_name\" content=\"Joe Sack\" \/>\n<meta property=\"article:published_time\" content=\"2014-03-02T21:17:45+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2014-03-02T21:23:52+00:00\" \/>\n<meta name=\"author\" content=\"Joseph Sack\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Joseph Sack\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/new-ce-database-session-context-matters\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/new-ce-database-session-context-matters\/\",\"name\":\"For the New CE, Database Session Context Matters - Joe Sack\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#website\"},\"datePublished\":\"2014-03-02T21:17:45+00:00\",\"dateModified\":\"2014-03-02T21:23:52+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/533eb0113a15fb5a6e8067a49e4ae648\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/new-ce-database-session-context-matters\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/joe\/new-ce-database-session-context-matters\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/new-ce-database-session-context-matters\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Cardinality Estimation\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/category\/cardinality-estimation\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"For the New CE, Database Session Context Matters\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/\",\"name\":\"Joe Sack\",\"description\":\"SQL Server Performance Tuning, High Availability and Disaster Recovery Blog\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/533eb0113a15fb5a6e8067a49e4ae648\",\"name\":\"Joseph Sack\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/a4b39a7719a6bfff1add3ec00527810734579ee114d6d983e8e68f937b77be96?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/a4b39a7719a6bfff1add3ec00527810734579ee114d6d983e8e68f937b77be96?s=96&d=mm&r=g\",\"caption\":\"Joseph Sack\"},\"description\":\"Joe Sack is a Principal Consultant with SQLskills. He has worked as a SQL Server professional since 1997 and has supported and developed for SQL Server environments in financial services, IT consulting, manufacturing, retail and the real estate industry. Prior to joining SQLskills he worked at Microsoft as a Premier Field Engineer supporting very large enterprise customer environments. He was responsible for providing deep SQL Server advisory services, training, troubleshooting and ongoing solutions guidance. His areas of expertise include performance tuning, scalability, T-SQL development and high-availability. In 2006 Joe earned the \u201cMicrosoft Certified Master: SQL Server 2005\u201d certification and in 2008 he earned the \u201cMicrosoft Certified Master: SQL Server 2008\u201d certification. In 2009 he took over responsibility for the entire SQL Server Microsoft Certified Master program and held that post until 2011. He was given the SQL Server MVP award in 2013.\",\"sameAs\":[\"http:\/\/3.209.169.194\/blogs\/joe\",\"https:\/\/twitter.com\/https:\/\/twitter.com\/josephsack\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/author\/joe\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"For the New CE, Database Session Context Matters - Joe Sack","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\/joe\/new-ce-database-session-context-matters\/","og_locale":"en_US","og_type":"article","og_title":"For the New CE, Database Session Context Matters - Joe Sack","og_description":"Testing on SQL Server 2014, CTP2, version 12.0.1524, imagine you have two databases \u2013 one called T1 and one called T2 configured with database compatibility levels as follows: USE [master]; GO ALTER DATABASE [T1] SET COMPATIBILITY_LEVEL = 120; GO ALTER DATABASE [T2] SET COMPATIBILITY_LEVEL = 110; GO Now with database T1, we know that using […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/joe\/new-ce-database-session-context-matters\/","og_site_name":"Joe Sack","article_published_time":"2014-03-02T21:17:45+00:00","article_modified_time":"2014-03-02T21:23:52+00:00","author":"Joseph Sack","twitter_misc":{"Written by":"Joseph Sack","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/new-ce-database-session-context-matters\/","url":"https:\/\/www.sqlskills.com\/blogs\/joe\/new-ce-database-session-context-matters\/","name":"For the New CE, Database Session Context Matters - Joe Sack","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#website"},"datePublished":"2014-03-02T21:17:45+00:00","dateModified":"2014-03-02T21:23:52+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/533eb0113a15fb5a6e8067a49e4ae648"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/new-ce-database-session-context-matters\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/joe\/new-ce-database-session-context-matters\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/new-ce-database-session-context-matters\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/joe\/"},{"@type":"ListItem","position":2,"name":"Cardinality Estimation","item":"https:\/\/www.sqlskills.com\/blogs\/joe\/category\/cardinality-estimation\/"},{"@type":"ListItem","position":3,"name":"For the New CE, Database Session Context Matters"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/joe\/","name":"Joe Sack","description":"SQL Server Performance Tuning, High Availability and Disaster Recovery Blog","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/joe\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/533eb0113a15fb5a6e8067a49e4ae648","name":"Joseph Sack","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/a4b39a7719a6bfff1add3ec00527810734579ee114d6d983e8e68f937b77be96?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/a4b39a7719a6bfff1add3ec00527810734579ee114d6d983e8e68f937b77be96?s=96&d=mm&r=g","caption":"Joseph Sack"},"description":"Joe Sack is a Principal Consultant with SQLskills. He has worked as a SQL Server professional since 1997 and has supported and developed for SQL Server environments in financial services, IT consulting, manufacturing, retail and the real estate industry. Prior to joining SQLskills he worked at Microsoft as a Premier Field Engineer supporting very large enterprise customer environments. He was responsible for providing deep SQL Server advisory services, training, troubleshooting and ongoing solutions guidance. His areas of expertise include performance tuning, scalability, T-SQL development and high-availability. In 2006 Joe earned the \u201cMicrosoft Certified Master: SQL Server 2005\u201d certification and in 2008 he earned the \u201cMicrosoft Certified Master: SQL Server 2008\u201d certification. In 2009 he took over responsibility for the entire SQL Server Microsoft Certified Master program and held that post until 2011. He was given the SQL Server MVP award in 2013.","sameAs":["http:\/\/3.209.169.194\/blogs\/joe","https:\/\/twitter.com\/https:\/\/twitter.com\/josephsack"],"url":"https:\/\/www.sqlskills.com\/blogs\/joe\/author\/joe\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/posts\/1163","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/comments?post=1163"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/posts\/1163\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/media?parent=1163"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/categories?post=1163"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/tags?post=1163"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}