By default the query store is disabled but it can be enabled for the [model] database, although that’s something I wouldn’t recommend. But there is a way a brand new database have this feature enabled if we want to.<\/p>\n You can see there is nothing returned by the query although this setting will actually be part of new databases. To double check, let’s create a database without any options which would give us a database exactly as [model] is<\/p>\n There is our Query Store enabled, in case we want to do it afterwards, the way of enabling it in its simplest way as we have seen already would be <\/p>\n There are some other comparable DMO’s which hold statistical information like [sys].[dm_db_index_usage_stats]<\/a>, [sys].[dm_exec_procedure_stats]<\/a> or [sys].[dm_exec_query_stats]<\/a> but there is no way a DBA can decide how much information they can hold. <\/p>\n This is possible for the query store by using one of the options provided<\/p>\n It’s good to remind that once we have reached the limit, the Query Store silently will be set to READ_ONLY and new information will not be collected, so keep an eye on it!<\/p>\n Some others DMV’s allow us to remove their data using DBCC commands, but usually that requires elevated privileges most DBA’s do not want to grant lightly.<\/p>\n For cleaning sys.dm_exec_procedure_stats and sys.dm_exec_query_plan we can use DBCC FREEPROCCACHE<\/a><\/p>\n For cleaning sys.dm_os_latch_stats and sys.dm_os_wait_stats we can use DBCC SQLPERF<\/a><\/p>\n Unfortunately for clearing sys.dm_db_index_usage_stats we don’t have any specific statement, although we can detach and attach, SET READ_ONLY and READ_WRITE or for all of them just restart our server.<\/p>\n For the Query Store we have this ALTER DATABASE option which seems more appropriate than DBCC commands<\/p>\n Other metadata like the DMV’s shown above is not backed up together with the user data, but this is not the case for the Query Store data.<\/p>\n Let’s create some data and run some queries<\/p>\n We can see our 10 INSERT and 5 of each SELECT, scan and seeks. If now we take a backup and restore the database, only the metadata which belongs to the Query Store will be back when restoring the data, this might be extremely useful as we can do troubleshooting in a different server than production but looking at production execution plans and stats.<\/p>\n If now we back up and restore our database, only the Query Store metadata will come back, let’s see it<\/p>\n This point is important, because other metadata will keep working when the database goes READ_ONLY, but the query store won’t.<\/p>\n You can find more information about this behaviour in a recent post by Kendra Little (b<\/a>|t<\/a>) where she shows that in order to get the new Query Store the database must be read-write<\/a>, which I encourage you to read it.<\/p>\n To demonstrate it, let’s clear the Query Store and set the database to READ_ONLY before running a few queries<\/p>\n
\n1. You can optionally enable or disable<\/strong><\/p>\n\r\nUSE model\r\nGO\r\n\r\nALTER DATABASE model SET QUERY_STORE = ON\r\nGO\r\n\r\nSELECT *\r\nFROM sys.database_query_store_options \r\nGO\r\n<\/pre>\n
![\"01_enable_Query_Store_model_database\"](\"https:\/\/sqldoubleg.live-website.com\/wp-content\/uploads\/2016\/07\/01_enable_Query_Store_model_database.png\")
<\/a><\/p>\n\r\nUSE master\r\nGO\r\n\r\nCREATE DATABASE QS_on\r\nGO\r\n\r\nUSE QS_on\r\nGO\r\n\r\nSELECT *\r\nFROM sys.database_query_store_options \r\nGO\r\n<\/pre>\n
<\/a><\/p>\n\r\nALTER DATABASE [dbname] SET QUERY_STORE = ON\r\nGO\r\n<\/pre>\n
\n2. You can define how big it can grow<\/strong><\/p>\n\r\nALTER DATABASE [dbname] SET QUERY_STORE = ON (MAX_STORAGE_SIZE_MB = number)\r\nGO\r\n<\/pre>\n
\n3. You can delete the data in a simple way<\/strong><\/p>\n\r\nALTER DATABASE [dbname] SET QUERY_STORE CLEAR\r\nGO\r\n<\/pre>\n
\n4. Query Store data is backed up and restored along the rest of user data<\/strong><\/p>\n\r\nUSE QS_on\r\nGO\r\n\r\nCREATE TABLE dbo.t(\r\nc1 INT IDENTITY PRIMARY KEY\r\n)\r\nGO\r\n\r\nINSERT INTO dbo.t DEFAULT VALUES \r\nGO 10\r\n\r\nSELECT * FROM dbo.t WHERE c1 = 1\r\nSELECT * FROM dbo.t\r\nGO 5\r\n\r\nSELECT q.query_id, qt.query_sql_text, q.last_execution_time, qr.count_executions\r\n\tFROM sys.query_store_query AS q\r\n\t\tLEFT JOIN sys.query_store_query_text AS qt\r\n\t\t\tON qt.query_text_id = q.query_text_id\r\n\t\tLEFT JOIN sys.query_store_plan AS qp\r\n\t\t\tON qp.query_id = q.query_id\r\n\t\tCROSS APPLY (SELECT TOP 1 count_executions FROM sys.query_store_runtime_stats WHERE plan_id = qp.plan_id ORDER BY runtime_stats_id DESC) AS qr\r\nGO\r\n\r\nSELECT OBJECT_NAME(object_id), * \r\n\tFROM sys.dm_db_index_usage_stats \r\n\tWHERE database_id = DB_ID()\r\nGO\r\n<\/pre>\n
<\/a><\/p>\n\r\n\r\nUSE master\r\nGO\r\n\r\nBACKUP DATABASE QS_on TO DISK = 'QS_on.bak' WITH INIT\r\nGO\r\n\r\nRESTORE DATABASE QS_on FROM DISK = 'QS_on.bak' WITH RECOVERY, REPLACE\r\nGO\r\n\r\nUSE QS_on\r\nGO\r\n\r\nSELECT q.query_id, qt.query_sql_text, q.last_execution_time, qr.count_executions\r\n\tFROM sys.query_store_query AS q\r\n\t\tLEFT JOIN sys.query_store_query_text AS qt\r\n\t\t\tON qt.query_text_id = q.query_text_id\r\n\t\tLEFT JOIN sys.query_store_plan AS qp\r\n\t\t\tON qp.query_id = q.query_id\r\n\t\tCROSS APPLY (SELECT TOP 1 count_executions FROM sys.query_store_runtime_stats WHERE plan_id = qp.plan_id ORDER BY runtime_stats_id DESC) AS qr\r\nGO\r\n\r\nSELECT OBJECT_NAME(object_id), * \r\n\tFROM sys.dm_db_index_usage_stats \r\n\tWHERE database_id = DB_ID()\r\nGO\r\n<\/pre>\n
<\/a><\/p>\n
\n5. Query Store become READ_ONLY when the database goes READ_ONLY<\/strong><\/p>\n\r\nALTER DATABASE QS_on SET QUERY_STORE CLEAR \r\nGO\r\n\r\nALTER DATABASE QS_on SET READ_ONLY WITH ROLLBACK IMMEDIATE\r\nGO\r\n\r\nSELECT * FROM dbo.t WHERE c1 = 1\r\nSELECT * FROM dbo.t\r\nGO 5\r\n\r\n\r\nSELECT q.query_id, qt.query_sql_text, q.last_execution_time, qr.count_executions\r\n\tFROM sys.query_store_query AS q\r\n\t\tLEFT JOIN sys.query_store_query_text AS qt\r\n\t\t\tON qt.query_text_id = q.query_text_id\r\n\t\tLEFT JOIN sys.query_store_plan AS qp\r\n\t\t\tON qp.query_id = q.query_id\r\n\t\tCROSS APPLY (SELECT TOP 1 count_executions FROM sys.query_store_runtime_stats WHERE plan_id = qp.plan_id ORDER BY runtime_stats_id DESC) AS qr\r\nGO\r\n\r\nSELECT OBJECT_NAME(object_id), * \r\n\tFROM sys.dm_db_index_usage_stats \r\n\tWHERE database_id = DB_ID()\r\nGO\r\n\r\n<\/pre>\n