Querying the Query Store

So far, we have only been using the Query Store user interface directly in SQL Server Management Studio. It won’t be long after you start working with the Query Store that you will need to query the Query Store tables directly. As a quick introduction on how to access such tables, I will cover two examples here. For more details, please refer to the SQL Server documentation.

One of my favorites is the following query to collect queries that have timed out or finished because of an error. But first, let’s create at least one example. Run the following query. Since it is running an expensive CROSS JOIN, it will take too long to finish. Cancel the query after running it for a few seconds:

SELECT * FROM Sales.SalesOrderDetail sod1 CROSS JOIN Sales.SalesOrderDetail sod2

Now, you can run the following report to list all the queries that timed out or finished because of an error:

SELECT rs.avg_logical_io_reads, qt.query_sql_text,

 q...