Troubleshoot performance problems

If your SQL Server is slow, you absolutely must gather evidence, so that you do not assume problems — and solutions — that have nothing to do with the actual issue.

If you were a doctor, would you prescribe a treatment without making a diagnosis first?

Hypotheses

Where can performance problems come from?

1. An undersized server;
2. Bad configuration of SQL Server or of the databases;
3. Badly written queries that run too long;
4. Excessive round trips between the client and the server;
5. Blocking caused by too much locking;
6. Trigger execution, or transactions that run too long;
7. Missing indexes;
8. Waits, on query parallelism for instance;
9. The client code, rather than SQL Server itself;
10. Execution plan, compilation and parameter sniffing problems.

How to test the hypotheses

Undersized server

SQL Server runs perfectly well on modest hardware, provided the database and the queries are optimized.

A few leads:

• Run the Paul Randal, tell me where it hurts query and look at the most common waits. Pay attention to the signal time, which tells you about CPU pressure.
• Look at the Query Store, “Overall Resource Consumption” report.
• Check the buffer (data cache) counters with this query.
• Should you disable hyperthreading? It depends. Generally, you can leave it on. On a VMware host, leave it enabled — VMware handles HT well.

Is there a disk problem?

To find out whether disk performance is to blame, you can:

• Follow the performance counters:
  • Physical Disk / Avg. Disk sec/Read on the drive holding the data files and tempdb.
  • Physical Disk / Avg. Disk sec/Write on the drive holding the transaction log files and tempdb.
  • Some reference values in this blog post.
• Check the I/O latencies per file with this query.
• Use Diskspd (here is an article on how to use it).

Bad configuration of SQL Server or of the databases

To find out whether the instance configuration is involved:

• Parallelism? The one configurable thing that can genuinely change the picture.
  • Experiment with it.
  • Look at the waits of type CXPACKET. If you have a lot of them, check whether hyperthreading is enabled.

  • The sys.dm_os_latch_stats DMV contains information about the specific latch waits that have occurred in the instance, and if one of the top latch waits is ACCESS_METHODS_DATASET_PARENT, in conjunction with CXPACKET, LATCH_*, and SOS_SCHEDULER_YIELD wait types as the top waits, the level of parallelism on the system is the cause of bottlenecking during query execution, and reducing the ‘max degree of parallelism’ sp_configure option may be required to resolve the problems.

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;

Database configuration

• auto close — you can feel this one. Always FALSE.
• auto shrink — always FALSE. This option should not even exist.
• auto create statistics — always true.
• auto update statistics — always true.
• In short: leave the database options at their defaults.
• compatibility level — as high as possible, to benefit from the relational engine improvements.
• Manage the version of the cardinality estimator (legacy cardinality estimation) as needed, based on the estimation errors you observe in your queries.

Badly written queries that run too long

• 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.

Excessive round trips between the client and the server

Harder to spot. The pattern to look for is an excess of small repetitive queries doing one round trip at a time between the client and the server, instead of set-based queries. This is behaviour to change in the client code.

• 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.

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.