Optimize SQL queries

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 behaviour (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 (in French).
  • Steven Naudet (in French).
• Use an Extended Events session:
  • Sample sessions on my GitHub
  • To learn how to use Extended Events, there is a full video on the subject on my YouTube channel (in French).
• Query the dm_exec_query_stats view.
• In real time, use the sp_whoisactive procedure.
• If you have stored procedures, use the dm_exec_procedure_stats view.

Row-at-a-time behaviour

Too many round trips between the client and the server cause serious performance problems. This behaviour 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 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.

Parallelism

• To tune parallelism on the instance, configure the following:

  • Cost threshold for parallelism — set it to 50.
  • Max degree of parallelism — 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.

• Since SQL Server 2016 you can configure the degree of parallelism per database, with a scoped configuration.

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 query and look for LCK... waits.
• If you find waits of that kind, take the diagnosis further with these instructions.
• You can also schedule a check every few minutes with this script.
• Watch active transactions and long transactions. You can use this query on my GitHub 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 diagnostic view.
• Look for blocking with the tools from the previous section.
• If you have stored procedures, use the dm_exec_procedure_stats 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.

Missing indexes

• Check the missing index report.
• Check that your indexes are actually used, with the index usage report.
• 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.

Cardinality estimation errors

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

• Check the statistics with this script, and look at the tables that have seen a lot of modifications.
• Look at the actual execution plan.
• Use 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.
• Check whether the query uses table variables, whose cardinality is badly estimated in older compatibility levels.