Docs › Plans and performance

Query Store

The database's slowest and most frequent queries over a time window, queries that regressed, their plans, and forcing a good plan, from SQL Server's Query Store.

Query Store, built into SQL Server 2016 and later, records every query's plans and runtime statistics in the database. Lukla's Query Store tab reads it, so you can find what's slow without tracing.

Open it with Database ▸ Tools ▸ Query Store… (right-click a database in Object Explorer) or Tools ▸ Query Store… for the current tab's database.

#Top queries

Choose what to rank by and over what window:

• By: total CPU, duration, logical reads, executions or memory;
• Over: the last hour, 24 hours, 7 days or 30 days.

Each row shows the query, the total with a bar, how many times it ran, its average, and how many plans it has had. A query with more than one plan is worth a look: its speed may depend on which one it gets.

#Regressed queries

Regressed queries compares the recent window with the period before it (for example the last day against the seven before) and lists the queries that got slower, with how much.

#Plans

Select a query to see its plans on the right, with runs, average duration and when each was last used.

• Open Query opens the query text in a tab.
• Open Plan opens the plan in a plan tab, with the full plan view.
• Force… opens a script that forces that plan for the query (and Unforce… one that undoes it). Nothing runs until you run it:

EXEC sys.sp_query_store_force_plan @query_id = 198, @plan_id = 24;

#When Query Store is off

The tab says so and offers Script Turning It On:

ALTER DATABASE [Northwind] SET QUERY_STORE = ON
    (OPERATION_MODE = READ_WRITE);

It starts collecting from then on; give it a while (or a day) of normal traffic before reading the top queries. The tab's own queries are left out of what it shows.