# Optimize SQL queries

> A method for optimizing SQL Server queries: IO and TIME statistics, Query Store, Extended Events, row-by-row processing, indexes, statistics.
>
> Source : https://www.pachadata.com/en/docs/howtos/performance/queries/
> Author : Rudi Bruchez · updated 2026-10-14
> Markdown version of the page, for tools and LLMs. The HTML page is the reference.


> What methodology can you use to optimize your Transact-SQL queries?

## Hypotheses

Query performance problems can come from:

1. A bad data model;
2. Badly written queries that run too long;
3. Row-at-a-time rather than set-based behavior (*RBAR*);
4. Trigger execution, or transactions that run too long;
5. Missing indexes;
6. Waits, on query parallelism for instance;
7. Execution plan, compilation and *parameter sniffing* problems.

## A bad data model

A bad data model is hard to fix. You can:

- Use `ROW` or `PAGE` compression to reduce the physical size of the tables and improve I/O.
- Fix the schema gradually, using views as an abstraction layer so you can remodel the tables without changing the client code. Updates through views can be reproduced with `INSTEAD OF` triggers.

## Diagnosing badly written and overlong queries

- Check the I/O and time statistics of your queries.
  - `SET STATISTICS IO ON` — displays I/O statistics per table accessed by the query. The unit is the page (8 KB). The statistics separate logical I/O (in the buffer) from physical I/O (physical and read-ahead reads).
  - `SET STATISTICS TIME ON` — displays time statistics per query, separating compilation time (building the execution plan) from execution time. The times given are CPU time and total elapsed time, including sending the results to the client.
