{"id":1165,"date":"2014-03-02T13:53:18","date_gmt":"2014-03-02T21:53:18","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/joe\/?p=1165"},"modified":"2014-03-02T13:53:18","modified_gmt":"2014-03-02T21:53:18","slug":"using-the-sql-server-2014-database-compatibility-level-without-the-new-ce","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/joe\/using-the-sql-server-2014-database-compatibility-level-without-the-new-ce\/","title":{"rendered":"Using the SQL Server 2014 Database Compatibility Level without the New CE"},"content":{"rendered":"<p>Consider the following scenario:<\/p>\n<ul>\n<li>You want to migrate all databases on a specific SQL Server instance to a new SQL Server 2014 instance<\/li>\n<li>You want to leverage new SQL Server 2014 functionality and move to the latest database compatibility level<\/li>\n<li>You <em>don\u2019t<\/em> want to enable the new Cardinality Estimator right away (various reasons why this might be \u2013 but perhaps you didn\u2019t have time to fully test key workloads)<\/li>\n<li>You don\u2019t want to have to manually add QUERYTRACEON hints for specific queries<\/li>\n<\/ul>\n<p>To accommodate this scenario, you can do the following:<\/p>\n<ul>\n<li>Change the migrated databases to COMPATIBILITY_LEVEL = 120 in order to leverage the SQL Server 2014 database compatibility level<\/li>\n<li>Enable trace flag <u>9481<\/u> at the server-level as a startup trace flag (or via DBCC TRACEON \u2013 but remembering this doesn\u2019t persist on restarts unless you re-execute)<\/li>\n<\/ul>\n<p>Trace flag 9481, for using the legacy CE behavior and trace flag 2312, for using the new CE behavior are both fully supported and documented by Microsoft here:<\/p>\n<blockquote>\n<p><a href=\"http:\/\/support.microsoft.com\/kb\/2801413\" target=\"_blank\">Enable plan-affecting SQL Server query optimizer behavior that can be controlled by different trace flags on a specific-query level<\/a><\/p>\n<\/blockquote>\n<p>That KB article focuses mostly on QUERYTRACEON \u2013 but the CE trace flags can apply at the server-level scope as well.<\/p>\n<p>There are other combinations of CE enabling\/disabling that you can use, depending on your requirements, but I just wanted to point out what I think will be a more common scenario.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Consider the following scenario: You want to migrate all databases on a specific SQL Server instance to a new SQL Server 2014 instance You want to leverage new SQL Server 2014 functionality and move to the latest database compatibility level You don\u2019t want to enable the new Cardinality Estimator right away (various reasons why this [&hellip;]<\/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-1165","post","type-post","status-publish","format-standard","hentry","category-cardinality-estimation"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v21.9.1 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Using the SQL Server 2014 Database Compatibility Level without the New CE - 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\/using-the-sql-server-2014-database-compatibility-level-without-the-new-ce\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Using the SQL Server 2014 Database Compatibility Level without the New CE - Joe Sack\" \/>\n<meta property=\"og:description\" content=\"Consider the following scenario: You want to migrate all databases on a specific SQL Server instance to a new SQL Server 2014 instance You want to leverage new SQL Server 2014 functionality and move to the latest database compatibility level You don\u2019t want to enable the new Cardinality Estimator right away (various reasons why this [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/joe\/using-the-sql-server-2014-database-compatibility-level-without-the-new-ce\/\" \/>\n<meta property=\"og:site_name\" content=\"Joe Sack\" \/>\n<meta property=\"article:published_time\" content=\"2014-03-02T21:53:18+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=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/using-the-sql-server-2014-database-compatibility-level-without-the-new-ce\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/using-the-sql-server-2014-database-compatibility-level-without-the-new-ce\/\",\"name\":\"Using the SQL Server 2014 Database Compatibility Level without the New CE - Joe Sack\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#website\"},\"datePublished\":\"2014-03-02T21:53:18+00:00\",\"dateModified\":\"2014-03-02T21:53:18+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/533eb0113a15fb5a6e8067a49e4ae648\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/using-the-sql-server-2014-database-compatibility-level-without-the-new-ce\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/joe\/using-the-sql-server-2014-database-compatibility-level-without-the-new-ce\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/using-the-sql-server-2014-database-compatibility-level-without-the-new-ce\/#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\":\"Using the SQL Server 2014 Database Compatibility Level without the New CE\"}]},{\"@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":"Using the SQL Server 2014 Database Compatibility Level without the New CE - 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\/using-the-sql-server-2014-database-compatibility-level-without-the-new-ce\/","og_locale":"en_US","og_type":"article","og_title":"Using the SQL Server 2014 Database Compatibility Level without the New CE - Joe Sack","og_description":"Consider the following scenario: You want to migrate all databases on a specific SQL Server instance to a new SQL Server 2014 instance You want to leverage new SQL Server 2014 functionality and move to the latest database compatibility level You don\u2019t want to enable the new Cardinality Estimator right away (various reasons why this [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/joe\/using-the-sql-server-2014-database-compatibility-level-without-the-new-ce\/","og_site_name":"Joe Sack","article_published_time":"2014-03-02T21:53:18+00:00","author":"Joseph Sack","twitter_misc":{"Written by":"Joseph Sack","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/using-the-sql-server-2014-database-compatibility-level-without-the-new-ce\/","url":"https:\/\/www.sqlskills.com\/blogs\/joe\/using-the-sql-server-2014-database-compatibility-level-without-the-new-ce\/","name":"Using the SQL Server 2014 Database Compatibility Level without the New CE - Joe Sack","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#website"},"datePublished":"2014-03-02T21:53:18+00:00","dateModified":"2014-03-02T21:53:18+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/533eb0113a15fb5a6e8067a49e4ae648"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/using-the-sql-server-2014-database-compatibility-level-without-the-new-ce\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/joe\/using-the-sql-server-2014-database-compatibility-level-without-the-new-ce\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/using-the-sql-server-2014-database-compatibility-level-without-the-new-ce\/#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":"Using the SQL Server 2014 Database Compatibility Level without the New CE"}]},{"@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\/1165","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=1165"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/posts\/1165\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/media?parent=1165"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/categories?post=1165"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/tags?post=1165"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}