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.
64
questions
0
votes
1answer
41 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 ...
5
votes
1answer
78 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 ...
3
votes
3answers
258 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 ...
0
votes
1answer
17 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
1answer
29 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 ...
0
votes
0answers
23 views
Cannot Change Azure SQL QueryStore Parameters
I change QueryStore parameters (such as MAX_STORAGE_SIZE_MB) on any of our Azure SQL Server managed instances. But then the parameter change back to default the next day. What could be the reason(s)?
1
vote
1answer
72 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 ...
2
votes
0answers
51 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....
1
vote
1answer
214 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 ...
1
vote
1answer
56 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 ...
2
votes
2answers
3k 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 ...
3
votes
1answer
81 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 ...
2
votes
1answer
255 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:...
2
votes
1answer
97 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 ...
0
votes
2answers
282 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. ...
16
votes
1answer
671 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 ...
2
votes
3answers
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 ...
3
votes
1answer
386 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....
7
votes
1answer
421 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 ...
7
votes
2answers
1k 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 ...
6
votes
1answer
314 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=@...
0
votes
1answer
351 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 ...
11
votes
1answer
149 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 ...
4
votes
2answers
355 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, ...
6
votes
2answers
3k 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 ...
1
vote
0answers
220 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 ...
5
votes
1answer
296 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 ...
asked Feb 19 '19 at 19:35
Danielle Paquette-Harvey
1,9121 gold badge14 silver badges23 bronze badges
4
votes
1answer
337 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 ...
0
votes
1answer
219 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 ...
1
vote
1answer
688 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 ...
4
votes
1answer
405 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 ...
10
votes
2answers
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 ...
1
vote
1answer
440 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
10
votes
1answer
386 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....
2
votes
1answer
211 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 ...
2
votes
0answers
105 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 ...
8
votes
1answer
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 ...
4
votes
1answer
492 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 ...
0
votes
1answer
188 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....
11
votes
2answers
1k 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 ...
4
votes
1answer
381 views
SQL Server Query Store on isolated volume
Is there a way to isolate Query Store data to a separate volume? I have a thousand or so databases that we’ve gotten pretty good at predicting growth for. I’m unsure of how much storage Query Store ...
1
vote
1answer
163 views
Multiple plans (with/without missing index) in query store for same query
I'm a newbie regarding the query store and have some problems understanding what I'm seeing.
We have a third-party application (running on SQL Server 2016 Enterprise SP1 CU7) that uses the query-hints ...
4
votes
1answer
90 views
Query Store Settings
I'm interested in the overall CPU utilization for a database in a time span. I see how to get that, but I'm concerned about the query store retention settings.
I don't need detailed info about each ...
2
votes
1answer
455 views
Query Store "Overall Resource Consumption" details
I want to know how much total CPU a database has consumed for a given date span, for all queries.
I can see it in SSMS (under Query Store / Overall Resource Consumption), but how can I query for ...
6
votes
3answers
2k views
Database corruption: QueryStore internal table
This morning, the following email alert was received:
DATE/TIME: 2/28/2018 9:26:42 AM
DESCRIPTION: Attempt to fetch logical page (1:3948712) in database 9
failed. It belongs to ...
6
votes
1answer
728 views
What to consider when setting flush_interval_seconds in SQL Query Store?
Most of the settings for Query Store are fairlying straight forward. But as best as I can tell the one most likely to cause performance issues (waits) and with the least clear documentation is ...
4
votes
1answer
396 views
Can I have 'Query Store' automatically on when I create new databases?
Query Store is available in SQL Server 2016+ and on Azure SQL DB it is on by default. With an on-premises SQL Server, it has to be started on each database individually.
Is there a way I can have ...
9
votes
2answers
516 views
What benefit is there from enabling Query Store on msdb?
Of the SQL system database (master, model, msdb, tempdb) query store can only be used on msdb. I looked and don't find any documentation about query store on msdb.
While you can't see it in the GUI, ...
5
votes
2answers
5k views
Enable query store for all databases
I want to start using Query Store with suggested setting by Erin Stellato.
USE [master]
GO
ALTER DATABASE DatabaseName SET QUERY_STORE = ON
GO
ALTER DATABASE DatabaseName SET QUERY_STORE (...
5
votes
1answer
2k views
Query Store - What are the Security permissions needed to view reports?
Query store is enabled on 2 of our production databases but I no longer have access to see the Query Store node under the database since my SA privileges were revoked (I had them temporarily for a new ...
