A few community tools have been developed to help with configuration and harvesting of the data in Query Store. dbatools is a community-driven PowerShell module that includes three cmdlets for helping you configure Query Store. The First Responder Kit contains a set of stored procedures including one that queries Query Store data. In this chapter we will cover these tools to how they can help see the options of Query Store, set the options of Query Store, and harvest the data in Query Store.
dbatools
dbatools is a PowerShell open source module developed with currently over 400 commands to help manage SQL Server. There currently are three commands for Query Store: Get-DbaDbQueryStoreOption, Set-DbaDbQueryStoreOption, and Copy-DbaQueryStoreConfig. By using dbatools, you can retrieve and set the settings across multiple databases and servers at the same time. Full documentation and information on dbatools can be found at http://dbatools.io and installed on any machine with PowerShell 5 or higher by running the code in Listing 10-1 at a PowerShell command prompt.
Install-Module dbatools
Listing 10-1
Install dbatools
Get-DbaDbQueryStoreOption
The first command from dbatools we will explore is Get-DbaDbQueryStoreOption. This command retrieves the Query Store options that have been set on a database. There are several ways to run the command to return the data. Listing 10-2 will return the Query Store options for all databases on the server specified except master and tempdb because you cannot use Query Store with those databases. The results of the code from Listing 10-2 can be seen in Figure 10-1.
Get-DbaDbQueryStoreOption -SqlInstance MyServer
Listing 10-2
Return all the Query Store options for all the databases on the specified server
Figure 10-1
The output from Get-DbaDbQueryStoreOption for the whole SQL Server instance
To see the settings for a particular database, you can run the code in Listing 10-3. The results are show in Figure 10-2.
Get-DbaDbQueryStoreOption -SqlInstance MyServer
-Database AdventureWorks
Listing 10-3
Return all the Query Store options for the AdventureWorks database
Figure 10-2
The output from Get-DbaDbQueryStoreOption for the AdventureWorks database
The results if you have several databases on one server can be hard to read and require a lot of scrolling, but there is an option to get this data back in a table format. See the code in Listing 10-4 for how to accomplish this. Part of the results from the code in Listing 10-4 can be seen in Figure 10-3.
The output from Get-DbaDbQueryStoreOption in table format
Lastly, you can use the Get-DbaDbQueryStoreOption command to return all the databases with a particular configuration setting. In the following example, we will return all the databases that have the ActualState of Query Store as ReadWrite as seen in Listing 10-5. The results of the code in Listing 10-5 can be seen in Figure 10-4.
Return database where Query Store ActualState is ReadWrite
Figure 10-4
The output from Get-DbaDbQueryStoreOption for ReadWrite Query Store databases
More parameters can be found for this command online at the dbatools website (https://dbatools.io).
Set-DbaDbQueryStoreOption
The second command from dbatools we will explore is Set-DbaDbQueryStoreOptions. This command sets the Query Store options that exists in the databases you specify. You can use the command to set just one setting or them all depending on your needs. We will, in Listing 10-6, set the Query Store options for the best practices laid out in Chapter 3 for all the databases on the SQL Server instance. The only exceptions to this configuration, if you remember from Chapter 3, would be if you needed a larger Query Store or if you wanted a different collection interval.
-CaptureMode AUTO -CleanupMode Auto -StaleQueryThreshold 30
Listing 10-6
Use Set-DbaDbQueryStoreOption to set options on all the databases to best practices
After issuing the command from Listing 10-6, the command outputs the new settings for each database as you have seen before from the Get-DbaDbQueryStoreOption command. These can be seen in Figure 10-5.
Figure 10-5
Output from Set-DbaDbQueryStoreOption for best practices
It is possible, for example, to change the size of the particular database and the statistics collection interval if you need to, by using the code in Listing 10-6. This code allows you to keep more data at a more granular level. The output from the code in Listing 10-7 can be seen in Figure 10-6.
Set-DbaDbQueryStoreOption -SqlInstance MyServer
-Database AdventureWorks -MaxSize 4096
-CollectionInterval 15
Listing 10-7
Use Set-DbaDbQueryStoreOption to change the size and collection interval for AdventureWorks
Figure 10-6
Output from Set-DbaDbQueryStoreOption changing max size and collection interval
More parameters can be found for this command online at the dbatools website (https://dbatools.io).
Copy-DbaQueryStoreConfig
The last command we will explore is Copy-DbaQueryStoreConfig. This command can copy the Query Store options between databases on the same server or the settings from one database on one server to databases on another server, with the parameter -AllDatabases. Listing 10-8 will copy the options from MyServerA and the AdventureWorks database to all the databases on MyServerB.
Copy Query Store options from one database to another database on another server
dbatools Summary
In summary, we covered the three cmdlets in dbatools that help you see the configuration of Query Store and configure Query Store. The Get-DbaDbQueryStoreOption cmdlet allows you to view the options set for Query Store. The Set-DbaDbQueryStoreOptions cmdlet allows you to set the options for Query Store. The Copy-DbaQueryStoreConfig cmdlet allows you to copy configurations between databases and servers.
sp_BlitzQueryStore
sp_BlitzQueryStore is part of the First Responder Kit made by Brent Ozar, ULTD., located at http://FirstResponderKit.org. The whole kit can be downloaded from there, or you can install it using the code in Listing 10-10 if you have dbatools installed.
The procedure by default looks at the data in Query Store for the last 7 days (by default), finds the times that consumed the most resources for each metric, and finds the top three queries (by default) that consumed the most resources for each metric. By analyzing by each time period and each metric, you can get a more balanced and targeted analysis of data from Query Store. For example, it will find the time period in the last 7 days where you did the most logical reads and return the top three queries doing the reads during that time period. Then you can dive in and try to figure out how to improve the performance for those queries during that time period
By default, you can execute the procedure with just the database you want to see data for, and it will return the top query for each metric for the last 7 days. Listing 10-11 shows you the T-SQL to execute to see the results. Figures 10-7 and 10-8 show some of the columns that are returned when you run the stored procedure.
Execute sp_BlitzQueryStore for the top query for each metric the last 7 days
Figure 10-7
sp_BlitzQueryStore result set
Figure 10-8
sp_BlitzQueryStore result set
You can click on the query_plan_xml column and bring up the query plan to view and troubleshoot. Some analysis has been done for you by giving you the top_three_waits, missing_indexes, and implicit_conversion_info. Other columns that returned are as follows:
top_three_waits – with total ms in parentheses
missing_indexes – listed from the missing index hints in the query plans
implicit_conversion_info – listed from the query plan
cached_execution_parameters
count_executions
count_compiles
total_cpu_time
avg_cpu_time
total_duration
avg_duration
total_logical_io_reads
avg_logical_io_reads
total_physical_io_reads
avg_physical_io_reads
total_logical_io_writes
avg_logical_io_writes
total_rowcount
avg_rowcount
total_query_max_used_memory
avg_query_max_used_memory
total_tempdb_space_used
avg_tempdb_space_used
total_log_bytes_used
avg_log_bytes_used
total_num_physical_io_reads
avg_num_physical_io_reads
first_execution_time
last_execution_time
last_force_failure_reason_desc
context_settings
You can see below the results returned that there is a detailed analysis of queries that were executed during that time period including if it finds problems with your execution plans, high tempdb usage, long-running queries but low CPU, and then it wraps up by identifying the worst times by each metric that the system was performing in. See Figure 10-9 for example of this output. This gives you the ability to drill down on specific poorly performing times or queries that are troublesome within the specified time period.
Note
Use the @TOP parameter to return more than the top one query. But use it cautiously as this procedure does a lot processing through the query plans. Recommendations are to limit to 10.
Figure 10-9
sp_BlitzQueryStore time period analysis
There are other options for running this procedure; we will go through a few important ones to help you to get the data you need. In Listing 10-12 you can specify the date range you want to return in your output by specifying the @StateDate and @EndDate parameters.
If you are trying to troubleshoot a specific stored procedure, you can look for that stored procedure specifically. In Listing 10-13 you will find the code on how to return the top statement in the procedure MyStoredProcedure by specifying the @StoredProcName parameter.
Return top statement for a specific stored procedure
You can also use it to look at failed queries by specifying the @Failed parameter. Listing 10-14 is an example of how to return the top query that has failed.
Back in Chapter 4, we looked at the many reports in Grid View you could see the plan_ids and query_ids. If you are looking to see the sp_BlitzQueryStore data for a query you have identified from those reports, then you can use the code from Listings 10-15 and 10-16 to pull the data out of Query Store.
Other parameters you can specify include the following in Table 10-1.
Table 10-1
sp_BlitzQueryStore parameters
Parameter name
Data type
Default
@Help
bit
0
@MinimumExecutionCount
int
NULL
@DurationFilter
decimal(38, 4)
NULL
@ExportToExcel
bit
0
@HideSummary
bit
0
@SkipXML
bit
0
@Debug
bit
0
@ExpertMode
bit
0
@Version
varchar(30)
NULL
@VersionDate
datetime
NULL
@VersionCheckMode
bit
0
Conclusion
Community tools prove to enhance our ability to use Query Store. dbatools lets us configure and check the settings of Query Store across multiple databases and servers with ease. sp_BlitzQueryStore lets you harvest the data in Query Store in a different format than the reports shown in Chapter 4 and can be useful for finding out what your top queries are for each metric quickly and what your busiest periods of times are for each metric. Finally, it allows you to track data for a stored procedure, query_id, or plan_id that you have identified you want to investigate further.
