12th Chapter--sqlserver Statistics (1)--Create and update statistics

Original: Chapter 12th--sqlserver Statistics (1)--Create and update statistics

Summary: Statistical information for the query:

So far, the selection index and maintenance index have been introduced. If you have an appropriate index and update statistics in real time, the optimizer chooses a useful index for querying, because the SQL Server Optimizer is cost-based optimization. When the data on the where and on columns needs to be displayed in the result set, if there is real-time statistics, the optimizer chooses the best way to execute because the optimizer obtains the details of the data from the statistics.

When you create an index,SQL Server creates statistics on the index columns. In simple terms, statistics are data that can describe the distribution of data in an index or column.

Query selectivity:

Formula: Total number of non-repeating data on columns / total number of data on columns

The higher the selectivity, the better the index performance, which can be used as a primary key or a unique key when the above formula has a value of 1 .

To create and UPDATE statistics:

Statistics help the SQL Server optimization engine Select the appropriate indexes and related actions for executing select statements. There are two ways to create and update statistics:

1. Create and UPDATE statistics manually

2. automatically create and update statistics

Preparatory work:

Before you begin, let's look at how to find the current statistics settings for your database:

SELECT Case  -databasepropertyex (' Master ', ' isautocreatestatistics ') = 1 then             ' Yes '             ELSE ' No '        END As ' isautocreatestatistics ', Case-        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 display statistics about the database or table in the where clause:

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:

Steps:

1. now take a look at the different ways to create and update statistics, at the database level, there is an option , the default is on, this option is:auto_create_statistics:

2. enable the option to synchronize statistics on the Create column,auto_create_statistics, when executing a query for an exact amount of data, the optimization engine creates a bar chart on this column. The statistics created by SQL Server start with _wa , and 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 statistics above do not end because the auto_create_statistics option is set to ON. These are mandatory updates of your statistics to ensure excellent performance. This just defines whether your stats are updated synchronously. By default, this option is on . But sometimes you don't necessarily meet your requirements, you can use the manual update schedule:

ALTER DATABASE AdventureWorks SET auto_update_statistics on

4. Auto_update_statistics options automatically update STATISTICS when you create an index, through auto_create_statistics , or when you manually create statistics by using the Create Statistics command. The following command updates the statistics using asynchronous methods:

ALTER DATABASE AdventureWorks SET auto_update_statistics_async on

5. now look at the configuration of the database statistics after executing the above statement:

SELECT  is_auto_update_stats_async_on,        is_auto_create_stats_on,        is_auto_update_stats_onfrom    Sys.databaseswhere   

6. the above method is to automatically create and update statistics, now look at how to implement manually:

--Create statistics on the DueDate column of the Sales.SalesOrderHeader table create STATISTICS st_duedate_salesorderheader on Sales.SalesOrderHeader ( duedate) go--Update all statistics for the Sales.SalesOrderHeader table update STATISTICS Sales.salesorderheadergo-- Update St_duedate_salesorderheader statistics for sales.salesorderheader table update STATISTICS Sales.SalesOrderHeader St_duedate_ Salesorderheadergo--Update all statistics available in the database exec sys.sp_updatestatsgo--manually delete statistics drop STATISTICS Sales.SalesOrderHeader.st_ Duedate_salesorderheadergo

Analysis:

When an index is created, the optimizer creates statistics to the table or view on which the index column resides, and in addition, if the auto_create_statistics option is set on, the optimizer creates a single-column statistic. In time it does not appear on the desired column of the query. If you feel that some query performance is problematic, check all predicates, and if these columns are missing statistics, you can add them manually, and sometimesDTA(Database Tuning Advisor) also advises you to create statistics.

In general, before query compilation, if synchronization update statistics is turned on,SQL Server will cause update statistics to occur if statistics are found to be out of date, then your query will use real-time statistics. This action blocks the query, knows that the update is complete, but does not keep the queries, and it updates the statistics so that the next time you run the query, you can use the more recent statistics.

Expand your knowledge:

By default, only members of the three roles that are creators of the sysadmin/db_owner/ object have permission to create and update statistics.

Bar Chart:

A histogram is a class of tables generated by SQL Server for statistical information. Can be thought of as a report that displays the maximum and minimum range of statistics on the corresponding column.

12th Chapter--sqlserver Statistics (1)--Create and update statistics