Statistics and cardinality estimates

This lesson uses DDI_SQL_Training dataset v1.0.0 on SQL Server 2019 or later. Inspect statistics objects used to estimate how many rows a query may return.

Run the example

SELECT name AS StatisticName, auto_created AS AutoCreated, user_created AS UserCreated
FROM sys.stats
WHERE object_id = OBJECT_ID(N'dbo.Orders')
  AND name = N'PK_Orders'
ORDER BY name;

Read it step by step

1. sys.stats lists statistics associated with Orders.
2. The PK index has a statistics object that the optimizer can use for cardinality estimates.
3. Metadata flags identify creation source; estimated rows must still be checked in an execution plan.

Check the result

Filtering to the named PK_Orders statistic avoids depending on optional automatically created statistics. Both creation flags are 0 here because this statistic belongs to an index.

Practice

Inspect the named PK statistic on dbo.Products.

SELECT name AS StatisticName, auto_created AS AutoCreated, user_created AS UserCreated
FROM sys.stats
WHERE object_id = OBJECT_ID(N'dbo.Products')
  AND name = N'PK_Products'
ORDER BY name;