{"id":1182,"date":"2021-03-08T08:30:00","date_gmt":"2021-03-08T05:30:00","guid":{"rendered":"https:\/\/www.dataplatform.gr\/?p=1182"},"modified":"2025-01-15T14:35:08","modified_gmt":"2025-01-15T11:35:08","slug":"ti-einai-to-query-store-kai-pos-to-chrisi","status":"publish","type":"post","link":"https:\/\/www.dataplatform.gr\/en\/ti-einai-to-query-store-kai-pos-to-chrisi\/","title":{"rendered":"What is Query Store and how we use it in SQL Server"},"content":{"rendered":"

The Query Store<\/strong> appeared from the release SQL Server 2016<\/strong> and then. It is a tool provided so that we can track each query <\/strong>(query) to the database. <\/p>\n\n\n\n

Through the Query Store we can see a query, when it was executed, its duration, the execution plan<\/strong> which he used and many others.<\/p>\n\n\n\n

Let's remember what the execution plan is<\/h2>\n\n\n\n

The execution plan<\/strong> is essentially a map that you create from the RDBMS query optimizer taking into account the statistics\/cardinality<\/strong> to find the optimal way to pass the data for the process we have asked it to do.<\/p>\n\n\n\n

Through Query Store we can see it execution plan<\/strong> (plan) that existed every time each query was executed over time and to identify when the plan changed. It enables us to do force <\/strong>the plan so that the optimizer does not choose but we declare that we want the specific one.<\/p>\n\n\n\n

Very useful features are also the ability to see which queries are the most expensive, the history of each query, how many times the query was executed in a certain period of time and when the performance of a query dropped due to a change of plan.<\/p>\n\n\n\n

\"\"
The Query Store in the SSMS GUI.<\/figcaption><\/figure>\n\n\n\n

How to enable and configure with T-SQL<\/h2>\n\n\n\n

<\/p>\n\n\n\n

To activate it, simply run the command:<\/p>\n\n\n\n

ALTER DATABASE [partition] SET QUERY_STORE = ON;<\/pre>\n\n\n\n
With the OPERATION_MODE<\/strong> we define whether in the Query Store we will only be able to read data READ_ONLY <\/strong>or that it will be updated with new ones READ_WRITE<\/strong>:<\/p>\n\n\n\n
ALTER DATABASE [partition] \nSET QUERY_STORE (OPERATION_MODE = READ_WRITE); <\/pre>\n\n\n\n
The MAX_STORAGE_SIZE_MB<\/strong> is the query store data size limit. In a production environment the value must be above 2048 MB, but the value must also be proportional to the number of days in case we set the number of days STALE_QUERY_THRESHOLD_DAYS <\/strong>in 90 days e.g. it is recommended to use 8192 MB and accordingly current_storage_usage <\/strong>in the sys<\/strong>.database_query_store_options<\/strong>:<\/p>\n\n\n\n
ALTER DATABASE [partition] \nSET QUERY_STORE (MAX_STORAGE_SIZE_MB = 1024); <\/pre>\n\n\n\n
With the parameter SIZE_BASED_CLEANUP_MODE <\/strong>= AUTO <\/strong>when Query Store data approaches the maximum size the oldest will be deleted. Otherwise case defined as OFF <\/strong>the operation mode changes to read-only, as a result of which the collection of new data is stopped:<\/p>\n\n\n\n
ALTER DATABASE [partition] \nSET QUERY_STORE (SIZE_BASED_CLEANUP_MODE = AUTO); <\/pre>\n\n\n\n
With the parameter CLEANUP_POLICY<\/strong> we define for how many days the data will be kept. The higher the value, the more disk space it will consume:<\/p>\n\n\n\n
ALTER DATABASE [partition] \n\nSET QUERY_STORE (CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30));<\/pre>\n\n\n\n
The Interval Length Minutes <\/strong>determines every time the query statistics (duration, tempdb_usage, memory_usage, count executions, etc.) will be aggregated (average, sum ...) in a different record in the table sys<\/strong>.query_store_runtime_stats <\/strong>. The smaller the value, the more information we have over time, but at the same time the data on the disk increases:<\/p>\n\n\n\n
*Caution this parameter can only take specific values (1, 5, 10, 15, 30, 60, 1440)<\/em><\/p>\n\n\n\n
ALTER DATABASE [partition]  \n\nSET QUERY_STORE (INTERVAL_LENGTH_MINUTES = 60);<\/pre>\n\n\n\n
\"\"
Example with the interval set to 60 minutes, we see a different record with hourly statistics<\/figcaption><\/figure>\n\n\n\n
That data flush interval <\/strong> is the time in seconds that query store statistics are written from memory to disk. In case of memory pressure, this parameter is automatically ignored and the data on the disk is flushed at that moment. As long as this data has not been flushed, it will not be visible in the dynamic views of the Query Store. Suggested value of 900 seconds (15 minutes):<\/p>\n\n\n\n
ALTER DATABASE [partition]  \n\nSET QUERY_STORE (DATA_FLUSH_INTERVAL_SECONDS = 900);\n\n<\/pre>\n\n\n\n
With the parameter ALL <\/strong>in the  QUERY_CAPTURE_MODE <\/strong>we define to record all the queries that are executed. With the parameter AUTO<\/strong> only those queries that have been executed many times or have a long duration will be recorded. With the parameter NONE<\/strong> the statistics will be updated only for those queries that have already been recorded:<\/p>\n\n\n\n
ALTER DATABASE [partition]  \n\nSET QUERY_STORE (QUERY_CAPTURE_MODE = ALL);<\/pre>\n\n\n\n
To SQL Server 2019<\/strong> we also have the choice CUSTOM<\/strong> where we can define multiple criteria, e.g.:<\/p>\n\n\n\n
ALTER DATABASE [partition]  SET QUERY_CAPTURE_POLICY = (\n        STALE_CAPTURE_POLICY_THRESHOLD = 24 HOURS,\n        EXECUTION_COUNT = 30,\n        TOTAL_COMPILE_CPU_TIME_MS = 1000,\n        TOTAL_EXECUTION_CPU_TIME_MS = 100\n      );\n<\/pre>\n\n\n\n
Also in SQL Server 2017 \/ 2019 we have the possibility to define whether the wait statistics will also be recorded with the parameter WAIT_STATS_CAPTURE_MODE <\/strong>which default is ON:<\/p>\n\n\n\n
ALTER DATABASE [partition]\nSET QUERY_STORE (WAIT_STATS_CAPTURE_MODE = ON);<\/pre>\n\n\n\n
Done with the parameter MAX_PLANS_PER_QUERY<\/strong> we define the maximum number of shots per query. The value 200 is the default and we recommend leaving it as is:<\/p>\n\n\n\n
ALTER DATABASE [partition]\nSET QUERY_STORE (MAX_PLANS_PER_QUERY = 200);<\/pre>\n\n\n\n
Query Store Activation Example for SQL Server 2019<\/h3>\n\n\n\n
ALTER DATABASE [partition] \n\nSET QUERY_STORE = ON\n    (\n      OPERATION_MODE = READ_WRITE,\n      CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 90 ),\n      DATA_FLUSH_INTERVAL_SECONDS = 900,\n      MAX_STORAGE_SIZE_MB = 2048,\n      INTERVAL_LENGTH_MINUTES = 60,\n      SIZE_BASED_CLEANUP_MODE = AUTO,\n      MAX_PLANS_PER_QUERY = 200,\n      WAIT_STATS_CAPTURE_MODE = ON,\n      QUERY_CAPTURE_MODE = CUSTOM,\n      QUERY_CAPTURE_POLICY = (\n        STALE_CAPTURE_POLICY_THRESHOLD = 24 HOURS,\n        EXECUTION_COUNT = 30,\n        TOTAL_COMPILE_CPU_TIME_MS = 1000,\n        TOTAL_EXECUTION_CPU_TIME_MS = 100\n      )\n   );<\/pre>\n\n\n\n
How we check the good operation of the Query Store<\/h2>\n\n\n\n
To check the status, the space used and the parameters of the query store:<\/p>\n\n\n\n
select * from sys.database_query_store_options; <\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
To clear the space of all data taking up space:<\/p>\n\n\n\n
ALTER DATABASE [partition] SET QUERY_STORE CLEAR;<\/pre>\n\n\n\n
If the query store stops working, we can do a check with the procedure:<\/p>\n\n\n\n
exec sp_query_store_consistency_check;<\/pre>\n\n\n\n
To disable the Query Store hard way which, however, has the effect of not writing the data that is in memory to the disk:<\/p>\n\n\n\n
ALTER DATABASE [partition] SET QUERY_STORE = OFF (FORCED);<\/pre>\n\n\n\n
In editions before SQL Server 2019 <\/strong>should be enabled trace flag 7745<\/em>. Its function is in the event of a shutdown or failover not to wait to write all the Query Store data that is in memory to the disk. Otherwise the Query Store would delay the restart of the instance.<\/p>\n\n\n\n
Extracting useful information with T-SQL<\/h2>\n\n\n\n
<\/p>\n\n\n\n
The query I made that makes our life easier...<\/h5>\n\n\n\n
We can do aggregate <\/strong>the data so that we can see for each plan that ran a query a comparison between them last interval  <\/strong>and his total, <\/strong>average of the statistics (run time, memory usage, tempdb, log usage, etc.). We can also see the full graphic of the shot:<\/p>\n\n\n\n
SELECT p.plan_id, p.query_id,qt.query_sql_text\n,case when q.object_id = 0 then 'ad_hoc' else OBJECT_NAME(q.object_id) end as object_name\n,DATEADD(HOUR,DATEDIFF(hour,  SYSUTCDATETIME(),SYSDATETIME() ),q.last_execution_time) as last_execution_time\n,ad.last_duration as agg_last_duration\n,ad.avg_duration_aggregate\n,cast(p.query_plan as xml) query_plan\n,ad.count_executions\n,ad.avg_tempdb_space_used\n,ad.avg_log_bytes_used\n,ad.avg_query_max_used_memory\n,ad.max_dop\nFROM sys.query_store_query AS q\ninner JOIN sys.query_store_plan AS p on p.query_id = q.query_id\ninner JOIN sys.query_store_query_text AS qt on q.query_text_id = qt.query_text_id  \ninner JOIN (select plan_id,avg(last_duration)as last_duration,avg(avg_duration)as avg_duration_aggregate,sum(count_executions) as count_executions, avg(avg_query_max_used_memory) as avg_query_max_used_memory,  avg(avg_tempdb_space_used) as avg_tempdb_space_used,avg(avg_log_bytes_used) as avg_log_bytes_used,max(max_dop) max_dop  from sys.query_store_runtime_stats group by plan_id ) as ad on ad.plan_id = p.plan_id\nWHERE 1=1\n--and p.is_forced_plan = 1 \n--and  p.query_id = 201\n--and  qt.query_sql_text like '%SELECT p.plan_id%'\norder by q.last_execution_time,q.query_id desc;<\/pre>\n\n\n\n
*dateadd is needed because SQL Server normally brings the dates in UTC time zone.<\/em><\/p>\n\n\n\n
**The cast to query_plan is needed so that when we click on the plan, the graphic opens.<\/em><\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
\"\"<\/figure>\n\n\n\n
To see which shots are forced and the reason they may have failed to be made<\/h5>\n\n\n\n
SELECT p.plan_id, p.query_id, qt.query_sql_text,q.object_id as containing_object_id, \n    force_failure_count, last_force_failure_reason_desc \nFROM sys.query_store_plan AS p \ninner JOIN sys.query_store_query AS q on p.query_id = q.query_id\ninner JOIN sys.query_store_query_text AS qt on q.query_text_id = qt.query_text_id  \nWHERE is_forced_plan = 1; <\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
The information we see with the Query Store GUI<\/h2>\n\n\n\n
The convenience that Query Store provides us is the extensive use of a graphical environment. Let's take a detailed look at the screens it provides:<\/p>\n\n\n\n
Query wait statistics:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
The queries that consumed the most resources:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
We can customize the data we see and change the filters:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
We can see the queries that are forced, the plan and the changes made to the plan over time.<\/p>\n\n\n\n
When we select a query from the query report it shows us all the shots that have changed over time. By selecting the button "Force Plan"<\/strong> we declare that from now on we want it to always run with the specific plan.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
In closing, we should not forget that like every extra function we add to a server, so too Query Store <\/strong>in SQL Server it comes with one overhead<\/strong>, which will reduce performance even to a small degree.<\/p>\n\n\n\n
We can change the parameters according to the problem we face in each case. It may still not be beneficial for our system to activate it at all and instead to activate the corresponding Extended Event (from SQL Server 2017 CU 14 version onwards). <\/p>\n\n\n\n
We will see how this is done in the next article...!<\/p>\n\n\n\n
Sources:<\/h2>\n\n\n\n