Chapter 1 SQL server statistics (1) create and update statistics

Summary: The statistics of the query: so far, we have introduced the selection and maintenance of indexes. If appropriate indexes are available and statistics are updated in real time, the optimizer selects useful indexes for query, because the SQLServer optimizer is overhead-based optimization. When data in columns on where and on needs to be displayed in the result set,

Summary: The statistics of the query: so far, we have introduced the selection and maintenance of indexes. If appropriate indexes are available and statistics are updated in real time, the optimizer selects useful indexes for query, because the SQLServer optimizer is overhead-based optimization. When data in columns on where and on needs to be displayed in the result set,

Introduction: The queried Statistics Information:

So far, we have introduced how to select and maintain indexes. If appropriate indexes are available and real-timeUpdateStatisticsInformationThe optimizer selects useful indexes for query, because the SQL Server optimizer is based on overhead optimization. When data in columns on the where and on needs to be displayed in the result setStatisticsInformation, The optimizer will select the best execution method, because the optimizer willStatisticsInformation.

InCreateWhen indexing, SQLServer will be on the index ColumnCreateStatisticsInformation. To put it simply,StatisticsInformationThat is, the data that can describe the data distribution in the index or column.

Query selectivity:

Formula: Total number of non-duplicated data in a column/total number of data in a column

The higher the selectivity, the better the index performance. When the value of the above formula is 1, it can be used as a primary key or a unique key.

CreateAnd Update Statistics Information:

StatisticsInformationThis helps the SQL Server Optimization engine SELECT appropriate indexes and related operations for executing SELECT statements. There are two methodsCreateAndUpdateStatisticsInformation:

1. ManualCreateAndUpdateStatisticsInformation

2. AutomaticCreateAndUpdateStatisticsInformation

Preparations:

Before getting started, let's take a look at how to find the currentStatisticsInformationSettings:

SELECT  CASE WHEN DATABASEPROPERTYEX('master', 'IsAutoCreateStatistics') = 1             THEN 'Yes'             ELSE 'No'        END AS 'IsAutoCreateStatistics?' ,        CASE WHEN DATABASEPROPERTYEX('Master', 'IsAutoUpdateStatistics') = 1             THEN 'Yes'             ELSE 'No'        END AS 'IsAutoUpdateStatistics?' ,        CASE WHEN DATABASEPROPERTYEX('Master', 'Is_Auto_Update_stats_async_on') = 1             THEN 'Yes'             ELSE 'No'        END AS 'IsAutoUpdateStatsaAyncOn?'GO

The following statement is used to displayStatisticsInformationSituation:

SELECT  object_id ,        OBJECT_NAME(object_id) AS TableName ,        name AS StatisticsName ,        auto_createdFROM    sys.stats--where object_id=OBJECT_ID('Sales.SalesOrderHeader')ORDER BY object_id DESC GO

You can also view it in the following ways:

sp_helpstats 'Sales.SalesOrderHeader' 

Steps:

1. Now let's take a look.CreateAndUpdateStatisticsInformationIn different ways, at the database level, there is an option, the default is ON, this option is: Auto_Create_Statistics:

 ALTER DATABASE AdventureWorks SET AUTO_CREATE_STATISTICS ON 

2. enable synchronizationCreateColumnStatisticsInformationAuto_Create_StatisticsCreateA bar chart. By SQLServerCreateOfStatisticsInformationStarting with _ WA, you can look at these lists:

SELECT  st.name AS StatName ,        COL_NAME(stc.object_id, stc.column_id) AS ColumnName ,        OBJECT_NAME(st.object_id) AS TableNameFROM    sys.stats AS st        INNER JOIN sys.stats_columns AS stc ON st.object_id = stc.object_id                                               AND st.stats_id = stc.stats_idWHERE   st.name LIKE '_WA%'

3. The aboveStatisticsInformationIt does not end because the Auto_Create_Statistics option is set to ON. These are mandatory.StatisticsInformationUpdateTo ensure excellent performance. This just defines yourStatisticsInformationSynchronous or notUpdate. By default, this option is ON. But sometimes it does not necessarily meet your requirements. You can use manualUpdatePlan:

ALTER DATABASE AdventureWorks SET AUTO_UPDATE_STATISTICS ON

4. The Auto_Update_Statistics option is displayed inCreateWhen indexing, you can use Auto_Create_Statistics or the create statistics command to manuallyCreateStatisticsInformationAutomaticUpdateStatisticsInformation, The following command uses the Asynchronous MethodUpdateStatisticsInformation:

ALTER DATABASE AdventureWorks SET AUTO_UPDATE_STATISTICS_ASYNC ON

5. Now let's look at the database after the above statement is executed.StatisticsInformationConfiguration:

 SELECT  is_auto_update_stats_async_on ,        is_auto_create_stats_on ,        is_auto_update_stats_onFROM    sys.databasesWHERE   name = 'AdventureWorks' 

6. The method above is automaticCreateAndUpdateStatisticsInformationNow let's see how to implement it manually:

--CreateStatisticsInformationCreate statistics st_DueDate_SalesOrderHeader ON Sales. SalesOrderHeader (DueDate) GO --UpdateAll of the Sales. SalesOrderHeader tablesStatisticsInformationUpdate statistics Sales. SalesOrderHeaderGO --UpdateSt_DueDate_SalesOrderHeader of the Sales. SalesOrderHeader tableStatisticsInformationUpdate statistics Sales. SalesOrderHeader st_DueDate_SalesOrderHeaderGO --UpdateAll availableStatisticsInformationEXEC sys. sp_updatestatsGO -- manually deleteStatisticsInformationDrop statistics Sales. SalesOrderHeader. st_DueDate_SalesOrderHeaderGO

Analysis:

When the indexCreateThe optimizer willCreateStatisticsInformationTo the table or view where the index column is located. In addition, if the Auto_Create_Statistics option is set to ON, the optimizer willCreateA single columnStatisticsInformationIn a timely manner, it does not appear in the columns required for the query. If you think there are some query performance problems, check all the predicates. If these columns are missingStatisticsInformationYou can add them manually. Sometimes, DTA (Database optimization consultant) also recommends youCreateStatisticsInformation.

Generally, if synchronization is enabled before the query compilationUpdateStatisticsInformationIf SQLServer discoversStatisticsInformationWill causeUpdateStatisticsInformationThen your query will use the real-timeStatisticsInformation. This operation will block the query.UpdateBut these queries are not retained.UpdateStatisticsInformationSo that the newStatisticsInformation.

Expand knowledge:

By default, only the sysadmin/db_owner/ObjectCreateOnly members of these three roles have permissions.CreateAndUpdateStatisticsInformation.

Bar chart:

A column chart is a typeStatisticsInformationThe generated table. It can be considered as a display ColumnStatisticsInformationReports with the maximum and minimum ranges.