{"id":1078,"date":"2020-01-17T06:15:37","date_gmt":"2020-01-17T14:15:37","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/erin\/?p=1078"},"modified":"2020-01-17T07:11:55","modified_gmt":"2020-01-17T15:11:55","slug":"database-compatibility-level-and-query-store","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/erin\/database-compatibility-level-and-query-store\/","title":{"rendered":"Database Compatibility Level and Query Store"},"content":{"rendered":"

A question I\u2019ve gotten a few times when teaching relates to database compatibility level and Query Store. I was talking to a client yesterday about post-upgrade plans and implementing Query Store, and the topic came again. They wanted to know what compatibility level<\/a> the database needed in order to use Query Store.<\/p>\n

The quick answer: it doesn\u2019t matter.<\/p><\/blockquote>\n

Query Store is an engine feature that was introduced in SQL Server 2016 (and available in Azure SQL Database since Q4 of 2015) and continues to evolve with each release. It is a feature that has to be enabled at the database level, using ALTER DATABASE SET<\/a>, and it is compatibility level agnostic.<\/p>\n

However, Query Store does track both engine version and compatibility level at the plan level in sys.query_store_plan. This allows you to use Query Store data for comparing plan performance for a query before and after upgrades, both at the engine level and database compatibility level.<\/p>\n

Query Store Demo Setup<\/h2>\n

I have a copy of WideWorldImporters<\/a> restored on a SQL Server 2019 instance, and I\u2019ll mention that it\u2019s not the standard copy you get from GitHib, it\u2019s one I\u2019ve run the DataLoadSimulation.DailyProcessToCreateHistory stored procedure against to add more data and introduce some skew. If you recreate this against a copy of WideWorldImporters that you have, you may not see the same results.<\/p>\n

The first step after restoring is to enable Query Store, and then clear out any old QS data. This is something I do only for demos; I don\u2019t recommend it for a production database unless you\u2019re ok with removing all Query Store data.<\/p>\n

\r\nALTER DATABASE [WideWorldImporters] SET QUERY_STORE = ON;\r\nGO\r\nALTER DATABASE [WideWorldImporters] SET QUERY_STORE (\r\n\tOPERATION_MODE = READ_WRITE, INTERVAL_LENGTH_MINUTES = 10\r\n\t);\r\nGO\r\n\r\nALTER DATABASE [WideWorldImporters] SET QUERY_STORE CLEAR;\r\nGO\r\n<\/pre>\n
Next, set the compatibility level to 110, and set the database to use the legacy Cardinality Estimator, just as if we had upgraded this database from SQL Server 2012 to SQL Server 2019.<\/p>\n
\r\nUSE [master];\r\nGO\r\nALTER DATABASE [WideWorldImporters] \r\n\tSET COMPATIBILITY_LEVEL = 110;\r\nGO\r\n\r\nUSE [WideWorldImporters];\r\nGO\r\nALTER DATABASE SCOPED CONFIGURATION \r\n\tSET LEGACY_CARDINALITY_ESTIMATION = ON;\r\nGO\r\n<\/pre>\n
Compatibility Level 120<\/h2>\n
We have a very simple query that we\u2019ll execute 10 times against WideWorldImporters, and display the actual execution plan.<\/p>\n
\r\nUSE [WideWorldImporters];\r\nGO\r\n\r\nSELECT \r\n\t[ol].[StockItemID], \r\n\t[ol].[Description], \r\n\t[ol].[UnitPrice],\r\n\t[o].[CustomerID], \r\n\t[o].[SalespersonPersonID],\r\n\t[o].[OrderDate]\r\nFROM [Sales].[OrderLines] [ol]\r\nJOIN [Sales].[Orders] [o]\r\n\tON [ol].[OrderID] = [o].[OrderID]\r\nWHERE [ol].[UnitPrice] = 240.00\r\nAND [o].[OrderDate] = '2016-04-16';\r\nGO 10\r\n<\/pre>\n
\"Execution<\/a>
Execution Plan with Compatibility Level = 110<\/figcaption><\/figure>\n
The plan has two nested loops, with a clustered index scan against the Orders table, and an index seek against the FK_Sales_OrdersLines_OrderID nonclustered index.\u00a0 Within Query Store, the sys.query_store_plan table shows a compatibility_level of 110, and an engine_version of 15.0.1900.25.<\/p>\n
\"Output<\/a>
Output from sys.query_store_plan<\/figcaption><\/figure>\n
Compatibility Level 150<\/h2>\n
Without making any changes to the Query Store configuration, change the compatibility level to 150. The Cardinality Estimator version stays set to Legacy.<\/p>\n
\r\nUSE [master];\r\nGO\r\nALTER DATABASE [WideWorldImporters] \r\n\tSET COMPATIBILITY_LEVEL = 150;\r\nGO\r\n<\/pre>\n
Next, re-run the same query against Orders and OrderLines and note the plan change:<\/p>\n
\"Execution<\/a>
Execution Plan with Compatibility Level = 150<\/figcaption><\/figure>\n
We still have the clustered index scan against Order, but it feeds into a Hash Mash that uses a Columnstore index scan against OrderLines to probe the matching rows \u2013 it\u2019s a different plan. We see this in Query Store as a different plan_id (4), with a compatibility_level of 150.<\/p>\n
\r\nSELECT \r\n    [qst].[query_text_id],\r\n\t[qsq].[query_id],  \r\n\t[qst].[query_sql_text], \r\n\t[rs].[count_executions],\r\n\t[qsp].[plan_id], \r\n\t[qsp].[compatibility_level],\r\n\t[qsp].[engine_version]\r\nFROM [sys].[query_store_query] [qsq] \r\nJOIN [sys].[query_store_query_text] [qst]\r\n\tON [qsq].[query_text_id] = [qst].[query_text_id]\r\nJOIN [sys].[query_store_plan] [qsp] \r\n\tON [qsq].[query_id] = [qsp].[query_id]\r\nJOIN [sys].[query_store_runtime_stats] [rs] \r\n\tON [qsp].[plan_id] = [rs].[plan_id] \r\nWHERE [qst].[query_text_id] = 1; \r\nGO\r\n<\/pre>\n
\"Output<\/a>
Output from sys.query_store_plan after change to compatibility level 150<\/figcaption><\/figure>\n
Leveraging Query Store<\/h2>\n
One of the many ways you can take advantage of the data in Query Store, beyond just identifying problematic queries, is to use it for A\/B testing.\u00a0 This entirely the purpose of the Query Tuning Assistant<\/a>, which will compare plan performance you, as well as stabilize query performance.\u00a0 If you are unable to run a representative workload against your database, or just want to investigate specific, critical queries, you have all the data at your disposal in Query Store.\u00a0 You simply need to be running SQL Server 2016 or higher, or Azure SQL Database and have Query Store enabled with compatibility level set to any supported value (100 or higher).<\/p>\n","protected":false},"excerpt":{"rendered":"
A question I\u2019ve gotten a few times when teaching relates to database compatibility level and Query Store. I was talking to a client yesterday about post-upgrade plans and implementing Query Store, and the topic came again. They wanted to know what compatibility level the database needed in order to use Query Store. The quick answer: […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[42,46],"tags":[],"yoast_head":"\nDatabase Compatibility Level and Query Store - Erin Stellato<\/title>\n<meta name=\"description\" content=\"Compatibilty level and Query Store are two separate, independent database settings; Query Store can be used with any database compatibility level.\" \/>\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\/database-compatibility-level-and-query-store\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Database Compatibility Level and Query Store - Erin Stellato\" \/>\n<meta property=\"og:description\" content=\"Compatibilty level and Query Store are two separate, independent database settings; Query Store can be used with any database compatibility level.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/erin\/database-compatibility-level-and-query-store\/\" \/>\n<meta property=\"og:site_name\" content=\"Erin Stellato\" \/>\n<meta property=\"article:published_time\" content=\"2020-01-17T14:15:37+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2020-01-17T15:11:55+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2020\/01\/plan_110.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=\"4 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\/database-compatibility-level-and-query-store\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/database-compatibility-level-and-query-store\/\",\"name\":\"Database Compatibility Level and Query Store - Erin Stellato\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#website\"},\"datePublished\":\"2020-01-17T14:15:37+00:00\",\"dateModified\":\"2020-01-17T15:11:55+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158\"},\"description\":\"Compatibilty level and Query Store are two separate, independent database settings; Query Store can be used with any database compatibility level.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/database-compatibility-level-and-query-store\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/erin\/database-compatibility-level-and-query-store\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/database-compatibility-level-and-query-store\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Database Compatibility Level and Query Store\"}]},{\"@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":"Database Compatibility Level and Query Store - Erin Stellato","description":"Compatibilty level and Query Store are two separate, independent database settings; Query Store can be used with any database compatibility level.","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\/database-compatibility-level-and-query-store\/","og_locale":"en_US","og_type":"article","og_title":"Database Compatibility Level and Query Store - Erin Stellato","og_description":"Compatibilty level and Query Store are two separate, independent database settings; Query Store can be used with any database compatibility level.","og_url":"https:\/\/www.sqlskills.com\/blogs\/erin\/database-compatibility-level-and-query-store\/","og_site_name":"Erin Stellato","article_published_time":"2020-01-17T14:15:37+00:00","article_modified_time":"2020-01-17T15:11:55+00:00","og_image":[{"url":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2020\/01\/plan_110.jpg"}],"author":"Erin Stellato","twitter_misc":{"Written by":"Erin Stellato","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/database-compatibility-level-and-query-store\/","url":"https:\/\/www.sqlskills.com\/blogs\/erin\/database-compatibility-level-and-query-store\/","name":"Database Compatibility Level and Query Store - Erin Stellato","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#website"},"datePublished":"2020-01-17T14:15:37+00:00","dateModified":"2020-01-17T15:11:55+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158"},"description":"Compatibilty level and Query Store are two separate, independent database settings; Query Store can be used with any database compatibility level.","breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/database-compatibility-level-and-query-store\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/erin\/database-compatibility-level-and-query-store\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/database-compatibility-level-and-query-store\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/erin\/"},{"@type":"ListItem","position":2,"name":"Database Compatibility Level and Query Store"}]},{"@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\/1078"}],"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=1078"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/posts\/1078\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/media?parent=1078"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/categories?post=1078"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/tags?post=1078"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}