Sfrgttk

Bicommutant theorem for commutative operator algebras

Why is Shelob considered evil?

Does a star need to be inside a galaxy?

Does an increasing sequence of reals converge if the difference of consecutive terms approaches zero?

What happens to someone who dies before their clone has matured?

Simple Question About Conservation of Angular Momentum

How to know if I am a 'Real Developer'

How can guns be countered by melee combat without raw-ability or exceptional explanations?

Buying a "Used" Router

How to announce in an ATIS message that two parallel runways are in use?

Is candidate anonymity at all practical?

Why does RAM (any type) access time decrease so slowly?

Contribution form

Which was the first story to feature helmets which reads your mind to control a machine?

Why do climate experts from the UN/IPCC rarely mention Grand Solar Minimum?

Badly designed reimbursement form. What does that say about the company?

Found a major flaw in paper from home university – to which I would like to return

How to not forget my phone in the bathroom?

How to write a character overlapping another character

Why does finding small effects in large studies indicate publication bias?

Workplace intimidation due to child's chronic health condition

Python 3.7 UltimateBruteforcer

Rigorous Geometric Proof That dA=rdrdθ?

Suggestions on how to improve logo

Should the Query Store be enabled on all databases?

What are the compelling resons to update SQL Server name to a real one after its changing?Problems caused by SQL Server 2016 Query StoreSQL Server 2016 Bad Query Plan locks up DB once a weekIs there a way to get index fragmentation without using dm_db_index_physical_stats?Enable query store for all databasesNever ending Query Store searchHow to create BAG on non-wsfc nodes (SQL Server 2016 STANDARD Edition)Memory Pressure in SQL Server - Enable LPIM or not?What does it means when you have “sql_exit_invoked” in System Health?SQL Server 2016 & 2017 Force Plan failing with reason 8695

2

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 there are lots of advantages to turning on the Query Store for tuning. I was wondering, are there any cases when the query store should not be turned on? Or should I turn it on on all databases?

sql-server query-store

share|improve this question

edited 2 hours ago

Tony Hinkle

2,4801422

asked 3 hours ago

Danielle Paquette-HarveyDanielle Paquette-Harvey

6091615

• 
  
  
  

  
  3
  

  
  
  
  
  
  

  
  
  support.microsoft.com/en-us/help/4340759/…. So this is just to note that it can cause problems. Anything you enable can cause problems. So this is opinion-based, but I would not recommend enabling unless you have a problem that needs to be solved.
  
  – Tony Hinkle
  3 hours ago
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Enabling or changing settings in the query store can also flush all or parts of the plan cache: sqlskills.com/blogs/erin/…
  
  – Randi Vertongen
  1 hour ago
  

  
  
  
  

add a comment | 

2

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 there are lots of advantages to turning on the Query Store for tuning. I was wondering, are there any cases when the query store should not be turned on? Or should I turn it on on all databases?

sql-server query-store

share|improve this question

edited 2 hours ago

Tony Hinkle

2,4801422

asked 3 hours ago

Danielle Paquette-HarveyDanielle Paquette-Harvey

6091615

• 
  
  
  

  
  3
  

  
  
  
  
  
  

  
  
  support.microsoft.com/en-us/help/4340759/…. So this is just to note that it can cause problems. Anything you enable can cause problems. So this is opinion-based, but I would not recommend enabling unless you have a problem that needs to be solved.
  
  – Tony Hinkle
  3 hours ago
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Enabling or changing settings in the query store can also flush all or parts of the plan cache: sqlskills.com/blogs/erin/…
  
  – Randi Vertongen
  1 hour ago
  

  
  
  
  

add a comment | 

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 there are lots of advantages to turning on the Query Store for tuning. I was wondering, are there any cases when the query store should not be turned on? Or should I turn it on on all databases?

sql-server query-store

share|improve this question

edited 2 hours ago

Tony Hinkle

2,4801422

asked 3 hours ago

Danielle Paquette-HarveyDanielle Paquette-Harvey

6091615

share|improve this question

edited 2 hours ago

Tony Hinkle

2,4801422

asked 3 hours ago

Danielle Paquette-HarveyDanielle Paquette-Harvey

6091615

share|improve this question

edited 2 hours ago

Tony Hinkle

2,4801422

asked 3 hours ago

Danielle Paquette-HarveyDanielle Paquette-Harvey

6091615

edited 2 hours ago

Tony Hinkle

2,4801422

edited 2 hours ago

Tony Hinkle

2,4801422

asked 3 hours ago

Danielle Paquette-HarveyDanielle Paquette-Harvey

6091615

asked 3 hours ago

Danielle Paquette-HarveyDanielle Paquette-Harvey

6091615

1 Answer
1

active

oldest

votes

2

So you really have two questions:

1. Are there any cases when Query Store should not be enabled?

Based on some reports of significant performance issues in the field when Query Store was enabled (although they are quite rare), one might infer that you should not enable Query Store on databases where it causes significant performance issues. Of course the only way you'll know for sure whether it will cause a performance issue is to research all the known issues, make sure none of them will affect your system, and then enable it--but you still won't know if it will cause a performance issue until you enable it.

Secondly, if you aren't going to use the information and functionality it provides to improve the performance of the queries running on your server, then you shouldn't enable it. Some environments, particularly large, heavily regulated organizations, may have such strict change control that you can't realistically do anything with Query Store.

2. Should I turn it on on all databases?

I would certainly not enable it on hundreds of databases all at once, due to the possibility of causing a lot of problems and having too much data to analyze. If your environment is such that you'll be able to use the Query Store to improve performance, then I would recommend enabling on a few database at a time.

If you have hundreds of databases, you probably don't have the staff to analyze all of the information, so it might make sense to enable it periodically on a few servers, see if there are any improvements to be made, then disable it and use it on some other servers for a while. Or, when an application upgrade takes place, enable it for a while to see if the new code is triggering anything that Query Store can help with.

share|improve this answer

answered 2 hours ago

Tony HinkleTony Hinkle

2,4801422

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks that makes a lot of sense :-)
  
  – Danielle Paquette-Harvey
  2 hours ago
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f230172%2fshould-the-query-store-be-enabled-on-all-databases%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

2

So you really have two questions:

1. Are there any cases when Query Store should not be enabled?

Based on some reports of significant performance issues in the field when Query Store was enabled (although they are quite rare), one might infer that you should not enable Query Store on databases where it causes significant performance issues. Of course the only way you'll know for sure whether it will cause a performance issue is to research all the known issues, make sure none of them will affect your system, and then enable it--but you still won't know if it will cause a performance issue until you enable it.

Secondly, if you aren't going to use the information and functionality it provides to improve the performance of the queries running on your server, then you shouldn't enable it. Some environments, particularly large, heavily regulated organizations, may have such strict change control that you can't realistically do anything with Query Store.

2. Should I turn it on on all databases?

I would certainly not enable it on hundreds of databases all at once, due to the possibility of causing a lot of problems and having too much data to analyze. If your environment is such that you'll be able to use the Query Store to improve performance, then I would recommend enabling on a few database at a time.

If you have hundreds of databases, you probably don't have the staff to analyze all of the information, so it might make sense to enable it periodically on a few servers, see if there are any improvements to be made, then disable it and use it on some other servers for a while. Or, when an application upgrade takes place, enable it for a while to see if the new code is triggering anything that Query Store can help with.

share|improve this answer

answered 2 hours ago

Tony HinkleTony Hinkle

2,4801422

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks that makes a lot of sense :-)
  
  – Danielle Paquette-Harvey
  2 hours ago
  

  
  
  
  

add a comment | 

2

So you really have two questions:

1. Are there any cases when Query Store should not be enabled?

Based on some reports of significant performance issues in the field when Query Store was enabled (although they are quite rare), one might infer that you should not enable Query Store on databases where it causes significant performance issues. Of course the only way you'll know for sure whether it will cause a performance issue is to research all the known issues, make sure none of them will affect your system, and then enable it--but you still won't know if it will cause a performance issue until you enable it.

Secondly, if you aren't going to use the information and functionality it provides to improve the performance of the queries running on your server, then you shouldn't enable it. Some environments, particularly large, heavily regulated organizations, may have such strict change control that you can't realistically do anything with Query Store.

2. Should I turn it on on all databases?

I would certainly not enable it on hundreds of databases all at once, due to the possibility of causing a lot of problems and having too much data to analyze. If your environment is such that you'll be able to use the Query Store to improve performance, then I would recommend enabling on a few database at a time.

If you have hundreds of databases, you probably don't have the staff to analyze all of the information, so it might make sense to enable it periodically on a few servers, see if there are any improvements to be made, then disable it and use it on some other servers for a while. Or, when an application upgrade takes place, enable it for a while to see if the new code is triggering anything that Query Store can help with.

share|improve this answer

answered 2 hours ago

Tony HinkleTony Hinkle

2,4801422

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks that makes a lot of sense :-)
  
  – Danielle Paquette-Harvey
  2 hours ago
  

  
  
  
  

add a comment | 

So you really have two questions:

1. Are there any cases when Query Store should not be enabled?

Based on some reports of significant performance issues in the field when Query Store was enabled (although they are quite rare), one might infer that you should not enable Query Store on databases where it causes significant performance issues. Of course the only way you'll know for sure whether it will cause a performance issue is to research all the known issues, make sure none of them will affect your system, and then enable it--but you still won't know if it will cause a performance issue until you enable it.

Secondly, if you aren't going to use the information and functionality it provides to improve the performance of the queries running on your server, then you shouldn't enable it. Some environments, particularly large, heavily regulated organizations, may have such strict change control that you can't realistically do anything with Query Store.

2. Should I turn it on on all databases?

I would certainly not enable it on hundreds of databases all at once, due to the possibility of causing a lot of problems and having too much data to analyze. If your environment is such that you'll be able to use the Query Store to improve performance, then I would recommend enabling on a few database at a time.

If you have hundreds of databases, you probably don't have the staff to analyze all of the information, so it might make sense to enable it periodically on a few servers, see if there are any improvements to be made, then disable it and use it on some other servers for a while. Or, when an application upgrade takes place, enable it for a while to see if the new code is triggering anything that Query Store can help with.

share|improve this answer

answered 2 hours ago

Tony HinkleTony Hinkle

2,4801422

share|improve this answer

answered 2 hours ago

Tony HinkleTony Hinkle

2,4801422

answered 2 hours ago

Tony HinkleTony Hinkle

2,4801422

answered 2 hours ago

Tony HinkleTony Hinkle

2,4801422