Tracking expensive queries

Query Store only collects plans for Data Manipulation Language (DML) statements such as SELECT, INSERT, UPDATE, DELETE, MERGE, and BULK INSERT, as these are the T-SQL statements that will be responsible for most of our SQL Database Engine’s resource usage. Most database administrators (DBAs) and database reliability engineers are constantly looking for ways to optimize resource usage; after all, if T-SQL queries are using just the resources they need (CPU, I/O, and memory), then the SQL Database Engine is operating at peak efficiency and allows for maximum concurrency with its current hardware resources.

This brings us to one of the main benefits of Query Store: tracking our workload heavy hitters – the most resource-consuming queries. With this exercise, we may be able to uncover tuning opportunities that, if successful, further improve the efficiency of the server’s resource usage.

To generate enough workload in our AdventureWorks...