Analyze queries
How to analyze queries to identify performance problems. A step-by-step method.
Here is a condensed checklist of the method for analysing the performance of a query, or of a SQL Server database.
1. Look at the I/O — SET STATISTICS IO ON
- If there is a lot of I/O on a table, is there an index on it? (video, in French)
2. Look at the time — SET STATISTICS TIME ON
SET STATISTICS IO, TIME ON(video, in French)- Mind the difference between CPU time and total elapsed time.
- CPU < elapsed ⇒ waits. Not the query’s fault: lock blocking, disk reads, or the behaviour of the client’s data reader (video, in French).
- CPU > elapsed ⇒ parallelism (video, in French). Not a problem in itself.
3. Look at the actual execution plan
- Is there a discrepancy in the cardinality estimate? Under-estimation is the dangerous direction.
- Prefer Plan Explorer for reading plans.
- If there is a discrepancy, update the statistics (video, and this one for updating them from the maintenance plan — both in French).
- It could be a table variable: if so, use
OPTION (RECOMPILE), or SQL Server 2017 and later. - It could be the Cardinality Estimator itself.
- On a cardinality estimation error in a complex query, experiment with the CE version.
OPTION (USE HINT('FORCE_LEGACY_CARDINALITY_ESTIMATION'))OPTION (USE HINT('ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS'))OPTION (USE HINT('FORCE_DEFAULT_CARDINALITY_ESTIMATION'))ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON;
4. Analyse performance across the whole workload
- Use Extended Events, filtering on duration.
- Use Extended Events with no filter for a short while, to see whether there are repetitive or duplicated queries.
- Look at the query statistics:
dm_exec_query_stats.