What are Statistics in SQL Server?
Statistics store information about how data is distributed in a table or index column.
They mainly contain:
-
A histogram showing the distribution of values
-
The number of distinct values
-
Data density and row-count information
SQL Server can create statistics automatically for indexed columns and columns used in query conditions.
Why are they important?
The Query Optimizer uses statistics to estimate how many rows a query will return. Based on this estimate, it chooses:
-
Index seek or scan
-
Join type
-
Join order
-
Memory allocation
-
Parallel or serial execution
If statistics are missing or outdated, SQL Server may estimate rows incorrectly and choose a slow execution plan.
Example
SELECT *
FROM Orders
WHERE CustomerId = 100;Statistics help SQL Server estimate how many orders belong to customer 100.
Updating statistics
UPDATE STATISTICS dbo.Orders;
-- Update statistics for the entire database
EXEC sp_updatestats;SQL Server normally updates them automatically when AUTO_UPDATE_STATISTICS is enabled.
Key points
-
Statistics are not indexes; they describe data distribution.
-
An index normally has statistics on its key columns.
-
Fresh statistics usually help SQL Server create better execution plans.
-
Updating statistics can trigger query-plan recompilation.
-
Update them based on evidence; frequent full scans can be expensive on large tables.