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
50 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 ...
user avatar
  • 39
1 vote
1 answer
50 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 ...
user avatar
7 votes
2 answers
327 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 ...
user avatar
  • 307
2 votes
1 answer
91 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 ...
user avatar
  • 307
3 votes
1 answer
201 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?
user avatar
  • 307
2 votes
0 answers
77 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 ...
user avatar
  • 307
7 votes
2 answers
304 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 ...
user avatar
  • 24.6k
1 vote
1 answer
31 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 ...
user avatar
0 votes
0 answers
47 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 ...
user avatar
  • 1,349
4 votes
3 answers
281 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 ...
user avatar
1 vote
1 answer
131 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) ...
user avatar
0 votes
1 answer
107 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 ...
user avatar
  • 1
5 votes
1 answer
123 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 ...
user avatar
  • 1,382
3 votes
3 answers
296 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 ...
user avatar
  • 171
0 votes
1 answer
29 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 ...
user avatar
0 votes
1 answer
67 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 ...
user avatar
  • 119
1 vote
1 answer
222 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 ...
user avatar
  • 113
2 votes
0 answers
76 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....
user avatar
  • 21
1 vote
1 answer
399 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 ...
user avatar
1 vote
1 answer
77 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 ...
user avatar
  • 179
6 votes
2 answers
6k 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 ...
user avatar
  • 322
3 votes
1 answer
105 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 ...
user avatar
  • 93
2 votes
1 answer
302 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:...
user avatar
2 votes
1 answer
125 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 ...
user avatar
0 votes
2 answers
372 views

Should I believe Query Store runtime stats?

There is this weird behavior of Query Store runtime stats that I observe at many of my servers with different scenarios that makes me not to believe these stats. Or am I wrong in something? E.g. ...
user avatar
  • 935
16 votes
1 answer
770 views

Forced plans on readable secondaries

If a plan is forced on the primary in an Availability Group, is it applied to queries run on a secondary? I'm looking for answers that cover both possibilities for plan forcing: Plan Guides Query ...
user avatar
  • 70.8k
2 votes
3 answers
1k views

How do I query for system views in a SQL Server database?

I have some queries that run against Query Store, the view sys.query_store_wait_stats is not in SQL Server 2016, but it is in SQL Server 2017. I want my queries to work in SQL Server 2016 and SQL ...
user avatar
3 votes
1 answer
447 views

Trying to repair query store, but sp_query_store_consistency_check does not exist

The query store on our SQL Server 2016 Standard (SP2 CU6) database has gone into ERROR state and we are now trying to get it back online. All of the articles I've seen regarding troubleshooting QS (e....
user avatar
  • 322
7 votes
1 answer
525 views

Understanding Plan Choice When Query Store Doesn't Respect Forced Plan

I'm trying to figure out a situation that I encountered last week where, in an attempt to temporarily fix a plan regression during an incident, I forced an execution plan using Query Store, but found ...
user avatar
  • 95
7 votes
2 answers
2k views

Why is Query Store missing details?

This is slightly complex to explain, but very easy to reproduce. Short version: For about 10%-30% queries (by count) significant amounts of Query Store data is not available. I have looked at SQL ...
user avatar
6 votes
1 answer
383 views

QueryStore plan forcing limitations

I have a DELETE statement running against a table with a full-text indexed column, a handful of foreign keys with cascade enabled. It looks like this: DELETE FROM dbo.STUDENTS WHERE STUDENTID=@...
user avatar
  • 1,480
0 votes
1 answer
461 views

How to search Query Store for queries with multiple plans?

There are a number of blog posts about why a single query with multiple plans is bad. But they mostly address when you find them, not how to find them. Query Store does not offer a report, that ...
user avatar
11 votes
1 answer
151 views

What is an "unmentionable word" to SQL?

What is an "unmentionable word" to SQL? I am reading sys.query_store_query_text (Transact-SQL) and I see the following, so I google {SQL "unmentionable" word} has_restricted_text bit Query ...
user avatar
4 votes
2 answers
435 views

What is 'Log Memory' in Query Store 2017

In SQL 2017 there is a new execution metric, 'Log memory' other than that it was added in 2017 I am not finding anything about it. Execution metric: (SQL 2017) CPU time, Duration, Execution Count, ...
user avatar
7 votes
3 answers
4k views

