12th Chapter--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, and when the value of the above formula is 1 o'clock, it can be used as a primary key or a unique key.

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. Manually create and update STATISTICS

2. automatic creation and update of statistical information

Preparatory work:

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

[SQL]View Plaincopy print?

2. SELECT case when databasepropertyex (' master ', ' isautocreatestatistics ') = 1
4. then ' Yes '
6. ELSE ' No '
8. END as ' Isautocreatestatistics? ',
10. Case when databasepropertyex (' Master ', ' isautoupdatestatistics ') = 1
12. then ' Yes '
14. ELSE ' No '
16. END as ' IsAutoUpdateStatistics? ',
18. Case when databasepropertyex (' Master ', ' is_auto_update_stats_async_on ') = 1
20. then ' Yes '
22. ELSE ' No '
24. END as ' Isautoupdatestatsaayncon? '
26. GO

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:

[SQL]View Plaincopy print?

2. SELECT object_id,
6. object_name (object_id) as TableName,
10. name as Statisticsname,
14. auto_created
18. From Sys.stats
22. --where object_id=object_id (' Sales.SalesOrderHeader ')
26. ORDER by object_id DESC
30. GO

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:

[SQL]View Plaincopy print?

1. Sp_helpstats ' Sales.SalesOrderHeader '

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:

[SQL]View Plaincopy print?

1. ALTER DATABASE AdventureWorks SET auto_create_statistics on

2 . Enable the option to synchronize the 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:

[SQL]View Plaincopy print?

2. SELECT St.name as Statname,
6. Col_name (stc.object_id, stc.column_id) as ColumnName,
10. object_name (st.object_id) as TableName
14. From Sys.stats as St
18. INNER JOIN sys.stats_columns as stc on st.object_id = stc.object_id
22. and st.stats_id = stc.stats_id
26. WHERE St.name like ' _wa% '

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 will 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:

[SQL]View Plaincopy print?

1. ALTER DATABASE AdventureWorks SET auto_update_statistics on

ALTER DATABASE AdventureWorks SET auto_update_statistics on

4. The Auto_update_statistics option will be used when creating an index, through auto_create_statistics, or using the create The statistics command automatically updates statistics when statistics are created manually, and the following command updates statistics asynchronously:

[SQL]View Plaincopy print?

1. ALTER DATABASE AdventureWorks SET auto_update_statistics_async on

ALTER DATABASE AdventureWorks SET auto_update_statistics_async on

5. At this point, look at the configuration of the database statistics after executing the above statement:

[SQL]View Plaincopy print?

1. SELECT is_auto_update_stats_async_on,
3. IS_AUTO_CREATE_STATS_ON,
5. is_auto_update_stats_on
7. From sys.databases
9. WHERE name = ' AdventureWorks '

SELECT  is_auto_update_stats_async_on,        is_auto_create_stats_on,        is_auto_update_stats_onfrom    Sys.databaseswhere   

6, the above methods are automatically created and updated statistics, now look at how to manually implement:

[SQL]View Plaincopy print?

2. --Create statistics on the DueDate column of the Sales.SalesOrderHeader table
6. CREATE STATISTICS st_duedate_salesorderheader on sales.salesorderheader (duedate)
10. GO
14. --Update all statistics for the Sales.SalesOrderHeader table
18. UPDATE STATISTICS Sales.SalesOrderHeader
22. GO
30. --Update St_duedate_salesorderheader statistics for Sales.SalesOrderHeader tables
34. UPDATE STATISTICS sales.salesorderheader st_duedate_salesorderheader
38. GO
46. --Update all available statistics in the database
50. EXEC Sys.sp_updatestats
54. GO
58. --Manual deletion of statistical information
62. DROP STATISTICS Sales.SalesOrderHeader.st_DueDate_SalesOrderHeader
66. GO

--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 is located, and in addition, if the auto_create_statistics option is set on , the optimizer creates a single-column statistic that does not appear on the desired column of the query in time. 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