Captured information about waits is stored in the sys.query_store_wait_stats system catalog view. To check the output of this view, you will create two connections and execute two queries simultaneously. Before that, ensure that Query Store is enabled and empty by running these statements from previous sections:

USE WideWorldImporters;
GO
ALTER DATABASE WideWorldImporters SET QUERY_STORE CLEAR;
ALTER DATABASE WideWorldImporters SET QUER_STORE = OFF;
GO
ALTER DATABASE WideWorldImporters 
SET QUERY_STORE = ON 
( 
OPERATION_MODE = READ_WRITE, 
INTERVAL_LENGTH_MINUTES = 1 
); 
GO

To see how Query Store captures waits, you need to open two connections to a SQL Server 2017 instance. In the first connection, you need to type these statements:

USE WideWorldImporters;
SET NOCOUNT ON;
SELECT *
FROM Sales.Orders o
GO 5

In the second connection, type the following code:

USE WideWorldImporters;
BEGIN TRAN
UPDATE Sales.Orders SET ContactPersonID = 3003 WHERE OrderID = 1;
--ROLLBACK

As you may have guessed, in the second connection you will simulate the LOCK wait type since the transaction is neither committed nor rolled back. Now execute the query from the first connection and, a few seconds later, execute the command in the second connection. At this point, the first query cannot proceed with the execution until you finish the transaction in the second connection. After 20 seconds, for instance, finish the transaction by removing the comment near to the ROLLBACK and executing it. In this way, the commands from the second connection are finished and the query from the first connection continue with the execution. To check what Query Store has collected during these actions, execute the following query:

SELECT * FROM sys.query_store_wait_stats;

This exercise is performed to explore the output of this view, so let’s have a look at the following screenshot:

You can see two rows for two category waits for the plan for the first query: Lock and Network IO. They correspond to the LCK_M_S and ASYNC_NETWORK_IO wait types respectively.

You can see the following, if you repeat the preceding scenario by opening a third connection, where you will execute this query (use the session_id of the query in the first connection):

SELECT * FROM sys.dm_os_waiting_tasks WHERE session_id = <Your_Session_Id>;

During the query execution following the preceding scenario, this query will (most probably) return one row at a time: either a ASYNC_NETWORK_IO wait type or LCK_M_S, depending on the status of the transaction in the second connection, as shown in the following screenshot:

You cannot see both entries at the same time, since this DMV returns only active waits; if a resource is free, previous waits for it are not shown. Query Store waits on the other hand are cumulative and all waits during the query execution are included in the wait category statistics.

Capturing wait statistics is a great Query Store improvement in SQL Server 2017 and makes Query Store an excellent tool for fast performance troubleshooting.