- Use the Query Store to identify the most resource-hungry queries.
  - [Nicolas Souquet – Tout savoir sur le Query Store](https://youtu.be/Adwtl1QtYvI) (in French).
  - [Steven Naudet](https://youtu.be/EVchvLCk-IQ) (in French).
- Use an Extended Events session:
  - [Sample sessions on my GitHub](https://github.com/rudi-bruchez/tsql-scripts/tree/main/extended-events)
  - To learn how to use Extended Events, there is [a full video on the subject](https://youtu.be/SC7MfYsd-p0) on my YouTube channel (in French).
- Query the [dm_exec_query_stats](https://github.com/rudi-bruchez/tsql-scripts/blob/main/diagnostics/execution-stats/dm_exec_query_stats.sql) view.
- In real time, use the [sp_whoisactive](https://github.com/amachanic/sp_whoisactive/releases) procedure.
- If you have stored procedures, use the [dm_exec_procedure_stats](https://github.com/rudi-bruchez/tsql-scripts/blob/main/diagnostics/stored-procedures/procedure-execution-analysis.sql) view.

## Row-at-a-time behavior

Too many round trips between the client and the server cause serious performance problems. This behavior has a name: RBAR, *Row By Agonizing Row*.

What you want instead is to get as much data as possible in one set-based query, and to make as few round trips as possible. Much like doing the shopping: you buy everything in one trip to the supermarket, and come home with all of it at once.

- With an Extended Events session and no query cost filter, look for repeated calls of similar queries — for example runs of inserts, or similar single-row `SELECT` statements with only the parameters changing.
- Query the [dm_exec_query_stats](https://github.com/rudi-bruchez/tsql-scripts/blob/main/diagnostics/execution-stats/query_stats.sql) view looking for cheap queries executed very often — sort on the `execution_count` column.
- Use the Query Store to identify the most frequently executed queries.

To fix it:

- Send arrays to your stored procedures. Several methods are available:
  - an XML parameter, parsed with `NODES()`;
  - a JSON parameter (`NVARCHAR(MAX)`), parsed with `OPENJSON`;
  - a string parameter, parsed with `STRING_SPLIT()`;
  - a table-valued parameter.
- Always verify how your application actually behaves, with an Extended Events session on the server.
- With Entity Framework, make sure you are using the right [loading strategy](https://learn.microsoft.com/ef/core/querying/related-data/lazy).

### Parallelism

- To tune parallelism on the instance, configure the following:
  - [Cost threshold for parallelism](https://learn.microsoft.com/sql/database-engine/configure-windows/configure-the-cost-threshold-for-parallelism-server-configuration-option) — set it to **50**.
  - [Max degree of parallelism](https://learn.microsoft.com/sql/database-engine/configure-windows/configure-the-max-degree-of-parallelism-server-configuration-option) — do not leave it at 0 if you have more than eight processors. 4 is often a good value for OLTP servers. See the [recommendations](https://learn.microsoft.com/sql/database-engine/configure-windows/configure-the-max-degree-of-parallelism-server-configuration-option#recommendations).

- Since SQL Server 2016 you can configure the degree of parallelism per database, with a [scoped configuration](https://learn.microsoft.com/sql/t-sql/statements/alter-database-scoped-configuration-transact-sql).

```sql
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 1;
```

### Blocking caused by too much locking

Blocking is a wait on locks held by other sessions.

Is there any blocking?

- Run the [Paul Randal, *tell me where it hurts*](https://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/) query and look for `LCK...` waits.
- If you find waits of that kind, take the diagnosis further [with these instructions](../blocking/).
- You can also schedule a check every few minutes with [this script](https://github.com/rudi-bruchez/tsql-scripts/blob/main/diagnostics/locking/monitor-blocking.sql).
- Watch active transactions and long transactions. You can use [this query on my GitHub](https://github.com/rudi-bruchez/tsql-scripts/blob/main/diagnostics/execution/active-transactions.sql) to see the active transactions.

### Trigger execution, transactions that run too long

- Measure the cost of your triggers with the [sys.dm_exec_trigger_stats](https://github.com/rudi-bruchez/tsql-scripts/blob/main/diagnostics/execution-stats/trigger-stats.sql) diagnostic view.
- Look for blocking with the tools from the previous section.
- If you have stored procedures, use the [dm_exec_procedure_stats](https://github.com/rudi-bruchez/tsql-scripts/blob/main/diagnostics/stored-procedures/procedure-execution-analysis.sql) view.
- If you suspect triggers, stored procedures or function calls, create an Extended Events session on the `sp_statement_completed` event — [Azure code, to be adapted for on-premises](https://github.com/rudi-bruchez/tsql-scripts/blob/main/extended-events/azure-sql-database/trace-procedure-create.sql).

### Missing indexes

- Check the [missing index report](https://github.com/rudi-bruchez/tsql-scripts/blob/main/index-management/missing-indexes.sql).
- Check that your indexes are actually used, with the [index usage report](https://github.com/rudi-bruchez/tsql-scripts/blob/main/index-management/index-usage.sql).
- Use the Database Engine Tuning Advisor on a query or on a workload.
- If your queries are more analytical in nature, try ColumnStore indexes.

### Execution plan, compilation and *parameter sniffing* problems

When the problem shows up, flush the plan cache with `DBCC FREEPROCCACHE`. Does that fix it? You can run this command instead of restarting a SQL Server.

Check the statistics with this [diagnostic query](https://github.com/rudi-bruchez/tsql-scripts/blob/main/diagnostics/tables/statistics.sql).

### Cardinality estimation errors

A classic sign: the query degrades over time. Schedule more frequent statistics updates.

- Check the statistics with [this script](https://github.com/rudi-bruchez/tsql-scripts/blob/main/diagnostics/optimizer/statistics.sql), and look at the tables that have seen a lot of modifications.
- Look at the **actual** execution plan.
- Use [Plan Explorer](https://www.solarwinds.com/free-tools/plan-explorer).
- Trigger a recalculation with `UPDATE STATISTICS`.
- Enable the legacy cardinality estimator, either at database level or per query with `OPTION (USE HINT('FORCE_LEGACY_CARDINALITY_ESTIMATION'))`. See [Cardinality Estimator](../cardinality-estimator/).
- Check whether the query uses table variables, whose cardinality is badly estimated in older compatibility levels.


---

## About the author

Rudi Bruchez, independent SQL Server consultant since 2006: performance audits, tuning, migrations, high availability and training. For an audit or hands-on help on your server: https://www.pachadata.com/en/contact/ (remote audit: https://www.pachadata.com/en/services/remote-sql-server-audit/).
