Stack Overflow
Stack Overflow

Questions tagged [sql-query-store]

SQL Server Query store introduced in SQL Server 2016 stores multiple query plans per query. This information gathered over time is helpful in troubleshooting performance issues for a query over time, unlike procedural cache which stores only last plan. Please use this tag if you are using SQLAzure or versions greater than SQLServer 2016

Common scenarios for using the Query Store feature are:

  • Quickly find and fix a plan performance regression by forcing the previous query plan. Fix queries that have recently regressed in performance due to execution plan changes.
  • Determine the number of times a query was executed in a given time window, assisting a DBA in troubleshooting performance resource problems.
  • Identify top n queries (by execution time, memory consumption, etc.) in the past x hours.
  • Audit the history of query plans for a given query.
  • Analyze the resource (CPU, I/O and Memory) usage patterns for a particular database.
203 questions
121
votes
4 answers

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified. I am getting the above said error while trying to execute the following…
Kamran Ahmed
  • 11,809
  • 23
  • 69
  • 101
11
votes
3 answers

How to tell if a SQL Database has QUERY_STORE enabled?

How can I tell if a Azure SQL Database has QUERY_STORE turned on? You enable it with this command: ALTER DATABASE SET QUERY_STORE = ON; I figure it should be simple to check the database for this, but I have not found the…
Zain Rizvi
  • 23,586
  • 22
  • 91
  • 133
7
votes
3 answers

left join two tables on a non-unique column in right table

I have two tables in sql server and i wanna select and join some data from these table.the first tables have some customer like: --------------- customer id Dave 1 Tom 2 --------------- and second table i table of…
iman
  • 173
  • 2
  • 2
  • 11
6
votes
2 answers

How to find which Program or User executed Query using Query Store in SQL Server 2016+

After enabling query store, how to find who executed the query. For example, in case of trace collection, there is TRC file which will get the hostname and program details for query and in case of Extended-Events, we have XEL file which will get the…
vCillusion
  • 1,749
  • 20
  • 33
6
votes
3 answers

Remove query from Query Store report

I've just started using Query data store in SQL server 2016, and its very useful indeed. I have a problem in that on the server there are a number of services that are monitoring service broker queues, and as a result their WAITFOR statements…
Rob Marsh
  • 549
  • 5
  • 22
5
votes
1 answer

Same query - different queries in query store

Problem: We use entity framework (6.21) as our ORM manager. Our database is Azure Sql Database. Because some of the parametrized queries (frequently used in our app) are slow on some of the inputs (on some input it runs 60 seconds on other input it…
videokojot
  • 499
  • 3
  • 12
3
votes
1 answer

How to format percentage?

I need help in formatting percentages. My original reason for formatting is that I want to display percentages near zero as 0.05% and not as .05% So I did this: IF (a.TOTAL <> 0 AND b.mkt <> 0) THEN v_perc := TO_CHAR(ROUND(100-( a.TOTAL*100/…
karthik adiga
  • 133
  • 2
  • 12
3
votes
0 answers

MSSQL - Log Memory Used - Query Store

I couldn't find any good documentation about this metric value on the MsSql Query Store. Can you please explain us the meaning of this one? Or instead provide a valid paper on it. Thanks for your help.
gio
  • 91
  • 8
3
votes
0 answers

Issue with SQL Server Query store

Few weeks back I did run an in-place upgrade from SQL Server 2016 to SQL Server 2017 CU 5 in one of our main environments. I used Slipstream upgrade so it was one shot. This is a 2 nodes shared storage FCI with single instance and 1 replica on a…
Javier Villegas
  • 31
  • 2
3
votes
2 answers

Azure SQL - Automatic Tuning with Geo-replication - Server in unspecified state and query store has reached its capacity limit

I have a primary db and a secondary geo-replicated db. On the primary, the server atuomatic tuning is turned on. On the replica when I try to do the same I encounter the following issues. The database is inheriting settings from the server, but the…
François
  • 3,164
  • 25
  • 58
3
votes
0 answers

Sudden drop in SQL Azure query performance after moving web app to Azure

What could explain this big drop in performance in an Azure SQL DB after moving the app from an hosted VPS to an Azure App service? Here's a typical chart from Query Store's High Variation chart over the past two weeks. The red arrow indicates when…
rwalter
  • 891
  • 1
  • 7
  • 19
3
votes
2 answers

SQL Server - Sql query like operator with special characters

We want to allow all below special characters in search query based on which results should be available to end user. so we enter all below values in one column itself and now want to run sql query with Like…
Manish Joisar
  • 1,256
  • 3
  • 23
  • 47
2
votes
1 answer

SQL Query in MS Access even if ID is not present

I've 3 tables in MS Access 1st table saves the suppliers details with opening balance (may or may not be) 2nd table saves the purchases made by suppliers which stores their ID and Total_Amount 3rd table keeps track of the payments made by suppliers…
SMA Creations
  • 23
  • 3
2
votes
1 answer

Query fix for a SQL View

Please use the below temporary table and dataset for reference. Using the below dataset, I am trying to creating a new dataset. DECLARE @Temp TABLE ( year_month int, Vuid int, Puid int, ac_cd varchar(20), …
Arpita Dutta
  • 91
  • 1
  • 1
  • 9
2
votes
2 answers

Reissue ids to records with duplicates

We have a CRM DB which for the last 6 weeks has been creating duplicate CaseID's I need to go in and give new case id's int he 20000000 range to all of the duplicates. So I have found all the duplicates like this SELECT CaseNumber, …
Chris Leonard
  • 21
  • 1
1
2 3
…
13 14