Skip to content
Tutorials Center
My learning

Topics

Lesson 31 of 39 ยท Indexes and Performance

Query Store and a Tuning Workflow

Find slow queries with Query Store and dynamic management views, force plans, track waits, and follow a repeatable tuning workflow for SQL Server from symptom to fix.

Tuning is not guessing at indexes. It is a loop: find what is slow, understand why, change one thing, measure again. SQL Server gives you two main sources of evidence: Query Store, which keeps a history of queries, plans and runtime statistics inside the database, and the dynamic management views (DMVs), which expose live server state.

This lesson shows both and ends with a workflow you can apply to any slow system. All the SQL Server features here are read-only; a runnable stand-in at the end aggregates a query log the way a Query Store report does. Combine this with execution plans and indexes.

Query Store

Query Store (SQL Server 2016 and later, on by default for new databases from 2022) records, per query and per time interval: the query text, every plan used, durations, CPU, reads, executions and waits. The data survives restarts and failovers.

SQL
ALTER DATABASE Shop SET QUERY_STORE = ON;
ALTER DATABASE Shop SET QUERY_STORE
    (OPERATION_MODE = READ_WRITE,
     QUERY_CAPTURE_MODE = AUTO,         -- skip trivial, one-off queries
     MAX_STORAGE_SIZE_MB = 1000,
     INTERVAL_LENGTH_MINUTES = 60);

In SSMS, open Database > Query Store for built-in reports:

  • Top Resource Consuming Queries: where the time and reads go.
  • Regressed Queries: queries that got slower after a plan change.
  • Tracked Queries for one query over time, with its plans side by side.
  • Query Wait Statistics for what queries wait on.

You can also query the catalog views:

SQL
SELECT TOP (10)
       q.query_id,
       qt.query_sql_text,
       SUM(rs.count_executions)                    AS executions,
       SUM(rs.count_executions * rs.avg_duration) / 1000.0 AS total_duration_ms,
       SUM(rs.count_executions * rs.avg_logical_io_reads)  AS total_reads
FROM   sys.query_store_query_text AS qt
JOIN   sys.query_store_query AS q          ON q.query_text_id = qt.query_text_id
JOIN   sys.query_store_plan AS p           ON p.query_id = q.query_id
JOIN   sys.query_store_runtime_stats AS rs ON rs.plan_id = p.plan_id
GROUP  BY q.query_id, qt.query_sql_text
ORDER  BY total_duration_ms DESC;

Note the sort: total time (executions times average), not the slowest single run. A 50 ms query run a million times a day costs more than a 10 s monthly report.

Plan regression and forcing a plan

A query that was fast yesterday and slow today often just got a worse plan, perhaps after a statistics update or a parameter-sniffing change. Query Store shows both plans. If the old plan was better, force it:

SQL
EXEC sp_query_store_force_plan @query_id = 1042, @plan_id = 3377;
-- later, if it is no longer wanted
EXEC sp_query_store_unforce_plan @query_id = 1042, @plan_id = 3377;

Forcing is a bandage that buys time to find the root cause. SQL Server 2017 added automatic plan correction (AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON)), and 2022 added Query Store hints for fixes like RECOMPILE without changing application code.

Dynamic management views

DMVs show what is happening now. Four you should know:

SQL
-- 1. Currently running requests and what they wait on
SELECT r.session_id, r.status, r.wait_type, r.total_elapsed_time, t.text
FROM   sys.dm_exec_requests AS r
CROSS  APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE  r.session_id > 50;

-- 2. The most expensive cached queries since the plan was cached
SELECT TOP (10) qs.total_worker_time / qs.execution_count AS avg_cpu_us,
       qs.execution_count, SUBSTRING(t.text, 1, 200) AS sample
FROM   sys.dm_exec_query_stats AS qs
CROSS  APPLY sys.dm_exec_sql_text(qs.sql_handle) AS t
ORDER  BY qs.total_worker_time DESC;

-- 3. Index usage
SELECT OBJECT_NAME(s.object_id) AS tbl, i.name, s.user_seeks, s.user_scans, s.user_lookups, s.user_updates
FROM   sys.dm_db_index_usage_stats AS s
JOIN   sys.indexes AS i ON i.object_id = s.object_id AND i.index_id = s.index_id
WHERE  s.database_id = DB_ID();

-- 4. Server-wide waits
SELECT TOP (10) wait_type, wait_time_ms, waiting_tasks_count
FROM   sys.dm_os_wait_stats
ORDER  BY wait_time_ms DESC;

DMV data resets when the server restarts (and the plan cache can be flushed by memory pressure), so Query Store is better for history.

Wait statistics

Every time a thread can't continue, SQL Server records why. Reading waits tells you whether the problem is CPU, disk, memory or locks:

Wait Typical meaning
PAGEIOLATCH_SH Waiting to read pages from disk: slow storage, missing indexes or too little memory
LCK_M_X, LCK_M_S Blocking by locks (locking)
CXPACKET / CXCONSUMER Parallelism; often benign, check MAXDOP and cost threshold
SOS_SCHEDULER_YIELD CPU pressure
WRITELOG Slow transaction log writes
ASYNC_NETWORK_IO The client is slow to consume results

Ignore benign background waits (SLEEP_TASK, LAZYWRITER_SLEEP, XE_TIMER_EVENT ...) when reading the top list.

A repeatable workflow

flowchart TD
  A["Symptom: users report slowness"] --> B["Waits and Query Store: what is slow overall?"]
  B --> C["Pick the top query by total cost"]
  C --> D["Actual plan + STATISTICS IO"]
  D --> E{"Cause?"}
  E -- "no index / scan" --> F["Add or adjust index"]
  E -- "bad estimate" --> G["Update statistics, fix sniffing"]
  E -- "non-SARGable / conversion" --> H["Rewrite the query"]
  E -- "blocking" --> I["Shorten transactions, row versioning"]
  F --> J["Measure again"]
  G --> J
  H --> J
  I --> J

Change one thing at a time and keep the evidence.

Some rules of thumb that save weeks: fix the biggest total first, test with production-sized data, compare logical reads before and after, never tune by hardware alone, and document what you changed.

A stand-in: ranking a query log

A Query Store report is a GROUP BY over runtime stats. Here is the same idea over a small log table:

SQL Top queries by total cost

Press Run (or Ctrl + Enter) to see the result here.

Add many more executions of query 1 and it can overtake the monthly report on total cost. Try it:

SQL Frequency changes the ranking

Press Run (or Ctrl + Enter) to see the result here.

The "fast" query costs 24,000 ms in total, more than twice the monthly report's single 9,200 ms run.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does Query Store keep that the plan cache DMVs do not?

    Query Store persists data in the database, so history survives restarts and cache evictions.
  2. 2. Why sort a top-queries report by total duration instead of the slowest single run?

    A cheap query executed millions of times can cost far more than an expensive rare query.
  3. 3. What does forcing a plan in Query Store do?

    sp_query_store_force_plan pins a known-good plan, useful for regressions while you find the root cause.
  4. 4. A large share of PAGEIOLATCH_SH waits most likely points to what?

    The wait means sessions wait for pages to be read into memory.

Summary

  • Query Store records queries, plans and stats over time; use it to find top consumers and plan regressions, and force a plan when needed.
  • DMVs show live state: requests, waits, index usage and cached query stats.
  • Wait statistics reveal whether you are bound by CPU, I/O, locks or the network.
  • Tune with a loop: find, diagnose, change one thing, measure.

Next: Backup, restore and recovery models