How can I export Query Store data?

I have Query Store (QS) running on a SQL 2017 instance. Currently at RTM, with RTM CU13 currently in test, to be applied on prod in next month's patch window. While most queries and reports return ...
user avatar
1 vote
0 answers
258 views

Query Store- force plan not working as expected

Have similar issue as was ask in other question. In my case none of cases from microsoft docs seems to apply Can something else be the reason? Schema changes not happening, but after index ...
user avatar
  • 1,228
5 votes
1 answer
361 views

Should the Query Store be enabled on all databases?

We are using SQL Server 2016 Standard Edition (SP2-CU2). We have a few servers hosting each between a few dozens to hundreds of databases. We have never enabled Query Store on our databases. I know ...
user avatar
4 votes
1 answer
405 views

How to find inputs that caused different query plans in Query Store

Query store shows that a completely parameterized query has multiple query plans: How can I find the input that is causing such long response times? Why would the same parameterized query end up ...
user avatar
  • 143
0 votes
1 answer
250 views

Don't RESEED tables on Query Store Clean

We are using Query Store. And now we have solved one problem with it by cleaning the Query Store every night. And when you clean the Query Store, the Query Store tables are being RESEEDed :-( Do you ...
user avatar
1 vote
1 answer
806 views

Visual Studio SSDT Ignore Query Store Options in Publish Profile

Is there any way in Visual Studio SSDT Publish Profile, to Totally Ignore Query Store Options Completely? It keeps asking us to Rerun publish code below. I go to Database advanced settings, and turn ...
user avatar
4 votes
1 answer
465 views

How to find when Query Store flushes stale query data? Extended Events not working

I searched online far and wide and didn't see anyone else posting about this so I thought I would. I am trying to audit when the query store does its cleanup and is purged from the query store. There ...
user avatar
10 votes
2 answers
1k views

Blocking caused by Query Store. Unable to clear or disable

I recently updated our 2016 SQL Server to SP2 and the most recent CU (KB4458621) released in August 2018. Just in the last day or so, I noticed that I have some blocking going on. I can't kill the ...
user avatar
  • 101
1 vote
1 answer
494 views

Monitor SQL Server stored procedure latency

What is the best way to capture SQL Server stored procedure latency in SQL Server 2016? Plan is to create a SSRS report and send it to management. We also enabled query store. Thanks much
user avatar
  • 13
10 votes
1 answer
429 views

Why doesn't join elimination work with sys.query_store_plan?

The following is a simplification of a performance problem encountered with the Query Store: CREATE TABLE #tears ( plan_id bigint NOT NULL ); INSERT #tears (plan_id) VALUES (1); SELECT T....
user avatar
  • 70.8k
2 votes
1 answer
243 views

How to search Query Store for a specific word or command in a query?

I have an Enterprise Edition of SQL 2016 SP1, that has a database with Query Store active. I want to search for a specific word or command in the saved queries. I have googled around and not seen ...
user avatar
2 votes
0 answers
142 views

Query store works in dev, but not production in Amazon RDS

I'm having an issue getting query store to collect and record data on our production workload, however, it works as expected on our non-production instances (development and staging). The only ...
user avatar
  • 21
10 votes
1 answer
2k views

Unbelievably slow and unusable Query Store

I'm beginning with Query Store and it's with problems :-( No report of Top Resource Consuming Queries is ever generated. It just keeps saying "Waiting" for hours and hours. Query on the ...
user avatar
  • 935
4 votes
1 answer
655 views

Where is this Query Coming From?

I have enabled Query Store on SQL 2017, and I'm seeing at query that's occurring frequently doing a SELECT * on a specific table. I would like to narrow down where this request is coming from and ...
user avatar
  • 183
0 votes
1 answer
221 views

Auto cleanup querystore not working

We have a SQL Server 2016 SP1 CU7 Enterprise server where we enabled the query store on a database. This is a highly used database.The Max Size has been set to 200MB and Stale Query Threshold is 1 day....
user avatar
13 votes
2 answers
2k views

Never ending Query Store search

I'll say from the beginning that my question/problem looks similar to this previous one, but since I'm not sure if the cause or the starting info is the same, I decided to post my question with some ...
user avatar
  • 15.1k