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.
107 questions
11
votes
2
answers
286
views
Huge log backups due to enabling querystore
We have a SQL Server 2019 CU18 where we discovered a strange issue with querystore.
Normally the average size of the hourly logbackup is 40MB but as soon as we enable querystore the average size of ...
- 2,114
2
votes
1
answer
104
views
Is there any information in Query Store that can be used to find block leaders?
I am on a server with major blocking issues. Not deadlocks, just plain blocks. We know that it is the server's primary bottleneck. However, they do not know what the block leaders are, so I do not ...
- 823
0
votes
0
answers
74
views
Is Automatic Plan Correction the only Query Store feature that is in SQL Server 2019 Enterprise Edition but not Standard Edition?
Hunting through the documentation for what is locked behind Enterprise Edition is not easy, but I gave it a good try. My conclusion was that Automatic Plan Correction is the only feature using Query ...
- 823
2
votes
3
answers
160
views
Timed out queries in Query Store (SQL Server)
I'm trying to find out timed out queries in Query store for production database .
I received logs from application site where for sure there was 5 timed out queries in short time window but somehow ...
- 21
1
vote
1
answer
61
views
Does Query Store track extended stored procedures?
My experiments suggest that Query Store does not track extended stored procedures. However, they each have their own individual behaviour, I am not even a beginner on the topic of extended stored ...
- 823
15
votes
1
answer
1k
views
Broken Query Store
We have SQL Server 2022 Enterprise (16.0.4131.2) on Windows 2022 VM (8 vCPU cores) and there is an issue with the Query Store on one of the databases.
Below is the sequence of steps I can proceed to ...
- 508
3
votes
1
answer
182
views
Query Store Plan Forcing is Failing with OPTIMIZATION_REPLAY_FAILED
I am trying to use Query Store to force a particular plan in an Azure SQL DB database, and it's not obeying me! I've checked in sys.query_store_plan, and it has a column which describes the reason for ...
- 131
2
votes
3
answers
336
views
High Memory Usage for SqlBulkCopy
I've started to use the QueryStore to monitor my App and one of the things I noticed is the unexpected high memory usage for what I thought a simple operation:
Which would mean the statement uses ...
- 27
1
vote
1
answer
112
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?
...
- 53
0
votes
0
answers
241
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 ...
- 823
1
vote
2
answers
98
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 ...
- 313
0
votes
1
answer
193
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 ...
- 133
0
votes
1
answer
236
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 ...
- 823
0
votes
1
answer
215
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.
- 823
1
vote
3
answers
296
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 ...
- 823
-4
votes
2
answers
256
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 ...
- 823
2
votes
1
answer
533
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 ...
- 823
2
votes
2
answers
229
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. ...
- 823
1
vote
1
answer
378
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 ...
- 203
4
votes
2
answers
557
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 ...
- 93
-2
votes
1
answer
82
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
, '...
- 421
4
votes
1
answer
1k
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 ...
- 421
0
votes
0
answers
42
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. ...
- 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
...
- 2,083
1
vote
0
answers
79
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 ...
- 31
1
vote
2
answers
373
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 ...
- 352
0
votes
1
answer
125
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 );...
3
votes
1
answer
566
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 ...
- 2,083
1
vote
1
answer
413
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 ...
- 311
6
votes
1
answer
355
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 ...
- 2,083
1
vote
2
answers
895
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
...
- 2,083
2
votes
0
answers
46
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.
0
votes
0
answers
124
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 ...
- 421
1
vote
1
answer
274
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 ...
- 39
1
vote
0
answers
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 ...
- 19
8
votes
2
answers
878
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 ...
- 421
2
votes
1
answer
888
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 ...
- 421
5
votes
1
answer
895
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?
- 421
2
votes
0
answers
139
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 ...
- 421
7
votes
2
answers
1k
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 ...
- 39.5k
1
vote
1
answer
96
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 ...
- 33
0
votes
0
answers
98
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 ...
- 2,083
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 ...
- 53
1
vote
1
answer
591
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) ...
- 2,039
0
votes
1
answer
1k
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 ...
- 1
6
votes
1
answer
718
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 ...
- 5,512
3
votes
3
answers
572
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 ...
- 203
0
votes
1
answer
163
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 ...
0
votes
1
answer
355
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 ...
- 119
1
vote
1
answer
1k
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 ...
- 113