September 12, 2022 at 10:47 pm
I am about to test Automatic Tuning on a availability group. I have not tested this yet but I am certain I will need to enable this on each replica database. Since it is expected each database will get a different set of read calls, then query store records should be different.
Does anyone have experience in this area where they can share some pointers? Gotchas ?
Thank you
----------------------------------------------------
September 13, 2022 at 7:34 am
I have never done it but I think prior to SQL2022 query store only worked on the primary replica.
September 13, 2022 at 12:26 pm
Ken is correct, query store only works on the primary prior to 2022.
Yeah, query store is database by database, only, even in 2022. So each database gets it's own store. They're system tables and go into the primary file group. Feedback on it, most of the time, for most people, enabling query store, with just the defaults, works.
However!!!
I would strongly recommend testing it on each database individually, and not doing them all at once. I know people who hit issues. Next, take advantage of the knobs you have to adjust the behavior. For example, I'd switch the default capture mode from ALL to AUTO. There are more knobs on this in 2019, but even this minimal switch will help performance. You'll want to look at retention and management as well. This is where people really hit issues. Here's some recommendations from Erin Stellato, I'd follow them. Microsoft also lists some best practices that are worth looking at.
As to the automated tuning itself, it works. However, it's nowhere near a panacea. You'll still be required to do some of the heavy lifting. There'll just be less of it.
Oh, and it does have to retrain itself after a failover, so anticipate a slowdown there.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 14, 2022 at 12:47 am
Thank you both for the good feedback.
Yes I see on the secondary there are no records when I select from
sys.dm_db_tuning_recommendations.
I dont even know the state on the 2ndary as nothing returns from
Select * from sys.database_query_store_options. I trust this will change if I do a manual failover. For the moment I am not sure if it is just turned off on the secondary.
----------------------------------------------------
September 14, 2022 at 11:56 am
It can't be turned on with the secondary in 2019 or less (2022 only). When the primary fails over, it will have Query Store and the last good plan enabled if they were on the primary.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 15, 2022 at 11:23 pm
It can't be turned on with the secondary in 2019 or less (2022 only). When the primary fails over, it will have Query Store and the last good plan enabled if they were on the primary.
Thank you Grant. But I may still be confused on something. SO I will break it down with this example.
At time T1 > replica A is the primary and I force a plan (say queryid 100, plan 1). Replica B has no forced plans at this time.
At time T2 > The replicas switch roles. On replica B, I force (say queryID 200, plan 2).
At time T3 > The replicas switch back to their original roles.
My question is , will replicas A and B have two forced plans at this point (T3) ? I ask this because I am thinking that T1 and T2 the plan I force gets copied to the secondary , much like the sys.query_store_query table would.
But I a not sure . Thank you again
----------------------------------------------------
September 16, 2022 at 2:57 am
Grant Fritchey wrote:It can't be turned on with the secondary in 2019 or less (2022 only). When the primary fails over, it will have Query Store and the last good plan enabled if they were on the primary.
Thank you Grant. But I may still be confused on something. SO I will break it down with this example.
At time T1 > replica A is the primary and I force a plan (say queryid 100, plan 1). Replica B has no forced plans at this time.
At time T2 > The replicas switch roles. On replica B, I force (say queryID 200, plan 2).
At time T3 > The replicas switch back to their original roles.
My question is , will replicas A and B have two forced plans at this point (T3) ? I ask this because I am thinking that T1 and T2 the plan I force gets copied to the secondary , much like the sys.query_store_query table would.
But I a not sure . Thank you again
I actually tested this out now. From what I see, everything is preserved. Though the plans all originate on the primary they are copied to the secondary. The rows in sys.query_store_query and sys.query_store_plan are the same.
I also checked forced plans,
Select * from sys.query_store_plan where is_forced_plan = 1;
Is the same on both secondary and primary.
I did a failover and still the results are preserved. I even went as far as forcing a plan on the new primary , failed back over to put things in their original state. Now both forced plans exist on both of the AG nodes.
----------------------------------------------------
September 19, 2022 at 8:23 am
Yeah, plan forcing will translate. Automated tuning won't. It will have to "relearn" the automated tuning stuff. Also doesn't survive reboots.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply