Introduction to Statistics in SQL Server

Another video brought to you by BeardedDev, bringing you tutorials on Data Engineering, Business Intelligence, T-SQL Programming and Data Analysis.

In this video I provided an introduction as to how statistics are created and maintained in SQL Server. Statistic objects include a header, density and histogram which is what I mainly cover.

Ever wondered when looking at an execution plan where the estimated number of rows come from, in this video I show you exactly how you can look at the histogram of an statistics object and find out what number will be produced.

DBCC SHOW_STATISTICS is a database console command, we need to pass in the table or indexed view name and either an index name, statistics object name or column name. It will return the header, density vector and histogram, we can also add the WITH option which will return the relevant result set.

Histograms have up to 200 steps and are only stored for the first column of an index. We can use DBCC SHOW_STATISTICS to retrieve the statistical information that is stored, I have included some code samples below if you want to follow along and are using the AdventureWorks database, if you are using a different database, you will need to change the table and index when running DBCC SHOW_STATISTICS.

Code examples:

SELECT
SalesOrderID,
SalesOrderDetailID
FROM Sales.SalesOrderDetail
WHERE ProductID = 819;

DBCC SHOW_STATISTICS ("Sales.SalesOrderDetail", [IX_SalesOrderDetail_ProductID]) WITH HISTOGRAM;