Statistics

 

1. Terms & Applies To

• Applies to:  SQL Server 2022 (16.x)  Azure SQL Database  Azure SQL Managed Instance

• https://learn.microsoft.com/en-us/sql/relational-databases/statistics/statistics?view=sql-server-ver16 

• 
  

2. Statistics - SYS.STATS Overview

• The Query Optimizer uses statistics to create query plans that improve query performance. For most queries, the Query Optimizer already generates the necessary statistics for a high-quality query plan; in some cases, you need to create additional statistics or modify the query design for best results. 

• Statistics are database objects defined on table / view columns and indexes to control the column address location.

• These statistics are auto created by database engine service in below scenarios:

• 1. Whenever we create indexes 

• 2. We use columns in query conditions

Purpose of statistics: 

• To identify / locate address information of table / view columns easily.

• This means to index the address of table column in the metadata page = master page.

• 
  

How to verify the list of statistics in a database?

• Select * from sys.stats 

• 
  

Advantage of statistics:

• The stats which are auto created as per above scenarios would be auto reused 

• For faster access of column data.  

• We can also update statistics manually. 

• Or

• We can schedule auto update of statistics automatically in Database maintenance plans.

To determine when statistics were last updated, use the sys.dm_db_stats_properties or STATS_DATE functions.

3. When we use Statistics?

1. Query execution times are slow

2. Insert operations occur on ascending or descending key columns

3. After maintenance operations

4. a

4. Statistics options

1. AUTO_CREATE_STATISTICS 

• When the Query Optimizer creates statistics as a result of using the AUTO_CREATE_STATISTICS option, the statistics name starts with _WA.

SELECT OBJECT_NAME(s.object_id) AS object_name,

    COL_NAME(sc.object_id, sc.column_id) AS column_name,

    s.name AS statistics_name

FROM sys.stats AS s

INNER JOIN sys.stats_columns AS sc

    ON s.stats_id = sc.stats_id AND s.object_id = sc.object_id

WHERE s.name like '_WA%'

ORDER BY s.name;

2. AUTO_UPDATE_STATISTICS 

• the Query Optimizer determines when statistics might be out-of-date and then updates them when they are used by a query. This action is also known as statistics recompilation. Statistics become out-of-date after modifications from insert, update, delete, or merge operations change the data distribution in the table or indexed view.

3. AUTO_UPDATE_STATISTICS_ASYNC

4. AUTO_DROP

5. INCREMENTAL