Questions tagged [query-store]

Introduced in SQL Server 2016, the Query Store feature collects execution plans and runtime performance metrics for analysis, troubleshooting, and testing purposes.

Filter by
Sorted by
Tagged with
1 vote
1 answer
81 views

SSMS 20 - Query Store - Configure tracked queries broken?

Tracking a query in the Query Store is stuck in SSMS 20 on Avg duration via the Configure option. It shows a red line around it, but it ignores the selected Metric and Statistic Any way to solve it? ...
Firat Aygur's user avatar
0 votes
0 answers
84 views

Can Query Store readonly reason 262144 be removed without clearing the Query Store?

I recently had Query Store enter readonly mode due to Readonly Reason 262144. It is documented here. 262144 - Size of in-memory items waiting to be persisted on disk has reached the internal memory ...
J. Mini's user avatar
  • 139
1 vote
2 answers
84 views

QueryStore plan forcing errors

We have a weekly stored procedure that ocassionaly picks up wrong execution plan that causes much longer execution times. I've tried to force correct execution plan but still every few weeks the ...
Andrzej Błoch's user avatar
0 votes
1 answer
66 views

Cannot disable Query store due to QUERY STORE ASYNC LOAD

I'm using Microsoft SQL Server 2016 (SP2-GDR) (KB4505220) - 13.0.5101.9 (X64). One of our databases seem to have a run away Query Store with over 90 gb stored. I want to disable QS, but cannot do so ...
siggemannen's user avatar
0 votes
1 answer
77 views

When do physical reads indicate a lack of RAM?

With Query Store, I can track how big my physical reads are and how often they occur. How can I tell if these numbers are so big that they indicate that I need more RAM? For that it's worth, my other ...
J. Mini's user avatar
  • 139
-1 votes
1 answer
80 views

Can Query Store Show Spills To Tempdb?

I know that Query Store can show tempdb usage as of SQL Server 2017, but can it show spills to tempdb? I'm highly confident that the plan cache can.
J. Mini's user avatar
  • 139
1 vote
0 answers
48 views

sp_BlitzQueryStore reports "multiple plans", but I can only find one execution plan. Why?

sp_BlitzQueryStore reports a particular query on several of my rows. In each of these, it reports Unparameterized Query, Parallel, Multiple Plans, Row estimate mismatch However, each of these rows ...
J. Mini's user avatar
  • 139
1 vote
3 answers
99 views

What is the Postgres Equivalent of SQL Server's Query Store?

I consider Query Store to be the best idea that Microsoft's SQL Server has had in the past ten years. I worry that I will miss it very dearly when I move to Postgres. What equivalents does Postgres ...
J. Mini's user avatar
  • 139
-4 votes
2 answers
127 views

Why Prefer Extended Events to sp_WhoIsActive and Query Store?

If I want to find out what is happening right now, then I use Adam Machanic's sp_whoisactive. If I want to find out what has happened on my server recently, then I will use Query Store. Extended ...
J. Mini's user avatar
  • 139
2 votes
1 answer
194 views

What are the advantages and disadvantages of inspecting the Plan Cache rather than the Query Store?

The Plan Cache and the Query Store are not the same, even for the same query. When hunting for performance or monitoring information on a specific query or set of related queries, what are the ...
J. Mini's user avatar
  • 139
1 vote
2 answers
147 views

How to measure the cost of enabling Query Store?

Enabling Query Store can have performance benefits, particularly as of SQL Server 2022, but what about the costs? The documentation insists that the costs are small, but I want proof on my own system. ...
J. Mini's user avatar
  • 139
1 vote
1 answer
139 views

SQL Server Extended Event Session for Attention is missing field sql_text

I have an extended event session set up to track Attention events, to monitor procedures that timed out. This is the create script: CREATE EVENT SESSION [ApplicationTimeout3] ON SERVER ADD EVENT ...
Sylvia's user avatar
  • 203
4 votes
2 answers
267 views

How can SQL Server's Query Store Reports show data for minute-length intervals, when "Statistics Collection Interval" is set to 1 hour?

In the database properties under Query Store, the "Statistics Collection Interval" is set to 1 hour. This is also confirmed by querying the view query_store_runtime_stats_interval - all ...
Matthias L's user avatar
-2 votes
1 answer
48 views

Why force plan is not working with failure "NO_PLAN"?

We have query which is inserting data to heap: INSERT INTO [heap] ( 80 COLUMNS... ) SELECT 14 columns 65 NULL VALUES , '...
adam.g's user avatar
  • 355
3 votes
1 answer
681 views

What is "execution count" in Query Store?

We are using SQL Server 2016. We have some problems in terms of queries duration on 2023-09-11. Would like to check it on QueryStore and I saw on query store executed on 2023-09-11 05:00 PM: That we ...
adam.g's user avatar
  • 355
0 votes
0 answers
38 views

Why are there entries with no runtime stats interval values for DOP_FeedBack feature verification query

In the SQL2022 Workshop files there is a query used in the demo for DOP_Feedback, which is the below. However, it doesn't have the runtime stats interval so I can't see when the affected queries ran. ...
Lee M's user avatar
  • 388
