This afternoon I tweeted that a new Query Store related stored procedure I created is available: sp_WhatsupQueryStore.This blog post goes into more detail on how you can use sp_WhatsupQueryStore to easily and quickly retrieve query performance data from the Query Store.
Installation
The first thing you want to do is download sp_WhatsupQueryStore from its GitHub page here: https://github.com/Evdlaar/sp_WhatsupQueryStore. Just like the Query Store Dashboards I created, sp_WhatsupQueryStore is 100% free and open-source, this means you can customize and use it as you much as you like!
The installation of sp_WhatsupQueryStore is very straightforward and easy. Just copy the contents of the .sql file on the GitHub page and copy it into a new query window inside SQL Server Management Studio. When you run the script it will install the stored procedure inside the “master” database. This means you can run it from any database you are connected to without having to switch to a different database context first. And that’s it! Now you are ready to use sp_WhatsupQueryStore against your Query Store enabled database to retrieve a wide variety of query performance metrics!
Usage
After you have installed sp_WhatsupQueryStore you can run it immediately. The quickest way to get started is running the following command:
EXEC sp_WhatsupQueryStore @dbname = ‘your_database’
Just replace the your_database part with the name of the database you want to retrieve Query Store runtime data from.
If you want to have some more control over the data that is returned by sp_WhatsupQueryStore you supply two extra, optional, parameters:
@timewindow: Configure the time, in hours, that we grab and aggregate query performance data. For instance, running the command below will return and aggregate runtime statistics for the last 4 hours.
EXEC sp_WhatsupQueryStore @dbname = ‘your_database’, @timewindow = 4
@topqueries: This limits the amount of rows that get returned by the different categories. The example below adds the @topqueries parameter to the query above.
EXEC sp_WhatsupQueryStore @dbname = ‘your_database’, @timewindow = 4, @topqueries = 10
In this example we will return and aggregate the last 4 hours of query performance metrics and limit the results to the top 10 queries.
As mentioned before, both the @timewindow and @topqueries parameters are optional. If you do not specify them sp_WhatsupQueryStore will use the default values of 1 hour and the top 25 queries returned.
What is returned by sp_WhatsupQueryStore
sp_WhatsupQueryStore returns all kinds of information:
- Query Store configuration
- Forced Execution Plans
- Queries that ran in the last x hour(s) and compiled more than one execution plan
- Most frequently executed queries in the last x hour(s)
- Most expensive queries based on average runtime in the last x hour(s)
- Most expensive queries based on average CPU time in the last x hour(s)
- Most expensive queries based on average logical read IO in the last x hour(s)
- Most expensive queries based on average logical write IO in the last x hour(s)
- Most expensive queries based on average physical read IO in the last x hour(s)
All of the data is returned as rows and before the result set there is an extra category result that specifies what information you are looking at:
I will be working on adding more metrics to sp_WhatsupQueryStore so be sure to always download the latest version from the GitHub page.
If you have any questions, run into issues or want to report a bug. Feel free to reach out to me on Twitter at @evdlaar or report the issue through the GitHub page.
