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: https://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 https://dbatools.io/!