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

27022017_dbatools_qs_01

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"}

27022017_dbatools_qs_02

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

27022017_dbatools_qs_03

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

27022017_dbatools_qs_04

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/!

99 comments to “Configuring and analyzing the Query Store through dbatools”

You can leave a reply or Trackback this post.
  1. 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.

  2. 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?

  3. 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.

  4. 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.

  5. 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

  6. Post grande. Eu estava verificando continuamente este blog e eu
    sou impressionado! Extremamente útil informação
    especialmente a última parte :) Eu me importo com tal
    informação muito . Eu estava procurando
    isto particular informação para um muito tempo.

    Obrigado e boa sorte .

  7. Just tried the free trail…..very disappointed with this service.
    Navigation isn’t good, some titles were in French and I couldn’t figure out how to switch settings to
    just English. Unable to eliminate other languages from menu so I had to scroll through
    tons of useless categories to find one channel. This program needs a lot of work especially on the layout…..not user friendly at all,
    my advise is try it before you buy it.

  8. What you said made a great deal of sense. But, think on this,
    suppose you added a little content? I am not suggesting your information isn’t
    good., but what if you added a post title to maybe grab a person’s
    attention? I mean Configuring and analyzing the Query Store
    through dbatools – DotNine is kinda boring.

    You should peek at Yahoo’s front page and watch
    how they create post headlines to get people interested.
    You might try adding a video or a picture or two
    to get readers excited about everything’ve written.
    Just my opinion, it might make your posts a little livelier.

  9. If you are going for finest contents like myself, simply go to see
    this website every day for the reason that it gives quality
    contents, thanks

  10. I got this website from my buddy who shared with me
    on the topic of this website and now this time I am browsing this web
    page and reading very informative articles or reviews
    at this time.

  11. Hmm it appears like your site ate my first comment (it was super long)
    so I guess I’ll just sum it up what I had written and say,
    I’m thoroughly enjoying your blog. I as well am an aspiring blog writer but I’m still new to everything.

    Do you have any helpful hints for first-time blog writers?

    I’d really appreciate it.

  12. I know this website gives quality based content and other information, is there any other
    website which presents these information in quality?

  13. With havin so much content do you ever run into any
    problems of plagorism or copyright violation? My
    site has a lot of completely unique content I’ve either
    authored myself or outsourced but it appears a lot of it is popping it up all over the internet without
    my permission. Do you know any solutions to help reduce content from being stolen? I’d definitely appreciate it.

  14. I don’t even understand how I stopped up right here, but I thought this post was great.
    I don’t recognize who you might be but definitely you are going to a well-known blogger
    in the event you aren’t already. Cheers!

  15. First off I would like to say awesome blog! I had a quick question in which
    I’d like to ask if you do not mind. I was interested to find out how
    you center yourself and clear your mind prior to writing.
    I have had a difficult time clearing my thoughts in getting my ideas out there.
    I do enjoy writing but it just seems like the first 10 to 15 minutes tend to be lost
    simply just trying to figure out how to begin. Any ideas or tips?

    Appreciate it!

  16. Hi! I am leaving something behind because you made me very interested in your articles. Thank you and I keep my fingers crossed for further actions!

  17. You inspire, advise, explain – and that’s what I like the most about you. I’ll be stopping by more often and recommending the site to my friends!

  18. I read the first article and I already know that I will be a frequent visitor to this site! Waiting for more tips and curiosities! Bravo and good luck!

  19. Wonderful job, amazing articles. I just simply love this blog and I will for sure be a frequent guest here. Keep doing such an amazing job and you will grow it in a very fast way.

  20. Exellent article! I found here all of the needed informations for me. Im so happy I got here and found this blog. I will be visiting your amazing blog ever single day now.

  21. You may be wondering if it is possible to take an IQ test online.
    These standardized tests measure the cognitive abilities
    of a person. However, they are timed and are not a substitute for the real thing.
    Here’s how you can take an online Iq Tests IQ
    test.

  22. It doesn’t matter if you live in an old building or a modern residence it is
    essential to have your windows fixed as soon as you can. broken window
    repair near me (Nellie)
    windows do not just affect the comfort of your home, but they can also add to the cost
    of energy.

  23. The no-cost IQ Test your iq from Fun Education will take you to
    the Hall of Fame, where you will find a listing of historical figures and their reported IQ scores.
    It takes about 30 minutes to complete the test, and it
    has forty questions.

  24. There are many brands that offer cbd oil online oil in your area.

    You can test Lord Jones, NuLeaf, Charlotte’s Web, and Zatural.
    These companies are dedicated to customer satisfaction and you can count on their high quality.

  25. There are a lot of things to take into consideration when deciding if CBD is the right choice for you.

    We’ll talk about the advantages, the negative effects and how cbd bedfordshire interacts with other substances.
    There are also security concerns.

  26. The Web Application Firewall (Waf in security) is
    a security measure that protects your website from DDoS attacks as well as network security breaches and other threats by blocking access to known IP addresses that are malicious and blocking access to
    the.

  27. เว็บฝาก5บาทรับ50ล่าสุดรวมค่ายสล็อตออนไลน์ชั้นหนึ่งฝาก-ถอน AUTO ที่มีความเสถียรภาพสูง การเงินป้อมอาจจะ เว็บไซต์คุณภาพดีชั้น
    1 ชั่วฟ้าดินสลาย เล่นง่ายโบนัสมากมาย

  28. Attractive component of content. I just stumbled upon your website and in accession capital to claim that I acquire in fact enjoyed account your blog posts.
    Anyway I will be subscribing to your feeds and even I fulfillment you
    get right of entry to persistently quickly.

  29. Kekalahan dalam permainan slot online sudah pasti orang
    nya 100% kesel, marah dan frustasi yang sangat mendalam.
    Bagaimana tidak ? kalian yang bermain itu menggunakan uang asli dan bukan dengan kredit ecek ecek.
    Kalau di tanya semua orang sudha pasti tidak mau bermain slot online
    dan kalah bukan ?

  30. Это означает, что вы сможете, например, сделать
    ставку, пополнить счет, вывести средства, получить бонус и выполнить другие задачи.

    Here is my web page … 1win

Write a Reply or Comment

Your email address will not be published.