Configuring and analyzing the Query Store through dbatools
In case you didn’t know this already, dbatools is an awesome collection of Powershell functions that will help you immensely in your work as a DBA. Just one example: migrating SQL Server Instances takes a massive amount of work. You need to backup and restore every database, write down instance specific configuration and apply it to the instance you are migrating to and don’t even get me started on migrating SQL Server Agent jobs and logins. Using dbatools you can basically migrate an entire SQL Server Instance in one single Powershell command! Don’t believe me? Check out this video: //www.youtube.com/watch?v=PciYdDEBiDM!
Another awesome thing about dbatools is that it is completely free and open-source! This means you do not have to pay for using it and if you want you can contribute to the script and introduce new functions that the entire SQL Server community can use.
Talking about contributing, that exactly what I did. What started out as a conversation with other dbatools contributors at a SQL Server event (I am looking at you @sqldbawithbeard) ended up with me writing my own custom functions that are now available in the latest release of dbatools (snowball). And as you know I am a big fan of the Query Store, so it was only logical those new functions have everything to do with it!
Get-DbaQueryStoreConfig
Getting the Query Store configuration for a single database is easy right? You can navigate to the properties of a database, hit the page named “Query Store” and look at the properties. If you prefer T-SQL you can also write a simple query that retrieves all the Query Store configuration for the database you are connected to:
SELECT * FROM sys.database_query_store_options;
But have you ever tried retrieving the Query Store configuration of all databases inside a SQL Server 2016 Instance? Well clicking through the properties of every single database seems a bit like a waste of time. T-SQL can help you out here but since the Query Store DMVs are sensitive to the database context you are on you will have to use some creative methods like sp_msforeachdb to loop through every database.
So I have a better idea. Why not use dbatools for this and try out the new Get-DbaQueryStoreConfig command! By writing one single line of Powershell you can immediately retrieve the Query Store configuration of every user database!
Get-DbaQueryStoreConfig -SqlServer localhost
The function even accepts filters so you can quickly figure out what databases have a specific Query Store configuration set. For instance the command below only returns databases that have a Query Store maximum size of 250MB configured:
Get-DbaQueryStoreConfig -SqlServer localhost | Where-Object {$_.MaxSize -eq "250"}
Don’t like the results inside a command window? Would you rather have a table where you can sort and filter? No problem! Like practically every command in dbatools you can use Out-GridView to return the results in a separate grid view window!
Get-DbaQueryStoreConfig -SqlServer localhost | Out-GridView
How cool is that? But wait, there are more Query Store related functions available in dbatools!
Set-DbaQueryStoreConfig
Just like retrieving the Query Store configuration for many databases inside a single SQL Server 2016, configuring the Query Store properties can be a hassle.
To make the configuration of the Query Store for one or multiple databases in the same, or even another Instance, easier Set-DbaQueryStoreConfig was developed.
Through Set-DbaQueryStoreConfig you can configure one or all properties of the Query Store. For example, running the command below will configure all the Query Store properties for one specific database.
Set-DbaQueryStoreConfig -SqlInstance localhost -Databases AdventureWorks -State ReadWrite -FlushInterval 600 -CollectionInterval 10 -MaxSize 100 -CaptureMode All -CleanupMode Auto -StaleQueryThreshold 100
But you can just as easily use this command to configure all these properties for all user databases inside the Instance:
Set-DbaQueryStoreConfig -SqlInstance localhost -State ReadWrite -FlushInterval 600 -CollectionInterval 10 -MaxSize 100 -CaptureMode All -CleanupMode Auto -StaleQueryThreshold 100 -AllDatabases
Running the command above will configure the Query Store for every user database inside the default SQL Server Instance on localhost with the following properties:
- State: Read/Write
- Data Flush Interval: 600 seconds
- Runtime Statistics Collection Interval: 10 minutes
- Maximum Size: 100MB
- Query Capture Mode: All
- Cleanup Mode: Auto
- Stale Query Threshold: 100 days
If you are not interested in configuring every option, you can also set one or more specific options:
Set-DbaQueryStoreConfig -SqlInstance Localhost -FlushInterval 600 –Databases AdventureWorks, DatabaseB
In this case we are only modifying the Data Flush Interval setting to 600 seconds for only the databases AdventureWorks and DatabaseB inside the localhost Instance.
Copy-DbaQueryStoreConfig
The final function I would like to show you is a special one. Personally I love to configure one specific “best-practice” and make sure every system complies with this “best-practice”. Through the Copy-DbaQueryStoreConfig you can copy the configuration of one Query Store database and apply that configuration to all other databases. This means you only have to configure the Query Store properties of one database and use Copy-DbaQueryStoreConfig to apply that configuration to every database inside one, or multiple, SQL Server 2016 Instances.
For example, the command below extract the Query Store configuration of the AdventureWorks database inside the SQL Instance on ServerA and applies it to every userdatabase inside the SQL Instance on ServerB.
Copy-DbaQueryStoreConfig -Source ServerA\SQL -SourceDatabase AdventureWorks -Destination ServerB\SQL –AllDatabases
You do not necessarily have to apply the configuration to every database, just like the other functions you can specify which databases should receive the configuration.
Copy-DbaQueryStoreConfig -Source ServerA\SQL -SourceDatabase AdventureWorks -Destination ServerB\SQL -DestinationDatabase WorldWideTraders
In the command above we are still extracting the Query Store configuration from the AdventureWorks database inside the ServerA\SQL Instance, but this time we are only pushing the configuration to the WorldWideTraders database inside the ServerB\SQL Instance.
Conclusion
In this article I introduced three new Query Store related commands that are available in the latest release of dbatools. Through some examples we retrieved, configured and even copied Query Store configuration for one or multiple databases.
Interested in using these functions, or any of the other awesome 100+ dbatools functions? Check out //dbatools.io/!
27 comments to “Configuring and analyzing the Query Store through dbatools”
This is a great tip particularly to those new to the blogosphere.
Short but very precise info… Thanks for sharing this one.
A must read article!
Great post! We will be linking to this particularly great article on our site.
Keep up the good writing.
If some one wants to be updated with most up-to-date technologies then he must be
go to see this web page and be up to date every day.
That is right… we offer you sports betting odds on who will win next
year’s Super Bowl, right now!
It’s amazing to pay a visit this web site and reading the views of all mates
about this post, while I am also keen of getting familiarity.
I wish to express my gratitude for your kindness for men who
absolutely need help on in this idea. Your real dedication to getting the solution all around had been unbelievably
good and have usually encouraged employees just like me to reach
their dreams. This important key points implies a great deal a
person like me and still more to my fellow workers.
Thanks a lot; from each one of us.
magnificent issues altogether, you simply received
a logo new reader. What may you suggest in regards to your
post that you made some days in the past? Any sure?
ᴡhoah this weblog is fantaѕtic i reaslly like studүing your posts.
Kеep up the good work! Yoս realize, lots of individuals are looking around for this info, you can aid them gгeatly.
My blog: Vidio Mak Gemuk Ngentot Indonesia Gif
What’s up to all, it’s truly a good for me tto pay a visit ths web site, it consists oof helpful Information.
What’s Happening i’m nnew too this, I stumbled upon this I have found It absolutely helpful and it has helped me out
loads. I hope too contribute & aid other users like
iits helped me. Great job.
I’m amazed, I must say. Seldom do I encounter a blog that’s both
equally educative and interesting, and without a doubt,
you’ve hit the nail on the head. The issue is something not enough men and women are speaking intelligently about.
I am very happy that I found this in my search for something relating to this.
In this awesome pattern of things you receive a B+ for effort. Exactly where you actually confused me personally was on the particulars. You know, it is said, details make or break the argument.. And it could not be more correct right here. Having said that, allow me say to you what exactly did do the job. Your text can be highly powerful and this is probably why I am taking the effort to opine. I do not make it a regular habit of doing that. Secondly, despite the fact that I can certainly see the leaps in reason you come up with, I am not necessarily convinced of how you seem to connect your details which in turn make your conclusion. For now I will subscribe to your issue however trust in the future you actually link your facts better.
//www.parimatch-turk.com
Asking questions are in fact good thing if you are not understanding something entirely, however this article presents pleasant understanding yet.
париматч
Pretty! This was an incredibly wonderful article. Many thanks for providing this info.
//www.parimatch-turk.com
Well I definitely enjoyed reading it. This subject offered by you is very constructive for correct planning.
parimatch
Your style is so unique in comparison to other people I’ve read stuff from. Thank you for posting when you have the opportunity, Guess I’ll just book mark this page.
//www.parimatch-turk.com/
More rapidly access to information will facilitate a more quickly feedback loop for
Verizon Media’s sports betting operation.
Thanks for every other excellent article. Where else could anybody get that type of information in such an ideal
method of writing? I have a presentation next week, and I
am at the look for such info.
Right away I am ready to do my breakfast, afterward having my
breakfast coming over again to read additional news.
Excellent, what a weblog it is! This website
provides valuable facts to us, keep it up.
I am in fact happy to read this webpage posts which contains lots
of useful facts, thanks for providing these kinds of data.
А только да линия такая, только толку а.
my blog post … зенит бк адреса
Hello mates, fastidious piece of writing and fastidious arguments commented here, I am actually
enjoying by these.