0 votes
0 answers
25 views

Two Databases using query store - one saves Paramerized Queries as literals in sys.query_store_sql_text , the other saves them as parameterized [duplicate]

I have two test databases - AdventureWorks2012 and StackOverflow2010 on the same SQL Server: Microsoft SQL Server 2019 (RTM-CU21) (KB5025808) - 15.0.4316.3 (X64) Jun 1 2023 16:32:31 ...
SE1986's user avatar
  • 1,960
1 vote
0 answers
40 views

Scheduling Query Store data clean up time

Is there a way to specify when QueryStore should perform cleanup based on the STALE_QUERY_THRESHOLD_DAYS setting? I am currently experiencing issues when the cleanup kicks in at peak times. i.e. we ...
user8440261's user avatar
1 vote
2 answers
296 views

Query Store plan does not include parameter list

I use the query store to get an overview of executed queries on our databases and to generate performance tests where I execute those queries in parallel on multiple hosts. My task is to test how many ...
SteLoe's user avatar
  • 342
0 votes
1 answer
104 views

AUTOMATIC_TUNING SQL Server

IS it possible to force the correct plan in the stored procedure syntax? because current I found the option only at the base level ALTER DATABASE TEST SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON );...
Abdallah Mehdoini's user avatar
3 votes
1 answer
402 views

Query store plan is not forcing and there is no failure reason [duplicate]

I have forced a plan in query store as follows EXEC sys.sp_query_store_force_plan @query_id = 113366, @plan_id = 3687662 but when I run the query again, the query does not use the plan, nor does it ...
SE1986's user avatar
  • 1,960
0 votes
1 answer
308 views

How to interpret output of query store in sql server

I ran the following query shown in the image below. I understand the left pane of the query store below that says query (with id 1962) took about 151000 ms to run. Question: But I don't understand the ...
nam's user avatar
  • 291
6 votes
1 answer
288 views

Query Store plan force fails with NO_PLAN dependent on where filter operator is in plan

I have a query which I force a plan for in Query Store (the plan is the one SQL Server compiled for this query) If I run the query immediately afer forcing the plan, I get the NO_PLAN ...
SE1986's user avatar
  • 1,960
1 vote
2 answers
631 views

Query Store plan forcing failed with failure reason NO_PLAN [duplicate]

I have a query in query store for which I have forced a plan. I can confirm the plan is forced with SELECT * FROM sys.query_store_plan WHERE is_forced_plan = 1 and the plan shows up in the results ...
SE1986's user avatar
  • 1,960
2 votes
0 answers
43 views

How to tell if SQL Query Store is forcing the plan when executing a query in SSMS

Just wondering if there's an easy way to tell if the execution plan is being forced by query store when running a query in SSMS.
6b60449be4a642eeb74795e88d3d72's user avatar
0 votes
0 answers
117 views

Connect query from sp_whoisactive and Query Store

We have application which is returning result of sp_whoisactive every 1 minute. Is that any option to connect (join) results from sp_whoisactive and QueryStore ? I would like to add some stats from ...
adam.g's user avatar
  • 355
1 vote
1 answer
170 views

How to configure Query Store for resource usage

I want to know how many milliseconds of CPU utilization happens for a particular database per 5-minute period. Please correct me if I'm wrong, but the Query Store looks like a great way to find that ...
uncaged's user avatar
  • 39
1 vote
1 answer
1k views

SQL Server Query Store - why does it not always use the forced plan? [duplicate]

I forced a plan to be used for a specific query ID. However, SQL Server seems to only use the forced plan occasionally, interspersed with other plans. See image below. I forced plan 154315 for ...
camaro322hp's user avatar
8 votes
2 answers
796 views

Does killed session appears in Query Store?

Is there any option to see killed sessions in Query Store? I am asking, because we have an additional tool which is terminating sessions if they take more than 30 minutes to run (KILL command). I ...
adam.g's user avatar
  • 355
2 votes
1 answer
444 views

Strange behaviour of force plan in query store

I force plan in query store. Plan is connected to procedure in a job which run once a day. One of step of this job is just: EXEC [schema].[LoadData] Procedure [schema].[LoadData] looks like TRUNCATE ...
adam.g's user avatar
  • 355
5 votes
1 answer
742 views

Does Query Store force plan persist following server restart?

Would like to ask because cannot find this information. Does Query Store force plan persist following server restart? In which cases will forcing a plan via query store not remain in effect?
adam.g's user avatar
  • 355
2 votes
0 answers
121 views

Huge Difference in IO and CPU for same query

I saw that same query got more than two execution plans. Query is included inside procedure which runs once a day every day. All tables in query got from 2000 to 50000 additional records every day. I ...
adam.g's user avatar
  • 355
7 votes
2 answers
956 views

Is the Query Store's Overall Resource Consumption report telling me my database is doing terribly or are the numbers just whacked?

Recently we upgraded one of our production servers from SQL Server 2016 to SQL Server 2019 (CU 15). This was a great opportunity for me to enable Query Store on our main application database. It's ...
J.D.'s user avatar
  • 37.5k
1 vote
1 answer
79 views

Pairing Query Store Read/Write Status with Enabled Status

I am able to find if Query Store is enabled: SELECT name, is_query_store_on from sys.databases I am able to query a specific database to determine the Query Store Read/Write Status: SELECT ...
CrushingIT's user avatar
0 votes
0 answers
84 views

Query runtime regressed from 80 seconds to >1900 seconds despite Same Plan and Logical Reads

I have enabled Query Store on a development Data Warehouse SQL Server to try and work out which queries that are part of the overnight Data Warehouse build have inconsistent run times. The server runs ...
SE1986's user avatar
  • 1,960
4 votes
4 answers
2k views

Query Store takes a very long time to load duration

I'm running Query Store to make a DB perform better. It's been working fine until now.. When i try to load the Top durations of last day it took me 26 min to load the screen. The SQL version im ...
Firat Aygur's user avatar
1 vote
1 answer
425 views

Query Store internal queries being executed thousands of times per minute

I have an SQL Server 2016 Standard production server with more 59 clients Databases. Microsoft SQL Server 2016 (SP2-CU17) (KB5001092) - 13.0.5888.11 (X64) Mar 19 2021 19:41:38 Copyright (c) ...
Danielle Paquette-Harvey's user avatar
0 votes
1 answer
869 views

SSMS Query store keeps getting disabled

I activated query store about a month ago with the settings below. Now, every morning starting up SSMS I find the Query store folder gone and query store disabled. Initially I used 1 Gb size, but ...
mpn275's user avatar
  • 1
6 votes
1 answer
555 views

How to check Query Store settings on model database?

Query Store can be enabled on the model database and ensures that every new database has the same settings as the model database. The GUI option is missing But it can be enabled using TSQL ALTER ...
Zikato's user avatar
  • 4,507
3 votes
3 answers
519 views

Query store - bumping up max_storage_size_mb (Query Store Retention, Max Size) works for a short while then goes back to 250?

I've been unsuccessful doing this a few times now. In the UI I'm able to successfully bump up the size of the query store in Query Store Retention setting from 250 to 1000 mb. I make the change, close ...
Sylvia's user avatar
  • 203
0 votes
1 answer
130 views

Query store data maintenance background tasks

Query store has been running for years on the server without any noticeable disk IO spikes on log backups. We run on SQL Server 2016 EE version 13.0.5622.0 Recently we experienced a sudden surge in ...
Rasmus Remmer Bielidt's user avatar
0 votes
1 answer
267 views

Query Store System View JOINs

In SQL Server 2016, is there an optimal/correct way to JOIN sys.query_store_query_text and sys.query_store_query (joinable using query_text_id) with sys.query_store_runtime_stats? I don't see any ...
MinnRick's user avatar
  • 119
1 vote
1 answer
951 views

Reason for failed query plan executions in SQL Server

We are using Azure SQL Database and recently we have experienced a severe performance degradation on our production database. When looking into Regressed queries in Query Store to find out the ...
twoflower's user avatar
  • 113
2 votes
0 answers
152 views

SQL Server sys.query_store_runtime_stats max_dop field reporting wrong values?

I have been trying to use the Query Store in SQL Server as an easy way to identify queries running in parallel and the degree of parallelism. However, when I look at the max_dop field in sys....
Joe Lax's user avatar
  • 21
1 vote
1 answer
1k views

SQLServer Query Store not saving failed queries run by SP_ExecuteSQL

We are on SQL Server 2016 Query Store is activated on the database in question. We have had some timeout problems reported on a nightly batchjob, and we have located the query in question. The query ...
Peter Tilsted's user avatar
2 votes
1 answer
156 views

With SQL Server's Query Store, how can cleanup avoid deleting "least expensive" queries?

I'm only interested in resource consumption (QUERY_CAPTURE_MODE = AUTO), with a fairly short interval (e.g. INTERVAL_LENGTH_MINUTES = 5). I'll be grabbing data from the query store on a fairly ...
uncaged's user avatar
  • 189
13 votes
2 answers
23k views

Find specific query in Query Store

I have enabled Query Store on a database. I have a specific query which I want to track. I have a lot of details about the query from sp_BlitzCache (like SQL Text, SQL Handle, SQL Hash, Plan Cache ...
MHeld's user avatar
  • 402
3 votes
1 answer
291 views

Why are queries getting dropped from query store?

I see sometimes that queries are dropped from the query store. But I don't know why. The query store disk-space is not full and the query is far from the stale query treshold. What I experience is ...
Jostein's user avatar
  • 93
2 votes
1 answer
432 views

Some timeout in query store but get all SP:Starting and SP:Completed in profiler

I have a report SP running. The user complaint report timeout sometimes. And I do get some canceled execution plan in my query store. But in my profiler tracing file, every SP:Starting has its SP:...
DBALUKE HUANG's user avatar
2 votes
1 answer
171 views

Query store audit

We frequently use query store force plan feature. We would like to audit when and who forced each plan. Is extended events session is needed auditing this event, or there are dmvs or catalog views ...
Michael Cherevko's user avatar