Retrieving Query Store query performance data through sp_WhatsupQueryStore

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

24112016_spwhatsupquerystore_01

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.

13 comments to “Retrieving Query Store query performance data through sp_WhatsupQueryStore”

You can leave a reply or Trackback this post.
  1. Neat, looks useful!

    Question about the licensing though – the license.md file in the Github repo is the MIT License. That conflicts with the license in sp_WhatsupQueryStore.sql. Can you make the licensing consistent? Thanks!

  2. Hi,

    Thanks for putting out in to the community!

    Quick note…our databases have hyphens (don’t ask), so at the top of the proc I had to add: SET @dbname = QUOTENAME(@dbname);

  3. Nice blog,
    After ran this sp in New query window i am trying to retrieve the information,But i am facing following issue: please approach how to resolve it.

    Query Store is not enabled on database “TestDB” aborting execution of sp_WhatsupQueryStore.

  4. Hi Narendra,

    Are you sure the Query Store feature is enabled on the “TestDB” database.
    Keep in mind you can only use the Query Store feature on SQL Server 2016.

  5. Msg 9420, Level 16, State 1, Line 1
    XML parsing: line 1, character 2, illegal xml character

    Error in Section 4 setting variable SET @sql_qs_top_nr_executions. Any thoughts?

  6. Added an additional condition to the JOIN in all sections and it seems to be working now. Not sure if this is the best solution.

    INNER JOIN ‘ + @dbname + ‘.sys.query_store_plan qsp WITH (NOLOCK)
    ON cte.plan_id = qsp.plan_id
    AND LEFT (qsp.query_plan, 1) = ”<''

  7. Update: This trick worked for most of the XML but not all. I believe there is an issue with the implicit conversion of VARBINARY.

    Great work on the script, thank you!

  8. 13.0.1601.5 – SQL Server 2016, no SP1 yet.

    The odd thing is that these errors have gone away after the first day. May have something to do with enabling query store and then immediately installing sp_WhatsupQueryStore. Once the 4 hour window passed there were no longer any issues.

    EXEC sp_WhatsupQueryStore @dbname = ‘DB’, @timewindow = 4, @topqueries = 10

    • Hi Ryan,

      Thanks for the additional info!
      I’ll see if I can reproduce that behaviour and fix it in the next release.

  9. Hi Enrico,

    Nice post, useful script!
    I would only change the “IF EXISTS DROP” logic with “CREATE OR ALTER” in your stored procedure, since now it is possible to do so and it’s a nice way of writing it.

    :)
    Regards,
    Rigerta

Write a Reply or Comment

Your email address will not be published.