{"id":1745,"date":"2019-04-21T11:13:19","date_gmt":"2019-04-21T11:13:19","guid":{"rendered":"http:\/\/www.theSQLReport.com\/?p=1745"},"modified":"2019-04-21T11:14:17","modified_gmt":"2019-04-21T11:14:17","slug":"quickly-enable-the-query-store-for-a-sql-server-database","status":"publish","type":"post","link":"http:\/\/www.theSQLReport.com\/?p=1745","title":{"rendered":"Quickly Enable the Query Store for a SQL Server Database"},"content":{"rendered":"<p>Probably the fastest way to enable Query Store for a database is run the &#8220;ALTER DATABASE&#8221; command in a query window.<\/p>\n<p>First check that the database is not already have the Query Store enabled.<\/p>\n<pre>select name as 'DATABASE NAME', \r\nCASE is_query_store_on when 1 then 'ENABLED'\r\nelse 'OTHER' END AS 'QUERY STORE STATE'\r\nfrom sys.databases\r\nwhere is_query_store_on = 1\r\norder by 1 ;<\/pre>\n<p><a href=\"http:\/\/www.theSQLReport.com\/wp-content\/uploads\/2019\/01\/QueryStoreNotEnabled.jpg\" target=\"_blank\" rel=\"noopener\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-1746 size-full\" src=\"http:\/\/www.theSQLReport.com\/wp-content\/uploads\/2019\/01\/QueryStoreNotEnabled.jpg\" alt=\"Check to see if the Query Store is Enabled on the database\" width=\"734\" height=\"250\" srcset=\"http:\/\/www.theSQLReport.com\/wp-content\/uploads\/2019\/01\/QueryStoreNotEnabled.jpg 734w, http:\/\/www.theSQLReport.com\/wp-content\/uploads\/2019\/01\/QueryStoreNotEnabled-300x102.jpg 300w, http:\/\/www.theSQLReport.com\/wp-content\/uploads\/2019\/01\/QueryStoreNotEnabled-210x72.jpg 210w\" sizes=\"(max-width: 734px) 100vw, 734px\" \/><\/a><\/p>\n<p>In a query window, run the &#8220;ALTER DATABASE&#8221; command to enable the query store:<\/p>\n<pre>-- enabling Query Store for a Database\r\nALTER DATABASE &lt;database name&gt; SET QUERY_STORE = ON;<\/pre>\n<p><a href=\"http:\/\/www.theSQLReport.com\/wp-content\/uploads\/2019\/01\/EnableQueryStore.jpg\" target=\"_blank\" rel=\"noopener\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-1747 size-full\" src=\"http:\/\/www.theSQLReport.com\/wp-content\/uploads\/2019\/01\/EnableQueryStore.jpg\" alt=\"ALTER DATABASE SET QUERY_STORE ON\" width=\"806\" height=\"156\" \/><\/a><\/p>\n<p>Then verify that the Query Store has been enabled for the database with the query shown above:<\/p>\n<p><a href=\"http:\/\/www.theSQLReport.com\/wp-content\/uploads\/2019\/01\/VerifyQueryStoreEnabled.jpg\" target=\"_blank\" rel=\"noopener\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-1748 size-full\" src=\"http:\/\/www.theSQLReport.com\/wp-content\/uploads\/2019\/01\/VerifyQueryStoreEnabled.jpg\" alt=\"Verify the Query Store is Enabled\" width=\"887\" height=\"267\" srcset=\"http:\/\/www.theSQLReport.com\/wp-content\/uploads\/2019\/01\/VerifyQueryStoreEnabled.jpg 887w, http:\/\/www.theSQLReport.com\/wp-content\/uploads\/2019\/01\/VerifyQueryStoreEnabled-300x90.jpg 300w, http:\/\/www.theSQLReport.com\/wp-content\/uploads\/2019\/01\/VerifyQueryStoreEnabled-768x231.jpg 768w, http:\/\/www.theSQLReport.com\/wp-content\/uploads\/2019\/01\/VerifyQueryStoreEnabled-210x63.jpg 210w\" sizes=\"(max-width: 887px) 100vw, 887px\" \/><\/a><\/p>\n<p>Note:<\/p>\n<p>Unless the Query Store options are configured differently, the default options will be used. The Max Size will default to the value 100 MB. The Data Flush Interval is the default which is 900 seconds or 15 minutes. The Statistics Collection Interval default is 60 minutes. The default option for the Query Store Capture Mode is to capture all queries. Finally the Stale Query Threshold is is configured to keep the data for 30 days.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Probably the fastest way to enable Query Store for a database is run the &#8220;ALTER DATABASE&#8221; command in a query window. First check that the database is not already have &hellip; <a class=\"readmore\" href=\"http:\/\/www.theSQLReport.com\/?p=1745\">Continue Reading &rarr;<\/a><\/p>\n","protected":false},"author":1,"featured_media":1747,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4],"tags":[146,143,145],"class_list":["post-1745","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-sql-server","tag-alter-database","tag-query-store","tag-query_store"],"_links":{"self":[{"href":"http:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/posts\/1745"}],"collection":[{"href":"http:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.theSQLReport.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1745"}],"version-history":[{"count":6,"href":"http:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/posts\/1745\/revisions"}],"predecessor-version":[{"id":1754,"href":"http:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/posts\/1745\/revisions\/1754"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/media\/1747"}],"wp:attachment":[{"href":"http:\/\/www.theSQLReport.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1745"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.theSQLReport.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1745"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.theSQLReport.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1745"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}