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.
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.You can also create filtered statistics if your data set has a subset of rows with a unique data distribution
A much easier method for view object statistics, however, is to use the DBCC SHOW_STATISTICS command.
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.
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.
sys.stats
sys.stats_columns
sys.dm_db_stats_properties
sys.dm_db_stats_histogram
