Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

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.

3
votes
1answer
89 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....
6
votes
1answer
53 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=@...
6
votes
1answer
86 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 ...
5
votes
2answers
106 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 ...
5
votes
2answers
194 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 ...
4
votes
2answers
106 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, ...
11
votes
1answer
822 views

Query Store Force Plan feature doesn't work

Query Store Force plan feature doesn't appear to be enforcing the plan. I'm aware of Query Store - Forced Doesn’t Always Mean Forced; however, my plan may not change insignificantly, but query ...
8
votes
1answer
337 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 ...
0
votes
1answer
31 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
119 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 ...
0
votes
0answers
16 views

Azure SQL Data Warehouse will always return zero (0)

I am studying Query Store, and while reading sys.query_store_runtime_stats I found these Average CPU time for the query plan within the aggregation interval (reported in microseconds). Note: ...
1
vote
1answer
145 views

How can you view the Query Store settings for a database?

I see that you can use notion like the following to set the query store configuration: ALTER DATABASE <database name> SET QUERY_STORE (SIZE_BASED_CLEANUP_MODE = AUTO); But how can I ...
1
vote
0answers
45 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 ...
4
votes
1answer
724 views

Unbelievably slow and unusable Query Store

I'm begining with Query Store and it's with problems :-( No report of Top Resource Consuming Queries is ever generated. It's just keep saying "Waiting" for hours and hours. Query on the background (...
10
votes
2answers
382 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 ...
3
votes
1answer
77 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 ...
4
votes
1answer
125 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 ...
7
votes
0answers
92 views

SQL Server 2016 & 2017 Force Plan failing with reason 8695

I'm trying to force a plan using query store but it keeps failing with reason 8695 according to data in SYS.QUERY_STORE_PLAN the last_force_failure_reason_desc is 'General_Failure'. Does anyone know ...
10
votes
4answers
2k views

SQL Server Query Store - What is considered an 'ad-hoc' query?

I've been doing a deep dive into the SQL Server Query Store and I often see references to 'ad-hoc' queries. However, I haven't seen what the Query Store determines an ad-hoc query to be. I've seen ...
1
vote
1answer
197 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 ...
1
vote
1answer
91 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 ...
3
votes
1answer
156 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 ...
2
votes
1answer
95 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 ...
1
vote
1answer
77 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
11
votes
1answer
222 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
0answers
46 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 ...
4
votes
1answer
605 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 ...
0
votes
1answer
62 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....
4
votes
1answer
107 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 ...
4
votes
1answer
72 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
191 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
78 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 ...
7
votes
1answer
702 views

Does SQL Server Query Store capture parameter values?

The new Query Store introduced in SQL Server 2016 is great. It's a nice replacement for much of what I used to do with the older Profiler tool. However, I haven't found a way to capture parameter ...
2
votes
1answer
195 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
1answer
180 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
207 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
374 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, ...
4
votes
2answers
2k 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 (...
2
votes
1answer
636 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 ...
2
votes
0answers
298 views

Query store is slow/unresponsive

When opening the query store for the first time on any give day, it will take a VERY long time to output data. I took a SQL script from this redgate blog article : Top 10 most expensive queries ...
2
votes
1answer
472 views

How to interpret Azure SQL QueryStore Query Performance Insight

The Azure SQL QueryStore is supposed to give you insight into what is causing high DTU usage, but I can not figure out what is going on. In some cases, I get good data out of it, and I can pinpoint to ...
7
votes
2answers
2k views

Indexes and Statistics on Temp tables

I've just upgraded our Data warehouse to SQL 2016. I'm seeing some really interesting graphs in the Query Store (I love this feature!). Below is the weirdest example i've seen. 22 plans for the same ...
0
votes
1answer
170 views

A Query in the Query Store shows high Logical Reads

When I look at the Query Store reports for Top Resource Consuming Queries, there is one query that skews the report so much that I can barely see the next query because this one is so high. Highest ...
2
votes
0answers
297 views

Query Store on Amazon RDS

I'm trying to use Query Store on SQL Server on Amazon RDS, and not having any luck. The query store works for a few hours after each failover, but then stops working. For example, our last manual ...
14
votes
1answer
5k views

SQL Server 2016 Bad Query Plan locks up DB once a week

Once a week, for the past 5 weeks, around the same time of day (early morning, may be based on user activity when people are starting to use it), SQL Server 2016 (AWS RDS, mirrored) starts timing out ...
1
vote
1answer
483 views

Delete from table with(snapshot)

I was trying to learn querystore. When going through few queries involved in query store one of the query is below DELETE TOP (1000000) FROM [sys].[memory_optimized_history_table_1179151246] WITH (...
3
votes
1answer
870 views

How to list all forced query plans and cancel one

Using SSMS 2016 Query Store I forced a few weeks ago a specific query to use a specific plan. Now every now and then I have the error: Query processor could not produce a query plan because of the ...
3
votes
1answer
1k views

How to enable query store on the SSMS object browser with Sql Azure?

I am following this guide: Monitoring Performance By Using the Query Store I am using SSMS 2016 CTP3.2 and Sql Azure V12 I have enabled the query store on the database properties. I can use the ...
4
votes
1answer
3k views

Problems caused by SQL Server 2016 Query Store

I have been testing SQL Server 2016 (Standard) on an Azure VM and have encountered a problem. In my SQL logs I am seeing SQLExceptions and eventid 1001 and this is happening every 15 minutes. 2016-...