What are statistics.How do they get created, what are the options that can be set on a database level?

Problem: What are statistics.How do they get created, what are the options that can be set on a database level?

Solution: Statistics

The word Statistics in general means the collection, analysis, presentation, and structuring of data the same applies to SQL Server Indexes built on one or more Columns.

If you have worked extensively on Transact SQL you might have heard about this to improve performance on slow running queries or heavily fragmented indexes or high density statistics.

Statistics are located on the last drop down of a table when you navigate through in a SQL Server Management Studio. To see where it is stored please look at the below picture.

 Statistics information is auto generated by SQL Server based on cardinality estimation, number of rows in the table, query result to be generated. Statistics on a table are useful for query optimizer to improve query execution plans and performance. For the most part this determines whether to choose Index Seek or Index scan based on the statistics generated or created.

We have three options that can be set on a statistics and are at a database level only.

1.       AUTO_CREATE_STATISTICS Option.

When the automatic create statistics option, AUTO_CREATE_STATISTICS, is on, the query optimizer creates statistics on individual columns in the query predicate, as necessary, to improve cardinality estimates for the query plan. These single-column statistics are created on columns that do not already have a histogram in an existing statistics object. The AUTO_CREATE_STATISTICS option does not determine whether statistics get created for indexes. This option also does not generate filtered statistics. It applies strictly to single-column statistics for the full table.

 Statistics which are auto created generally have a prefix _WA, and are created by the SQL Server itself.

2.       AUTO_UPDATE_STATISTICS Option.

When the automatic update statistics option, AUTO_UPDATE_STATISTICS, is on, the query optimizer determines when statistics might be out-of-date and then updates them when they are used by a query. Statistics become out-of-date after insert, update, delete, or merge operations change the data distribution in the table or indexed view. The query optimizer determines when statistics might be out-of-date by counting the number of data modifications since the last statistics update and comparing the number of modifications to a threshold. The threshold is based on the number of rows in the table or indexed view.

3.       INCREMENTAL STATS.

When ON, the statistics created are per partition statistics. When OFF, the statistics tree is dropped and SQL Server re-computes the statistics. The default is OFF. This setting overrides the database level INCREMENTAL property.

Example:

Example 1: In this example I would like to show how we can see the statistics on the table and what type of options are set.

SELECT

                 s.name AS statistics_name 

    ,s.stats_id 

    ,s.auto_created 

    ,s.user_created 

    ,s.no_recompute 

    ,s.has_filter 

    ,s.filter_definition 

-- using the sys.stats catalog view 

FROM sys.stats  s

INNER JOIN sys.tables t

ON t.object_id = s.OBJECT_ID

WHERE t.is_ms_shipped=0

and t.name ='Orders'

OUTPUT:

statistics_name	stats_id	auto_created	user_created	no_recompute	has_filter	filter_definition
PK__MobileUn__44F5ECB5B4A1E1C9	1	0	0	0	0	NULL
_WA_Sys_00000002_2C3393D0	2	1	0	0	0	NULL
_WA_Sys_00000004_2C3393D0	3	1	0	0	0	NULL
_WA_Sys_00000003_2C3393D0	4	1	0	0	0	NULL

In the next article let’s see how we can create, update, delete, view, rename statistics.