Sql Server

How SQL Server Statistics Works

How SQL Server Statistics Works

How SQL Server Statistics Works

SQL Server statistics are essential for the query optimizer to prepare an optimized and cost-effective execution plan. These statistics provide distribution of column values to the query optimizer, and it helps SQL Server to estimate the number of rows (also known as cardinality).

When are SQL Server Statistics Created?

Statistics are created in a couple of different ways.

Create Automatically:
1. The first way is when an index is created on a tables column(s). When this happens the Microsoft SQL Server database automatically creates a statistics object for the column(s) that make up the index

2. The next way is also done automatically and this happens if you have the "AUTO CREATE STATISTICS" database option set. This can be checked by running the following TSQL.

SELECT name,is_auto_create_stats_on FROM sys.databases WHERE name = 'AdventureWorks2017';  

If it is not set it can be enabled by running the following TSQL but it should be enabled by default.


When this database option is enabled, anytime a column is used as a query predicate the Microsoft SQL Server engine will automatically generate statistics for the referenced column.

Create Manually:

you can manually create statistics for the JobTitle column in the HumanResources.Department table.
CREATE STATISTICS STATS_Employee_JobTitle on HumanResources.Employee (JobTitle);

You can also create filtered statistics if your data set has a subset of rows with a unique data distribution
CREATE STATISTICS STATS_Address_StateProvinceIDFiltered_City  
   ON Person.Address ( City ) WHERE StateProvinceID = 79;  

How are SQL Server Statistics Updated?
A much easier method for view object statistics, however, is to use the DBCC SHOW_STATISTICS command.
DBCC SHOW_STATISTICS ("Sales.SalesOrderHeader",IX_SalesOrderHeader_CustomerID)
DBCC SHOW_STATISTICS ("Sales.SalesOrderHeader",OrderDate)

When are SQL Server Statistics Updated
there are also a couple ways they can be updated, manually and automatically.

Update Manually: To update statistics manually we can use the UPDATE STATISTICS command or the built-in stored procedure sp_updatestats.  sp_updatestats will update the statistics for every user defined table in the database it is run against. The UPDATE STATISTICS command gives you the ability to use the same sampling options that were outlined above with the CREATE STATISTICS command.

UPDATE STATISTICS HumanResources.Employee STATS_Employee_JobTitle_fullscan;  
UPDATE STATISTICS HumanResources.Employee STATS_Employee_JobTitle_sample10rows WITH FULLSCAN;  

Note: When statistics are updated, queries in the plan cache referencing these objects will recompile the next time they are called so you do have to be careful not to update statistics too often as the performance impact of frequently recompiling queries can hurt your overall system performance.

Update Automatically: The second way statistics can be updated is automatically when the "AUTO UPDATE STATISTICS" database option is set. If this option is set SQL Server will check how many rows have been modified in the table and if a certain threshold is exceeded it will update the statistics and create a new execution plan for the plan cache.

Where are SQL Server Statistics stored
Statistics in SQL Server are stored in binary large objects (BLOBs) and can be accessed using the following system catalog views and DMVs.

Related Post

About Us

Community of IT Professionals

A Complete IT knowledgebase for any kind of Software Language, Development, Programming, Coding, Designing, Networking, Hardware and Digital Marketing.