Indexes and performance
Statistics, cardinality and join algorithms
The optimiser relies on imperfect information.
Understand the concept
Statistics summarise distribution using histograms and density information to help estimate cardinality. Stale statistics, sampling, correlations and skewed parameters can cause errors. Estimates influence join selection, memory grants and parallelism.
Nested loops can suit a small outer input with efficient inner access. Hash joins can suit large equality joins but may spill without enough memory. Merge joins use suitably ordered inputs, whose sorting cost also matters. Every algorithm has valid contexts. Before forcing a hint, inspect data, indexes, statistics and query logic. Do not launch broad production statistics maintenance without measurement.
Understand how estimation errors change plan choices.
Categorically declaring hash joins or scans bad.
One topic. Three levels of depth.
SELECT name, auto_created, user_created FROM sys.stats WHERE object_id = OBJECT_ID(N'lab.SalesOrder');Lists existing statistics.
This page does not execute SQL. Practise in the downloadable SqlStudioLab using SSMS. Before rerunning temporary-table examples, remove only the relevant #table. Views, procedures and functions create persistent lab objects; never use a production connection.
Try it yourself first.
Inspect statistics update dates for Product.
Show the solution and explanation
SELECT s.name, p.last_updated FROM sys.stats AS s OUTER APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS p WHERE s.object_id = OBJECT_ID(N'lab.Product');NULL metadata may reflect unavailable statistics information or permissions.